What Is ADO.NET?

The short answer is, it's an API designed to work within Microsoft's .NET framework. In order to handle data interactions on the web and support XML, ADO.NET allows ASP.NET developers to use an OLE DB (Object Linking and Embedding Database) connection to access any type of data- a database, a text file, XML data, and so on. As such, ADO.NET introduces a number of new data access objects to support its new disconnected data model. Disconnected refers to the new way ADO.NET creates and relinquishes a DataSet. As you know, one of the traditional behaviors of ADO was to create a recordset with a live connection to a database. Any change you apply to a row or a column in the recordset you could immediately apply to the database. However, the recordset was a static collection of data independent of any relationships it may have had in the database.

ADO.NET instead uses XML to transfer an independent representation of data to an application through a DataSet. A DataSet is a memory-only copy of the data. Therefore, modifying the data in a database is a two-step process-first apply the modifications to the DataSet and then apply the DataSet to the database.

Note 

This chapter focuses on the first step of the data-modification process. Unfortunately, committing a DataSet to a database under the custom events of the ASP.NET server controls is a long and complex issue because it is done completely outside the realm of Dreamweaver's help. Dreamweaver MX allows users to easily create an update form but not to customize script server controls with in-depth ADO.NET code. For more information on ADO, consult Sybex's ADO and ADO.NET Programming, by Mike Gunderloy. For more information on updating a database, consult Chapter 20.

ADO.NET Namespaces

As you know, the .NET framework is structured around namespaces, which organize components into groups based on functionality and purpose. You can quickly add functionality to your .NET applications simply by loading a namespace. A .NET namespace is simply a library, or a collection, of objects. It's therefore not surprising that to establish easy access to data connections and data objects, Microsoft has organized ADO.NET into namespaces. You'll find ADO.NET separated into three .NET namespaces-System.Data, System.Data.OLEDB, and System.Data.SQLClient. The System.Data namespace allows you to create and manage ADO.NET data objects, and the System.Data.OLEDB and System.Data.SQLClient allow you to connect to OLE DB data sources and to Microsoft SQL Server.

ADO.NET Object Model

The ADO.NET Object Model is made up of two primary parts-the Managed Provider and the DataSet. The Managed Provider is the connection between the DataSet and the original source of data, such as the database or data store. The Managed Provider handles the connection, access, manipulation, and retrieval of data that is represented in the DataSet.

SqlConnection and OLE DB Managed Providers

ADO.NET offers two Managed Providers: the SQL Managed Provider and the OLE DB Managed Provider. The SQL Managed Provider is strictly for interaction with Microsoft SQL Server. The OLE DB Managed Provider interacts with any OLE DB-compliant data source.

Since both providers offer identical basic functionality, why would Microsoft create two different providers? The SQL Managed Provider is optimized for Microsoft SQL Server. The SQL Managed Provider allows for a direct connection to Microsoft SQL Server that doesn't require OLE DB, ADO, and ODBC (Open Database Connectivity). In fact, the SQL Managed Provider connection is managed by the same Common Language Runtime (CLR) environment that manages ASP.NET scripts. Therefore the connection and interaction between SQL Server and a DataSet is optimal.

Both Managed Providers perform three tasks:

• They provide protocols to connect to data stores and create and interact with DataSets.

• They create a Data stream for fast access to data stores. (A Data stream is similar to a DataSet but faster with less functionality.)

• They create connected objects that execute database-specific commands.

DataSet

As we've mentioned, the DataSet is an independent representation of data pulled from a data source. The DataSet completely replaces the Recordset object used in traditional ASP and ADO. Although useful in its time, the Recordset could not deal with multiple sets of data or maintain data relationships. The DataSet can not only manage multiple sets of data, but maintain complex data relationships between those sets of data. In addition, the DataSet provides a consistent programming interface for developers to access any type of data. As shown in Figure 15.1, the DataSet itself is made up of a hierarchy of objects.

Figure 15.1: The hierarchy of objects in a DataSet

Yes, we know. Object hierarchies don't make a lot of sense until they are put into perspective. Imagine a neighborhood yard sale at your house. You and your neighbors bring items and drop them on a clothes table and a toys table. Obviously, someone must keep track of each item and its relationship-who originally owned the item and the purchase price once it's sold. The DataSet can accomplish all this.

