An Overview on ADO.NET

Introduction

This article describes Data Access in .NET. As such, it briefly mentions ADO and its shortcomings in order to encourage the use of ADO.NET.

Data Access using .NET

ADO (ActiveX Data Objects) is a library of components that consists primarily of the Connection, Command, RecordSet, and Field objects. Using ADO, a physical connection is made to the database. Those familiar with ADO know that data is transferred between recordsets. The recordset contains data in tabular form. Whether the recordset includes information from one or many tables in the database, the data is still returned in the form of rows and columns. ADO did not address the issues involved with a disconnected recordset. ADO.NET (System.Data) addresses the issues involved with ADO by shipping four main namespaces. These namespaces expose the classes and interfaces used in .NET data access:

• System.Data
• System.Data.SqlClient
• System.Data.OleDB
• System.Data.Odbc
• System.Data.Oracle

Shared Classes

ADO.NET contains a number of classes that are used regardless of whether you are using the SQL Server classes or the OLE DB classes.

The following classes are contained in the System.Data namespace:

• The DataSet is an in-memory view of the database. It can contain multiple DataTable and DataRelation objects.
• The DataTable is a container of data that consists of one or more DataColumns, and when populated, will have one more DataRows containing data.
• The DataAdapter is the object that connects to the database to fill the DataSet. Then, it connects back to the database to update the data there, based on operations performed while the DataSet holds the data. In the past, data processing has been primarily connection-based. Now, in an effort to make multi-tiered apps more efficient, data processing is turning to a message-based approach that revolves around chunks of information. At the center of this approach is the DataAdapter, which provides a bridge to retrieve and save data between a DataSet and its source data store. It accomplishes this by means of requests to the appropriate SQL commands made against the data store.
• The DataReader.

Using Database Connections

Assume we are going to access the Northwind database that resides on the SQL Server. Northwind is a sample database meant for this purpose. To access this database, we need to provide connection parameters, such as the machine that the database is running on (that could mean a distributed architecture or an encapsulated executable where the database resides on the same machine as the database access code).

For example, the user has to login, but that is separate from accessing the code and performing modifications. It is for this reason that we are going to use the command line:

using System;

public class Login
{
  // Alter this connection string here - all other examples use this class
  // Note: The Northwind database no longer
  // ships with SQL 2005 - you can however download it
  
  public static string Connection
  {
    get { return @"data source=.\sqlexpress;initial " + 
                 @"catalog=Northwind;integrated security=SSPI;"; }
  }
}

C:\Windows\Microsoft.NET\Framework\v2.0.50727>csc.exe /target:library /out:login.dll login.cs

Now that we have have a class library that contains a login class, we can issue commands against the SQL Server and retrieve data from the Northwind database:

using System;
using System.Data;
using System.Xml;
using System.Data.SqlClient;
using System.Data.OleDb;

public class ExecutingCommands
{
    
   /// SimpleDataAccess - show SQL & Stored Procs
    
    public static void Main ( )
    {
        // The following is the database connection string
                string source = Login.Connection ;

        // First section of code - using a SQL statement to select records
        ExecuteSql ( source ) ;

        // Second section - calling a stored procedure
        ExecuteStoredProc ( source ) ;

        // Third - batch statements
        ExecuteBatch ( source ) ;

        // Fourth - Return XML from SqlServer...
        ExecuteXml ( source ) ;
    }

    public static void ExecuteSql ( string source )
    {
        // And this is the SQL statement that will be issued
        string select = "SELECT ContactName,CompanyName FROM Customers";

        try
        {
            // Connect to the database...
            using ( SqlConnection conn=new SqlConnection(source) )
            {
                // Open the database connection
                conn.Open ( ) ;

                // Create the SQL command...
                SqlCommand        cmd = new SqlCommand ( select , conn ) ;

                // Construct the data reader
                using ( SqlDataReader    reader = cmd.ExecuteReader ( ) )
                {
                    // Output headings...
                    Console.WriteLine ( "*** SqlProvider ***" ) ;
                    Console.WriteLine ( "Output from direct SQL statement..." ) ;
                    Console.WriteLine ( ) ;
                    Console.WriteLine ( "CONTACT                        COMPANY" ) ;
                    Console.WriteLine ( "---------------------------------" + 
                                        "------------------------------------" ) ;

                    // And iterate through the data
                    while ( reader.Read ( ) )
                    {
                      Console.WriteLine ( "{0,-30} {1}" , reader[0] , reader[1] ) ;
                    }

                    reader.Close ( ) ;
                }
        
                conn.Close ( ) ;
            }
        }
        catch ( Exception e )
        {
            Console.WriteLine ( e.ToString( ) ) ;
        }
    }

