Insert a DataTable into Sql at once.

Entering Data into SQL Server one by one or row by row is quiet easy but entering whole DataTable or DataSet into SQLServer at once is also very easy.. Just copy below code into your insert button code block and you are done.

There are three mandatory things you must keep in mind. First of all Create SqlBulkCopy class object
1. Provide it connection to DataBase (Connection String).
2. Provide it Destination Table Name.
3. Provide column mapping (source to destination).

WriteToServer(DataTable) is the key method used to insert data in SQL Server.

[code]

DataTable newProducts = dt;

        // Create the SqlBulkCopy object. 

        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(DBConnectionManager.getConnection()))
        {
            bulkCopy.DestinationTableName = "dbo.Table1";

            //Column Mapping in Source and Destination Tables.

            bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("val1", "val1"));
            bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("val2", "val2"));

            try
            {
                // Write from the source to the destination.
                bulkCopy.WriteToServer(newProducts);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }