ASP.NET and ADO.NET
ASP.NET and ADO.NET You might have noticed that a significant portion of new technology is considered stateless or connectionless. For example, GDI+ is stateless, meaning you have to pass in font, color , brush, and pen information each time you interact with a GDI+ device context. ADO.NET is connectionless. What this means to you is that you do not have a live cursor when you get data from a database using ADO.NET. In fact, the DataSet component actually grabs a copy of the data and caches it locally. This means that you will want to reconsider those SELECT * FROM tablename queries and be more circumspect about how and what you request. On a positive note, working with ADO.NET is easier than ADO, and data-enabling an ASP.NET Web application is straightforward (after someone shows you the ropes ). There are two basic components you need to know about when working with ADO.NET. Forget about the Recordset and think about the DataSet and the DataReader. The new DataSet component is used for connectionless forward and backward, editable data and DataReaders are for read-only, forward-only datathe kind of navigability you need when you are querying for values. There are also two flavors of the DataSet and DataReader, the OLEDB flavor and the Sql flavor. The Sql flavor is designed and optimized for SQL Server 7.0 and higher and bypasses OLEDB, and the OLEDB flavor is for everything else, MS Access included. Thus if you are working with SQL Server 7.0 or higher, you will use classes that are prefixed with Sql, like SqlDataReader. If you are working with Access, Paradox, Sybase, or Oracle, you will use the OleDbDataReader. One means of getting data from a database to a Web page is to establish a connection, define a command that describes your result set, and bind that data to a page. There are several classes you can use to accomplish this feat. I will demonstrate the code here, and of course, you are welcome to supplant the literal code for components on your own to speed things up. Browsing a Code DatabaseWhat better example in a programming book than to implement a code database? The example in this section makes a database containing procedures and classes available for other developers available on the Web. (This is an idea I am playing with to make it easier for me to reuse my own code when I travel to customer's sites. Instead of taking code with me, I will just make it available on the Web.) The sample database contains just a few examples of code, enough to demonstrate the steps for getting our database connected. (A better example would be to put all of the code in this book in a similar databasenow, if I ever get around to it.) The schema for the code database is pretty straightforward. There is a lookup table describing the Language the code is in to help browsers filter their searches. There is a Submitter table, perhaps to allow users to submit code and log in, and there is the Source table that contains the actual code listing and some description fields. (I won't go into detail about the schema here. You can open the table and look at it for yourself.) In this first pass at a code database application, we will use the DataGrid component and bind the code listings to the DataGrid. The Web interface won't be fancy, but it will allow us to examine the basic steps for data-enabling Web pages. Most of the page is created using code. To create the basic application, you will need the CodeDatabase.mdb (Access database), available from the samspublishing.com Web site, and you will need to create an ASP.NET Web application with a Web Forms DataGrid control dropped on the form. The rest of the application, shown in Figure 19.4, is implemented as code (provided in Listing 19.3). Figure 19.4. The CacheDemo sample appli-cation, demonstrating basic ADO.NET. Listing 19.3 A data-aware ASP.NET Web application1: Imports System.Data.OleDb 2: 3: Public Class WebForm1 4: Inherits System.Web.UI.Page 5: 6: Protected WithEvents DataGrid1 As _ 7: System.Web.UI.WebControls.DataGrid 8: 9: [ Web Form Designer Generated Code ] 10: 11: Private Function ConnectionString() As String 12: Return "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 13: "Data Source=c:\ inetpub\ wwwroot\ " & _ 14: "CodeDatabase\ data\ CodeDatabase.mdb" 15: End Function 16: 17: Private Function Query() As String 18: Return "SELECT * FROM SOURCE" 19: End Function 20: 21: Private Sub Page_Load(ByVal sender As System.Object, _ 22: ByVal e As System.EventArgs) Handles MyBase.Load 23: 'Put user code to initialize the page here 24: 25: If (IsPostBack()) Then Exit Sub 26: BindData(Query()) 27: 28: End Sub 29: 30: Private Sub BindData(ByVal SQL As String) 31: Dim Adapter As New _ 32: OleDbDataAdapter(SQL, ConnectionString()) 33: Dim DataSet As New DataSet() 34: Adapter.Fill(DataSet, "Source") 35: DataGrid1.DataSource = DataSet.Tables("Source") 36: DataGrid1.DataBind() 37: End Sub 38: 39: End Class Line 1 imports the System.Data.OleDb. Because this sample uses Access, we need to use OleDb components instead of Sql components. Lines 3 and 4 indicate that this class is a Web Forma subclass of the System.Web.UI.Page class. Lines 6 and 7 were added by the designer when a DataGrid was dragged from the Web Forms page of the toolbox onto the .aspx page. Lines 11 to 15 implement a query method that returns a connection string. ADO.NET connection strings are consistent with ADO connection strings. (I prefer to use a query method to ensure that one convenient place contains all of that very specific text.) The connection string uses the Microsoft.Jet.OLEDB.4.0 provider. The method Query on lines 17 to 19 is implemented as a query method, too. SQL littered all over your application will uglify code. Using a query method makes the codesee line 26look a bit tidier. All of the code to line 20 should not be new to you at this point. Lines 21 to 39 represent the new information. Lines 21 to 26 implement a Page.Load event handler. ASP.NET is written as Visual Basic .NET (or any other .NET language), which means you can use all of the language features supported by Visual Basic .NET when implementing ASP.NET applications. Lines 25 and 26 represent new code. Line 25 checks to see if this event is being invoked because of an HTTP-Post form operation. If we are loading because of a Post, we don't need to run the query again. The sentinel short-circuits the method on line 25. Otherwise, we call BindData(Query()), passing the SQL string to the BindData method. It is the databinding that is the real workhorse here. My implementation of BindData creates an OleDbDataAdapter object. An OleDbDataAdapter can be instantiated with SQL and a connection string. The adapter will in turn create an OleDBConnection object and an OleDbCommand object for you. (As an alternative, you could create the connection and command object; using an adapter is more convenient.) The OleDbAdapter is used to populate the DataSet object with the specified table (see lines 33 and 34). Finally, the DataSet.Tables, which returns a DataTable, is used as the DataSource for the DataGrid, and DataGrid.DataBind populates the table. To summarize, the OleDbAdapter creates an OleDbCommand and OleDbConnection. The connection represents the connection to the database, and the command represents the SQL. The adapter uses the connection and command to populate a DataTable maintained in the DataSet.Tables collection. The DataTable object provides data to the DataGrid. It is important to keep in mind that we don't have a cursor or an open connection at this point. A copy of the data has been copied over to the requesting client. Sorting Data in a Web FormAnother reasonable operation you might want to allow consumers to perform is to sort the data on a Web page. The DataGrid facilitates this, too. You probably know how to sort a result set by using the ORDER BY clause of a SQL statement. The DataGrid provides an AllowSorting property. If you make the value of AllowSorting equal to True, the DataGrid will add hyperlinks to the column headers. When a user clicks on a column header, the DataGrid.CommandSort event will be raised. By rerunning your query using the selected column header as the sort field, you can reorganize the result set and bind the sorted result set to the DataGrid. Essentially you are performing all of the steps demonstrated in Listing 19.3 with a revised query. Before you try the code in Listing 19.4, make sure that you set the AllowSorting property to True. Listing 19.4 Supporting dynamic sorting using the DataGrid1: Imports System.Data.OleDb 2: 3: Public Class WebForm1 4: Inherits System.Web.UI.Page 5: 6: Protected WithEvents DataGrid1 As _ 7: System.Web.UI.WebControls.DataGrid 8: 9: [ Web Form Designer Generated Code ] 10: 11: Private Function ConnectionString() As String 12: Return "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 13: "Data Source=c:\ inetpub\ wwwroot\ CodeDatabase\ data\ CodeDatabase.mdb" 14: End Function 15: 16: Private Function Query() As String 17: Return "SELECT * FROM SOURCE" 18: End Function 19: 20: Private Function OrderBy(ByVal SortColumn As String) As String 21: If (SortColumn = "") Then 22: Return "" 23: Else 24: Return " ORDER BY " & SortColumn 25: End If 26: End Function 27: 28: Private Function Query(ByVal SortColumn As String) As String 29: Return Query() & OrderBy(SortColumn) 30: End Function 31: 32: Private Sub Page_Load(ByVal sender As System.Object, _ 33: ByVal e As System.EventArgs) Handles MyBase.Load 34: 'Put user code to initialize the page here 35: 36: If (IsPostBack()) Then Exit Sub 37: BindData(Query()) 38: 39: End Sub 40: 41: Private Sub BindData(ByVal SQL As String) 42: Dim Adapter As New _ 43: OleDbDataAdapter(SQL, ConnectionString()) 44: Dim DataSet As New DataSet() 45: Adapter.Fill(DataSet, "Source") 46: DataGrid1.DataSource = DataSet.Tables("Source") 47: DataGrid1.DataBind() 48: End Sub 49: 50: 51: 'Private Sub DataGrid1_PageIndexChanged(ByVal source As Object, _ 52: ' ByVal e As System.Web.UI. _ 53: ' WebControls.DataGridPageChangedEventArgs) _ 54: ' Handles DataGrid1.PageIndexChanged 55: 56: ' DataGrid1.CurrentPageIndex = e.NewPageIndex 57: ' BindData(Query(FSortColumn)) 58: 59: 'End Sub 60: 61: Private FSortColumn As String = "" 62: Private Property SortColumn() As String 63: Get 64: Return FSortColumn 65: End Get 66: Set(ByVal Value As String) 67: FSortColumn = Value 68: Changed() 69: End Set 70: End Property 71: 72: 73: Private Sub Changed() 74: BindData(Query(FSortColumn)) 75: End Sub 76: 77: Private Sub DataGrid1_SortCommand(ByVal source As Object, _ 78: ByVal e As System.Web.UI. _ 79: WebControls.DataGridSortCommandEventArgs) _ 80: Handles DataGrid1.SortCommand 81: 82: SortColumn = e.SortExpression 83: End Sub 84: End Class We won't cover the code discussed earlier in the context of Listing 19.3, just the revisions. Listing 19.4 adds the DataGrid1_SortCommand event handler. When the event is raised, we can determine the column clicked from the DataGridSortCommandEventArgs. SortExpression. (The value represented by e.SortExpression will be the name of one of the columns .) Line 82 calls the setter property method of a property named SortColumn, defined on lines 66 through 69. Line 68 calls the Changed method, which requests an updated query based on the FSortColumn field and binds the new DataTable to the DataGrid. (The balance of the changes were added to facilitate reuse and simplicity. For example, the OrderBy function determines a suitable ORDER BY clause depending on whether FSortColumn contains a value or not.) To recap, the basic mechanics are to create an OldDbConnection, OleDbCommand, a DataSet, and bind a DataTable from the DataSet to the DataGrid control. As an alternative, you can substitute an OleDbDataAdapter for the connection and command; the adapter will create a connection-and-command object for you.) Paging Using the DataGridSuppose you have too much data to show in a single Web page. A user might not want to wait for a huge result set; however, that same user might want all of the information accessible eventually. The DataGrid is a flexible control that automatically supports paging, too. If you set the DataGrid.AllowPaging to True, ASP.NET will display basic paging controls and raise the DataGrid.PageIndexChanged event (see the commented handler in Listing 19.4, lines 51 to 57). The DataGridPageChangedEventArgs supplies the NewPageIndex property, containing the newly requested page index. Assign the event argument to the DataGrid.PageIndexChanged property and rebind the data to the page. The result is paging la carte. The last two sections demonstrated some reasonably powerful capabilities supported by very little code. With some experimentation, you can modify the properties to create some advance presentations. |
 | |
Team-Fly  |
| Top |
