Batch update / insert using SQLCommand in C #

Question

How could I execute the update/insert package using SQLCommand . I would like to create SQLCommand text dynamically in a for MyObject[] loop in C # with 10 SQLParameter

in the case of mass insert , I need to check for each record that it already exists or not. i.e.

if not Exists(select pkid from table1 where fkid1=@fkid1 and fkid2=@fkid1 )

begin

 insert.... 

end

This should be made from C #. No stored procedure in db

Answer 1

 SqlCommand command = new SqlCommand(); // Set connection, etc. for(int i=0; i< items.length; i++) { command.CommandText += string.Format("update mytable set s_id=@s _id{0} where id = @id{0};", i); command.Parameters.Add("@s_id" + i, items[i].SId); command.Parameters.Add("@id" + i, items[i].Id); } command.ExecuteNonQuery(); 

Answer 2

Just adding all CommandTexts to one large command command is not as useful as it seems.

The main advantage of prepared statements in C # is that the workload in the database is executed when the team is created. No, when you execute it (for example, with ExecuteNonQuery() -, which executes a command only if you do not have a transaction object].

To avoid this and create a workload in the database only once for all your operators, it is much better to create a Transaction object and complete the transaction. Then all the commands will be executed without additional load in the database.

This would be a better application:

 // Try to create the Command as early as possible with a valid Connection object string commandString = "UPDATE Mytable SET s_id=@s _id where id = @id;"; var command = new SqlCommand(commandString, connection); // Then define a Transaction object with your Connection var transaction = connection.BeginTransaction(); command.Transaction = transaction; // Now iterate through your array for(int i=0; i<array.Length; i++) { command.Parameters.Add("@s_id", SqlDbType.YourType).Value = items[i].SId; command.Parameters.Add("@id", SqlDbType.YourType).Value = items[i].Id; command.ExecuteNonQuery(); // Not executed at this point } // And now execute it with the possibility to rollback all commands when it fails try { transaction.Commit(); } // Here the execution is committed to the DB catch (Exception) { transaction.Rollback(); throw; } 

Answer 3

SqlBulkCopy is quite convenient for such situations.

Answer 4

you can send your values ​​as comma-separated strings as parameters of a stored procedure, and then split them using the Split() sql function.

try it

  StringBuilder Par1 = new StringBuilder(""); StringBuilder Par2 = new StringBuilder(""); for (var i = 0; i < MyObject.Length; i++) { if (i > 0) { Par1.Append(","); Par2.Append(","); } Par1.Append(MyObject[i].Prop1); Par2.Append(MyObject[i].Prop2); } myCommand.Parameters.Add(new SqlParameter("@Par1", Par1.ToString())); myCommand.Parameters.Add(new SqlParameter("@Par2", Par2.ToString())); myCommand.CommandText = "MyStoredProcedure"; myCommand.CommandType = System.Data.CommandType.StoredProcedure; myCommand.ExecuteNonQuery(); 

your stored procedure will look like this:

 CREATE Procedure MyStoredProcedure ( @Par1 as varchar(max), @Par2 as varchar(Max) ) AS create table #AllValues ( value1 varchar(50), value2 varchar(50) ); create table #Par1s ( id integer, data varchar(50) ); create table #Par2s ( id integer, data varchar(50) ); insert into #Par1s select * from dbo.Split (@Par1,',') insert into #Par2s select * from dbo.Split (@Par2,',') Insert into #AllValues(value1,value2) Select #Par1s.data,#Par2s.data From #Par1s Inner Join #Par2s On #Par1s.ID = #Par2s.ID Insert into myTable(Col1,Col2) Select value1,value2 from #AllValues 

you can pass any number of parameters using the same approach in Insert or Update

Answer 5

This is my quick solution to quickly insert batch transactions.

  using (var conn = new SqlConnection(GetConnectionStringFromSecret(args))) { conn.Open(); stopwatch.Start(); long counter = 0; var tran = conn.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted); SqlCommand cmd = new SqlCommand("", conn, tran); cmd.CommandType = System.Data.CommandType.Text; int batch_param_counter = 0; foreach (var chars_table in table_of_table_of_chars) { var key = string.Concat(chars_table);//get 1st param var hash = BitConverter.ToString(hasher.ComputeHash(Encoding.UTF8.GetBytes(key))).Replace("-", "").ToLowerInvariant();//get 2nd param cmd.CommandText += $"insert into hash_table([key], hash) values(@key{batch_param_counter}, @hash{batch_param_counter});{Environment.NewLine}"; var param_key = new SqlParameter("@key" + batch_param_counter, System.Data.SqlDbType.VarChar, 20); param_key.Value = key; cmd.Parameters.Add(param_key); var hash_key = new SqlParameter("@hash" + batch_param_counter, System.Data.SqlDbType.VarChar, 32); hash_key.Value = hash; cmd.Parameters.Add(hash_key); batch_param_counter++; if (counter % 200 == 0) { cmd.Prepare(); cmd.ExecuteNonQuery(); cmd.Dispose(); cmd = new SqlCommand("", conn, tran); cmd.CommandType = System.Data.CommandType.Text; batch_param_counter = 0; } if (counter % 20000 == 0) { if (cmd != null && !string.IsNullOrEmpty(cmd.CommandText)) { cmd.Prepare(); cmd.ExecuteNonQuery(); cmd.Dispose(); cmd = new SqlCommand("", conn, tran); cmd.CommandType = System.Data.CommandType.Text; batch_param_counter = 0; } tran.Commit(); tran = null; if (Console.KeyAvailable) break; cmd.Transaction = tran = conn.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted); } counter++; } if (cmd != null && !string.IsNullOrEmpty(cmd.CommandText)) { cmd.Prepare(); cmd.ExecuteNonQuery(); cmd.Dispose(); } if (tran != null) tran.Commit(); stopwatch.Stop(); }