SQL BULK COPY

SQL BULK COPY

In below example, I am retrieving a data values from gridview to store in destination table.

You can modify the same with your own program logic / Requirement.

private DataTable GetGridData()

    {

      

        DataTable dt = new DataTable();

        dt.TableName = "TABLENAME";

        dt.Columns.Add("COL1", typeof(int));

        dt.Columns.Add("COL2", typeof(int));

        dt.Columns.Add("COL3", typeof(bool));

        foreach (GridDataItem item in grdAccess.MasterTableView.Items)

        {

            DataRow dr = dt.NewRow();

            string sCol1 = item["COL1"].Text;

            Int64 iCol1 = Convert.ToInt64(sCol1);

            string sCOL2 = item["COL2"].Text;

            Int64 iCOL2 = Convert.ToInt64(sCOL2);

            bAdd = true;

            dr["COL1"] = iCOL1;

            dr["COL2"] = iCOL2;

            dr["COL3"] = bAdd;

            dt.Rows.Add(dr);

            //}

        }

       

        return dt;

    }

Now call a function UpdateDataset with a parameter as Datatable.

public bool UpdateDataset( DataTable Ds)

        {

            try

            {

                //DataSet dsUp = new DataSet();

                SqlConnection con = new SqlConnection("Data Source=your connection string");

                con.Open();

                SqlBulkCopy sBCP = new SqlBulkCopy(con);

                sBCP.DestinationTableName = "TableName";

                sBCP.ColumnMappings.Add("COL1", "COL1");

                sBCP.ColumnMappings.Add("COL2", "COL2");

                sBCP.ColumnMappings.Add("COL3", "COL3");

                if (Ds.Rows.Count > 0)

                {

                    sBCP.WriteToServer(Ds);

                    return true;

                }

                else

                {

                    return false;

                }

                con.Close();

               

            }

            catch (Exception ex)

            {

                throw ex;

            }

        }