    public static void ExecuteStoredProc ( string source )
    {
        // Connect to the database...
        using ( SqlConnection    conn = new SqlConnection(source) )
        {
            // Open the database connection
            conn.Open ( ) ;

            // Create the SQL command that links to a stored procedure
            SqlCommand    cmd = new SqlCommand ( "CustOrderHist" , conn ) ;

            // Set the type to stored procedure
            cmd.CommandType = CommandType.StoredProcedure ;

            // And add the parameter to the stored proc...
            cmd.Parameters.AddWithValue ( "@CustomerID" , "QUICK" ) ;

            // Construct the data reader
            using ( SqlDataReader    reader = cmd.ExecuteReader ( ) )
            {
                Console.WriteLine ( "" ) ;
                Console.WriteLine ( "*** SqlProvider ***" ) ;
                Console.WriteLine ( "Call NorthWind CustOrderHist " + 
                                    "stored proc for customer 'QUICK'..." ) ;
                Console.WriteLine ( ) ;
                Console.WriteLine ( "Product Name                       Quantity" ) ;
                Console.WriteLine ( "----------------------------------" + 
                                    "-----------------------------------" ) ;

                // Iterate through the data
                while ( reader.Read ( ) )
                {
                    Console.WriteLine ( "{0,-34} {1}" , reader[0] , reader[1] ) ;
                }

                  reader.Close ( ) ;

                  Console.WriteLine ( ) ;
            }

            // Close the connection
            conn.Close ( ) ;
       }
    }

    protected static void ExecuteFullTable ( string source ) 
    {
        // Connect to the database...
        using ( OleDbConnection conn = 
                new OleDbConnection("Provider=SQLOLEDB;" + source) )
        {
            // Open the database connection
            conn.Open ( ) ;

            // Create the SQL command that links to a stored procedure
            OleDbCommand    cmd = new OleDbCommand ( "Categories" , conn ) ;

            // Set the type to TableDirect
            cmd.CommandType = CommandType.TableDirect;

            // Construct the data reader
            using ( OleDbDataReader    reader = cmd.ExecuteReader ( ) )
            {
                 Console.WriteLine ( "" ) ;
                 Console.WriteLine ( "*** OleDbProvider ***" ) ;
                 Console.WriteLine ( "Listing all records in Categories table..." ) ;
                 Console.WriteLine ( ) ;
                 Console.WriteLine ( "ID  Name            Description" ) ;
                 Console.WriteLine ( "--------------------------------" + 
                                     "-------------------------------------" ) ;

                 // Iterate through the data
                 while ( reader.Read ( ) )
                 {
                     Console.WriteLine ( "{0,-3} {1,-15} {2}" , 
                                         reader[0] , reader[1], reader[2] ) ;
                 }

                   reader.Close ( ) ;
                 Console.WriteLine ( ) ;
            }

            // Close the connection
            conn.Close ( ) ;
        }
    }

    protected static void ExecuteBatch ( string source )
    {
        string select = "SELECT COUNT(*) FROM Customers;SELECT COUNT(*) FROM Products";

        // Connect to the database...
        using ( SqlConnection    conn = new SqlConnection(source) )
        {
            // Open the database connection
            conn.Open ( ) ;

            // Create the SQL command...
            SqlCommand        cmd = new SqlCommand ( select , conn ) ;

            // Construct the data reader
            using ( SqlDataReader    reader = cmd.ExecuteReader ( ) )
            {
                // Output headings...
                Console.WriteLine ( "*** SqlProvider ***" ) ;
                Console.WriteLine ( "Output from batched SQL statements" ) ;
                Console.WriteLine ( ) ;

                int statement = 0 ;

                do
                {
                    statement++ ;

                    while ( reader.Read ( ) )
                    {
                        Console.WriteLine ( "Output from batch statement {0} is {1}" , 
                                            statement , reader[0] ) ;
                    }
                } while ( reader.NextResult ( ) ) ;

                reader.Close ( ) ;
            }

               conn.Close ( ) ;
        }
    }

    protected static void ExecuteXml ( string source )
    {
        string select = "SELECT ContactName,CompanyName FROM Customers FOR XML AUTO";

        using ( SqlConnection    conn = new SqlConnection(source) )
        {
            // Open the database connection
            conn.Open ( ) ;

            // Create the SQL command...
            SqlCommand        cmd = new SqlCommand ( select , conn ) ;

            // Construct an Xml Reader
            XmlReader        xr = cmd.ExecuteXmlReader ( ) ;

            Console.WriteLine ( "" ) ;
            Console.WriteLine ( "*** SqlProvider ***" ) ;
            Console.WriteLine ( "Use ExecuteXmlReader with a FOR XML AUTO SQL clause" ) ;
            Console.WriteLine ( ) ;

            // Do something useful with the xml
            while ( xr.Read() )
            {
                Console.WriteLine ( xr.ReadOuterXml ( ) ) ;
            }

            // And close the connection
            conn.Close ( ) ;
        }
    }
}

Here is the ManufacturedDataSet:

using System;
using System.Data;
using System.Data.SqlClient;

