Performing Batch Updates: Using SqlCommandBuilder
Previous:
Data Adapter and Database Connection
SqlCommandBuilder is a useful implementation of DbCommandBuilder that can help you generate update, insert and delete queries for a table given a select query for that same table. DbCommandBuilder is used by Visual Studio to implement a number of rapid application development features via much of the drag and drop functionality and wizards involving Table Adapters, etc. However, SqlCommandBuilder is a standalone class and can be used in your applications to create queries with almost no effort. SqlCommandBuilder is the SQL Server specific version of DbCommandBuilder for interfacing with SQL Server.
Example: Demonstrate SqlCommandBuilder and SqlDataAdapter
CommandBuilder.aspx
<% @ Page Language ="C#" AutoEventWireup ="true" CodeFile ="CommandBuilder.aspx.cs" Inherits ="CommandBuilder" %>
<! DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns ="http://www.w3.org/1999/xhtml" >
<head runat ="server">
</head >
<body>
<form id ="form1" runat ="server">
< div><strong</strong><br/><br/><br/>
<table style ="width:368px"> <tr><td></td><td><strong>SqlCommandBuilder</strong></td><td></td></tr>
<tr><td></td><td><asp:Button ID ="Button1" runat ="server" OnClick ="Button1_Click" Text ="Inserting Record Using CommandBuilder" Width ="254px" /></td><td></td></tr>
<tr><td></td><td>
<asp:GridView ID ="grdvEmployee" runat ="server" AutoGenerateColumns ="False" BackColor ="LightGoldenrodYellow" BorderColor ="Tan" BorderWidth ="1px" CellPadding ="2" ForeColor ="Black" GridLines ="None">< FooterStyle BackColor ="Tan" />
<Columns>
<asp:BoundField DataField ="EmployeeId" HeaderText ="EmployeeId" />
< asp : BoundField DataField ="FirstName" HeaderText ="First Name" />
< asp : BoundField DataField ="LastName" HeaderText ="Last Name" />
</Columns>
<SelectedRowStyle BackColor ="DarkSlateBlue" ForeColor ="GhostWhite" />
<PagerStyle BackColor ="PaleGoldenrod" ForeColor ="DarkSlateBlue" HorizontalAlign ="Center" />
<HeaderStyle BackColor ="Tan" Font-Bold ="True" />
<AlternatingRowStyle BackColor ="PaleGoldenrod" />
</asp:GridView></ td >< td ></ td ></ tr >
<tr> <td> </td>< td >
<asp:Repeater ID ="rptCategories" EnableViewState ="false" runat ="server">< HeaderTemplate > <table> <tr><th> CategoryName </th><th> Description </ th ></tr>
</HeaderTemplate> <ItemTemplate>
<tr><td>
<asp:TextBox ID ="txtCategoryName" Text =' <% #Eval("CategoryName") %>' runat="server" />
</ td >< td >
<asp:TextBox ID ="txtDescription" Text =' <% #Eval("Description") %> ' runat ="server"/></ td ></ tr > </ ItemTemplate > < FooterTemplate > </ table >
</FooterTemplate>
</asp:Repeater><br/></td><td></td></tr><tr >< td style ="height: 21px"></ td >< td style ="height: 21px">
<asp:LinkButton ID ="lnkUpdate" runat ="server" OnClick ="lnkUpdate_Click"> Update Categories </asp:LinkButton >
<asp:Label ID ="lblResult" runat ="server"></asp:Label></ td >< td style ="height: 21px"></td> </tr>
</table></div> </form>
</body>
</html>
CommandBuilder.aspx.cs
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
public partial class CommandBuilder : System.Web.UI. Page
{
SqlDataAdapter ada;
DataTable dtCategories = new DataTable ();
protected void Page_Load( object sender, EventArgs e)
{ //Bind Repeater On page Load
string strConn = "Data Source=localhost;Initial Catalog= Northwind; user Id=sa;Password =test" ;
string sqlQuery = "select CategoryID,CategoryName,Description from Categories" ;
SqlConnection conn = new SqlConnection (strConn);
ada = new SqlDataAdapter (sqlQuery, conn);
SqlCommandBuilder cmdBuilder = new SqlCommandBuilder (ada);
ada.Fill(dtCategories);
//Bind Data To Repeater Control
rptCategories.DataSource = dtCategories;
rptCategories.DataBind();
}
protected void Button1_Click( object sender, EventArgs e)
{ //Inserting Record Using SqlCommandBuilder
string strConn = "Data Source=localhost;Initial Catalog= Northwind; user Id=sa;Password =test" ;
string sqlQuery = "select EmployeeId,LastName,FirstName from Employees" ;
//Create Connection Object
SqlConnection conn = new SqlConnection (strConn);
//Create Data Adapter Instance
SqlDataAdapter ada = new SqlDataAdapter (sqlQuery, conn);
//Create SqlCommandBuilder Object.
SqlCommandBuilder cmd = new SqlCommandBuilder (ada);
//Create DataSet Instance
DataSet dst = new DataSet ();
ada.Fill(dst);
DataRow _newRow = dst.Tables[0].NewRow();
//Inserting Record in the DataBase
_newRow[ "EmployeeId" ] = 43;
_newRow[ "FirstName" ] = "Johny" ;
_newRow[ "LastName" ] = "Terryr" ;
dst.Tables[0].Rows.Add(_newRow);
ada.Update(dst);
//Displaying Data in the GridView
grdvEmployee.DataSource = dst.Tables[0].DefaultView;
grdvEmployee.DataBind();
}
protected void lnkUpdate_Click( object sender, EventArgs e)
{ //On Click Event
//Update DataTable with Changes
for ( int i = 0; i <= rptCategories.Items.Count - 1; i++) {
RepeaterItem rptItem = rptCategories.Items[i];
TextBox txtCategoryName = ( TextBox )(rptItem.FindControl( "txtCategoryName" ));
TextBox txtDescription =( TextBox )(rptItem.FindControl( "txtDescription" ));
if (dtCategories.Rows[i][ "CategoryName" ].ToString() != txtCategoryName.Text)
{
dtCategories.Rows[i][ "CategoryName" ] =txtCategoryName.Text;
}
if (dtCategories.Rows[i][ "Description" ].ToString() != txtDescription.Text)
{
dtCategories.Rows[i][ "Description" ]=txtDescription.Text;
}
}
// Set Batch Size to maximum Size
ada.UpdateBatchSize = 0;
int _numberOfUpdate = ada.Update(dtCategories);
lblResult.Text = _numberOfUpdate.ToString();
}
}
Output
Aspnet Discussion
- - Any idea??
- - How to Encode-Decode URL
- - Change IE settings using
- - Excel problem
- - Example of Server.Transfe
