|
SqlCommandBuilder object is used to generate the Insert, Update and Delete SQL Statement automatically. The generation of single table SQL statements can be accomplished by using the System.Data.SqlClient.SqlCommandBuilder object. This object uses the Select Command object's Command Text to generate the appropriate SQL Statements that are missing from the SqlDataAdapter.
There are a couple of requirements for using this object.
- The SelectCommand of the SqlDataAdapter must be using valid Select single table SQL Statement. Example: “Select * from customers”
- The SelectCommand must also return at least one primary key or unique column. If none are present, an InvalidOperation exception is generated, and the commands are not generated.
Example: Demonstrate Automatically Generation of SQL Statement
AutomaticgenerationofSQL.aspx
<%@Page Language ="C#" AutoEventWireup ="true" CodeFile ="AutomaticgenerationofSQL.aspx.cs" Inherits ="AutomaticgenerationofSQL" %>
<! 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><span style ="text-decoration: underline"> Automatically Generation of SQL Statement </span></strong><br/>
<br/>
<br/>
<asp:TextBox ID ="txtInsert" runat ="server" Height ="139px" TextMode ="MultiLine" Width ="515px"></asp: TextBox>
<asp:Button ID ="btnInsert" runat ="server" OnClick ="Button1_Click" Text ="Generate Insert Query" /><br/>
<br/>
<br/>
<asp:TextBox ID ="txtUpdate" runat ="server" Height ="110px" TextMode ="MultiLine" Width ="516px"></asp:TextBox>
<asp:Button ID ="btnUpdate" runat ="server" OnClick ="btnUpdate_Click" Text ="Generate Update Query" /><br/>
<br/>
<asp:TextBox ID ="txtDelete" runat ="server" Height ="160px" TextMode ="MultiLine" Width ="516px"></asp:TextBox>
<asp:Button ID ="btnDelete" runat ="server" OnClick ="btnDelete_Click" Text ="Generate Delete Query" /></div>
</form>
</body>
</html>
AutomaticgenerationofSQL.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 AutomaticgenerationofSQL : System.Web.UI. Page
{
protected void Button1_Click( object sender, EventArgs e)
{
string strConn = "Data Source=localhost;Initial Catalog= Northwind; user Id=sa;Password = test " ;
string sqlQuery = "select * from Employees" ;
SqlConnection conn = new SqlConnection (strConn);
SqlDataAdapter adaInsert = new SqlDataAdapter (sqlQuery,conn);
SqlCommandBuilder cbInsert;
cbInsert = new SqlCommandBuilder ();
cbInsert.DataAdapter = adaInsert;
txtInsert.Text = cbInsert.GetInsertCommand().CommandText;
}
protected void btnUpdate_Click( object sender, EventArgs e)
{
string strConn = "Data Source=localhost;Initial Catalog= Northwind; user Id=sa;Password = test " ;
string sqlQuery = "select * from Categories" ;
SqlConnection conn = new SqlConnection (strConn);
SqlDataAdapter adaUpdate = new SqlDataAdapter (sqlQuery, conn);
SqlCommandBuilder cbUpdate = new SqlCommandBuilder (adaUpdate);
txtUpdate.Text = cbUpdate.GetUpdateCommand().CommandText;
}
protected void btnDelete_Click( object sender, EventArgs e)
{
string strConn = "Data Source=localhost;Initial Catalog= Northwind; user Id=sa;Password = test " ;
string sqlQuery = "select * from Customers" ;
SqlConnection conn = new SqlConnection (strConn);
SqlDataAdapter adaDelete = new SqlDataAdapter (sqlQuery, conn);
SqlCommandBuilder cbDelete = new SqlCommandBuilder (adaDelete);
txtDelete.Text = cbDelete.GetDeleteCommand().CommandText;
}
}
Output
|
Total Members
Delicious
Digg
Technorati
Blink
Furl
Reddit
Newsvine
Google
