From the November 2001 issue of MSDN Magazine.

ith the release of Microsoft® .NET just around the corner, developers are gearing up to take advantage ot the many features, increased interoperability, and better data integration that the new framework promotes. While COM and Visual Basic® 6.0 are still widely used and will likely live on for years to come, I've met many developers who are anxious to get moving with the .NET components. Some of the questions I am asked most often regarding .NET are how developing with ActiveX Data Objects (ADO) 2.x will change under ADO .NET and how can you still abstract ADO into a data service component. So, in this month's Data Points column, I'll try to tackle these issues.
      The architecture of ADO .NET provides a rich framework that you can use to manipulate data from C#, Visual Basic .NET, or any other .NET-compliant language. In the last installment of this column, I demonstrated a technique of encapsulating a data services component using ADO 2.x and Visual Basic 6.0. But, of course, the concept of abstracting the data services from the business rules is not unique to Visual Basic 6.0. The same methodology applies to developing under .NET, so in this month's column I'll wade through the ADO .NET melee and review its impact on .NET development. Then I'll demonstrate how to abstract ADO .NET into a distinct data service, thus keeping the intricacies of ADO .NET separate from your ASP .NET business code.
      The examples I'll walk through will utilize three basic entities:

• A Web Form that contains server controls such as the asp:DataGrid to display your data
• A CodeBehind class that retrieves data from the data service tool and fills the Web Form's grids with data
• The source code for your own namespace and classes that make up the data service tool

      Many developers are debating which .NET language to use. Even though .NET was designed so that no particular language has any significant advantage over another, it is a good idea to try to keep your code to one flavor. So, just in case you're still debating which language to use, as an added bonus I'll demonstrate these .NET examples using both Visual Basic and C#. But also keep in mind that these languages allow interoperability between assemblies written in each other. In other words, you can create an assembly written in C# and call it from a Visual Basic .NET class.

The Web Form

      I'll start by looking at a simple Web Form that contains the presentation of my application. Shown in Figure 1, the code WebForm.aspx contains two DataGrid server controls that will be filled with data from two different ADO .NET DataSet objects. The DataGrid is a server control that is easily manipulated to render a rowset in an HTML table. (For more information on making the most out of the DataGrid, refer to the April, May, and June Cutting Edge columns by Dino Esposito in MSDN® Magazine.)
      There are two DataGrid server controls in my Web Form. The first DataGrid (grdSql) will be populated by running a SQL statement using the data services tool. The second DataGrid (grdProc) will be populated by running a stored procedure through the data services tool.
      Notice that the Web Form shown in Figure 1 contains only directives, HTML, and server controls. There is no code to be found here. In the .NET Framework, the code for a user interface can be separated from the interface itself. In other words, all of the HTML and server controls can go inside of a Web Form and all of the code that interacts with the user interface can go inside of a separate class file. This allows two developers to work on the code without running into each other. There is the option of intermixing the code and the interface, which is what standard ASP dictates. But it can be much easier to manage a project when the code and the interface are separated.
      Linking a Web Form to the code that interacts with it involves specifying in the Web Form where the linked code file (otherwise known as CodeBehind) is located. (For more information on CodeBehinds, refer to the August 2001 Cutting Edge column by Dino Esposito.) The @Page directive has an attribute called CodeBehind, as shown in the first line of code in Figure 1. This is where you can link the Web Form to its source code. The @Page directive also has an Inherits attribute which indicates the namespace and class that the Web Form inherits.
      The C# version of this page differs only slightly from its Visual Basic counterpart. In fact, only the first line of the page is different. For example, the Page Language attribute is set to C# instead of Visual Basic. The CodeBehind attribute points to the WebForm.aspx.cs file, and the Inherits attribute points to the MyCSDataLayer.WebForm class. The reason there are few language-specific differences in these files is that all of the supporting code for these pages is in CodeBehinds.

