Unless you are writing a simple web application, you should really encapsulate all your business logic into components and then bind your UI controls (such as the GridView) to the business objects.
A well-designed system uses different tiers for different operations
(such as a three-tier architecture). For example, programmers
frequently encapsulate their business logic in classes separate from
those that handle interactions with the user. In ASP.NET 2.0, you can
expose your data-aware classes to data-bound controls (such as the
GridView) through the ObjectDataSource control.
Doing so will allow changes to be made to either layer (business
logic or UI) without affecting the other.
To illustrate how you can bind a GridView control to an object through the ObjectDataSource control, you will create a class that encapsulates your data access logic and then use an ObjectDataSource control to bind to it. You will then bind the GridView control to the ObjectDataSource control. When you're finished, you'll have a three-tier application.
Launch Visual Studio 2005 and create a new web site project. Name the project C:\ASPNET20\chap04-ObjectDataSource.
Add a new App_Code folder to your project by right-clicking the project name in Solution Explorer and then selecting Add Folder→ App_Code Folder (see Figure 4-45).
Add a Web.config file to the project by right-clicking the project name in Solution Explorer and then selecting Add New Item→ Web Configuration File.
Add the following connection string to Web.config:
<connectionStrings> <add name="pubsConnectionString" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=pubs; Integrated Security=True" providerName="System.Data.SqlClient" /> </connectionStrings>
Right-click the App_Code folder and select Add New Item.... Select Class and name it Authors.vb.
Code Authors.vb as shown in Example 4-9. The Authors class contains a single
getAuthors( )—which returns a
Example 4-9. Authors.vb
Imports System.Data.SqlClient Imports System.Data Imports Microsoft.VisualBasic Public Class Authors Public Function getAuthors( ) As DataSet Dim conn As New SqlConnection( _ ConfigurationManager.ConnectionStrings _ ("pubsConnectionString").ConnectionString) Dim adapter As New SqlDataAdapter( _ "SELECT au_id, au_fname, au_lname FROM Authors", conn) Dim ds As New DataSet adapter.Fill(ds, "Authors") Return ds End Function End Class
Add a GridView control to the default Web Form. In the GridView Tasks menu, select <New data source...> (see Figure 4-46).
Choose Object as the data source in the window that opens (see Figure 4-47). Use the default ID of
ObjectDataSource1 for the data source. Click OK.
In the drop-down listbox, select
Authors as the
business object (see Figure 4-48). Click Next. You
are essentially binding the ObjectDataSource control to the Authors
In the SELECT tab, select the "getAuthors( ), returns DataSet" method (see Figure 4-49). Click Finish.
To test the application, press F5. You should now see the GridView control populated with records from the Authors table.
You can also use the GridView control to update the rows through the ObjectDataSource control.
Add a new method,
updateAuthors( ), to the Authors
class, as shown in Example 4-10.
Example 4-10. updateAuthors( )
Public Sub updateAuthors(ByVal au_id As String, _ ByVal au_fname As String, _ ByVal au_lname As String) Dim conn As New SqlConnection( _ ConfigurationManager.ConnectionStrings _ ("pubsConnectionString").ConnectionString) Dim adapter As New SqlDataAdapter( _ "SELECT au_id, au_fname, au_lname " & _ "FROM Authors WHERE au_id=@au_id", conn) Dim ds As New DataSet adapter.SelectCommand.Parameters.Add( _ "@au_id", SqlDbType.NVarChar, 11).Value = au_id adapter.Fill(ds, "Authors") With ds.Tables(0).Rows(0) .Item("au_fname") = au_fname .Item("au_lname") = au_lname End With Dim cb As New SqlCommandBuilder(adapter) adapter.Update(ds, "Authors") End Sub
Click on the Configure Data Source . . . link in the ObjectDataSource Tasks menu of the GridView control (see Figure 4-50).
Click Next, and in the UPDATE tab, select the updateAuthors( ) method
(see Figure 4-51). The use of the updateAuthors( )
method is to update the changes made to the
GridView back to the database. Click Finish.
Likewise for inserting and deleting records, you just need to add new methods to the Authors class and then select them in the INSERT and DELETE tabs.
On the GridView control, check the Enable Editing checkbox (see Figure 4-52) so that the
display the Edit links in the leftmost column.
Press F5 to test the application. The GridView control is now editable with the changes passed to the updateAuthors( ) method for updating (see Figure 4-53).
...using a data component together with the ObjectDataSource control?
Instead of encapsulating the data access logic within a class, it would be easier if Visual Studio automatically generated all the required logic for standard data access tasks such as selecting, inserting, deleting, and updating. In fact, you can do so using a data component.
Using the same project, right-click the project name in Solution Explorer and select Add New Item.... Select Web Form, and name the form Default2.aspx.
Right-click the project name in Solution Explorer and select Add New Item.... Select Dataset, and name the data set Dataset.xsd.
You will be prompted to save the data set in the special App_Code directory. Click Yes (see Figure 4-54).
The Data Component Configuration Wizard window will appear. Click Next.
Use the project's existing connection string
pubsConnectionString, saved in
Web.config). Click Next (see Figure 4-55).
In the next window, select the "Use SQL statements" option. Click Next (see Figure 4-56).
In the box "What data should the table load?", enter the following SQL statement:
SELECT au_id, au_fname, au_lname FROM Authors
Click the Advanced Options... button and check all three checkboxes (see Figure 4-57). Essentially, you want the Insert, Update, and Delete statements to be generated automatically. You would also use optimistic concurrency to prevent updating conflicts. Click OK and then Next.
In the next window, check the three checkboxes (see Figure 4-58). In this step, you are selecting the methods to be exposed by the data component. Use the default values and click Next and then Finish.
Save the project. Configure the GridView control to use a new data source (see Figure 4-59).
In the next window, select Object as the data source and use the
default name of
ObjectDataSource1 as the ID of the
data source. Click OK.
Select the business object
DataSetTableAdapter.authorsTableAdapter (see Figure 4-60). This table adapter is autocompiled based on
the data set you added earlier (Dataset.xsd).
The SELECT, UPDATE, INSERT, and DELETE tabs will all have a method assigned to them (see Figure 4-61). Click Finish.
Finally, check the Enable Editing and Enable Deleting checkboxes in the GridView Tasks menu (see Figure 4-62).
Due to a design decision made by Microsoft, you need to set the
ReadOnly attribute of the au_id field of the GridView control to
false, like this (in Source View):
<asp:BoundField ReadOnly="false" HeaderText="au_id" DataField="au_id" SortExpression="au_id"> </asp:BoundField>
This is because, by default, the GridView control assumes that primary keys are not updateable, whereas the data component allows primary keys to be updated. Thus the GridView control will not pass primary keys' values to the data component. So when you try to update the GridView control, you will get an error stating that the ObjectDataSource control could not find an Update method with the correct parameters.
This behavior might change in the final release of Visual Studio 2005.
Press F5 to test the application. You will now be able to edit and delete records in the GridView control.
To learn more about data access in .NET using ADO.NET, check out the following article: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vbconFundamentalDataConceptsInVisualStudioNET.asp.
To learn what is new in ADO.NET 2.0, check out the MSDN site at http://msdn.microsoft.com/data/DataAccess/Whidbey/default.aspx.
If you are not familiar with data components, refer to this link for an introduction: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vbconadonetdatasetcreationinvisualstudionet.asp.