The clothes table and the toy table are represented in a DataSet by a Clothes DataTable object and a Toys DataTable object in the TablesCollection object. In addition, there is an Owner Data- Table object. To define the relationships between the three data tables, the DataSet uses custom DataRelation objects held in the RelationsCollection object. The RelationsCollection object allows you to select from each table through simple queries. You no longer are required to craft complex joins to simply interact with data. The DataRelations object manages all the constraints and relations between the data tables.

Although simplistic and perhaps forced, our neighborhood yard sale example effectively illustrates the potential of the ADO.NET DataSet. The DataSet is indifferent to where the information items come from, how they were and are used, or where the data is going. The DataSet only cares how data is defined and related within its framework-which you specify, of course.

Using DataSets

You can use a DataSet in your ASP.NET pages in three ways-to hand-code an empty DataSet and fill it with your own data, to hand-code a DataSet with data from a data source, or to use the Dream- weaver MX custom tag MM:DataSet to create a DataSet from a defined connection. You will see that the custom tag MM:DataSet is by far the easiest way to create a DataSet. However, you need to know at least the basics of hand-coding a DataSet in ASP.NET. First, we'll demonstrate how to hand- code a DataSet in memory, and then we'll show you how to hand-code a DataSet from a connected data source. Finally, we'll demonstrate how to use the Dreamweaver MX MM:DataSet custom tag.

Creating a DataSet in Memory

To demonstrate how to create a DataSet, let's take a look at an ASP.NET script page that creates a new DataSet in memory and fills it with data sans any connection to a database. To make matters simple, let's create the Owner's table we mentioned in our earlier neighborhood yard sale example. Listing 15.1 shows the sample ASP.NET VB.NET script file.

Listing 15.1: DATASET_CREATE.ASPX

<%@ Page Language="VB" ContentType="text/html" ResponseEncoding="iso-8859-1" %>  <%@ import Namespace="system.Data" %>  <%@ import Namespace="system.Data.OLEDB" %> <script runat="server">   Sub Page_Load(Sender As Object, E As EventArgs)     'Create a DataSet     dim ds as new DataSet("MyDataSet")     'Create an instance of a DataTable     dim dTable as New DataTable("Owner")     'Create the columns FirstName, LastName, OwnerID  dTable.columns.Add("FirstName",System.Type.GetType("System.String"))  dTable.columns.Add("LastName",System.Type.GetType("System.String"))     dTable.columns.Add("OwnerID",System.Type.GetType("System.Int32"))     dTable.columns("OwnerID").AutoIncrement = True     'Add the DataTable to the DataSet     ds.Tables.Add(dTable)     'Define the OwnerID column as the PrimaryKey     dim keys() as DataColumn = {ds.Tables("Owner").Columns("OwnerID")}     ds.Tables("Owner").PrimaryKey = keys    'Create an instance of a DataRow and add values     dim dr as DataRow = dTable.NewRow()     dr(0) = "Bryan"     dr(1) = "Chamberlain"    'Add the DataRow to the DataTable    dTable.Rows.Add(dr)   End Sub  </script>

 On the CD-ROM   We strongly encourage you to key in all the code to create the example files yourself. To check your work, compare your files with the example files on the CD accompanying this book.

As you can see, the first step is to identify VBScript as the language we'll be using in this ASP.NET script page. Next, we import the appropriate namespaces to create our DataSet and eventually establish a connection to a database. To do so, we use the following lines:

<%@ import Namespace="system.Data" %>  <%@ import Namespace="system.Data.OLEDB" %>

At this point, we need a page event that loads and executes our DataSet code. For that, we use the

Page_Load event. As you recall from the ASP.NET chapter, the Page_Load event is triggered every time a page is loaded. The following code creates the event response.

<script runat="server">    Sub Page_Load(Sender As Object, E As EventArgs) ...    End Sub </script>

