Using the SqlCommandBuilder to Save Changes Back to Your Database

SqlCommandBuilder to Save Changes Back to Your Database"-->

only for RuBoard

Using the SqlCommandBuilder to Save Changes Back to Your Database

Now that you have seen how easy it is to manipulate data in both a DataView and DataTable you're probably wondering how hard is it to save these changes back to your database. Well, the answer is very easy. In this section I'll demonstrate how to use some of ADO.NET's built-in functionality to easily save data edited within a DataTable back to your database. I'll also be demonstrating how to use the SqlCommandBuilder to automatically generate your UPDATE , DELETE , and INSERT SQL statements. In the next section, "Bringing It All Together," I'll demonstrate another turnkey method to save your changes back to your database.

Using the SqlCommandBuilder (or one of its counterparts, OleDbCommandBuilder or OdbcCommandBuilder ) to generate your SQL statements is very quick and easy for single table changes. What that means is if all your data in a particular DataTable is from a single database table, this works great. If not, you can't use it and you'll have to manually supply the command text for each command by using the DataAdapter DeleteCommand , InsertCommand , and UpdateCommand as we'll demonstrate in the following section.

At a minimum, you must set the SqlDataAdpater.SelectCommand property to use the SqlCommandBuilder . The SqlCommandBuilder uses the SelectCommand to retrieve information about the table in which the edits will be made and uses this information to automatically generate Transact-SQL statements for the other commands. Note: If you set one of the additional commands, for example, UpdateCommand , the SqlCommandBuilder will not automatically generate an UpdateCommand and will use the one you created.

After being created and associated with a SqlDataAdpater , the SqlCommandBuilder becomes a listener for the SqlDataAdapter.RowUpdating event. This event occurs right before a command gets executed against a datasource. The SqlCommandBuilder then tells the SqlDataAdapter what command object to use. You associate a SqlCommandBuilder with a SqlDataAdapter in the SqlCommandBuilder constructer; you pass the SqlDataAdapter object in as a parameter.

After all row edits, inserts , or deletions are done to the DataTable , all you need to do is call the SqlDataAdapter.Update method and the rest is done for you under the covers. Essentially, the SqlDataAdapter examines all the DataRow.RowState values and executes the correlating SQL command. For instance, if DataRow.RowState is DataRowState.Added , then the INSERT command is executed and if its DataRowState.Deleted , the DELETE command is executed and so on. Easy enough? Let's look at an example. In Listing 11.12, I demonstrate how to use the SqlCommandBuilder to insert a new row into the Products table of the Northwind database. Because the SqlCommandBuilder does all the work for us, I am keeping this example as simple as possible and just inserting a new record. Editing and deleting is done the exact same way as inserting. First, you manipulate the DataTable and then call the DataAdapter.Update method.

