Multiple Records Insert into SQL Server Database

Multiple Records Insert

Step1: Created Table in Database

CREATE TABLE Person
(
PersonId INT PRIMARY KEY,
PersonName VARCHAR(100)
)

Step2: Created One StoredProcedure

CREATE PROCEDURE sp_BatchInsert ( @PersonId INT, @PersonName VARCHAR(100) ) 
AS 
BEGIN 
            INSERT INTO Person VALUES ( @PersonId, @PersonName); 

END

Step3: 
private void btnBatchInsert_Click(object sender, EventArgs e)
        {
            // Get the DataTable with Rows State as RowState.Added
            DataTable dtInsertRows = GetDataTable();
            SqlConnection connection = new SqlConnection(connectionString);
            SqlCommand command = new SqlCommand("sp_BatchInsert", connection);
            command.CommandType = CommandType.StoredProcedure;
            command.UpdatedRowSource = UpdateRowSource.None;

            // Set the Parameter with appropriate Source Column Name
            command.Parameters.Add("@PersonId", SqlDbType.Int, 4, dtInsertRows.Columns[0].ColumnName);          
            command.Parameters.Add("@PersonName", SqlDbType.VarChar, 100, dtInsertRows.Columns[1].ColumnName);
           
            SqlDataAdapter adpt = new SqlDataAdapter();
            adpt.InsertCommand = command;
            // Specify the number of records to be Inserted/Updated in one go. Default is 1.
            adpt.UpdateBatchSize = 2;          
            connection.Open();
            int recordsInserted = adpt.Update(dtInsertRows);          
            connection.Close();
            MessageBox.Show("Number of records affected : " + recordsInserted.ToString());

        }

Another Method

Use SQLBulkCopy of ADO.NET 2.0  

Valid for Environment: NET 2.0 or above on SQL Server 2005 database or above

With ADO.NET 2.0 we got the programming interface for Bulk Copy which provides quite simple and straight forward mechanism to transfer the data from one SQL server instance to another, from one table to another, from DataTable to SQL Server 2005 database, from DataReader to SQL Server 2005 database and many more.

SqlBulkCopy belongs to System.Data.SqlClient namespace and it is as simple as ADO.NET Command object when it comes to programming it. Let us see it working:

  private void btnSQLBulkCopyInsert_Click(object sender, EventArgs e)

        {

            // Get the DataTable 

            DataTable dtInsertRows = GetDataTable();

            

            using (SqlBulkCopy sbc = new SqlBulkCopy(connectionString))

            {

                sbc.DestinationTableName = "Person";

                

                // Number of records to be processed in one go

                sbc.BatchSize = 2;

                // Map the Source Column from DataTabel to the Destination Columns in SQL Server 2005 Person Table

                sbc.ColumnMappings.Add("PersonId", "PersonId");

                sbc.ColumnMappings.Add("PersonName", "PersonName");

                // Number of records after which client has to be notified about its status

                sbc.NotifyAfter = dtInsertRows.Rows.Count;

                // Event that gets fired when NotifyAfter number of records are processed.

                sbc.SqlRowsCopied+=new SqlRowsCopiedEventHandler(sbc_SqlRowsCopied);

                // Finally write to server

                sbc.WriteToServer(dtInsertRows);

                sbc.Close();

            }

The code above is very simple and quite self explanatory.

 Key Notes :

1.            BatchSize and NotifyAfter are two different properties. Former specify the number of records to be processed in one go while later specifies the number of records to be processed after which client needs to be notified.

 Reference:

1.            No better place than MSDN. Refer to http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx for details on SqlBulkCopy

2.            Refer http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy_properties.aspx to get details on all properties on SqlBulkCopy.