Bringing It Together

only for RuBoard

So we have seen how to manipulate the data in a DataTable and DataView , and how to use the SqlCommandBuilder to save changes back to the datasource. Now let's see how they all work together. In this rather lengthy example we are going to create a product edit page for Northwind . This two-page example will contain a list of all the suppliers available in the Suppliers table and a means to edit, insert, and delete each one of their products. Once all edits have been done to their products, there is a confirmation page that lists all inserts , edits, and deletions. From this page, you will be able to decide to save or reject the changes. If you choose to save the changes, then the Products table will be updated. If you choose not to save the changes, then all the changes made will be rolled back and you can start again with the original data.

An interesting thing about this example is that there are only two connections ever made to the database. The first is when you first enter the page, and the second is if you save the changes back to the database. What this means is that all edits, inserts, and deletions are held in memory until the user has completed. Then a batch update is performed. So let's look at the code. Because of the length of this example, we will only be showing the C# version; you can find the VB.NET version in this chapter's code folder.

Listing 11.13 Northwind Product Edit Page

 01: <%@ import namespace="System.Data" %> 02: <%@ import namespace="System.Data.SqlClient" %> 03: 04: <script language="C#" runat="Server"> 05: 06:  protected void Page_Load(Object sender, EventArgs e){ 07: 08:   if (! Page.IsPostBack) { 09: 10:    Bind(); 11: 12:   } 13: 14:  } 15: 16:  void SaveToSession(DataSet ds){ 17: 18:   Session["WorkingDataSet"] = ds; 19: 20:  } 21: 22:  DataSet GetFromSession() { 23: 24:   return (DataSet)Session["WorkingDataSet"]; 25: 26:  } 27: 28:  void Bind(){ 29: 30:   if (Session["WorkingDataSet"] == null) { 31: 32:    DDLSuppliers.DataSource = GetFromDatabase().Tables["Suppliers"]; 33: 34:   } else { 35: 36:    DDLSuppliers.DataSource = GetFromSession().Tables["Suppliers"]; 37: 38:   } 39: 40:    Page.DataBind(); 41:    GetProducts(); 42: 43:  } 44: 45:  DataSet GetFromDatabase(){ 46: 47:   SqlConnection SqlCon = new SqlConnection("server=localhost;  uid=sa;pwd=;database=northwind"); 48:   SqlDataAdapter SqlDA = new SqlDataAdapter("SELECT ProductName, ProductID,  SupplierID FROM Products ORDER By ProductName", SqlCon); 49: 50:   SqlDA.UpdateCommand = new SqlCommand("UPDATE Products SET ProductName =  @ProductName WHERE ProductID = @ProductID"); 51:   SqlDA.UpdateCommand.Parameters.Add(new SqlParameter( "@ProductName",  SqlDbType.NVarChar, 20, "ProductName")); 52:   SqlDA.UpdateCommand.Parameters.Add(new SqlParameter( "@ProductID", SqlDbType.Int, 4,  "ProductID")); 53: 54:   SqlDA.InsertCommand = new SqlCommand("INSERT INTO Products (ProductName,  SupplierID) VALUES (@ProductName,@SupplierID)"); 55:   SqlDA.InsertCommand.Parameters.Add(new SqlParameter( "@ProductName",  SqlDbType.NVarChar, 20, "ProductName")); 56:   SqlDA.InsertCommand.Parameters.Add(new SqlParameter( "@SupplierID", SqlDbType.Int,  4, "SupplierID")); 57: 58:   SqlDA.DeleteCommand = new SqlCommand("DELETE Products WHERE ProductID =  @ProductID"); 59:   SqlDA.DeleteCommand.Parameters.Add(new SqlParameter( "@ProductID", SqlDbType.Int, 4,  "ProductID")); 60: 61:   DataSet ds = new DataSet(); 62:   SqlDA.Fill(ds, "Products"); 63: 64:   SqlDA.SelectCommand = new SqlCommand("SELECT CompanyName, SupplierID FROM Suppliers  ORDER BY CompanyName", SqlCon); 65:   SqlDA.Fill(ds, "Suppliers"); 66: 67:   SaveToSession(ds); 68:   Session["WorkingDataAdpater"] = SqlDA; 69:   SqlCon.Close(); 70: 71:   return ds; 72: 73:  } 74: 75:  protected void GetProducts_Click(Object sender, EventArgs e) {  GetProducts(); } 76: 77:  protected void GetProducts(){ 78: 79:   lbProducts.Items.Clear(); 80: 81:   DataView dv = new DataView(GetFromSession().Tables["Products"]); 82:   dv.Sort = DDLColumn.SelectedItem.Value + " " + DDLSortOrder.SelectedItem.Value; 83:   dv.RowFilter = "SupplierID =" + DDLSuppliers.SelectedItem.Value; 84:   lbProducts.SelectedIndex = 0; 85:   lbProducts.DataSource = dv; 86:   lbProducts.DataBind(); 87:   txtEdit.Text = null; 88:  } 89: 90: 91:  protected void Delete_Click(Object sender, EventArgs e){ 92: 93:   DataSet ds = GetFromSession(); 94:   DataTable dt = ds.Tables["Products"]; 95:   DataRow[] dr = dt.Select("ProductID = " + lbProducts.SelectedItem.Value); 96:   dr[0].Delete(); 97:   SaveToSession(ds); 98:   GetProducts(); 99: 100:  } 101: 102:  protected void Insert_Click(Object sender, EventArgs e){ 103: 104:   DataSet ds = GetFromSession(); 105:   DataTable dt = ds.Tables["Products"]; 106:   DataRow dr = dt.NewRow(); 107:   dr["ProductName"]  =txtEdit.Text; 108:   dr["SupplierID"] = DDLSuppliers.SelectedItem.Value; 109:   Random RA = new Random(); 110:   string sProductID = "1234" + RA.Next(1000).ToString(); 111:   dr["ProductID"] = int.Parse(sProductID); 112:   dt.Rows.Add(dr); 113:   SaveToSession(ds); 114:   GetProducts(); 115: 116:  } 117: 118:  protected void Edit_Click(Object sender, EventArgs e){ 119: 120:   DataSet ds = GetFromSession(); 121:   DataTable dt = ds.Tables["Products"]; 122:   DataRow[] dr = dt.Select("ProductID = " + lbProducts.SelectedItem.Value); 123:   dr[0].BeginEdit(); 124:   dr[0]["ProductName"] = txtEdit.Text; 125:   dr[0].EndEdit(); 126:   SaveToSession(ds); 127:   GetProducts(); 128: 129:  } 130: 131: </script> 132: <html> 133:  <body> 134:   <form runat="server"> 135:    <TABLE CellPadding="4" CellSpacing="4" Border="0"> 136: 137:     <TR> 138:      <TD Style="font-size:18;background-color:green; color:white;text-align:center"> 139: 140:       Select A Supplier To Edit 141: 142:      </TD> 143:     </TR> 144: 145:     <TR> 146: 147:      <TD Style="text-align:center"> 148: 149:       <b>Get All Products For</b> 150: 151:       <asp:DropDownList 152:        id="DDLSuppliers" 153:        runat="server" 154:        DataTextField="CompanyName" 155:        DataValueField="SupplierID" /> 156: 157:       <b>Sort Results by </b> 158: 159:       <asp:DropDownList id="DDLColumn" runat="Server" > 160:        <asp:ListItem Value="ProductID" Text="Product ID" /> 161:        <asp:ListItem Value="ProductName" Text="Product Name" /> 162:       </asp:DropDownList> 163: 164:       <b> in </b> 165: 166:       <asp:DropDownList id="DDLSortOrder" runat="Server" > 167:        <asp:ListItem Value="DESC" Text="DESC" /> 168:        <asp:ListItem Value="ASC" Text="ASC" /> 169:       </asp:DropDownList> 170: 171:       <b>Order</b> 172: 173:      </TD> 174:     </TR> 175: 176:     <TR> 177: 178:      <TD Style="text-align:left"> 179: 180:       <asp:Button 181:        Text="Get Products" Width="200" 182:        BackColor="green" ForeColor="White" 183:        OnClick="GetProducts_Click" Runat="server" 184:        Font-Bold="true" Border="none" /> 185: 186:      </TD> 187:     </TR> 188: 189:     <TR> 190:      <TD Style="font-size:18;background-color:green; color:white;text-align:left"> 191: 192:       Edit Products 193: 194:      </TD> 195:     </TR> 196: 197:     <TR> 198:      <TD Style="text-align:left"> 199: 200:       <b>Choose Product From List to Edit or Delete</b> 201:       <br> 202:       <asp:ListBox 203:        Id="lbProducts" Runat="server" 204:        Rows="5" Width="100%" 205:        DataTextField="ProductName" DataValueField="ProductID" /> 206:       <BR> 207:       <b>Edit or Insert Product </b><font size="1">{ insert item by filling out  textbox and clicking on the insert button} 208:       <BR> 209:       <asp:TextBox 210:        Id="txtEdit" Runat="server" 211:        Width="100%"/> 212: 213:      </TD> 214:     </TR> 215: 216: 217:     <TR> 218:      <TD Style="text-align:left"> 219: 220:       <asp:Button 221:        Id="Delete" 222:        Runat="Server" Text="Delete" 223:        OnClick="Delete_Click" Width="200" 224:        BackColor="green" ForeColor="White" 225:        Runat="server"  Font-Bold="true" 226:        Border="none" /> 227:       <asp:button 228:        Id="Edit" Runat="server" 229:        Text="Edit" OnClick="Edit_Click" 230:        Width="200" BackColor="green" 231:        ForeColor="White" Runat="server" 232:        Font-Bold="true" Border="none" /> 233:       <asp:button 234:        Id="Insert" Runat="server" 235:        Text="Insert" OnClick="Insert_Click" 236:        Width="200" BackColor="green" 237:        ForeColor="White" Runat="server" 238:        Font-Bold="true" Border="none" /> 239:       <p> 240:       <a href="listing11.15.aspx">Save Changes</a> 241: 242:      </TD> 243:     </TR> 244: 245:    </TABLE> 246:   </form> 247:  </body> 248: </html> 

