Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Batch Update/insert in using SQLCommand in C#

Tags:

c#

asp.net

How I could achieve batch update/insert using SQLCommand. I wanted to create SQLCommand text dynamically in for loop of MyObject[] in C# with 10 SQLParameter

in case of bulk insert, i need to check for every record that it already exist or not. i.e.

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

begin

insert....

end

This is to be done from C#.No stored procedure in db

like image 215
MayureshP Avatar asked Jun 20 '11 09:06

MayureshP


4 Answers

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();
like image 126
Saurabh Avatar answered Oct 24 '22 11:10

Saurabh


SqlBulkCopy is rather handy for situations such as these.

like image 25
David Wick Avatar answered Oct 24 '22 13:10

David Wick


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

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

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

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

This would 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;
}
like image 22
FluepkeSchaeng Avatar answered Oct 24 '22 12:10

FluepkeSchaeng


That is my fast solution for testing fast 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();
        }
like image 31
Andrzej Pauli Avatar answered Oct 24 '22 11:10

Andrzej Pauli