How to insert into SQL Server database in a batch?

Question

We execute SQL Server stored procedure mySP to insert into the database (say it will call the stored procedure about 300,000 times).

ALTER PROCEDURE [dbo].[mySP]
    @ThePath VARCHAR(255),
    @TheID INT
AS
    INSERT INTO myTbl (TheTime, ThePath, TheID)
    VALUES (GETDATE(), @ThePath, @TheID)

This is the code to execute stored procedure mySP:

using (SqlConnection con = new SqlConnection(connectionString))
{
:
     foreach (.....)  //about 300,000 times
     {
         SqlCommand MyCmd = new SqlCommand("mySP", con);
         MyCmd.CommandType = CommandType.StoredProcedure;
         MyCmd.Parameters.Add(new SqlParameter("ThePath", sMyPath));
         MyCmd.Parameters.Add(new SqlParameter("TheID", sMyID));

         iRow = MyCmd.ExecuteNonQuery();

         MyCmd.Dispose();
         MyCmd = null;
     }
}

How can I do this in a batch of say 5000, then commit transaction ?

Thank you

Answer 1

Create type in SQL Server and pass table as parameter. Please look below

How to pass table value parameters to stored procedure from .net code

https://www.c-sharpcorner.com/UploadFile/ff2f08/table-value-parameter-use-with-C-Sharp/

Once, you pass the table variable to the stored procedure, you can insert data in one insert select statement.

Answer 2

When inserting thousands of rows at a time you are better off using something like .Net's own SqlBulkCopy, which basically means you have to populate a DataTable first and then use SqlBulkCopy to write it to the database. See https://sqlbulkcopy-tutorial.net/ for good examples. A little advice is to toy around with the BatchSize property; generally leaving it as-is is good enough. Don't forget to use a transaction as well.

Example:

using (SqlConnection connection = new SqlConnection("server=local);database=MyDatabase;integrated security=SSPI"))
{
    connection.Open();

    DirectoryInfo directory = new DirectoryInfo(@"C:\Temp\");

    var files = directory.GetFiles("*.dat");

    foreach (var file in files)
    {
        if (file.Length > 0)
        {
            string fileName = Path.GetFileNameWithoutExtension(file.FullName);

            SqlCommand cmd = new SqlCommand("dbo.uspStagingGetTableStructure", connection);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@tableName", fileName);

            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                }
            }

            //
            // Prepare Bulkcopy target (datatable)
            //

            string target = "MyBulkCopyTarget";

            DataTable dataTable = new DataTable(target);
            SqlBulkCopy bulkcopy = new SqlBulkCopy(connection);
            bulkcopy.DestinationTableName = target;
            bulkcopy.BulkCopyTimeout = 600;

            //
            // Map columns source -> target
            //

            //foreach (var dataField in dataFields)
            //{
            //    if (dataField.sqlDbType == SqlDbType.Int)
            //        dataTable.Columns.Add(dataField.fieldName, typeof(int));
            //    else
            //        dataTable.Columns.Add(dataField.fieldName, typeof(string));
            //    bulkcopy.ColumnMappings.Add(dataField.fieldName, dataField.fieldName);
            //}

            //
            // Populate Bulkcopy target (datatable)
            //

            string line = string.Empty;
            using (StreamReader reader = file.OpenText())
            {
                while ((line = reader.ReadLine()) != null)
                {
                    DataRow row = dataTable.NewRow();

                    //
                    // Use proper data types
                    //

                    //foreach (var dataField in dataFields)
                    //{
                    //    if (dataField.sqlDbType == SqlDbType.Int)
                    //        row[dataField.fieldName] = Convert.ToInt32(line.Substring(dataField.fieldStartPos, dataField.fieldLength));
                    //    else
                    //        row[dataField.fieldName] = line.Substring(dataField.fieldStartPos, dataField.fieldLength);
                    //}

                    dataTable.Rows.Add(row);
                }
            }

            //
            // Push datatable to server
            //

            bulkcopy.WriteToServer(dataTable);
        }
    }
}
}
}