Как я должен несколько вставлять несколько записей?
Мне назвали класс Entry заявленный как это:
class Entry{
string Id {get;set;}
string Name {get;set;}
}
и затем метод, который примет несколько такой Entry объекты для вставки в базу данных с помощью ADO.NET:
static void InsertEntries(IEnumerable<Entry> entries){
//build a SqlCommand object
using(SqlCommand cmd = new SqlCommand()){
...
const string refcmdText = "INSERT INTO Entries (id, name) VALUES (@id{0},@name{0});";
int count = 0;
string query = string.Empty;
//build a large query
foreach(var entry in entries){
query += string.Format(refcmdText, count);
cmd.Parameters.AddWithValue(string.Format("@id{0}",count), entry.Id);
cmd.Parameters.AddWithValue(string.Format("@name{0}",count), entry.Name);
count++;
}
cmd.CommandText=query;
//and then execute the command
...
}
}
И мой вопрос - это: если я продолжаю использовать вышеупомянутый способ отправить, несколько вставляют операторы (создайте гигантскую строку операторов вставки и их параметров и отправьте ее по сети), или если я сохраняю открытое соединение и отправляю единственный оператор вставки за каждым Entry как это:
using(SqlCommand cmd = new SqlCommand(){
using(SqlConnection conn = new SqlConnection(){
//assign connection string and open connection
...
cmd.Connection = conn;
foreach(var entry in entries){
cmd.CommandText= "INSERT INTO Entries (id, name) VALUES (@id,@name);";
cmd.Parameters.AddWithValue("@id", entry.Id);
cmd.Parameters.AddWithValue("@name", entry.Name);
cmd.ExecuteNonQuery();
}
}
}
Что Вы думаете? Будет различие в производительности в SQL-сервере между двумя? Есть ли какие-либо другие последствия, о которых я должен знать?
4 ответа
На вашем месте я бы не стал использовать ни один из них.
Недостатком первого является то, что имена параметров могут конфликтовать, если в списке есть одинаковые значения.
Недостатком второго является то, что вы создаете команду и параметры для каждой сущности.
Лучший способ - создать текст команды и параметры один раз (используйте Parameters.Add для добавления параметров), измените свои значения в цикле и выполните команду. Таким образом, выписка будет подготовлена только один раз. Вы также должны открыть соединение перед запуском цикла и закрыть его после него.
Вы должны выполнить команду на каждом цикле вместо того, чтобы строить огромную команду Text(btw,StringBuilder сделан для этого) Базовое соединение не будет закрываться и повторно открываться для каждого цикла,пусть диспетчер пула соединений справится с этим. Взгляните на эту ссылку для получения дополнительной информации: Настройка пула подключений ADO.NET в приложениях ASP.NET
Если вы хотите убедиться, что каждая команда успешно выполняется, вы можете использовать Транзакция и откат при необходимости,
Когда записей много, используйте SqlBulkCopy. Производительность намного выше, чем при серии одиночных вставок.
static void InsertSettings(IEnumerable<Entry> settings) {
using (SqlConnection oConnection = new SqlConnection("Data Source=(local);Initial Catalog=Wip;Integrated Security=True")) {
oConnection.Open();
using (SqlTransaction oTransaction = oConnection.BeginTransaction()) {
using (SqlCommand oCommand = oConnection.CreateCommand()) {
oCommand.Transaction = oTransaction;
oCommand.CommandType = CommandType.Text;
oCommand.CommandText = "INSERT INTO [Setting] ([Key], [Value]) VALUES (@key, @value);";
oCommand.Parameters.Add(new SqlParameter("@key", SqlDbType.NChar));
oCommand.Parameters.Add(new SqlParameter("@value", SqlDbType.NChar));
try {
foreach (var oSetting in settings) {
oCommand.Parameters[0].Value = oSetting.Key;
oCommand.Parameters[1].Value = oSetting.Value;
if (oCommand.ExecuteNonQuery() != 1) {
//'handled as needed,
//' but this snippet will throw an exception to force a rollback
throw new InvalidProgramException();
}
}
oTransaction.Commit();
} catch (Exception) {
oTransaction.Rollback();
throw;
}
}
}
}
}