Now create a DataSet object to eventually hold a DataTable object called Owner inside the Page_Load procedure. Next, create three columns in the Owner DataTable-OwnerID, FirstName, and LastName. Adding columns requires two parameters, the column name and data type. Notice that we use the AutoIncrement command to set the OwnerID column to increment automatically

   'Create a DataSet    dim ds as new DataSet("MyDataSet")    'Create an instance of a DataTable    dim dTable as New DataTable("Owner")    'Create the columns FirstName, LastName, OwnerID  dTable.columns.Add("FirstName",System.Type.GetType("System.String"))  dTable.columns.Add("LastName",System.Type.GetType("System.String"))    dTable.columns.Add("OwnerID",System.Type.GetType("System.Int32"))    dTable.columns("OwnerID").AutoIncrement = True 

The next few lines add the Owner DataTable to the DataSet and set the OwnerID column as the PrimaryKey.

 'Add the DataTable to the DataSet  ds.Tables.Add(dTable)  'Define the OwnerID column as the PrimaryKey  dim keys() as DataColumn = {ds.Tables("Owner").Columns("OwnerID")}  ds.Tables("Owner").PrimaryKey = keys

The final step is to create a DataRow object, fill it with data, and add it to the DataTable. The following lines accomplish this:

 'create an instance of a DataRow and add values  dim dr as DataRow = dTable.NewRow()  dr(0) = "Bryan"  dr(1) = "Chamberlain"  'Add the DataRow to the DataTable  dTable.Rows.Add(dr)

Creating a DataSet from a Data Source

Now we've created a DataSet in memory that your ASP.NET script page can access, manipulate, and even send to a database. You've got to admit, creating a DataSet and adding DataTable objects is rather easy. Let's move on to pushing data from a database into a DataSet. To demonstrate, we'll create a script page that pulls the CategoryID and Category columns of data from the tblCategories table in our sample Books database. You can see the code in Listing 15.2.

Listing 15.2: DATASET_DATACONNECTION.ASPX

<%@ Page Language="VB" ContentType="text/html" ResponseEncoding="iso-8859-1" %>  <%@ import Namespace="system.Data" %>  <%@ import Namespace="system.Data.OLEDB" %> <script runat="server">    Sub Page_Load(Sender As Object, E As EventArgs)    'Create a DataConnection    dim myconnection as new OleDbConnection("Provider=SQLOLEDB.1;Persist_    Security Info=False;User ID=SA;Password=XXXXXXX;Initial_    Catalog=MDWMX_BOOKS;Data Source=192.168.1.100")    'open connection    dim myCommand as new OLEDBDataAdapter("Select * from tblCategories",      myconnection)    'Fill DataSet    dim ds as DataSet = new DataSet()    myCommand.Fill(ds,"tblCategories")  End Sub  </script> 

As before, the script file begins by defining the default language and importing the Data and Data.OLEDB namespaces that allow us to create DataSets and connect to our database. Also as before, we position our DataSet code inside the Page_Load event. Next, we introduce two new objects to connect and pull data from our database-the OleDBConnection object and the OleDBDataAdapter object. Obviously, the OleDBConnection object creates the connection to our database. The OleDBData- Adapter object allows us to pass an SQL command to the database and retrieve data. As you can see in the code, the following lines create our data connection to an SQL Server version of our Books database.

'Create a DataConnection  dim myconnection as new OleDbConnection("Provider=SQLOLEDB.1;_ Persist Security Info=False;User ID=SA;Password=XXXXXXX;_  Initial Catalog=MDWMX_BOOKS;Data Source=192.168.1.100")

Then we use the OleDBDataAdapter object in the following lines to pass an SQL statement and return data.

'open connection  dim myCommand as new OleDbDataAdapter("Select * from tblCategories", myconnection)

At this point, we have only to fill a DataSet with the returned data of the OleDBDataAdapter. Doing so is even easier than our previous example of creating a DataSet. We can use the Fill command to literally dump the returned data of our SQL statement into a DataSet. The following lines direct the OleDBDataAdapter to create a DataTable in the Tables collection identical to the returned data from our SQL statement. The OleDBDataAdapter even names the DataSet for us.

'Fill Dataset  dim ds as DataSet = new DataSet()  myCommand.Fill(ds,"tblCategories")

The Dreamweaver MX MM:DataSet Custom Tag

