|
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)
{
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);
rptCategories.DataSource = dtCategories;
rptCategories.DataBind();
}
protected void Button1_Click( object sender, EventArgs e)
{
string strConn = "Data Source=localhost;Initial Catalog= Northwind; user Id=sa;Password =test" ;
string sqlQuery = "select EmployeeId,LastName,FirstName from Employees" ;
SqlConnection conn = new SqlConnection (strConn);
SqlDataAdapter ada = new SqlDataAdapter (sqlQuery, conn);
SqlCommandBuilder cmd = new SqlCommandBuilder (ada);
DataSet dst = new DataSet ();
ada.Fill(dst);
DataRow _newRow = dst.Tables[0].NewRow();
_newRow[ "EmployeeId" ] = 43;
_newRow[ "FirstName" ] = "Johny" ;
_newRow[ "LastName" ] = "Terryr" ;
dst.Tables[0].Rows.Add(_newRow);
ada.Update(dst);
grdvEmployee.DataSource = dst.Tables[0].DefaultView;
grdvEmployee.DataBind();
}
protected void lnkUpdate_Click( object sender, EventArgs e)
{
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;
}
}
ada.UpdateBatchSize = 0;
int _numberOfUpdate = ada.Update(dtCategories);
lblResult.Text = _numberOfUpdate.ToString();
}
}
Output
|
Total Members
Delicious
Digg
Technorati
Blink
Furl
Reddit
Newsvine
Google
