Anil Diggiwal - Software Developer Jaipur India: Difference between SQL Command and SQL Command Builder

Unknown

ADO.net - Difference between SQL Command and SQL Command Builder

a) SQLCommand is used to execute all kind of SQL queries like DML(Insert update Delete) & DDL like(Create table drop table etc)

b)SQLCommandBuilder object is used to build & execute SQL (DML) queries like select insert update & delete.

remove.aspx

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<html>
<head>
<title>Removing a Row of Data in a DataSet</title>
<link rel="stylesheet" href="/aspxtreme/shared/netdemos.css">

<style type="text/css">
#zap {
   background:rgb ( 224,224,232 ); border:thin inset}
</style>

<script language="C#" runat="server">
DataSet myDataSet;
DataTable myTable;
DataRow thisRecord;
SqlDataAdapter adapter;
String id, msgDate, msgFrom, msgEmail, msgSubject, msgBody;

protected void Page_Load ( object src, EventArgs e ) {
   SqlConnection myConn = new SqlConnection ( 
      ConfigurationSettings.AppSettings [ "aspa" ] );
   adapter = new SqlDataAdapter ( 
      "SELECT * FROM teacher.aspx_messages order by MessageDate desc", myConn );
   myDataSet = new DataSet ( );
   adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
   adapter.Fill ( myDataSet, "Messages" );
   SqlCommandBuilder cmdBuilder = new SqlCommandBuilder ( adapter );
   myTable = myDataSet.Tables [ "Messages" ];
   if ( Request.QueryString [ "id" ] ==null || IsPostBack ) {
      if ( Request.QueryString [ "id" ] ==null ) BindGrid ( );
      loadPanel.Visible = true;
      zapPanel.Visible = false;
   }
   else {
      int id = Int32.Parse ( Request.QueryString [ "id" ] );
      thisRecord = myTable.Rows.Find ( id );
      getMessage ( );
      loadPanel.Visible = false;
      zapPanel.Visible = true;
   }
}

public void BindGrid ( ) {
   myGrid.DataSource = myTable.DefaultView;
   myGrid.DataBind ( );
}

public void getMessage ( ) {
   msgDate = thisRecord [ "MessageDate" ].ToString ( );
   msgFrom = thisRecord [ "MessageFrom" ].ToString ( );
   msgEmail = thisRecord [ "Email" ].ToString ( );
   msgSubject = thisRecord [ "MessageSubject" ].ToString ( );
   msgBody = thisRecord [ "MessageBody" ].ToString ( );
   zapPanel.DataBind ( );
}

public void zapMessage ( object src, EventArgs e ) {
   int id = Int32.Parse ( Request.QueryString [ "id" ] );
   thisRecord = myTable.Rows.Find ( id );
   thisRecord.Delete ( );
   adapter.Update ( myTable );
   BindGrid ( );
}
</script>
</head>

<body>
<!-- #include virtual="~/shared/top.inc" -->

<div class="header"><h3>ADO.NET Primer: <span class="hilite">Removing a Row of Data in a DataSet</span></h3></div>

<hr size=1 width=90%>

<center>

<asp:panel id="loadPanel" runat="server">
   <h5>Select a Record to Delete</h5>

   <asp:datagrid id="myGrid" runat="server" 
      width=90% cellpadding=5 gridlines="vertical"
      bordercolor="black" borderwidth=1
      font-size="8pt"
      headerstyle-backcolor="lightsteelblue"
      headerstyle-font-bold
      headerstyle-height=25
      alternatingitemstyle-backcolor="whitesmoke"
      autogeneratecolumns=false>

      <columns>
         <asp:boundcolumn headertext="Date" 
            datafield="MessageDate" 
            dataformatstring="{0:d}" />
         <asp:hyperlinkcolumn
            headertext="From"
            datanavigateurlfield="MessageID"
            datanavigateurlformatstring="remove.aspx?id={0}"
            datatextfield="MessageFrom" />
         <asp:boundcolumn headertext="Subject" 
            datafield="MessageSubject" />
      </columns>
   </asp:datagrid>

   <p align="center"><a href="#top"><img alt="Back to top" border=0 height=32 onmouseover="status='Back to top of page'" src="/aspxtreme/shared/top.gif" width=32></a></p>

