Как передать переменные, такие как массивы / данные, на SQL-сервер?
Иногда вам нужно обновить базу данных, добавив в нее много строк, которые есть в таблице данных, или у вас есть массив, полный данных, вместо того, чтобы объединить все эти данные в строку и затем разбить ее в SQL SERVER, или вместо повторения таблицы данных в код построчно и обновляя базу данных, есть ли другой способ? Есть ли в SQL SERVER 2005 другие типы переменных помимо традиционных?
Ответы 3
Есть несколько способов сделать это.
Если вы просто вставляете строки, я бы создал объект DataTable с информацией в нем, а затем использовал бы объект SqlBulkCopy:
SqlBulkCopy copier = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.Default);
copier.BatchSize = 500; //# of rows to insert at a time
copier.DestinationTableName = "dbo.MyTable";
copier.WriteToServer(myDataTable);
Другой вариант - обернуть ваши данные в xml (однако вы хотите это сделать) и отправить их в свою хранимую процедуру (которая делает все, что вам нужно), используя тип данных sql 2005 'xml'.
Я согласен с Джоном в том, что SqlBulkCopy или sqlxml - лучшие варианты в SQL Server 2005; обратите внимание, что в SQL Server 2008 у вас также есть таблица значений параметров, который стоит рассмотреть; особенно при смешивании с новым объединить присоединиться.
Обратите внимание, что если вы используете SqlBulkCopy, я настоятельно рекомендую выполнять массовую вставку только в промежуточную таблицу, то есть в отдельную таблицу только для импорта; затем запустите хранимую процедуру, чтобы переместить данные в живую таблицу. Таким образом, вы получите надлежащее ведение журнала и сможете иметь транзакцию с узкой областью действия только в базе данных, пока вы запускаете SP (т.е. вам не нужна транзакция, охватывающая весь этот сетевой ввод-вывод).
Еще один момент; если вы имеете дело с большими объемами данных, возможно, вам не захочется загружать DataTable (поскольку это заставляет вас сначала буферизовать все данные в памяти); в качестве альтернативы также можно написать свой собственный IDataReader, который извлекает данные из потока (например, файла и т. д.); см. SimpleDataReaderздесь. SqlBulkCopy очень хорошо принимает данные от IDataReader.
Джон упомянул использование XML ... и этот подход я бы использовал для вашей ситуации (SQL Server 2005 и создание sproc, который обрабатывает SQL за вас).
Вот пример того, как начать работу (это просто оператор выбора, но вы можете сделать его обновлением, если хотите):
CREATE PROCEDURE MySproc ( @Accounts XML )
AS
SELECT
Accounts.AccountID.query('.')
FROM
@Accounts.nodes('//ID/text()') AS Accounts(AccountID)
GO
EXEC MySproc '<Accounts><ID>123</ID><ID>456</ID></Accounts>'