how to insert the records using sqlBulkCopy(bulk Insert)

This Example will help you to insert the records in a datatbse using BulkCopy.
If you have huge amount of data to be entered at a time then this bulk copy concept is very easy and faster to insert the records...

Step 1: drag all the textboxes like Id,Name,Address etc. into your form.
Step 2: drag a button and gridview into your form..
Step 3:Dim _dtUserdtls As New DataTable 'public declaration
Step 4: in page load write the below code
'adding the columns dynamically
Step 5: write the below code in button click event.
Dim dr As DataRow
dr = _dtUserdtls.NewRow()
dr("Id") = txtId.Text.ToString()
dr("Name") = txtName.Text.ToString()
dr("Address") = txtAddress.Text.ToString()
dr("Salary") = txtSalary.Text.ToString()
dr("Email") = txtEmail.Text.ToString()
dr("DOB") = dtpDOB.Text
dr("State") = cmbStatus.Text
dr("Country") = cmbCountry.Text
DgvDisplay.DataSource = _dtUserdtls
Step 6 :
Dim bcp As New SqlBulkCopy(DataLayer.ConnectionString(), SqlBulkCopyOptions.UseInternalTransaction)
bcp.DestinationTableName = "tb_UserMaster" 'table name has to be insert
'all the below columns mapping should match with source column(datatable column name) and destination column(table column name)
bcp.ColumnMappings.Add("Id", "Id")
bcp.ColumnMappings.Add("Name", "Name")
bcp.ColumnMappings.Add("Address", "Address")
bcp.ColumnMappings.Add("Salary", "Salary")
bcp.ColumnMappings.Add("Email", "Email")
bcp.ColumnMappings.Add("DOB", "DOB")
bcp.ColumnMappings.Add("State", "State")
bcp.ColumnMappings.Add("Country", "Country")

Note:if you want to insert all the gridview rows into table then assign all the rows ivalues into datatable using datasource property and use this datatable for the above code
_dtUserdtls = Gridview1.datasource

hope this code will help you....
any doubts plz free to ask..

No comments:

Post a Comment