</asp:panel>

<asp:panel id="zapPanel" runat="server">
   <h5>To remove this record, press Delete.</h5>

   <form runat="server">

   <table id="zap" width="85%" cellspacing=1 cellpadding=5 border=0>
   <col width=35% align="right">
   <tr>
      <td>Date:</td>
      <td><b><%# msgDate %></b></td></tr>
   <tr>
      <td>Name:</td>
      <td><b><%# msgFrom %></b></td></tr>
   <tr>
      <td>Email:</td>
      <td><b><%# msgEmail %></b></td></tr>
   <tr>
      <td>Subject:</td>
      <td><b><%# msgSubject %></b></td></tr>
   <tr>
      <td>Message:</td>
      <td><b><%# msgBody %></b></td></tr>
   </table>

   <p><hr size=1 width=90%><input type=submit value="Delete" runat="server" onServerClick="zapMessage"> <input type=reset value="Cancel" onClick="self.location.replace ( 'remove.aspx' )"></p>

   </form>
</asp:panel>

</center>
</body>
</html>

edit.aspx

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<html>
<head>
<title>Editing a Row of Data in a DataSet</title>
<link rel="stylesheet" href="/aspxtreme/shared/netdemos.css">

<style type="text/css">
#editPanel {
   width:90%; padding:10; background:rgb ( 232,240,255 ); 
   border:thin outset}
#posted {
   background:rgb ( 224,224,232 ); border:thin inset}
</style>

<script language="C#" runat="server">
DataSet myDataSet;
DataTable myTable;
DataRow thisRecord;
SqlDataAdapter adapter;

protected void Page_Load ( object src, EventArgs e ) {
   SqlConnection myConn = new SqlConnection ( 
      ConfigurationSettings.AppSettings [ "aspa" ] );
   adapter = new SqlDataAdapter ( 
      "SELECT * FROM teacher.aspx_messages order by MessageDate desc", myConn );
   myDataSet = new DataSet ( );
   adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
   adapter.Fill ( myDataSet, "Messages" );
   SqlCommandBuilder cmdBuilder = new SqlCommandBuilder ( adapter );
   myTable = myDataSet.Tables [ "Messages" ];
   if ( IsPostBack ) {
      int id = Int32.Parse ( Request.QueryString [ "id" ] );
      thisRecord = myTable.Rows.Find ( id );
      loadPanel.Visible = false;
      editPanel.Visible = false;
      postPanel.Visible = true;
   }
   else if ( Request.QueryString [ "id" ] !=null ) {
      int id = Int32.Parse ( Request.QueryString [ "id" ] );
      thisRecord = myTable.Rows.Find ( id );
      getMessage ( );
      loadPanel.Visible = false;
      editPanel.Visible = true;
      postPanel.Visible = false;
   }
   else {
      myGrid.DataSource = myTable.DefaultView;
      myGrid.DataBind ( );
      loadPanel.Visible = true;
      editPanel.Visible = false;
      postPanel.Visible = false;
   }
}

public void getMessage ( ) {
   msgDate.Value = thisRecord [ "MessageDate" ].ToString ( );
   msgFrom.Value = thisRecord [ "MessageFrom" ].ToString ( );
   msgEmail.Value = thisRecord [ "Email" ].ToString ( );
   msgSubject.Value = thisRecord [ "MessageSubject" ].ToString ( );
   msgBody.Value = thisRecord [ "MessageBody" ].ToString ( );
   editPanel.DataBind ( );
}

public void updateMessage ( object src, EventArgs e ) {
   if ( Page.IsValid ) {
      thisRecord [ "MessageFrom" ] = msgFrom.Value;
      thisRecord [ "MessageSubject" ] = msgSubject.Value;
      thisRecord [ "Email" ] = msgEmail.Value;
      thisRecord [ "MessageBody" ] = msgBody.Value;
      adapter.Update ( myTable );
   }
   postPanel.DataBind ( );
}
</script>
</head>

<body>
<!-- #include virtual="~/shared/top.inc" -->

<div class="header"><h3>ADO.NET Primer: <span class="hilite">Editing a Row of Data in a DataSet</span></h3></div>

<hr size=1 width=90%>

<center>

