Использование параметризованного запроса с OPENROWSET

У меня есть следующий код:

con.Open();

string exportQuery = "INSERT INTO OPENROWSET(@oledbType,@filepath,@query)";

string filepath = "'Excel 12.0;Database=C:\\Temp\\TestExcel.xlsx;";
string oledbType = "Microsoft.ACE.OLEDB.12.0";

string query = "SELECT * FROM categoryData";

SqlCommand cmd = new SqlCommand(exportQuery, con);
cmd.Parameters.AddWithValue("@filepath", filepath);
cmd.Parameters.AddWithValue("@oledbType", oledbType);
cmd.Parameters.AddWithValue("@query", query); 

cmd.ExecuteNonQuery();
con.Close();

Я получаю сообщение об ошибке:

System.Data.SqlClient.SqlException: «Неверный синтаксис рядом с @oledbType».

Поэтому я думаю, что синтаксис в

string exportQuery = "INSERT INTO OPENROWSET(@oledbType,@filepath,@query)";

это неверно. Любые идеи?

Мне удалось заставить это работать в SQL Server, используя более продвинутую версию предыдущего запроса.

Если файл существует, следующий код правильно экспортирует данные:

INSERT INTO OpenRowSet( 
'Microsoft.ACE.OLEDB.12.0' 
, 'Excel 12.0;Database=M:\TestExcel.xlsx;' 
, 'SELECT * FROM [Sheet1$]' 
) 
SELECT category as Category, SUM(ButtonClick) as Count FROM 
( SELECT COUNT(id) as id, category as category, locationName as location, sum(counter) as ButtonClick FROM categoryData AS t 
WHERE locationName IN ('all', 'loc1', 'loc2') 
AND date BETWEEN '2018-04-03' AND '2018-04-04'
GROUP BY locationName, category) 
AS SUBQUERY GROUP BY category ORDER BY Count ASC

При экспорте в С# я получаю сообщение об ошибке:

string exportQuery = @"INSERT INTO OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=M:\TestExcel.xlsx;' , 'SELECT * FROM [Sheet1$]') 
SELECT category as Category, SUM(ButtonClick) as Count FROM 
                    ( SELECT COUNT(id) as id, category as category, locationName as location, sum(counter) as ButtonClick FROM categoryData AS t 
WHERE locationName IN ('all', 'loc1', 'loc2') 
AND date BETWEEN '2018-04-03' AND '2018-04-04'
GROUP BY locationName, category) 
AS SUBQUERY GROUP BY category ORDER BY Count ASC";
SqlCommand cmd = new SqlCommand(exportQuery, con);
cmd.ExecuteNonQuery();

Поставщик OLE DB "Microsoft.ACE.OLEDB.12.0" для связанного сервера "(null)" сообщил об ошибке. Провайдер не дал никакой информации об ошибке. Не удается инициализировать объект источника данных поставщика OLE DB "Microsoft.ACE.OLEDB.12.0" для связанного сервера "(null)".'

