Insert dataset or datatable data to sql table using sql bulk copy in c#

This example shows you how to insert data from dataset or datatble to sql table using sql bulk copy in c#.

Dataset
sql bulk copy dataset

C# Code

protected void Page_Load(object sender, EventArgs e) { DataSet ds = GetData(); SqlConnection con = new SqlConnection(@"data source=.\sqlserver; user id=sa; password=123; database=Sample;"); SqlBulkCopy bulk = new SqlBulkCopy(con); bulk.DestinationTableName = "tblStudents"; foreach (DataColumn col in ds.Tables[0].Columns) bulk.ColumnMappings.Add(col.ColumnName, col.ColumnName); con.Open(); bulk.WriteToServer(ds.Tables[0]); con.Close(); } DataSet GetData() { DataTable dt = new DataTable(); dt.Columns.Add("StudentID", typeof(int)); dt.Columns.Add("StudentName", typeof(string)); dt.Columns.Add("RollNumber", typeof(int)); dt.Columns.Add("TotalMarks", typeof(int)); dt.Rows.Add(1, "Jame's", 101, 900); dt.Rows.Add(2, "Steave Smith", 105, 820); dt.Rows.Add(3, "Mark Waugh", 109, 850); dt.Rows.Add(4, "Steave Waugh", 110, 950); dt.Rows.Add(5, "Smith", 111, 910); dt.Rows.Add(6, "Williams", 115, 864); DataSet ds = new DataSet(); ds.Tables.Add(dt); return ds; }

SQL table before data inserted
SQL table before data inserted

SQL table after C# datatable/dataset data inserted using sql bulk copy
SQL table after C# datatable/dataset data inserted using sql bulk copy