Dreamweaver MX uses a custom tag to allow you to simplify the normal demands of coding an ASP.NET DataSet. MM:DataSet allows you to easily script and use DataSets in your ASP.NET pages. MM:DataSet also allows you to omit most of the required ASP.NET DataSet properties by setting these properties for you. The only required properties are ConnectionString, CommandText, and the standard RunAt ASP.NET setting. ConnectionString defines the connection to your database.

CommandText defines the SQL statement or stored procedure to pull data from your database through the database connection. RunAt is the ASP.NET setting instructing the server to run the control at the server. Besides these three required properties, there are a multitude of other properties you should know in order to take full advantage of the MM:DataSet custom tag. Table 15.1 describes the MM:DataSet properties.

Table 15.1: MM:DATASET PROPERTIES

Property	Default	Description
ConnectionString		[Required] Defines the connection string to connect to your database.
CommandText		[Required] Defines the SQL or stored procedure name to forward to the database. If IsStoredProcedure is False, Dreamweaver MX expects SQL. If IsStoredProcedure is True, Dreamweaver MX expects the name of the stored procedure. In place of variable names, you can use the question mark (?) as a placeholder for a parameter. For example, Select from tblCategories where categoryID = ?.
RunAt		[Required] Standard ASP.NET setting instructing the server to run the control at the server.
CreateDataSet	True	Specifies whether the server will preserve the DataSet created from the SQL in the CommandText property. For example, you definitely want to preserve the DataSet created from a SELECT statement. However, SQL commands such as INSERT, UPDATE, and DELETE do not produce results. Therefore, you want to set CreateDataSet to False.
Expression	True	Specifies under what condition the CommandText property is executed. For example, you want INSERT or UPDATE CommandText to execute only if all required fields are validated.
IsStoredProcedure	False	Specifies whether CommandText is sending SQL or specifying a stored procedure to execute.
TableName	"theTable"	Defines a name to use when you refer to records in a DataTable listed in the DataSet. For example DS.theDS["Categories"].DefaultView references the default view of the DataTable categories.
Debug	False	True activates debugging on pages that throw error exceptions. If True, the FailureURL property is ignored.
FailureURL	""	Defines a URL in which the server redirects a user if an error exception is thrown.
SuccessURL	""	Defines a URL in which the server redirects a user if no error exceptions are thrown.
CurrentPage	0	Defines the index of the displayed page based on the total number of records divided by the number of records defined in PageSize. For example, if PageSize is 10 and the total number of records is 40, the index of the current page could be 0, 1, 2, or 3. Each indexed page displays 10 records each from the total number of records beginning with the specific StartRecord. The StartRecord is always calculated from the index of CurrentPage ´ PageSize. So, if the CurrentPage index is 2 and the PageSize is 10, the calculated StartRecord is 20.
PageSize	0	If CreateDataSet is True, defines the number of records to retreive from the DataSet and display on your page.
MaxRecords	0	Defines the total number of records to retrieve.
StartRecord	0	Defines the record number you want to start collecting from the DataSet and display on your page.
GetRecordCount	True	If PageSize is greater than 0, defines the total number of records. GetRecordCount is performance heavy and should be avoided if possible.
RecordCountCommandTest	""	If PageSize is greater than 0 and GetRecordCount is True, sets an SQL statement to obtain a record count to improve performance. For example, SELECT COUNT (CategoryID) as MYRECORDCOUNT returns the number of records in the table.
DefaultView		[Read Only] Calls the default view of the DataSet through theDS.Tables[0].DefaultView.
EndRecord		[Read Only] The number of the final record of the collected records calculated by (CurrentPage + 1) ´
LastPage		[Read Only] If CreateDataSet is True and GetRecord- Count is True, the total number of records in the dataset is calculated and returned from the CommandText.
RecordCount		[Read Only] If PageSize is greater than 0 and GetRecord- Count is True, the index of the final page in the index is calculated from PageSize and RecordCount.
TheDS		If CreateDateSet is True, the .NET DataSet to hold records is retrieved as a result of CommandText.