The CodeBehind Page

      Looking at the beginning of the Visual Basic CodeBehind for the WebForm.aspx page (shown in Figure 2), you'll notice that the System.Data namespace is explicitly referenced using the Imports keyword. This namespace provides the page with access to the DataSet and DataView objects. I'll retrieve a DataSet from the data services tool; the DataView will be used to bind the DataSet's data to the DataGrid controls.
      Next, the WebForm class is declared as inheriting the System.Web.UI.Page namespace. This allows you to refer directly to objects in the page. Then, declare the two DataGrid controls with the same name as the CodeBehind's presentation file's controls. This allows you to interact with the DataGrid controls from this CodeBehind page. Notice that the variables are defined as Protected. This declares these elements to be accessible only from within the same class, or from a class derived from this class. You should also declare the constructor for the WebForm. If you wanted some code to run when the form is instantiated, this is where you would put it.
      Now that I have defined the basic parts of the CodeBehind, I'll take a close look at the main event: the Page_Load event (shown in Figure 3). Here is where I declared the DataSet object, which will hold the resultset from each call to the data services tool. You may also notice that this event handles the MyBase.Load event. In other words, the Page_Load event overrides the Load event for this page.
      Here I declared the data services tool, MyVisual BasicDataLayer.SqlService, as the oSQL variable. I then created an instance of the SqlService class using the four argument constructor, passing in the values needed to establish a connection. This constructor sets the connection string property of the SqlClient.Connection object that is contained within the SqlService class.
      Next, I defined the SQL statement and passed it into the RunSql method. This method runs the SQL and generates a DataSet, which is returned from the method to the oDs variable. The DataSource of the DataGrid (grdSql) is then set to the default DataView of the Category table within the DataSet. Now that I've told the DataGrid where to get its data, I issue the DataBind method of the DataGrid. This binds the data directly to the first DataGrid in the WebForm.aspx page.
      One of the main reasons to create a data services tool is to make data retrieval easier. As you can see, with only a few lines of code, I've done just that. By not using the SqlClient namespace's objects directly from within this page, I can also encapsulate specific settings within the custom methods. For example, you may want to create a single method that executes a SQL statement whether or not the SQL returns a resultset. By overloading a method, you can do this within your SqlService class. The only System.Data objects that I used are the DataSet and the DataView. Therefore, I have abstracted all of the SqlClient namespace's objects into the data service tool. If I wanted to take this one step further, I could return just the XML from the resultset and fill the grid with it. This would completely remove the need for referencing the System.Data namespace in the CodeBehind.
      While the first DataGrid is populated from a SQL statement, the second DataGrid is populated using a stored procedure. I passed my parameters into the data service tool by using its AddParameter method (see Figure 3). The name of the parameter, its data type, length, and value are all passed into the constructor of the AddParameter method. Notice that I used a custom enumerator defined within the SqlService namespace to identify the data type of the parameter. Then I issued the RunProc method, which runs the stored procedure and returns a DataSet.
      Take a look at how this code works in the C# example. The namespaces are references with the using keyword. Then I defined the namespace which I'll use in the project. The next difference is in the way I declared the class for the WebForm. In C#, the way you instruct a class to inherit from another class is by specifying the base class after the new class's definition, separated by a colon (as shown in Figure 4).
      Other than the basic syntax differences of using a semicolon to end a statement, destroying objects by setting them to null (instead of nothing), and declaring variables by preceding the variable name with the datatype or class, the code is quite similar as you can see in Figure 5. Just keep in mind that C# is case-sensitive. It is an easy mistake to make, especially if you came from the world of Visual Basic.