Okay, let's walk through the event chain. On the first request of this page, the Bind method is invoked (line 10) in the Page_Load event (lines 6 “14). The Bind method (lines 28 “43) has an if...then statement that checks if there is a session object with the name of WorkingDataSet ”WorkingDataSet is a DataSet which holds all of our data. Since this is the first request there isn't a session object with that name, so the code in the if statement is executed. Within the if statement, the DropDownList named DDLSuppliers is bound to the result of the method GetFromDatabase (line 32). The GetFromDatabase method (lines 45 “73) returns a DataSet object that is constructed from a database query.

Let's take a moment and look at the code in the GetFromDatabase method because this is where all our primary objects are created. The SqlDataAdapter is constructed on line 48. You'll notice that I create a SelectCommand , UpdateCommand , InsertCommand , and DeleteCommand for it (lines 48 “59). You'll recall in the previous section that this is done for us by using the SqlCommandBuilder object. Next, in lines 61 “65, I fill a DataSet with two queries ”the contents of the Products and Suppliers table. You'll understand why I create two DataTables later in this section. On line 67, I invoke the SaveToSession method passing in the DataSet . The SaveToSession method saves the DataSet to the users session. On line 68, I save the SqlDataAdapter to the session. We'll be using the SqlDataAdapter again on the next page of our sample. We finish up by returning the DataSet .