Listing 11.12 Using the SqlCommandBuilder
 [VisualBasic.NET] 01: <%@ import namespace="System.Data" %> 02: <%@ import namespace="System.Data.SqlClient" %> 03: <script language="vb" runat="server"> 04: 05:  protected sub Page_Load(sender as Object, e as EventArgs) 06: 07:   if (not IsPostBack) then 08:    Bind() 09:   end if 10: 11:  end sub 12: 13:  sub Bind() 14: 15:   dim SqlCon as new SqlConnection("server=localhost; uid=sa;pwd=;database=northwind") 16:   dim SqlDA as new SqlDataAdapter("SELECT ProductName, ProductID FROM Products", graphics/ccc.gif SqlCon) 17:   dim ds as new DataSet() 18:   SqlDA.Fill(ds, "Products") 19:   DG.DataSource = ds.Tables(0) 20:   DG.DataBind() 21: 22:  end sub 23: 24:  sub InsertRecord(sender as Object, e as EventArgs) 25: 26:   dim SqlCon as new SqlConnection("server=localhost; uid=sa;pwd=;database=northwind") 27:   dim SqlDA as new SqlDataAdapter("SELECT ProductName, ProductID FROM Products", graphics/ccc.gif SqlCon) 28:   dim SCB as new SqlCommandBuilder(SqlDA) 29:   dim ds as new DataSet() 30:   SqlDA.Fill(ds, "Products") 31:   dim dt as DataTable = ds.Tables(0) 32:   dim dr as DataRow = dt.NewRow() 33:   dr(0) = ChangeTo.Text 34:   dt.Rows.Add(dr) 35:   SqlDA.Update(ds, "Products") 36:   Bind() 37: 38: end sub 39: 40: </script> [C#.NET] 01: <%@ import namespace="System.Data" %> 02: <%@ import namespace="System.Data.SqlClient" %> 03: <script language="C#" runat="server"> 04: 05:  protected void Page_Load(Object sender, EventArgs e){ 06: 07:   if (! IsPostBack) {  Bind(); } 08: 09:  } 10: 11:  void Bind(){ 12: 13:   SqlConnection SqlCon = new SqlConnection("server=localhost; graphics/ccc.gif uid=sa;pwd=;database=northwind"); 14:   SqlDataAdapter SqlDA = new SqlDataAdapter("SELECT ProductName, ProductID FROM graphics/ccc.gif Products", SqlCon); 15:   DataSet ds = new DataSet(); 16:   SqlDA.Fill(ds, "Products"); 17:   DG.DataSource = ds.Tables[0]; 18:   DG.DataBind(); 19: 20:  } 21: 22:  void InsertRecord(Object sender, EventArgs e){ 23: 24:   SqlConnection SqlCon = new SqlConnection("server=localhost graphics/ccc.gif ;uid=sa;pwd=;database=northwind"); 25:   SqlDataAdapter SqlDA = new SqlDataAdapter("SELECT ProductName, ProductID FROM graphics/ccc.gif Products", SqlCon); 26:   SqlCommandBuilder SCB = new SqlCommandBuilder(SqlDA); 27:   DataSet ds = new DataSet(); 28:   SqlDA.Fill(ds, "Products"); 29:   DataTable dt = ds.Tables[0]; 30:   DataRow dr = dt.NewRow(); 31:   dr[0] = ChangeTo.Text; 32:   dt.Rows.Add(dr); 33:   SqlDA.Update(ds, "Products"); 34:   Bind(); 35: 36: } 37: 38: </script> [VisualBasic.NET & C#.NET] 39: <html> 40:  <body style="font:10"> 41:   <form runat="server"> 42:    <h3> 43:    SqlCommandBuilder 44:    </h3> 45:    Enter Product Name: 46:    <asp:TextBox ID="ChangeTo" Runat="server" /> 47:    <asp:Button Text="Submit" Runat="server" OnClick="InsertRecord" /> 48:    <p> 49:    <asp:DataGrid id="DG" runat="Server" font-size="10" /> 50:    </p> 51:   </form> 52:  </body> 53: </html> 

In Listing 11.12, there are three methods ” Page_Load (lines 5 “9), Bind (lines 11 “20), InsertRecord (lines 22 “36). Within the Page_Load event, I check if Page.IsPostBack is true . If it is not, then I invoke the Bind method. All the Bind method does is populates the DataGrid with the contents of the Products table. The InsertRecord method is used to handle the Button controls Click event and within it is where I make use of the SqlCommandBuilder . Notice I create my SqlConnection and SqlDataAdapter in the usual way (lines 24 and 25). On line 26 is where I create the SqlCommandBuilder object. Notice that I pass in the SqlDataAdapter as a parameter. In lines 29 “32, I add the new row to the DataTable (" Products ") and then on line 33 I invoke the DataAdapter.Update method, ending by invoking the Bind method so the DataGrid gets fresh data. You can see this page after a new row is added in Figure 11.12. You will notice that there is a ProductID associated with the new row even though we didn't specify one. This is because the ProductID field is autogenerated by SQL Server.

Figure 11.12. My name is shown as a product.
graphics/11fig12.gif
only for RuBoard


Programming Data-Driven Web Applications with ASP. NET
Programming Data-Driven Web Applications with ASP.NET
ISBN: 0672321068
EAN: 2147483647
Year: 2000
Pages: 170

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net