To use the DataSet Dreamweaver MX custom tag, you must properly register the tag in your ASP.NET script page. Fortunately, as you choose a Dreamweaver MX custom tag from the Dream- weaver MX interface, Dreamweaver MX automatically inserts the following code into your ASP.NET page for you.

<%@ Register TagPrefix="MM" Namespace="DreamweaverCtrls"_  Assembly="DreamweaverCtrls,version=1.0.0.0,_  publicKeyToken=836f606ede05d46a,culture=neutral" %> 

As you can see, the code references a special file named DreamweaverCtrls.dll. This file contains all the functionality of Dreamweaver MX custom tags. Obviously, your web application must have access to DreamweaverCtrls.dll. Therefore, you must be sure DreamweaverCtrls.dll is in the binfolder of your website or virtual directory. You can do this in two ways:

• Copy DreamweaverCtrls.dll from the Configuration/ServerBehaviors/Shared/ASP.Net/Scripts directory of your Dreamweaver MX application directory to the bin folder of your website.

• Use the Dreamweaver MX Site Manager to deploy the DreamweaverCtrls.dll to your site. To do so, choose Deploy Supporting Files from the Site menu in the main Dreamweaver MX document window menu bar, choose Site ® Deploy Supporting Files to open the Deploy Supporting Files To Testing Server dialog box, as shown in Figure 15.2. Set the access method and the directory path to the bin directory of your website, and click Deploy.

  
  Figure 15.2: The Deploy Supporting Files To Testing Server dialog box in Dreamweaver MX

The next step is to create a connection for a DataSet to pull data from a database. In Chapter 10, we demonstrated how to create a database connection. Using Chapter 10 as a reference, open a new ASP.NET script page and create a data connection to the Books database. Once you create and test your database connection, you can use the Dreamweaver MX MM:DataSet custom tag to create a DataSet and add it to your ASP.NET script page.

To do so, follow these steps:

1. Open the DataSet dialog box in one of the following ways:

   • Activate the Application panel group and click the DataSet icon.

   • Choose Insert ® Application Objects ® DataSet.

   • Activate the Bindings panel and choose DataSet(Query) from the panel menu.

   You can view the DataSet dialog box (shown in Figure 15.3) in two modes-Simple or Advanced. In either mode, you can define the DataSet name, the data connection, the target table or columns, and the Failure page to redirect your users to in case of DataSet error. In Simple mode, you can select a table and columns and leave Dreamweaver MX to build the SQL statement to access your database. In Advanced mode, you can dynamically build the SQL query.

   
   Figure 15.3: Creating a DataSet

2. In the mode you're most comfortable with, enter DS in the Name text box, and choose your created data connection.

3. Dreamweaver MX queries the data source and returns a list of tables and columns in the Table drop-down list box. For our purposes, set the mode to Simple, select dbo.tblCategories from the Table drop-down list box, and click the All option in the Columns section.

4. Click OK to create the DataSet and add the DataSet code to your ASP.NET script page.

Listing 15.3 shows the Dreamweaver MX-generated code that is inserted into your script page. In addition, Dreamweaver MX inserts the DS in the Bindings panel, as shown in Figure 15.4.

Listing 15.3: MMDATASET_CUSTOMTAG.ASPX

<%@ Page Language="VB" ContentType="text/html" ResponseEncoding="iso-8859-1" %>  <%@ Register TagPrefix="MM" Namespace="DreamweaverCtrls"_  Assembly="DreamweaverCtrls,version=1.0.0.0,_  publicKeyToken=836f606ede05d46a,culture=neutral" %> <MM:DataSet    runat="Server"  IsStoredProcedure="false"  ConnectionString='<%# System.Configuration.ConfigurationSettings._  AppSettings("MM_CONNECTION_STRING_connnorbert") %>'  DatabaseType='<%# System.Configuration.ConfigurationSettings._  AppSettings("MM_CONNECTION_DATABASETYPE_connnorbert") %>'  CommandText='<%# "SELECT * FROM dbo.tblCategories" %>'  Debug="true" > </MM:DataSet>  <MM:PageBind runat="server" PostBackBind="true" /> <html> <head> <title>MMDataSet</title>  <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">  </head> <body> </body>  </html>

Figure 15.4: The DataSet shown in the Bindings panel