After the if...then statement, the Page.DataBind method is invoked, followed by the GetProducts method. To understand why the Page.DataBind method was invoked before the GetProducts method, we must look at the GetProducts method. The GetProducts method is used to populate a ListBox named lbProducts that contains a list of all products that a particular supplier carries. Because there is always going to be a supplier selected in the DDLSuppliers we need to wait until it is populated , and invoking the Page.DataBind method does this.

Within the GetProducts method, we first clear the present contents of lbProducts ”line 79. Then, on line 81, a new DataView is created with the Product DataTable named dv . We use this DataView to enable the user to sort the products shown in lbProducts ”line 82. We also use dv to filter out all the products for a particular supplier ”line 83. On line 84, the ListBox.SelectedIndex is set to , the first item in the list, and on lines 85 and 86, lbProducts is bound to dv . Finally, any contents of the TextBox named txtEdit are cleared ” txtEdit is used to insert or edit a product in lbProducts .

At this point, the page load process is complete and you should have a page similar to Figure 11.13.

Figure 11.13. Page on first request.

The top of the page has a DropDownList with a list of suppliers. To the right of that is a DropDownList with fields you can sort by, and on the far right is a DropDownList you can use to determine if the sort order should be in ascending or descending order. Beneath that is a button you use to retrieve all the products for a particular supplier. When this button is clicked, the GetProducts_Click handles the click event. The GetProducts_Click , in turn , invokes the GetProducts method.

Once the products for a supplier have been retrieved, you can edit the product's name by selecting the product from lbProducts and typing the new product name in the TextBox with the heading "Edit or Insert Product" and clicking the edit button. You can delete a product by selecting an item from lbProducts and clicking the delete button. Finally, you can insert a new product by typing in a product name in a new product name and clicking the insert button.