<asp:panel id="loadPanel" runat="server">
   <h5>Select a Record to Edit</h5>

   <asp:datagrid id="myGrid" runat="server" 
      width=90% cellpadding=5 gridlines="vertical"
      bordercolor="black" borderwidth=1
      font-size="8pt"
      headerstyle-backcolor="lightsteelblue"
      headerstyle-font-bold
      headerstyle-height=25
      alternatingitemstyle-backcolor="whitesmoke"
      autogeneratecolumns=false>

      <columns>
         <asp:boundcolumn headertext="Date" 
            datafield="MessageDate" 
            dataformatstring="{0:d}" />
         <asp:hyperlinkcolumn
            headertext="From"
            datanavigateurlfield="MessageID"
            datanavigateurlformatstring="edit.aspx?id={0}"
            datatextfield="MessageFrom" />
         <asp:boundcolumn headertext="Subject" 
            datafield="MessageSubject" />
      </columns>

   </asp:datagrid>

   <p align="center"><a href="#top"><img alt="Back to top" border=0 height=32 onmouseover="status='Back to top of page'" src="/aspxtreme/shared/top.gif" width=32></a></p>

</asp:panel>

<asp:panel id="editPanel" runat="server">
   <h5>To save changes to the database, press Update.</h5>

   <form runat="server">

   <input type="hidden" id='msgDate' runat="server">

   <table width="85%" cellspacing=1 cellpadding=3 border=0>
   <col width=40% align="right">
   <col width=60%>
   <tr>
      <td>Your name:</td>
      <td><input maxlength=254 id='msgFrom' runat="server">
         <asp:requiredfieldvalidator
            controltovalidate="msgFrom"
            errormessage="Sender's name cannot be blank. "
            runat="server">*
         </asp:requiredfieldvalidator></td></tr>
   <tr>
      <td>Your email address:</td>
      <td><input maxlength=254 id='msgEmail' runat="server">
         <asp:regularexpressionvalidator
            controltovalidate="msgEmail"
            validationexpression="^[\w-]+@[\w-]+\.(com|net|org|edu|mil)$"
            errormessage="Please enter a valid e-mail address. "
            runat="server">*
         </asp:regularexpressionvalidator></td></tr>
   <tr>
      <td>Your subject:</td>
      <td><input maxlength=254 id='msgSubject' runat="server"></td></tr>
   <tr>
      <td>Your message:</td>
      <td><textarea id='msgBody' rows=5 cols=35 runat="server"></textarea></td></tr>
   </table>
   <p><input type=submit value="Update" runat="server" onServerClick="updateMessage"> <input type=reset value="Cancel" onClick="self.location.replace ( 'edit.aspx' )"></p>

   <asp:validationsummary runat="server"
      displaymode="SingleParagraph" 
      showmessagebox=true />

   </form>
</asp:panel>

<asp:panel id="postPanel" runat="server">
   <h5>Thank you for editing your comments. This record has been updated.</h5>
   <table id="posted" width="85%" cellspacing=1 cellpadding=5 border=0>
   <col width=35% align="right">
   <tr>
      <td>Date:</td>
      <td><b><%# msgDate.Value %></b></td></tr>
   <tr>
      <td>Name:</td>
      <td><b><%# msgFrom.Value %></b></td></tr>
   <tr>
      <td>Email:</td>
      <td><b><%# msgEmail.Value %></b></td></tr>
   <tr>
      <td>Subject:</td>
      <td><b><%# msgSubject.Value %></b></td></tr>
   <tr>
      <td>Message:</td>
      <td><b><%# msgBody.Value %></b></td></tr>
   </table>

   <p align="center"><hr size="1" width="90%">
   <a href="javascript:history.go ( -1 )"><img alt="Back to Edit View" border=0 height=32 onmouseover="status='Back to Edit View'" src="/aspxtreme/shared/prev.gif" width=32></a>
   <a href="edit.aspx"><img alt="On to Select" border=0 height=32 onmouseover="status='On to Select'" src="/aspxtreme/shared/next.gif" width=32></a></p>

</asp:panel>

</center>
</body>
</html>

addnew.aspx

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<html>
<head>
<title>Adding a Row of Data to a DataSet</title>
<link rel="stylesheet" href="/aspxtreme/shared/netdemos.css">