public class ManufacturedDataset
{
    public static void Main ( )
    {
        string    source = Login.Connection ;
        string    select = "SELECT * FROM Products" ;
        string    sel2   = "SELECT * FROM Categories" ;

        using ( SqlConnection  conn = new SqlConnection ( source ) )
        {
            SqlDataAdapter da = new SqlDataAdapter ( select , conn ) ;

            DataSet       ds = new DataSet ( ) ;

            // Create the products table
            ManufactureProductDataTable ( ds ) ;
            
            da.Fill ( ds , "Products" ) ;

            foreach ( DataRow row in ds.Tables["Products"].Rows )
                Console.WriteLine ( "'{0}' from {1}" , 
                    row[0] ,
                    row[1] ) ;

            SqlDataAdapter da2 = new SqlDataAdapter ( sel2 , conn ) ;

            // Now create the category table
            ManufactureCategoryTable ( ds ) ;

            da2.Fill ( ds , "Categories" ) ;

            // And add a foreign key constraint between
            // the products & categories tables
            AddForeignKeyConstraint ( ds ) ;

            conn.Close ( ) ;
        }
    }

    public static void ManufactureProductDataTable ( DataSet ds )
    {
        DataTable   products = new DataTable ( "Products" ) ;

        products.Columns.Add ( new DataColumn ( "ProductID" ,       typeof ( int ) ) ) ;
        products.Columns.Add ( new DataColumn ( "ProductName" ,     typeof ( string ) ) ) ;
        products.Columns.Add ( new DataColumn ( "SupplierID" ,      typeof ( int ) ) ) ;
        products.Columns.Add ( new DataColumn ( "CategoryID" ,      typeof ( int ) ) ) ;
        products.Columns.Add ( new DataColumn ( "QuantityPerUnit" , typeof ( string ) ) ) ;
        products.Columns.Add ( new DataColumn ( "UnitPrice" ,       typeof ( decimal ) ) ) ;
        products.Columns.Add ( new DataColumn ( "UnitsInStock" ,    typeof ( short ) ) ) ;
        products.Columns.Add ( new DataColumn ( "UnitsOnOrder" ,    typeof ( short ) ) ) ;
        products.Columns.Add ( new DataColumn ( "ReorderLevel" ,    typeof ( short ) ) ) ;
        products.Columns.Add ( new DataColumn ( "Discontinued" ,    typeof ( bool ) ) ) ;

        ManufacturePrimaryKey ( products ) ;

        ds.Tables.Add ( products ) ;
    }

    public static void ManufacturePrimaryKey ( DataTable dt )
    {
        DataColumn[]  pk = new DataColumn[1] ;
        
        pk[0] = dt.Columns["ProductID"] ;

        dt.Constraints.Add ( new UniqueConstraint ( "PK_Products" , pk[0] ) ) ;

        dt.PrimaryKey = pk ;
        
    }

    public static void ManufactureCategoryTable ( DataSet ds )
    {
        DataTable   categories = new DataTable ( "Categories" ) ;

        categories.Columns.Add ( new DataColumn ( "CategoryID" , 
                                                  typeof ( int )    ) ) ;
        categories.Columns.Add ( new DataColumn ( "CategoryName" , 
                                                  typeof ( string ) ) ) ;
        categories.Columns.Add ( new DataColumn ( "Description" ,  
                                                  typeof ( string ) ) ) ;

        categories.Constraints.Add ( new UniqueConstraint ( "PK_Categories" , 
                                     categories.Columns["CategoryID"] ) ) ;

        categories.PrimaryKey = 
          new DataColumn[1] { categories.Columns["CategoryID"] } ;

        ds.Tables.Add ( categories ) ;

    }

    public static void AddForeignKeyConstraint ( DataSet ds )
    {
        DataColumn   parent = 
          ds.Tables["Categories"].Columns["CategoryID"] ;
        DataColumn   child  = 
          ds.Tables["Products"].Columns["CategoryID"] ;

        ForeignKeyConstraint  fk = new ForeignKeyConstraint ( 
                                    "FK_Product_CategoryID" , parent , child ) ;
        
        fk.UpdateRule = Rule.Cascade ;
        fk.DeleteRule = Rule.SetNull ;

        // Create the constraint
        // If this fails, you have a row in the products
        // table with no associated category
        ds.Tables["Products"].Constraints.Add ( fk ) ;

    }
}

C:\windows\Microsoft.NET\Framework\v2.0.50727>csc.exe /reference:login.dll ManufacturedDataSet.cs

C:\windows\Microsoft.NET\Framework\v2.0.50727>ManufacturedDataSet.exe

Here is the output:

Typically, wel build a Windows Forms application that runs atop of the database. Many prefer a user interface with a tool bar. Who can beat it?

Sometimes, however, for conceptual purposes, it helps to access the database by using the command line. Much of this article was referenced from the www.wrox.com site.