Once an item is selected and the delete button is clicked Delete_Click handles the event (lines 91 “100). Within Delete_Click the GetFromSession method (line 93) is invoked to get our working Dataset . Then the DataTable.Select method is used to return the DataRow of the product you want to delete in the Products DataTable ”line 95. The ProductID value is used to find the proper row. On line 96, we invoke the returned DataRow's Delete method to delete it from the DataTable . Then we put the edited DataSet back into the session and invoke the GetProducts method again to rebind the ListBox with the updated data.

When the edit button is clicked, Edit_Click handles the event (lines 118 “129). The only difference between the Edit_Click and Delete_Click method is what is done with the DataRow . Instead of invoking the DataRow.Delete method, the DataRow is put into edit mode ”line 123. The ProductName column is changed to the value found in txtEdit ”line 124. Finally, the DataRow.EndEdit method is invoked to take it out of edit mode ”line 125.

When the insert button is clicked, Insert_Click handles the event (lines 102 “116). In this method, the DataTable.NewRow method is invoked to return a new DataRow ”line 106. Only three column values are set in this example, the ProductName column value is set on line 107 by using the value of txtEdit , the SupplierID column is set on line 108 by using DDLSuppliers.SelectedItem.Value , and the ProductID value is set on line 111. I set the ProductID with a random number that will never see the light of the database because when a new row is inserted into the Products table, the ProductID is autogenerated. Although, in this example it is needed; if you choose to delete or edit the ProductName after it has been added, this value finds the DataRow in the DataTable .

Figure 11.14 contains a screenshot of this page after a new item was added, and one was edited. Following Figure 11.14, in Listing 11.14, is the code for our page that will save this data back to the database.

Figure 11.14. The edited page.

Listing 11.14 Accept Changes Page

 01: <%@ Page EnableViewState ="false" %> 02: <%@ import namespace="System.Data" %> 03: <%@ import namespace="System.Data.SqlClient" %> 04: 05: <script language="C#" runat="server"> 06: 07:  protected void RejectChanges(Object sender, EventArgs e){ 08: 09:   ((DataSet)Session["WorkingDataSet"]).Tables["Products"].RejectChanges(); 10:   Response.Redirect("listing11.14.aspx"); 11: 12:  } 13: 14:  protected void Update_Click(Object sender, EventArgs e){ 15: 16:   try { 17:    SqlDataAdapter SqlDa = (SqlDataAdapter)Session["WorkingDataAdpater"]; 18:    SqlConnection SqlCon = new SqlConnection("server=localhost;  uid=sa;pwd=;database=northwind"); 19:    SqlDa.UpdateCommand.Connection = SqlCon; 20:    SqlDa.InsertCommand.Connection = SqlCon; 21:    SqlDa.DeleteCommand.Connection = SqlCon; 22:    SqlDa.Update(((DataSet)Session["WorkingDataSet"]).Tables["Products"]); 23:    Session.Remove("WorkingDataSet"); 24:    Message.Text = "Changes Successfully Saved <a href='listing11.14.aspx'> Click  Here</a> to start again"; 25:    Update.Visible = false; 26:    Reject.Visible = false; 27:   } catch (SqlException SqlEx) { 28: 29:    Message.Text = "The Following Error Occured:<br>" + SqlEx.Message.ToString() +  "<p>You may be trying to delete a restricted item!"; 30: 31:   } 32: 33:  } 34: 35:  protected void Page_Load(Object sender, EventArgs e){ 36: 37:   DataTable dt = ((DataSet)Session["WorkingDataSet"]).Tables["Products"]; 38: 39:   foreach (DataRow row in dt.Rows) { 40: 41:    if (row.RowState == DataRowState.Added){ 42: 43:     Added.Items.Add(new ListItem(row["ProductName"].ToString())); 44: 45:    }  else if (row.RowState == DataRowState.Deleted) { 46: 47:     row.RejectChanges(); 48:     Deleted.Items.Add(new ListItem(row["ProductName"].ToString())); 49:     row.Delete(); 50: 51:    }  else if (row.RowState == DataRowState.Modified) { 52: 53:     Modified.Items.Add(new ListItem(row["ProductName"].ToString())); 54: 55:    } else if (row.RowState == DataRowState.Unchanged) { 56: 57:     Unchanged.Items.Add(new ListItem(row["ProductName"].ToString())); 58: 59:    } 60: 61:   } 62: 63:  } 64: 65: </script> 66: 67: <html> 68:  <body> 69:   <form runat="server"> 70:     <center> 71: 72:     <table CellPadding="4" CellSpacing="0" Border="0" Width="500"> 73:      <tr> 74:       <td Style="font-size:18;background-color:green; color:white;text-align:center"> 75: 76:        Confirm Changes 77: 78:       </td> 79:      </tr> 80:      <tr> 81:       <td align="center"> 82: 83:        <b>Rows Added</b> 84:        <br> 85:        <asp:ListBox runat="server" font-size="10" id="Added" Width="410" /> 86: 87:       </td> 88:      </tr> 89:      <tr> 90:       <td align="center"> 91: 92:        <b>Rows Deleted</b> 93:        <br> 94:        <asp:ListBox runat="server" font-size="10" id="Deleted" Width="410" /> 05: 96:       </td> 97:      </tr> 98:      <tr> 99:       <td align="center"> 100: 101:        <b>Rows Modified</b> 102:        <br> 103:        <asp:ListBox runat="server" font-size="10" id="Modified" Width="410"      /> 104: 105:       </td> 106:      </tr> 107:      <tr> 108:       <td align="center"> 109: 110:        <b>Rows Unchanged</b> 111:        <br> 112:        <asp:ListBox runat="server" font-size="10" id="Unchanged" Width="410" /> 113: 114:       </td> 115:      </tr> 116:      <tr> 117:       <td align="center"> 118: 119:        <asp:Button 120:         Text="Update Database" 121:         runat="server" OnClick="Update_Click" 122:         Width="200" BackColor="green" 123:         ForeColor="White" Runat="server" 124:         Font-Bold="true" Border="none" /> 125: 126:        <asp:Button 127:         Text="Reject Changes and Go Back" 128:         runat="server" OnClick="RejectChanges" 129:         Width="200" BackColor="green" 130:         ForeColor="White" Runat="server" 131:         Font-Bold="true" Border="none" /> 132: 133: 134:       </td> 135:      </tr> 136:      <tr> 137:       <td align="Left"> 138: 139:        <asp:Label runat="server" Font-Color="Red" Font-Bold="true" id="Message" /> 140: 141:       </td> 142:      </tr> 143:     </table> 144:    </center> 145:   </form> 146:  </body> 147: </html> 