<style type="text/css">
#addPanel {
   width:90%; padding:10; background:rgb ( 232,240,255 ); 
   border:thin outset}
#posted {
   background:rgb ( 224,224,232 ); border:thin inset}
</style>

<script language="C#" runat="server">
DataSet myDataSet;
SqlDataAdapter adapter;

protected void Page_Load ( object src, EventArgs e ) {
   if ( IsPostBack ) {
      String strQuery = "SELECT * FROM teacher.aspx_messages";
      SqlConnection myConn = new SqlConnection ( 
         ConfigurationSettings.AppSettings [ "aspa" ] );
      adapter = new SqlDataAdapter ( strQuery, myConn );
      myDataSet = new DataSet ( );
      adapter.Fill ( myDataSet, "Messages" );
      SqlCommandBuilder cmdBuilder = new SqlCommandBuilder ( adapter );

      msgDate.Value = DateTime.Now.ToString ( );
      addPanel.Visible = false;
      postPanel.Visible = true;
   }
   else {
      addPanel.Visible = true;
      postPanel.Visible = false;
   }
}

public void addMessage ( object src, EventArgs e ) {
   if ( Page.IsValid ) {
      DataRow newRecord = myDataSet.Tables [ "Messages" ].NewRow ( );

      newRecord [ "MessageDate" ] = msgDate.Value;
      newRecord [ "MessageFrom" ] = msgFrom.Value;
      newRecord [ "Email" ] = msgEmail.Value;
      newRecord [ "MessageSubject" ] = msgSubject.Value;
      newRecord [ "MessageBody" ] = msgBody.Value;

      myDataSet.Tables [ "Messages" ].Rows.Add ( newRecord );
      adapter.Update ( myDataSet, "Messages" );
   }
   DataView myView = myDataSet.Tables [ "Messages" ].DefaultView;
   myView.Sort = "MessageDate desc";
   myGrid.DataSource = myView;
   postPanel.DataBind ( );
}
</script>
</head>

<body>

With the use of sqlcommandbuilder the following code helps to insert the record into gridview and once update method is called then only it insert record into table.. here you can extend this idea to add record more record in the grid and when need you can also update it in sql table


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
{
SqlConnection con;
SqlDataAdapter adap;
DataSet ds;
DataTable dt;
DataRow dr;
SqlCommandBuilder build;
protected void Page_Load(object sender, EventArgs e)
{
con = new SqlConnection("Server=D-1028;Database=kumar;uid=sa;pwd=sa123;");
adap = new SqlDataAdapter("select * from builder",con);
build = new SqlCommandBuilder(adap);
ds = new DataSet();
adap.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
}
protected void Button1_Click(object sender, EventArgs e)
{
dt = new DataTable();
dt = ds.Tables[0];
dr = dt.NewRow();
dr["bid"] = TextBox1.Text;
dr["bfname"]=TextBox2.Text;
dr["blname"]=TextBox3.Text;
dt.Rows.Add(dr);
adap.SelectCommand = build.GetInsertCommand();
adap.Update(dt);
GridView1.DataSource = dt;
GridView1.DataBind();

}
protected void Button2_Click(object sender, EventArgs e)
{
dt = new DataTable();
dt = ds.Tables[0];
int a=Convert.ToInt32(TextBox1.Text);
//dr=dt.Rows[a-1];
//dr["bid"] = TextBox1.Text;
dr["bfname"] = TextBox2.Text;
dr["blname"] = TextBox3.Text;
//dt.Rows.
adap.UpdateCommand = build.GetUpdateCommand();
adap.Update(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
}
protected void Button3_Click(object sender, EventArgs e)
{
dt = new DataTable();
dt = ds.Tables[0];
//int a = Convert.ToInt32(GridView1.SelectedRow.Cells[0].Text);
int a = Convert.ToInt32(TextBox1.Text);
dt.Rows[a].Delete();//.ToString();
//dr["bfname"] = TextBox2.Text;
// dr["blname"] = TextBox3.Text;
//dt.Rows.
adap.DeleteCommand = build.GetDeleteCommand();
adap.Update(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
}
}

Saturday, March 5, 2011

Difference between SQL Command and SQL Command Builder

ADO.net - Difference between SQL Command and SQL Command Builder

No comments :

Post a Comment