Note: 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
Add a Web.config file to the project by
right-clicking the project name in Solution Explorer and then
selecting Add New Item
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 methodgetAuthors( )which returns a data set.
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 ClassTip: Remember to save the Authors.vb file before proceeding to the next step.
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 class.
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.
Note: If you don't see the getAuthors( ) method, be sure to save your Authors.vb class file first.
...editing the rows in the GridView control through the ObjectDataSource control?
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.
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 SubClick 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.
Tip: 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.
Tip: Note that the updateAuthors( ) method has parameters that are the same as the columns displayed in the GridView control.
On the GridView control, check the Enable Editing checkbox (see Figure 4-52) so that the GridView will 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). Click Next.
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).
Tip: 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>
Note: 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/vbconl/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/vbconl/vbconadonetdatasetcreationinvisualstudionet.asp.