The SqlService

      The SqlService class defines the data service component, including all of its methods, properties, and enumerators. I started the SqlService by referencing the namespaces I'll be using.

  Imports System
  

  Imports System.Data
  

  Imports System.Data.SqlClient
  

  Imports System.Data.SqlTypes
  

  Imports System.Collections
  

      Then I defined an enumerator that will be used by the calling code (in this case, the CodeBehind). This enumerator outlines the different datatypes that can be passed into the AddParameter method. By using the enumerator, I can keep the calling program's code from having to use the ADO .NET-specific namespaces for the SqlDataTypes.

  Public Enum ssenumSqlDataTypes
  

      ssSDT_Bit
  

      ssSDT_DateTime
  

      ssSDT_Decimal
  

      ssSDT_Integer
  

      ssSDT_Money
  

      ssSDT_String
  

  End Enum
  

      The next step is to define the SqlService class itself (see Figure 6). Here are declared the class-level variables which will hold the values used to establish the connection to the data source. I also defined an ArrayList object so I could use it to store the parameters that are passed into the AddParameter method when a stored procedure is used.
      By creating different constructors, I can give the calling program options on how to instantiate the SqlService class. To define multiple constructors, I simply defined the constructors to each have a different number of arguments and/or argument datatypes.
      There are three different constructors that are defined for the SqlService class. The first constructor has no arguments and therefore just instantiates the class with no property settings. The second takes a connection string as its only argument. If you pass one argument to the SqlService constructor, this code will execute and set the value of the argument to the connection string variable. The third and final constructor takes four arguments. These arguments represent the database server name, the database name itself, and the user ID and password for the database server (see Figure 7).
      There are also public properties defined for the SqlService class. I defined these as an alternative way for the calling program to set the connection string values. This can be used to set the connection string properties instead of using one of the constructors.
      The RunSql method, shown in Figure 8, is an overloaded method that accepts the SQL statement to execute and the table name of the DataTable that will be retrieved. The method also returns a DataSet to the calling program. The second version of the RunSql method, shown in Figure 9, only accepts the SQL statement to execute as an argument and has no return value. Both of these methods execute the SQL statement, but the former can be used to retrieve data, while the latter should be used to execute an action query (also known as a SQL Insert, Update, or Delete). By overloading the RunSql method, you can create one method name that chooses which method to run based on the parameters that are passed to it. In this case, if you pass just a SQL statement to the method, the latter RunSql method will be triggered.
      In the RunSql method shown in Figure 8, (the one that returns a DataSet), I declared and instantiated my ADO .NET objects. Then, I set up the SqlConnection object by calling the Connect method. (The SqlConnection object can be considered the counterpart to the ADO 2.x ADODB.Connection object.) Now that the SqlConnection has its properties set, but is not yet opened, I can set the properties of the SqlCommand object. (The SqlCommand object can be considered the counterpart of the ADO 2.x ADODB.Command object.) Next, I set the SelectCommand property of the SqlDataAdapter object to be the SqlCommand we just created from the SQL statement. This tells the SqlDataAdapter to run this SqlCommand object when retrieving data. There are other types of commands (InsertCommand, UpdateCommand, and DeleteCommand) that I can set, but that is a topic I'll dive into in a future column. Finally, I issued the Fill method of the SqlDataAdapter in order to fill the DataSet object with the resultset from the SelectCommand and the data is returned via the DataSet return value.
      The SqlDataAdapter (formerly known as the SqlDataCommand in ADO .NET Beta 1) doesn't relate directly to any of the objects from the ADO 2.x architecture. It is used as a tool that manages four different commands (SELECT, INSERT, UPDATE, and DELETE) that can be executed against a DataSet. Basically, the SqlDataAdapter is the bridge between the connected objects (the SqlCommand and the SqlConnection) and the disconnected object (the DataSet).
      A DataSet is roughly related to the ADO 2.x Recordset object in that it contains the data from my queries. However, a DataSet can contain multiple sets of data contained within what is known as a DataTable. For example, a DataSet can contain three DataTables that are all related to one another, such as a DataTable of customers, a DataTable of the customers' orders, and a DataTable of the orders' details. Also, a DataSet doesn't know which data source it came from, which is very unlike an ADO 2.x Recordset object. In fact, there are more differences between a DataSet and Recordset than there are similarities. Other features of a DataSet are: its Extended properties, which you can use to define custom properties of your own; the DataRelations, which you can use to relate one DataTable to another; and the XML features which allow you to export or import XML in and out of a DataSet.
      If you are not expecting a DataSet back from the method and are just issuing an action query, you can use the RunSql method shown in Figure 9. This method accepts a SQL statement and establishes a SqlConnection. Then the properties for the SqlCommand are set and the ExecuteNonQuery method is issued. This method issues the SQL statement but also tells ADO .NET that it is not expecting any data back from the SQL call. (This is similar to calling the ADO 2.x Connection.Execute method with the adExecuteNoRecords option.) If you are not expecting any data back, it is more efficient to use the ExecuteNonQuery method.
      To go along with the RunSql methods, I have two overloaded versions of the RunProc method. The first RunProc method (see Figure 10) returns a DataSet and the second (see Figure 11) does not return anything. The first RunProc method works much the same way as the RunSql method, other than where I established its parameters. The parameters are stored within an ArrayList called m_oParmList. To iterate through the parameters, an instance of the IEnumerator class is defined as oEnumerator. This will allow you to loop through the parameters in the ArrayList and add them to the SqlParameters collection.
      The loop iterates until the MoveNext method of the IEnumerator returns a false value, which occurs when there are no more parameters in the ArrayList. The oP variable is set to reference the current iteration of the Arraylist. Then I converted the parameter into the format that ADO .NET understands, a SqlParameter object. Finally, I added the SqlParameter object to the SqlParameters collection of the SqlCommand object and continued through the loop. Once all of the Parameters were added to the collection, I issued the SQL statement and filled a DataSet using the SelectCommand and Fill methods of the SqlDataAdapter, just like I did with the RunSql methods.
      The second RunProc method, shown in Figure 11, is used to execute action queries and thus does not return a DataSet. Again, I used the Overloads keyword to tell Visual Basic that I wanted this method to be called if a single argument is passed to it. Otherwise, if two arguments are passed to this method, the other RunProc method is triggered (see Figure 10).
      The AddParameter method can be used to add parameters to the SqlService class when you want to execute a stored procedure (see Figure 12). It accepts the parameter name, its datatype, length, and value. Those settings are put into a local Parameter object and put into the ArrayList. This is the same ArrayList that I iterated through in the RunProc method. Notice that I translated the datatype that is passed (which is one of the ssenumSqlDataTypes enumerator's values) into the appropriate SqlDBType.

The C# SqlService

      The C# version of the SqlService does the same job, but uses a different syntax. The complete code for both of these examples (Visual Basic and C#) can be downloaded from the link at the top of this article. The RunSql method shown in Figure 13 shows the C# method that executes a SQL statement and returns a DataSet. One key feature to notice here is that the return value is indicated by the datatype of the public method. Also, even though the method is overloaded (there are two RunSql methods), there is no need to indicate it as such like there is in Visual Basic when using the Visual Basic Overloads keyword. In C#, to overload a method you simply define two methods, each with a different argument list. All of the code is identical to the Visual Basic version in its purpose. The only differences are the basic C# syntax nuances.
      There is a second RunSql method that does not return a DataSet and is intended to be used for action queries. To define a method as not having a return value, you declare the method as the void datatype as such:

  public void RunSql(string sSql)
  

      The RunProc method (see Figure 14), shows the C# method that executes a stored procedure and returns a DataSet. Once again, all of the code is identical to the Visual Basic version in it's result. The only differences are the basic C# syntax nuances.
      There is a second RunProc method that does not return a DataSet and is intended to be used for action queries. To define a method as not having a return value, you declare the method as the void datatype:

  public void RunProc(string sProcName)
  

Executing the Code

      Now that you've examined the code, all you have to do is browse to the WebForm.aspx. Figure 15 shows the results of running the WebForm.aspx. (Both the Visual Basic and C# versions of this example will display the same output.) Since the data is bound to the DataGrid controls, you don't have to worry about looping through the records and writing them to HTML. This is a vast improvement over traditional ASP as you now have an object model for ASP that you can manipulate.


Figure 15 WebForm.aspx

Wrapping Up

      The data service component in this column abstracts the ADO .NET-specific code to a separate namespace and class. This allows you to keep your ADO .NET code separate from your business logic. It also makes all of your ADO .NET calls uniform since all calls use the same settings and style in executing SQL and stored procedures.
      Of course, this namespace could be extended to incorporate the System.Data.OleDb namespace as well. The examples in this column use the SqlClient namespace, which is faster when used against the SQL Server database than is the OleDb namespace. However, if your application needs to connect to non-SQL Server databases, you can simply create a SqlService that makes use of the System.Data.OleDb namespace, or you could extend the SqlService class to include both the OleDb and the SqlClient namespace techniques. The option is up to you; however, if you do not have to connect to non-SQL Server databases, I suggest sticking with the SqlClient namespace.
      This code in this column demonstrates how easy it is to abstract your own ADO .NET namespaces under the .NET Framework in either C# or the Visual Basic .NET languages. Feel free to download the sample code and add your own methods as needed. In future columns, I'll explore the ADO .NET techniques in further detail, including how to make use of the SqlDataAdpater's four different commands, how to use DataSets to pass data between the presentation layer and the business layer, and how to establish relations among multiple DataTables within a DataSet.