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

Question

Why returning dataset or data table from WCF service is not a good practice?
What are the alternatives ?

Answer 1

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

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 after C# datatable/dataset data inserted using sql bulk copy