Copy data from DataTable to SQL Server Table

An example to copy data from DataTable to SQL Server Table is done by using c# code. Here the data from the datatable are inserted into the TestTable on button click.

Download the table schema and data by clicking the below link.

Download Schema and Data of Table

Aspx Code

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Bulk Copy data from DataTable (DataSet) to SQL Server Table using C#</title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <h3>Bulk Copy data from DataTable (DataSet) to SQL Server Table using C#</h3>
            <asp:GridView ID="gv_Students" runat="server" AutoGenerateColumns="false">
                <Columns>
                    <asp:TemplateField>
                        <ItemTemplate>
                            <asp:CheckBox ID="CheckBox1" runat="server" />
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:BoundField DataField="StudId" HeaderText="Id" ItemStyle-Width="30" />
                    <asp:BoundField DataField="StudName" HeaderText="Name" ItemStyle-Width="150" />
                    <asp:BoundField DataField="StudCity" HeaderText="City" ItemStyle-Width="150" />
                </Columns>
            </asp:GridView>
            <br />
            <asp:Button ID="btn_Click" Text="Bulk Insert" OnClick="Bulk_Insert" runat="server" />
        </div>
    </form>
</body>
</html>

 

Cs Code

DataTable dt = null;
       protected void Page_Load(object sender, EventArgs e)
       {
           if (!this.IsPostBack)
           {
               dt = GetData();
               gv_Students.DataSource = dt;
               gv_Students.DataBind();
           }
       }

       public static DataTable GetData()
       {
           DataTable dt = new DataTable();
           try
           {
               string constr = ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString;
               using (SqlConnection con = new SqlConnection(constr))
               {
                   con.Open();
                   SqlCommand cmd = new SqlCommand("SELECT StudId,StudName, StudCity FROM Students", con);
                   cmd.CommandType = CommandType.Text;
                   SqlDataAdapter da = new SqlDataAdapter(cmd);
                   da.Fill(dt);
                   con.Close();
               }
           }
           catch (Exception ex)
           {
               throw ex;
           }
           return dt;
       }

       protected void Bulk_Insert(object sender, EventArgs e)
       {
           dt = GetData();
           DataTable dtCopy = dt.Clone();

           foreach (GridViewRow row in gv_Students.Rows)
           {
               if ((row.FindControl("CheckBox1") as CheckBox).Checked)
               {
                   string id = row.Cells[1].Text;
                   string name = row.Cells[2].Text;
                   string city = row.Cells[3].Text;
                   dtCopy.Rows.Add(id, name, city);
               }
           }
           if (dtCopy.Rows.Count > 0)
           {
               string consString = ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString;
               using (SqlConnection con = new SqlConnection(consString))
               {
                   using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
                   {
                       //Set the database table name
                       sqlBulkCopy.DestinationTableName = "dbo.TestTable";

                       //[OPTIONAL]: Map the DataTable columns with that of the database table
                       sqlBulkCopy.ColumnMappings.Add("StudId", "SId");
                       sqlBulkCopy.ColumnMappings.Add("StudName", "StudName");
                       sqlBulkCopy.ColumnMappings.Add("StudCity", "StudCity");
                       con.Open();
                       sqlBulkCopy.WriteToServer(dtCopy);
                       con.Close();
                   }
               }
           }
       }

 

Output to Copy data from DataTable to SQL Server

 

Demo Code

Download Demo