Действительно раздражающая ошибка и ломаю голову над этим. :( :(


person David Folksman    schedule 13.04.2018    source источник
comment
Зачем использовать SQL Server только для того, чтобы написать файл Excel? Сделайте это прямо со стороны клиента.   -  person Alejandro    schedule 13.04.2018
comment
Зачем вам нужно параметризовать этот запрос? Все значения жестко закодированы, так чего вы пытаетесь достичь, делая их параметрами?   -  person Racil Hilan    schedule 13.04.2018
comment
К сожалению, здесь нельзя использовать параметры; см. stackoverflow.com/a/13831792/361842   -  person JohnLBevan    schedule 13.04.2018
comment
Также похоже, что вам нужно использовать opendatasource вместо openrowset: stackoverflow.com/a/29078719/361842   -  person JohnLBevan    schedule 13.04.2018
comment
1) Поскольку этот код является частью события щелчка, которое экспортирует данные из приложения С# после того, как оно изменило данные. 2) Поскольку ручное добавление строк в строку запроса затруднено с escape-последовательностями и т. д.   -  person David Folksman    schedule 13.04.2018


Ответы (1)


@Praveen заявляет, что вы не можете использовать выражения, поэтому вам нужно использовать динамический SQL: https://stackoverflow.com/a/13831792/361842< /а>

Согласно вышеизложенному, я считаю, что это то, что вам нужно:

string filepath = @"Excel 12.0;Database=C:\Temp\TestExcel.xlsx;";
string oledbType = "Microsoft.ACE.OLEDB.12.0";
string querySheet = "SELECT * FROM [Sheet1$]"; //amend to match the sheet name: https://stackoverflow.com/a/910199/361842
string query = "SELECT * FROM categoryData"; //this is the SQL to get the data from your SQL DB to send to your sheet.

string exportQuery = @"
declare @sql nvarchar(max) = '
    INSERT INTO OpenRowSet(
    ' + quotename(@oledbType,'''') + '
    , ' + quotename(@filepath,'''') + '
    , ' + quotename(@querySheet,'''') + '
    )' +
    @query + ';'
exec (@sql)
";

SqlCommand cmd = new SqlCommand(exportQuery, con);
cmd.Parameters.AddWithValue("@filepath", filepath);
cmd.Parameters.AddWithValue("@oledbType", oledbType);
cmd.Parameters.AddWithValue("@querySheet", querySheet); 
cmd.Parameters.AddWithValue("@query", query); 

cmd.ExecuteNonQuery();
con.Close();

Приведенное выше генерирует и выполняет динамический SQL:

INSERT INTO OpenRowSet(
    'Microsoft.ACE.OLEDB.12.0'
    , 'Excel 12.0;Database=C:\Temp\TestExcel.xlsx;'
    , 'SELECT * FROM [Sheet1$]'
)
SELECT * FROM categoryData;

(Это не функциональность, с которой я играл сам, поэтому не могу подтвердить, будет ли это работать; просто отключаю ответы в другом месте на этом сайте).


Обновить

Ранее я сказал

@Stephan заявляет, что для обновления данных вы должны использовать OpenDataSource вместо OpenRowSet: https://stackoverflow.com/a/29078719/361842

С тех пор я видел, что это не так. см. @AleksandrFedorenko в https://stackoverflow.com/a/13891752/361842. Таким образом, внесены изменения в вышеизложенное.

person JohnLBevan    schedule 13.04.2018
comment
Спасибо за ответ. Я полностью потерял все символы конкатенации и escape-последовательности и символы кавычек. Почему в начале строки стоит знак @? Почему перед конкатенацией стоит ' + извините за вопросы новичка. - person David Folksman; 13.04.2018
comment
Не беспокойся. @ превращает его в дословную литеральную строку. См. stackoverflow.com/a/1100265/361842. В приведенном выше случае это на самом деле не нужно; но я склонен использовать это при работе со сложными строками, чтобы мне не приходилось думать об escape-символах, когда мне не нужны выражения обратной косой черты. (например, такие вещи, как \r\n) - person JohnLBevan; 13.04.2018
comment
ну ок полезно. Как насчет ' + (код) + ' и (@variable,'''') - person David Folksman; 13.04.2018
comment
Кавычки и плюсы по существу объединяют следующие строки: 'INSERT INTO OpenRowSet(', quotename(@oledbType,''''), ', ', quotename(@filepath,''''), ', ', quotename(@query,''''), ');' - person JohnLBevan; 13.04.2018
comment
Это выглядит немного более запутанным, поскольку я поместил разрывы строк в соединенные строки; это облегчает чтение в SO (т. е. код не выходит за правую часть страницы) и упрощает чтение динамического SQL, если вы сделали print @sql для просмотра значения. - person JohnLBevan; 13.04.2018
comment
quotename(@...,'''') нужны для того, чтобы гарантировать, что если бы в параметрах были какие-либо апострофы (одинарные кавычки), они были бы экранированы перед объединением; т. е. поскольку при использовании динамического SQL мы подвергаем себя риску внедрения SQL-кода, несмотря на использование параметров при вызове этого кода; это снова защищает нас. - person JohnLBevan; 13.04.2018
comment
См. sqlfiddle.com/#!18/9eecb/12360 для примера объединения биты; т. е. это также показывает, как помогает цитата, добавляя кавычки вокруг значений параметров и экранируя любые кавычки: sqlfiddle.com/#!18/9eecb/12360 - person JohnLBevan; 13.04.2018
comment
теперь я получаю новую ошибку: "System.Data.SqlClient.SqlException: 'Incorrect syntax near ';'.'" Может быть, мои строки тоже неправильно отформатированы? "string filepath = @"'Excel 12.0;Database=C:\Temp\TestExcel.xlsx;";" "string oledbType = "Microsoft.ACE.OLEDB.12.0";" "string query = "SELECT * FROM categoryData"; - person David Folksman; 13.04.2018
comment
Попробуйте: string filepath = @"Excel 12.0;Database=C:\Temp\TestExcel.xlsx;" - person JohnLBevan; 13.04.2018
comment
Все та же ошибка: "Incorrect syntax near ';'." :( - person David Folksman; 13.04.2018
comment
Давайте продолжим обсуждение в чате. - person JohnLBevan; 13.04.2018