By clicking on "Save Changes," as shown in Figure 11.14, you can navigate to Listing 11.14. When Listing 11.14 first renders , you'll receive a page with four multiple-lined TextBox controls. The top will contain all products that were added during the session. The second will contain all products that were deleted. The third contains all items that were edited. And the last TextBox contains all items that were unchanged.

These TextBox es are populated in the Page_Load event handler (lines 35 “61). First, I retrieve the Products DataTable on line 37. Then I loop through its DataRowCollection and check each DataRow.RowState property for its value, and depending on the value I insert the row into its correlating TextBox . You will notice in lines 47 “49, I have to invoke the DataRow.RejectChanges method before I can get the value of the ProductName field for deleted DataRows . If I didn't rollback the changes before trying to access the columns , I would get a DeletedRowInaccessibleException because the DataRow has indeed been deleted from this version of the DataTable . Then after I get the value to display, I delete the DataRow again.

At the bottom of this page are two buttons . The first "Update Database" will execute code that will attempt to save the changes to the rows DataTable back to the database. The second, "Reject Changes and Go Back" will reject all the changes made to the DataTable and send the user back to the original page.

When the "Update Database" button is clicked, Update_Click handles the event. In this method is where I retrieve the SqlDataAdapter we created in the first code example from sessionstate . The SqlDataAdapter is pretty much ready to use right when we get it. We set it up before we threw it into sessionstate. The only thing we have to do is wire it to a valid SqlConnection object. This is done on lines 18 “21, where the Connection property of each of the commands ( UpdateCommand , InsertCommand , and DeleteCommand ) is set to the SqlConnection object created on line 18 ” SqlCon . On line 22, I use the SqlDataAdapter.Update method to save the changes back to the database.

After updating the database, I remove the DataSet from sessionstate and provide a means for the user to start a new session. Figure 11.15 contains the page after edits have been made and saved back to the database. Notice the different values in the different TextBox controls.

Figure 11.15. Edited page after data has been saved back to the database.

If you're trying this on your own computer, be aware: If you try to delete one of the products already contained in the Products table, you will receive an exception because of a foreign key constraint with the "Order Details" table. Edits and inserts will update fine, and you can delete any products that you add yourself. In this code example, there is no exception handling so it will break if you try to use it outside the bounds of the example. However, it should provide you a good foundation of how you make all the objects work with one another.

only for RuBoard