Introduction:
Here I will explain how to insert multiple selected rows or records data from gridview into database in asp.net using sqlbulkcopy in c#, vb.net or insert selected gridview rows or records bulkily in database in asp.net using sqlbulkcop in c#, vb.net with example.
Description:
In previous articles I explained insert gridview data into database in asp.net, take database backup in sql server using query or management studio, import data from excel to database in asp.net, read xml file and bind data to gridview in asp.net, Delete multiple rows in gridview using checkbox in asp.net and many articles relating to gridview, asp.net, c#,vb.net and jQuery. Now I will explain how insert selected gridview rows data into database in asp.net using sqlbulkcopy in c#, vb.net.
In previous articles I explained insert gridview data into database in asp.net, take database backup in sql server using query or management studio, import data from excel to database in asp.net, read xml file and bind data to gridview in asp.net, Delete multiple rows in gridview using checkbox in asp.net and many articles relating to gridview, asp.net, c#,vb.net and jQuery. Now I will explain how insert selected gridview rows data into database in asp.net using sqlbulkcopy in c#, vb.net.
Before
we implement this example first design one table EmployeeInfo in your database as
shown below
 |
Now
create one new web application and open your aspx page and write the code like
as shown below
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1"
runat="server">
<title>Insert Selected
Gridview Rows Data into Database in Asp.net using SQLBulkCopy</title>
</head>
<body>
<form id="form1"
runat="server">
<div>
<asp:GridView ID="gvDetails"
runat="server"
AutoGenerateColumns="false">
<HeaderStyle BackColor="#df5015"
Font-Bold="true"
ForeColor="White"
/>
<Columns>
<asp:TemplateField>
<ItemTemplate>
<asp:CheckBox ID="chkSelect"
runat="server"
/>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="userid"
HeaderText="UserId"
/>
<asp:BoundField DataField="username"
HeaderText="UserName"
/>
<asp:BoundField DataField="firstname"
HeaderText="FirstName"
/>
<asp:BoundField DataField="lastname"
HeaderText="LastName"
/>
<asp:BoundField DataField="designation"
HeaderText="Designation"
/>
</Columns>
</asp:GridView>
</div><br />
<asp:Button ID="btnInsert"
runat="server"
Text="Insert
Gridview Data"
Font-Bold="true" onclick="btnInsert_Click"
/><br
/>
<asp:Label ID="lblMsg"
runat="server"/>
</form>
</body>
</html>
|
Now right click on your application Ã
Select Add New Item à Select XML file Ã
Give name as “sample.xml” and click
OK
<?xml version="1.0" encoding="utf-8" ?>
<users>
<user>
<userid>1</userid>
<FirstName>Suresh</FirstName>
<LastName>Dasari</LastName>
<UserName>SureshDasari</UserName>
<Designation>Team Leader</Designation>
</user>
<user>
<userid>2</userid>
<FirstName>Mahesh</FirstName>
<LastName>Dasari</LastName>
<UserName>MaheshDasari</UserName>
<Designation>Software Developer</Designation>
</user>
<user>
<userid>3</userid>
<FirstName>Madhav</FirstName>
<LastName>Yemineni</LastName>
<UserName>MadhavYemineni</UserName>
<Designation>Business Analyst</Designation>
</user>
</users>
|
After completion of xml file now open aspx page codebehind
behind file and add following namespaces
C#
Code
using System;
using System.Data;
using
System.Web.UI.WebControls;
using System.Data.SqlClient;
|
After completion of adding namespaces you need to write the
code like as shown below
DataTable dt = new DataTable();
protected void Page_Load(object
sender, EventArgs e)
{
if (!IsPostBack)
{
//Bind Data to Gridview
GetXMLData();
}
}
// This method is used to get xml node values and bind
to gridview
protected void GetXMLData()
{
DataSet ds = new DataSet();
ds.ReadXml(Server.MapPath("Sample.xml"));
dt = ds.Tables[0];
gvDetails.DataSource = dt;
gvDetails.DataBind();
}
// Insert data in database using SqlBulkCopy
protected void btnInsert_Click(object
sender, EventArgs e)
{
String
strConnection = "Data Source=Suresh;Initial
Catalog=MySampleDB;Integrated Security=True";
DataTable dt = new DataTable();
dt.Columns.Add("userid",
typeof(int));
dt.Columns.Add("username",
typeof(string));
dt.Columns.Add("firstname",
typeof(string));
dt.Columns.Add("lastname",
typeof(string));
dt.Columns.Add("designation",
typeof(string));
foreach (GridViewRow gvrow in
gvDetails.Rows)
{
//Find checkbox control in gridview for particular row
CheckBox chkSelect
= (CheckBox)gvrow.FindControl("chkSelect");
//Condition to check checkbox selected or not
if (chkSelect.Checked)
{
int userid = int.Parse(gvrow.Cells[1].Text);
string username =
gvrow.Cells[2].Text;
string firstname = gvrow.Cells[3].Text;
string lastname =
gvrow.Cells[4].Text;
string designation =
gvrow.Cells[5].Text;
dt.Rows.Add(userid, username, firstname, lastname, designation);
}
}
using (SqlConnection con = new
SqlConnection(strConnection))
{
con.Open();
SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
//Give your Destination table name
sqlBulk.DestinationTableName = "EmployeeInfo";
sqlBulk.WriteToServer(dt);
con.Close();
}
lblMsg.Text = "Details Inserted
Successfully";
lblMsg.ForeColor = System.Drawing.Color.Green;
}
|
VB.NET
Code
Imports System.Data
Imports
System.Web.UI.WebControls
Imports System.Data.SqlClient
Partial Class VBCode
Inherits System.Web.UI.Page
Private dt As New DataTable()
Protected Sub Page_Load(ByVal
sender As Object,
ByVal e As EventArgs) Handles
Me.Load
If Not
IsPostBack Then
'Bind Data to Gridview
GetXMLData()
End If
End Sub
' This method is used to get xml node values and bind
to gridview
Protected Sub GetXMLData()
Dim ds As New DataSet()
ds.ReadXml(Server.MapPath("Sample.xml"))
dt = ds.Tables(0)
gvDetails.DataSource = dt
gvDetails.DataBind()
End Sub
' Insert data in database using SqlBulkCopy
Protected Sub btnInsert_Click(ByVal
sender As Object,
ByVal e As EventArgs)
Dim strConnection As [String] = "Data Source=Suresh;Initial
Catalog=MySampleDB;Integrated Security=True"
Dim dt As New DataTable()
dt.Columns.Add("userid",
GetType(Integer))
dt.Columns.Add("username",
GetType(String))
dt.Columns.Add("firstname",
GetType(String))
dt.Columns.Add("lastname",
GetType(String))
dt.Columns.Add("designation",
GetType(String))
For Each gvrow As GridViewRow In
gvDetails.Rows
'Find checkbox control in gridview for particular row
Dim chkSelect As CheckBox = DirectCast(gvrow.FindControl("chkSelect"), CheckBox)
'Condition to check checkbox selected or not
If chkSelect.Checked Then
Dim userid As Integer = Integer.Parse(gvrow.Cells(1).Text)
Dim username As String =
gvrow.Cells(2).Text
Dim firstname As String =
gvrow.Cells(3).Text
Dim lastname As String =
gvrow.Cells(4).Text
Dim designation As String =
gvrow.Cells(5).Text
dt.Rows.Add(userid, username, firstname, lastname, designation)
End If
Next
Using con As New SqlConnection(strConnection)
con.Open()
Dim sqlBulk As New SqlBulkCopy(strConnection)
'Give your Destination table name
sqlBulk.DestinationTableName = "EmployeeInfo"
sqlBulk.WriteToServer(dt)
con.Close()
End Using
lblMsg.Text = "Details Inserted
Successfully"
lblMsg.ForeColor = System.Drawing.Color.Green
End Sub
End Class
|
Demo
 |
Download
Sample Code Attached
|
If you enjoyed this post, please support the blog below. It's FREE! Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email. |
|||
Subscribe by RSS
Subscribe by Email
|
|||
2 comments :
Like this article
Please Write Article On MVC..
Note: Only a member of this blog may post a comment.