[ACCEPTED]-Batch Update/insert in using SQLCommand in C#-asp.net

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();

0

Edited Warning: this answer, albeit partially 18 correct, does not address the issue asked, in 17 fact ExecuteNonQuery submits the workload to the database 16 (this can be proved by writing an incorrect 15 query: the exception is thrown on ExecuteNonQuery and not 14 on Commit).

Only to append all CommandTexts to one big batch 13 command is not as useful as it seems to 12 be.

The main benefit of prepared statements 11 in C# is, that the workload in the database 10 is done while creating the command. Not, when 9 you execute it [e.g. with ExecuteNonQuery() - which executes 8 the command only if you don't have a transaction 7 object created].

To avoid this and to create 6 the workload in the database only once for 5 all your statements, it's significant better 4 to create a Transaction object and to commit this transaction. Then 3 all commands will be executed without any 2 more workload in the database.

This would 1 be a better approach:

// 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;
}

SqlBulkCopy is rather handy for situations such as 1 these.

That is my fast solution for testing fast 1 batch-transaction inserting.

        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();
        }

you can send your values as comma separated 5 string into as stored procedure parameters 4 then split them using Split() sql function.

try 3 this

    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 2 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 paramters 1 using the same approach in Insert or Update