When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
Related Web Technologies
User Tips!
Coding Tips

Sample Chapters
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Stump the SQL Guru!
XML Info
Author an Article
Print this page.
Published: Friday, September 15, 2000

Data Access with ASP.NET, Part 2

  • Read Part 1

  • In Part 1 we looked at how to access data in an ASP.NET page through old-school ADO. In this part, we'll examine using ADO+!

    - continued -

    Stepping up to ADO+
    ADO+ differs from ADO not only in syntax, but also in style and purpose. ADO+ focuses on disconnected data. When using ADO, it helps to think of data access involving the following steps:

      1.) Open a connection to a database
      2.) Ask for a subset of data (via a SELECT statement, perhaps)
      3.) Step through this information
      4.) Close the connection to the database

    With ADO+ there is a subtle difference. With ADO+ there is the notion of DataSets. DataSets can be thought of as a gelatinous blob of data that is disconnected from the database. With ADO, you connected to a database, retrieved a recordset, and stepped through it, closing the database connection once you completed your tasks with the data. With ADO+, the following steps are taken:

      1.) Open a connection to a database
      2.) Ask for a subset of data (via a SELECT statement, perhaps)
      3.) Shove that data into a DataSet
      *** Return to Step 2 if you are interested in squeezing more data into the DataSet***
      4.) Close the database connection
      5.) Do what you please with the gelatinous DataSet

    DataSets serve as a disconnected holding-place for various chunks of data. DataSets are physically represented via XML, and can be easily passed around to various applications on various platforms due to its universal structure. DataSets can also house related data, such as parent-child relationships, all squished together into the proper hierachical format.

    I like to think of a DataSet as a big jello brick and a database as a bunch of grapes. Say that you are interested in obtaining a certain number of grapes, perhaps all grapes larger than an inch in diameter. With ADO, you'd pick one grape at a time, handing them to the person who was interested in reviewing these large grapes. With ADO+, you would pick the grapes, shoving them into the jello brick. Once you had picked the grapes from the bunch that had met the criteria, you'd hand the jello brick to the interested party. The nice thing about the gelatinous nature of a DataSet (the jello brick) is that the person could also ask for other information that could also be nestled into the jello brick and passed along...

    OK, enough of this high-level, jello brick-talking nonsense, let's get back into the concrete!

    The ADO+ Object Model at a Glance
    There are many ADO+ objects, but, for this article, we will focus on the basic objects that will get you up and running. The first object we'll look at is the ADOConnection object. This object, as its name suggests, is used to connect to a database. To use this object (or any of the other objects we'll be examining in this article), we will need to include two namespaces in our ASP.NET page: System.Data and System.Data.ADO. These can be included in an ASP.NET page using the Import directive like so:

    <%@ Import Namespace="System.Data" %>
    <%@ Import Namespace="System.Data.ADO" %>

    The ADOConnection object contains Open and Close methods similar to ADO's Connection object. Below is a simple example that connects to a local SQL Server's pubs database:

    <%@ Import Namespace="System.Data" %>
    <%@ Import Namespace="System.Data.ADO" %>
      'Set the connection string...
      Dim strConnString as String
      strConnString = "Provider=SQLOLEDB; Data Source=(local); " & _
                      "Initial Catalog=pubs; User ID=sa"
      'Create an ADOConnection instance
      Dim objConn as ADOConnection
      objConn = New ADOConnection
      'Set the ADOCOnnection object's connection string property
      objConn.ConnectionString = strConnString
      objConn.Open()  'Open the connection
      'Work with the database
      objConn.Close()    'Close the connection
      objConn = Nothing  'Clean up!  :-)

    Pretty simple and straightforward. Nothing to alien to ADO... yet... :-)

    To SQL or Not to SQL?
    When connecting to a database through an ASP.NET page there are two routes that you can take: use ADO or use the native SQL provider. The native SQL provider is faster, but the catch is you must be using Microsoft SQL Server as your database. If you are going to be using Access, Excel, a comma-delimited file, or some other data source, you must use the ADO provider. (You can use the ADO provider just fine with SQL Server, however it is not as fast as the native SQL provider.) In this article, I will just concentrate on using the ADO provider.

    The second object we need to look at is the ADODatasetCommand object. This object is responsible for creating the disconnected Datasets that we discussed earlier. Another similar object that is equally important is the Dataview object. This object represents a particular view of the Dataset object. Remember that a Dataset object can contain all sorts of data in all sorts of related ways. To return to our jello analogy, a Dataview is a handy way to look at a particular chunk of jello in the overall jello brick.

    To populate a Dataset object we use the ADODatasetCommand object like so:

    <%@ Import Namespace="System.Data" %>
    <%@ Import Namespace="System.Data.ADO" %>
        '... assume an ADOConnection, objConn, has been created and opened
      'Create a SQL string
      Dim strSQL as String = "SELECT * FROM authors"
      'Create an instance of the ADODatasetCommand and Dataset objects
      Dim objDSCommand as ADODatasetCommand
      Dim objDataset as Dataset
      objDataset = New Dataset
      objDSCommand = New ADODatasetCommand(strSQL, objConn)
      'Populate the dataset object with the results of the SQL query
      '... Name the dataset "Author Information"
      objDSCommand.FillDataSet(objDataset, "Author Information")

    OK, great, we have our gelatinous Dataset brimming over with the information from the ADODatasetCommand query. Now... we need to display this information! We'll look at how to accomplish this in Part 3!

  • Read Part 3

  • ASP.NET [1.x] [2.0] | ASPFAQs.com | Advertise | Feedback | Author an Article