ADO.NET 概述

logicchild

2.39/5 (11投票s)

2008年7月6日

CPOL

3分钟阅读

36623

如何理解 .NET 中的数据访问。

引言

本文介绍了 .NET 中的数据访问。因此，它简要地提到了 ADO 及其缺点，以鼓励使用 ADO.NET。

使用 .NET 进行数据访问

ADO（ActiveX 数据对象）是一个组件库，主要由 Connection、Command、RecordSet 和 Field 对象组成。使用 ADO，可以与数据库建立物理连接。熟悉 ADO 的人都知道数据在记录集之间传输。记录集包含表格形式的数据。无论记录集包含来自数据库中一个或多个表的信息，数据仍然以行和列的形式返回。 ADO 没有解决与断开连接的记录集相关的问题。 ADO.NET (System.Data) 通过发布四个主要命名空间来解决与 ADO 相关的问题。这些命名空间公开了 .NET 数据访问中使用的类和接口

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

共享类

无论您使用的是 SQL Server 类还是 OLE DB 类，ADO.NET 都包含许多要使用的类。

以下类包含在 System.Data 命名空间中

DataSet 是数据库的内存视图。它可以包含多个 DataTable 和 DataRelation 对象。
DataTable 是一个数据容器，由一个或多个 DataColumn 组成，并且在填充后，将包含一个或多个包含数据的 DataRow。
DataAdapter 是连接到数据库以填充 DataSet 的对象。然后，它连接回数据库以更新其中的数据，这是基于 DataSet 保持数据时执行的操作。过去，数据处理主要基于连接。现在，为了使多层应用程序更高效，数据处理正在转向一种基于消息的方法，该方法围绕着信息块。该方法的核心是 DataAdapter，它提供了一个桥梁，用于在 DataSet 和其源数据存储之间检索和保存数据。它通过对数据存储发出适当的 SQL 命令请求来完成此操作。
DataReader。

使用数据库连接

假设我们要访问位于 SQL Server 上的 Northwind 数据库。 Northwind 是一个专为此目的而设计的示例数据库。要访问此数据库，我们需要提供连接参数，例如数据库运行所在的机器（这可能意味着分布式架构或封装的可执行文件，其中数据库与数据库访问代码位于同一台机器上）。

例如，用户必须登录，但这与访问代码和执行修改是分开的。因此，我们将使用命令行

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

现在我们有了一个包含登录类的类库，我们可以针对 SQL Server 发出命令并从 Northwind 数据库检索数据

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 ( ) ;
        }
    }
}

这是 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

输出如下

通常，我们会构建一个运行在数据库之上的 Windows 窗体应用程序。许多人喜欢带有工具栏的用户界面。谁能打败它？

然而，有时，出于概念目的，通过使用命令行访问数据库会很有帮助。本文的大部分内容都参考了 www.wrox.com 网站。