| Enough theory, let's get on with the ASP.NET development. You're going to build your first full-featured (but simple) database application, which will allow users to view and modify data. You need to put these newly acquired skills to the test. You're only going to need one page for this application. It will display data from your users table in a DataGrid control. You'll use the editing features of the DataGrid to make changes to the data, and then you'll push everything back to the data source using an OleDbCommand object. First, let's build the user interface. Listing 10.10 shows a standard DataGrid with bound columns for each of the fields in your database, plus an EditCommandColumn and a ButtonCommandColumn to allow deletions of data. You also use a panel to display additional input fields for inserting new rows into the database. You also build a label control to display any messages to the user. Listing 10.10 The UI for Your Database Application 1: <html><body> 2: <asp:Label runat="server"/> 3: 4: <form runat="server"> 5: <asp:DataGrid runat="server" 6: BorderColor="black" GridLines="Vertical" 7: cellpadding="4" cellspacing="0" width="100%" 8: AutoGenerateColumns="False" 9: OnDeleteCommand="dgData_Delete" 10: OnEditCommand="dgData_Edit" 11: OnCancelCommand="dgData_Cancel" 12: OnUpdateCommand="dgData_Update" 13: OnPageIndexChanged="dgData_PageIndexChanged" > 14: 15: <Columns> 16: <asp:TemplateColumn HeaderText="ID"> 17: <ItemTemplate> 18: <asp:Label runat="server" 19: Text='<%# Container.DataItem ("UserID") %>'/> 20: </ItemTemplate> 21: </asp:TemplateColumn> 22: 23: <asp:BoundColumn HeaderText="FirstName" 24: DataField="FirstName" /> 25: <asp:BoundColumn HeaderText="LastName" 26: DataField="LastName" /> 27: <asp:BoundColumn HeaderText="Address" 28: DataField="Address" /> 29: <asp:BoundColumn HeaderText="City" 30: DataField="City"/> 31: <asp:BoundColumn HeaderText="State" 32: DataField="State" /> 33: <asp:BoundColumn HeaderText="Zip" 34: DataField="Zip" /> 35: <asp:BoundColumn HeaderText="Phone" 36: DataField="Phone"/> 37: 38: <asp:EditCommandColumn 39: EditText="Edit" 40: CancelText="Cancel" 41: UpdateText="Update" 42: HeaderText="Edit"/> 43: 44: <asp:ButtonColumn HeaderText="" text="Delete" 45: CommandName="delete" /> 46: </Columns> 47: </asp:DataGrid><p> 48: 49: <asp:Panel runat="server"> 50: <table> 51: <tr> 52: <td width="100" valign="top"> 53: First and last name: 54: </td> 55: <td width="300" valign="top"> 56: <asp:TextBox runat="server"/> 57: <asp:TextBox runat="server"/> 58: </td> 59: </tr> 60: <tr> 61: <td valign="top">Address:</td> 62: <td valign="top"> 63: <asp:TextBox runat="server" /> 64: </td> 65: </tr> 66: <tr> 67: <td valign="top">City, State, ZIP:</td> 68: <td valign="top"> 69: <asp:TextBox 70: runat="server" />, 71: <asp:TextBox runat="server" 72: size=2 /> 73: <asp:TextBox runat="server" 74: size=5 /> 75: </td> 76: </tr> 77: <tr> 78: <td valign="top">Phone:</td> 79: <td valign="top"> 80: <asp:TextBox runat="server" 81: size=11 /><p> 82: </td> 83: </tr> 84: <tr> 85: <td colspan="2" valign="top" align="right"> 86: <asp:Button runat="server" 87: text="Add" OnClick="Submit" /> 88: </td> 89: </tr> 90: </table> 91: </asp:Panel> 92: </form> 93: </body></html>  | There's really not much to do on this page. You're just displaying server controls with a number of different properties. The real work will come in the code declaration block, which you'll see in a moment. The DataGrid and its event handlers (lines 9 13) are the most important part of this listing. Each of the server controls in the panel allows the user to add new data to the database, after the user clicks the Add button on line 86. |
If you're writing this in C#, you'll need to change line 19 to read as follows: Text='<%# DataBinder.Eval(Container, "DataItem.UserID") %>'/> Let's examine your code declaration block next. You'll have to handle several things in this section, including loading data into the DataGrid, handling the DataGrid's events, and updating the data source when the user fills out the form and clicks the Submit button. Listing 10.11 shows the code in VB.NET, and Listing 10.12 shows the code in C# (be prepared it's long). Listing 10.11 The ASP.NET Code for Listing 10.10 1: <%@ Page Language="VB" %> 2: <%@ Import Namespace="System.Data" %> 3: <%@ Import Namespace="System.Data.OleDb" %> 4: 5: <script runat="server"> 6: 'declare connection 7: dim Conn as new OleDbConnection( _ 8: "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 9: "Data Source=c:\ASPNET\data\banking.mdb") 10: 11: sub Page_Load(Sender as Object, e as EventArgs) 12: if Not Page.IsPostBack then 13: FillDataGrid() 14: end if 15: end sub 16: 17: sub Submit(Sender as object, e as eventargs) 18: 'insert new data 19: dim i, j as integer 20: dim params(7) as string 21: dim strText as string 22: dim blnGo as boolean = true 23: 24: j = 0 25: 26: for i = 0 to AddPanel.Controls.Count - 1 27: if AddPanel.controls(i).GetType Is _ 28: GetType(TextBox) then 29: strText = Ctype(AddPanel.Controls(i), _ 30: TextBox).Text 31: if strText <> "" then 32: params(j) = strText 33: else 34: blnGo = false 35: lblMessage.Text = lblMessage.Text & _ 36: "You forgot to enter a value for " & _ 37: AddPanel.Controls(i).ID & "<p>" 38: lblMessage.Style("ForeColor") = "Red" 39: end if 40: j = j + 1 41: end if 42: next 43: 44: if not blnGo then 45: exit sub 46: end if 47: 48: dim strSQL as string = "INSERT INTO tblUsers " & _ 49: "(FirstName, LastName, Address, City, State, " & _ 50: "Zip, Phone) VALUES (" & _ 51: "'" & params(0) & "'," & _ 52: "'" & params(1) & "'," & _ 53: "'" & params(2) & "'," & _ 54: "'" & params(3) & "'," & _ 55: "'" & params(4) & "'," & _ 56: "'" & params(5) & "'," & _ 57: "'" & params(6) & "')" 58: 59: ExecuteStatement(strSQL) 60: 61: FillDataGrid() 62: end sub 63: 64: sub dgData_Edit(Sender as object, e as DataGridCommandEventArgs) 65: FillDataGrid(e.Item.ItemIndex) 66: end sub 67: 68: sub dgData_Delete(Sender as object, e as DataGridCommandEventArgs) 69: dim strSQL as string = "DELETE FROM tblUsers " & _ 70: "WHERE UserID = " & e.Item.ItemIndex + 1 71: 72: ExecuteStatement(strSQL) 73: 74: FillDataGrid() 75: end sub 76: 77: sub dgData_Update(Sender as object, e as DataGridCommandEventArgs) 78: if UpdateDataStore then 79: FillDataGrid(-1) 80: end if 81: end sub 82: 83: sub dgData_Cancel(Sender as object, e as DataGridCommandEventArgs) 84: FillDataGrid(-1) 85: end sub 86: 87: sub dgData_PageIndexChanged(Sender as Object, e as DataGridPageChangedEventArgs) 88: dgData.DataBind() 89: end sub 90: 91: function UpdateDataStore(e as DataGridCommandEventArgs) as boolean 92: 93: dim i,j as integer 94: dim params(7) as string 95: dim strText as string 96: dim blnGo as boolean = true 97: 98: j = 0 99: 100: for i = 1 to e.Item.Cells.Count - 3 101: strText = Ctype(e.Item.Cells(i).Controls(0), _ 102: TextBox).Text 103: if strText <> "" then 104: params(j) = strText 105: j = j + 1 106: else 107: blnGo = false 108: lblMessage.Text = lblMessage.Text & _ 109: "You forgot to enter a value<p>" 110: end if 111: next 112: 113: if not blnGo then 114: return false 115: exit function 116: end if 117: 118: dim strSQL as string = "UPDATE tblUsers SET " & _ 119: "FirstName = '" & params(0) & "'," & _ 120: "LastName = '" & params(1) & "'," & _ 121: "Address = '" & params(2) & "'," & _ 122: "City = '" & params(3) & "'," & _ 123: "State = '" & params(4) & "'," & _ 124: "Zip = '" & params(5) & "'," & _ 125: "Phone = '" & params(6) & "'" & _ 126: " WHERE UserID = " & Ctype(e.Item.Cells(0). _ 127: Controls(1), Label).text 128: 129: ExecuteStatement(strSQL) 130: return blnGo 131: end function 132: 133: sub FillDataGrid(Optional EditIndex as integer=-1) 134: 'open connection 135: dim objCmd as new OleDbCommand _ 136: ("select * from tblUsers", Conn) 137: dim objReader as OleDbDataReader 138: 139: try 140: objCmd.Connection.Open() 141: objReader = objCmd.ExecuteReader() 142: catch ex as Exception 143: lblMessage.Text = "Error retrieving from the " & _ 144: database." 145: end try 146: 147: dgData.DataSource = objReader 148: if not EditIndex.Equals(Nothing) then 149: dgData.EditItemIndex = EditIndex 150: end if 151: 152: dgData.DataBind() 153: 154: objReader.Close 155: objCmd.Connection.Close() 156: 157: end sub 158: 159: function ExecuteStatement(strSQL) 160: dim objCmd as new OleDbCommand(strSQL, Conn) 161: 162: try 163: objCmd.Connection.Open() 164: objCmd.ExecuteNonQuery() 165: catch ex as Exception 166: lblMessage.Text = "Error updating the database." 167: end try 168: 169: objCmd.Connection.Close() 170: end function 171: </script> Listing 10.12 The ASP.NET Code for Listing 10.1 in C# 1: <%@ Page Language="C#" %> 2: <%@ Import Namespace="System.Data" %> 3: <%@ Import Namespace="System.Data.OleDb" %> 4: 5: <script runat="server"> 6: OleDbConnection Conn = new OleDbConnection ("Provider=Microsoft.Jet.OLEDB.4.0;  Data Source= c:\\ASPNET\\data\\banking.mdb"); 7: 8: void Page_Load(Object Sender, EventArgs e) { 9: if (!Page.IsPostBack) { 10: FillDataGrid(); 11: } 12: } 13: 14: void Submit(Object Sender, EventArgs e) { 15: int i, j; 16: string[] paramso = new string[7]; 17: string strText; 18: bool blnGo = true; 19: 20: j = 0; 21: 22: for (i = 0; i <= AddPanel.Controls.Count - 1; i++) { 23: if (AddPanel.Controls[i].GetType() == typeof(TextBox)) { 24: strText = ((TextBox)AddPanel.Controls[i]).Text; 25: if (strText != "") { 26: paramso[j] = strText; 27: } else { 28: blnGo = false; 29: lblMessage.Text += "You forgot to enter a value for " + AddPanel. Controls[i].ID.ToString() + "<p>"; 30: lblMessage.Style["ForeColor"] = "Red"; 31: } 32: j++; 33: } 34: } 35: 36: if (!blnGo) { 37: return; 38: } 39: 40: string strSQL = "INSERT INTO tblUsers (FirstName, LastName, Address, City, State, Zip, Phone) VALUES (" + 41: "'" + paramso[0] + "'," + 42: "'" + paramso[1] + "'," + 43: "'" + paramso[2] + "'," + 44: "'" + paramso[3] + "'," + 45: "'" + paramso[4] + "'," + 46: "'" + paramso[5] + "'," + 47: "'" + paramso[6] + "')"; 48: 49: ExecuteStatement(strSQL); 50: 51: FillDataGrid(); 52: } 53: 54: void dgData_Edit(Object Sender, DataGridCommandEventArgs e) { 55: FillDataGrid(e.Item.ItemIndex); 56: } 57: 58: void dgData_Delete(Object Sender, DataGridCommandEventArgs e) { 59: string strSQL = "DELETE FROM tblUsers " + 60: "WHERE UserID = " + (e.Item.ItemIndex + 1). ToString(); 61: 62: ExecuteStatement(strSQL); 63: 64: FillDataGrid(); 65: } 66: 67: void dgData_Update(Object Sender, DataGridCommandEventArgs e) { 68: if (UpdateDataStore) { 69: FillDataGrid(-1); 70: } 71: } 72: 73: void dgData_Cancel(Object Sender, DataGridCommandEventArgs e) { 74: FillDataGrid(-1); 75: } 76: 77: void dgData_PageIndexChanged(Object Sender, DataGridPageChangedEventArgs e) { 78: dgData.DataBind(); 79: } 80: 81: bool UpdateDataStore(DataGridCommandEventArgs e) { 82: int i,j; 83: string[] paramso = new string[7]; 84: string strText; 85: bool blnGo = true; 86: 87: j = 0; 88: 89: for (i = 1; i <= e.Item.Cells.Count - 3; i++) { 90: strText = ((TextBox)e.Item.Cells[i].Controls[0]). Text; 91: if (strText != "") { 92: paramso[j] = strText; 93: j++; 94: } else { 95: blnGo = false; 96: lblMessage.Text += "You forgot to enter " + 97: "a value<p>"; 98: } 99: } 100: 101: if (!blnGo) { 102: return false; 103: } 104: 105: string strSQL = "UPDATE tblUsers SET " + 106: "FirstName = '" + paramso[0] + "'," + 107: "LastName = '" + paramso[1] + "'," + 108: "Address = '" + paramso[2] + "'," + 109: "City = '" + paramso[3] + "'," + 110: "State = '" + paramso[4] + "'," + 111: "Zip = '" + paramso[5] + "'," + 112: "Phone = '" + paramso[6] + "'" + 113: " WHERE UserID = " + ((Label)e.Item.Cells[0]. Controls[1]).Text; 114: 115: ExecuteStatement(strSQL); 116: return blnGo; 117: } 118: 119: void FillDataGrid() { 120: FillDataGrid(-1); 121: } 122: 123: void FillDataGrid(int EditIndex) { 124: OleDbCommand objCmd = new OleDbCommand ("select * from tblUsers", Conn); 125: OleDbDataReader objReader; 126: 127: try { 128: objCmd.Connection.Open(); 129: objReader = objCmd.ExecuteReader(); 130: 131: dgData.DataSource = objReader; 132: if (!EditIndex.Equals(null)) { 133: dgData.EditItemIndex = EditIndex; 134: } 135: 136: dgData.DataBind(); 137: 138: objReader.Close(); 139: objCmd.Connection.Close(); 140: 141: } catch(Exception ex) { 142: lblMessage.Text = "Error retrieving from the database. Please" + 143: " make sure all values are correctly input"; 144: } 145: } 146: 147: void ExecuteStatement(String strSQL) { 148: OleDbCommand objCmd = new OleDbCommand(strSQL, Conn); 149: 150: try { 151: objCmd.Connection.Open(); 152: objCmd.ExecuteNonQuery(); 153: } catch(Exception ex) { 154: lblMessage.Text = "Error updating the database. Please" + 155: " make sure all values are correctly input"; 156: } 157: 158: objCmd.Connection.Close(); 159: } 160: </script> | That's quite a bit of code, but it's not as bad as it looks. First, let's look at the methods used to retrieve data and fill the DataGrid, and then we'll return to the methods to update the database (the following discussion will refer to the VB.NET version of the previous listing). |
On line 7, outside of any methods, you declare an OleDbConnection object. Because this object doesn't belong to any particular method (rather, it belongs to the page), it can be used by every method. The first method that executes (as always), is the Page_Load method, shown on lines 11 15. This method checks whether the form has been posted back; if not, it calls another method, FillDataGrid, which is responsible for retrieving data from the database and binding it to the DataGrid. This method is shown on lines 133 157, and it should look familiar to you. Note the optional parameter EditIndex in the declaration of the method; I'll explain this in a moment. On lines 135 137, you create OleDbCommand and OleDbDataReader objects, using the OleDbConnection object declared on line 7. The next step is wrapping your calls to the database in a try statement. This statement tentatively executes a block of code, upon the condition that no errors occur. If an error does occur, you can use a catch statement, shown on line 142, to handle the error and continue execution. Otherwise, your application would simply crash. You haven't done this so far because your applications have only been simple examples. However, there are a number of things that could go wrong when you're connecting to a system outside of your ASP.NET environment, which in this case is the database. When you start to develop professional applications, you'll need to prevent such errors from crashing your application, or even worse, letting the users see an error happen. | Do | Don't | | Do use try...catch...finally statements when connecting to any object or system outside your ASP.NET environment. This includes databases, managed components, COM objects, and so on. | Don't expect that nothing will go wrong, or that you've dealt with all possible problems. | |
Don't worry if this doesn't make complete sense yet. You'll look at the try statement in detail next week in the discussion of debugging (see Day 20, "Debugging ASP.NET Pages"). Inside the try block, you connect to the database and try to fill your OleDbDataReader. If that doesn't work, you send a message to the user with your label. You set the data source of the DataGrid to the DataReader on line 147. Before you bind the data, however, let's examine that optional parameter, EditIndex, again. Every time the user tries to edit an item, you have to set the EditItemIndex property of the DataGrid appropriately, and then bind the data. This method allows you to set the EditItemIndex property here, which saves you the hassle of setting the edit mode and rebinding later on. Therefore, you include an if statement on line 148 to see if this EditIndex parameter was specified. If so, you set the EditItemIndex property of the DataGrid. (To create an optional parameter in C#, you simply create the same method twice: once with the parameter as normal, and once without. The method without the parameter can then simply call the one with the parameter supplying a valid value for the parameter. This way, the user can choose which version she wants to use; this is known as method overloading, and is a part of object-oriented programming. See the FillDataGrid methods of Listing 10.12 on lines 119 and 123 to see it in action.) Finally, you close the DataReader and connection objects on lines 154 and 155. When you request this page from the browser, you should see something similar to Figure 10.8, depending on the data you have in your database. Figure 10.8. The UI for your database application.
Now that your data is viewable, you need to add the methods to allow the user to edit the items in the DataGrid. Lines 64, 68, 77, 83, and 91 show the methods that will execute on the Edit, Delete, Update, Cancel, and Update events of the DataGrid. For the Edit and Cancel commands of your DataGrid, you can simply call the FillDataGrid method with an optional edit index parameter, like so: sub dgData_Edit(Sender as object, e as _ DataGridCommandEventArgs) FillDataGrid(e.Item.ItemIndex) end sub sub dgData_Cancel(Sender as object, e as _ DataGridCommandEventArgs) FillDataGrid(-1) end sub The Edit method sets the EditItemIndex property of the DataGrid to the item the user selected, and Cancel sets it to -1, which turns off edit mode. For the Update command, you're going to have to be creative. You need to collect all the data from the text boxes that will be generated dynamically when the user clicks the edit link. Therefore, you need a for loop, shown on line 100. This loop iterates through all but the last three cells for the selected item in the DataGrid (remember the last three don't contain any data they are the Update, Cancel, and Delete buttons), and stores the value in the variable strText, shown on line 101. If this value is not an empty string, you store it in an array that will be used later to update the database. If it is empty, you need to stop the procedure and alert the user, as shown on lines 106 108. Notice the different looping variables, i and j. i is used for iterating through the row's cells, while j is used for the indices in the parameter array. The reason you need both is that the cells' indices don't match up to your array indices. For example, the "FirstName" cell is at index 1, but you need to store it at index 0 in your array. Otherwise, you'll get "index out of bounds" errors. You use j to keep track of what array index you're at. Figure 10.9 illustrates this concept. Figure 10.9. The cell indices and array indices don't match.
If any of the text boxes were empty, on line 106 you set a Boolean (blnGo) parameter to remind yourself. If this value is false, meaning one of the text boxes is blank, you want to stop the update. Otherwise, you'll end up with invalid data. You don't want blanks for your user database. This is done with the check on lines 113 116: if not blnGo then return false exit function end if Next, you want to construct your SQL update statement. Because all your values are stored in the param array (or paramso in Listing 10.12), you can do this easily, as shown on lines 118 127. On lines 126 127, you set the WHERE clause of the insert. You only want to update the item that was edited, so you need to determine the identity field of that item. Luckily, the identity field is "UserID", which is already on your DataGrid, in cell 1 of each row. You grab the control from this cell, cast it as a label, and grab the text. Then, according to Figure 10.10, if you edit the first row, the corresponding SQL statement will be UPDATE tblUsers SET FirstName = 'Christopher',LastName = 'Payne',Address = '1335 ASP Street',City = 'ASPTown', State = 'ID',Zip = '83657',Phone = '800-555-4594' WHERE UserID = 1 Finally, you simply need to execute the update statement. This is done by calling another method, ExecuteStatement, which is shown on lines 159 170. This method simply encapsulates the steps of creating an OleDbCommand object and executing the SQL statement. Again, you use a try...catch statement here to ensure there are no unhandled errors, and you use the ExecuteNonQuery() method to perform your update. Next, let's look at the Delete command method, shown on lines 68 75: sub dgData_Delete(Sender as object, e as _ DataGridCommandEventArgs) dim strSQL as string = "DELETE FROM tblUsers " & _ "WHERE UserID = " & Ctype(e.Item.Cells(0). _ Controls(1), Label).text ExecuteStatement(strSQL) FillDataGrid() end sub Relatively speaking, this method is simple. You create your SQL delete statement using the UserID field of the row for your WHERE clause, and then you simply execute the statement and refill your DataGrid. Note, however, that this method is not foolproof. When you delete an identity field in a database, the field is typically not reseeded; meaning if the current user ID is 19, and you delete that user, the next user will be 20, not 19 again. Thus, the calculation used in the previous code snippet won't work once you start adding and deleting records. Instead, you'll need to go with another method I'll leave that as an exercise for you. The only thing left to do is add new records to the database when a user fills out the form fields and clicks the Add button. The Submit method (lines 17 62) is similar to the Update method you just examined, except that you're executing an insert command instead of an update. Let's take a quick overview of the Submit method. Remember placing all the user input controls in a panel? The reason you did that was so that you could loop through them, just as you did with the cells in the DataGrid. This time, instead of looping through a DataGridCommandEventArgs's Cells collection, you're looping the panel's Controls collection. Now you see the reason for the panel. Also, the SQL statement varies in syntax slightly. Other than that, everything is exactly the same as the Update method. Congratulations, that's it! You should now have a complete application that allows users to update, add, and delete records from your database. There was a lot of code, but much of it was fairly straightforward. You also got to practice some good coding design methods by separating things into separate functions as much as you could. Modularizing your applications is great for readability and code reuse. It would be very simple to just copy and paste much of this code into another application, and it should work fine with few modifications. As you might have noticed, you lose a little control over how the fields are rendered when you use the EditCommand for the DataGrid. The text boxes were longer than they needed to be, for example. In the future, when you're using a DataGrid control, it may be easier to use TemplateColumns and text boxes instead of BoundColumns and the EditCommand. There are fewer events to handle, and you have more control over the rendering. But that's a stylistic issue that you can handle as you please. |
