ADO.NET 代码示例

转自：http://msdn.microsoft.com/zh-cn/library/dw70f090

本主题中的代码列表演示如何使用下面的 ADO.NET 技术从数据库中检索数据：

ADO.NET 数据提供程序：
- SQL Server .NET Framework 数据提供程序 (System.Data.SqlClient)
- OLE DB .NET Framework 数据提供程序 (System.Data.OleDb)
- ODBC .NET Framework 数据提供程序 (System.Data.Odbc)
- Oracle .NET Framework 数据提供程序 (System.Data.OracleClient)
ADO.NET 实体框架：
- LINQ to Entities
- 类型化 ObjectQuery
- EntityClient 数据提供程序 (System.Data.EntityClient)
LINQ to SQL

以下代码列表演示如何使用 ADO.NET 数据提供程序从数据库中检索数据。数据在一个 DataReader 中返回。有关更多信息，请参见使用 DataReader 检索数据。

SqlClient

此示例中的代码假定您可以连接到 Microsoft SQL Server 的 Northwind 示例数据库。在此情形 5 中，示例代码创建一个 SqlCommand 以从 Products 表中选择行，并添加SqlParameter 来将结果限制为其 UnitPrice 大于指定参数值的行。 SqlConnection 在 using 块内打开，这将确保在代码退出时会关闭和释放资源。示例代码使用SqlDataReader 执行命令，并在控制台窗口中显示结果。

using System;

using System.Data;

using System.Data.SqlClient;

class Program

{

    static void Main()

    {

        string connectionString =

            "Data Source=(local);Initial Catalog=Northwind;"

            + "Integrated Security=true";

        // Provide the query string with a parameter placeholder.

        string queryString =

            "SELECT ProductID, UnitPrice, ProductName from dbo.products "

                + "WHERE UnitPrice > @pricePoint "

                + "ORDER BY UnitPrice DESC;";

        // Specify the parameter value.

        int paramValue = 5;

        // Create and open the connection in a using block. This

        // ensures that all resources will be closed and disposed

        // when the code exits.

        using (SqlConnection connection =

            new SqlConnection(connectionString))

        {

            // Create the Command and Parameter objects.

            SqlCommand command = new SqlCommand(queryString, connection);

            command.Parameters.AddWithValue("@pricePoint", paramValue);

            // Open the connection in a try/catch block.

            // Create and execute the DataReader, writing the result

            // set to the console window.

            try

            {

                connection.Open();

                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())

                {

                    Console.WriteLine("\t{0}\t{1}\t{2}",

                        reader[0], reader[1], reader[2]);

                }

                reader.Close();

            }

            catch (Exception ex)

            {

                Console.WriteLine(ex.Message);

            }

            Console.ReadLine();

        }

    }

[返回页首]

OleDb

此示例中的代码假定您可以连接到 Microsoft Access Northwind 示例数据库。在此情形 5 中，示例代码创建一个 OleDbCommand 以从 Products 表中选择行，并添加一个OleDbParameter 来将结果限制为其 UnitPrice 大于指定参数值的行。 OleDbConnection 在 using 块内打开，这将确保在代码退出时会关闭和释放资源。示例代码使用OleDbDataReader 执行命令，并在控制台窗口中显示结果。

using System;

using System.Data;

using System.Data.OleDb;

class Program

{

    static void Main()

    {

        // The connection string assumes that the Access

        // Northwind.mdb is located in the c:\Data folder.

        string connectionString =

        "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="

            + "c:\\Data\\Northwind.mdb;User Id=admin;Password=;";

        // Provide the query string with a parameter placeholder.

        string queryString =

            "SELECT ProductID, UnitPrice, ProductName from products "

                + "WHERE UnitPrice > ? "

                + "ORDER BY UnitPrice DESC;";

        // Specify the parameter value.

        int paramValue = 5;

        // Create and open the connection in a using block. This

        // ensures that all resources will be closed and disposed

        // when the code exits.

        using (OleDbConnection connection =

            new OleDbConnection(connectionString))

        {

            // Create the Command and Parameter objects.

            OleDbCommand command = new OleDbCommand(queryString, connection);

            command.Parameters.AddWithValue("@pricePoint", paramValue);

            // Open the connection in a try/catch block.

            // Create and execute the DataReader, writing the result

            // set to the console window.

            try

            {

                connection.Open();

                OleDbDataReader reader = command.ExecuteReader();

                while (reader.Read())

                {

                    Console.WriteLine("\t{0}\t{1}\t{2}",

                        reader[0], reader[1], reader[2]);

                }

                reader.Close();

            }

            catch (Exception ex)

            {

                Console.WriteLine(ex.Message);

            }

            Console.ReadLine();

        }

    }

[返回页首]

Odbc

此示例中的代码假定您可以连接到 Microsoft Access Northwind 示例数据库。在此情形 5 中，示例代码创建一个 OdbcCommand 以从 Products 表中选择行，并添加一个OdbcParameter 来将结果限制为其 UnitPrice 大于指定参数值的行。 OdbcConnection 在 using 块内打开，这将确保在代码退出时会关闭和释放资源。示例代码使用OdbcDataReader 执行命令，并在控制台窗口中显示结果。

using System;

using System.Data;

using System.Data.Odbc;

class Program

{

    static void Main()

    {

        // The connection string assumes that the Access

        // Northwind.mdb is located in the c:\Data folder.

        string connectionString =

            "Driver={Microsoft Access Driver (*.mdb)};"

            + "Dbq=c:\\Data\\Northwind.mdb;Uid=Admin;Pwd=;";

        // Provide the query string with a parameter placeholder.

        string queryString =

            "SELECT ProductID, UnitPrice, ProductName from products "

                + "WHERE UnitPrice > ? "

                + "ORDER BY UnitPrice DESC;";

        // Specify the parameter value.

        int paramValue = 5;

        // Create and open the connection in a using block. This

        // ensures that all resources will be closed and disposed

        // when the code exits.

        using (OdbcConnection connection =

            new OdbcConnection(connectionString))

        {

            // Create the Command and Parameter objects.

            OdbcCommand command = new OdbcCommand(queryString, connection);

            command.Parameters.AddWithValue("@pricePoint", paramValue);

            // Open the connection in a try/catch block.

            // Create and execute the DataReader, writing the result

            // set to the console window.

            try

            {

                connection.Open();

                OdbcDataReader reader = command.ExecuteReader();

                while (reader.Read())

                {

                    Console.WriteLine("\t{0}\t{1}\t{2}",

                        reader[0], reader[1], reader[2]);

                }

                reader.Close();

            }

            catch (Exception ex)

            {

                Console.WriteLine(ex.Message);

            }

            Console.ReadLine();

        }

    }

[返回页首]

OracleClient

此示例中的代码假定已建立与 Oracle 服务器上的 DEMO.CUSTOMER 的连接。您还必须添加对 System.Data.OracleClient.dll 的引用。示例代码在 OracleDataReader 中返回数据。

using System;

using System.Data;

using System.Data.OracleClient;

class Program

{

    static void Main()

    {

        string connectionString =

            "Data Source=ThisOracleServer;Integrated Security=yes;";

        string queryString =

            "SELECT CUSTOMER_ID, NAME FROM DEMO.CUSTOMER";

        using (OracleConnection connection =

                   new OracleConnection(connectionString))

        {

            OracleCommand command = connection.CreateCommand();

            command.CommandText = queryString;

            try

            {

                connection.Open();

                OracleDataReader reader = command.ExecuteReader();

                while (reader.Read())

                {

                    Console.WriteLine("\t{0}\t{1}",

                        reader[0], reader[1]);

                }

                reader.Close();

            }

            catch (Exception ex)

            {

                Console.WriteLine(ex.Message);

            }

        }

    }

}

[返回页首]

实体框架示例

以下代码列表演示如何通过查询实体数据模型 (EDM) 中的实体来从数据源检索数据。这些示例使用 Northwind 模型。有关更多信息，请参见实体框架概述。

LINQ to Entities

此示例中的代码使用 LINQ 查询以 Categories 对象的形式返回数据，这些对象将作为仅包含 CategoryID 和 CategoryName 属性的匿名类型提取。有关更多信息，请参见LINQ to Entities Overview。

Option Explicit On

Option Strict On

Imports System

Imports System.Linq

Imports System.Data.Objects

Imports NorthwindModel

Class LinqSample

    Public Shared Sub ExecuteQuery()

        Using context As NorthwindEntities = New NorthwindEntities()

            Try

                Dim query = From category In context.Categories _

                            Select New With _

                            { _

                                .categoryID = category.CategoryID, _

                                .categoryName = category.CategoryName _

                            }

                For Each categoryInfo In query

                    Console.WriteLine(vbTab & "{0}" & vbTab & "{1}", _

                        categoryInfo.categoryID, categoryInfo.categoryName)

                Next

            Catch ex As Exception

                Console.WriteLine(ex.Message)

            End Try

        End Using

    End Sub

End Class

在 C# 中：

using System;

using System.Linq;

using System.Data.Objects;

using NorthwindModel;

class LinqSample

{

    public static void ExecuteQuery()

    {

        using (NorthwindEntities context = new NorthwindEntities())

        {

            try

            {

                var query = from category in context.Categories

                            select new

                            {

                                categoryID = category.CategoryID,

                                categoryName = category.CategoryName

                            };

                foreach (var categoryInfo in query)

                {

                    Console.WriteLine("\t{0}\t{1}",

                        categoryInfo.categoryID, categoryInfo.categoryName);

                }

            }

            catch (Exception ex)

            {

                Console.WriteLine(ex.Message);

            }

        }

    }

}

[返回页首]

类型化 ObjectQuery

此示例中的代码使用 ObjectQuery<T> 以 Categories 对象的形式返回数据。有关更多信息，请参见Object Queries (Entity Framework)。

Option Explicit On

Option Strict On

Imports System

Imports System.Data.Objects

Imports NorthwindModel

Class ObjectQuerySample

    Public Shared Sub ExecuteQuery()

        Using context As NorthwindEntities = New NorthwindEntities()

            Dim categoryQuery As ObjectQuery(Of Categories) = context.Categories

            For Each category As Categories In _

                categoryQuery.Execute(MergeOption.AppendOnly)

                Console.WriteLine(vbTab & "{0}" & vbTab & "{1}", _

                    category.CategoryID, category.CategoryName)

            Next

        End Using

    End Sub

End Class

在 C# 中：

using System;

using System.Data.Objects;

using NorthwindModel;

class ObjectQuerySample

{

    public static void ExecuteQuery()

    {

        using (NorthwindEntities context = new NorthwindEntities())

        {

            ObjectQuery<Categories> categoryQuery = context.Categories;

            foreach (Categories category in

                categoryQuery.Execute(MergeOption.AppendOnly))

            {

                Console.WriteLine("\t{0}\t{1}",

                    category.CategoryID, category.CategoryName);

            }

        }

    }

}

[返回页首]

EntityClient

此示例中的代码使用 EntityCommand 来执行实体 SQL 查询。此查询会返回表示 Categories 实体类型的实例的记录的列表。 EntityDataReader 用于访问结果集中的数据记录。有关更多信息，请参见用于实体框架的 EntityClient 提供程序。

Option Explicit On

Option Strict On

Imports System

Imports System.Data

Imports System.Data.Common

Imports System.Data.EntityClient

Imports NorthwindModel

Class EntityClientSample

    Public Shared Sub ExecuteQuery()

        Dim queryString As String = _

            "SELECT c.CategoryID, c.CategoryName " & _

                "FROM NorthwindEntities.Categories AS c"

        Using conn As EntityConnection = _

            New EntityConnection("name=NorthwindEntities")

            Try

                conn.Open()

                Using query As EntityCommand = _

                New EntityCommand(queryString, conn)

                    Using rdr As DbDataReader = _

                        query.ExecuteReader(CommandBehavior.SequentialAccess)

                        While rdr.Read()

                            Console.WriteLine(vbTab & "{0}" & vbTab & "{1}", _

                                              rdr(0), rdr(1))

                        End While

                    End Using

                End Using

            Catch ex As Exception

                Console.WriteLine(ex.Message)

            End Try

        End Using

    End Sub

End Class

在 C#" 中

using System;

using System.Data;

using System.Data.Common;

using System.Data.EntityClient;

using NorthwindModel;

class EntityClientSample

{

    public static void ExecuteQuery()

    {

        string queryString =

            @"SELECT c.CategoryID, c.CategoryName

                FROM NorthwindEntities.Categories AS c";

        using (EntityConnection conn =

            new EntityConnection("name=NorthwindEntities"))

        {

            try

            {

                conn.Open();

                using (EntityCommand query = new EntityCommand(queryString, conn))

                {

                    using (DbDataReader rdr =

                        query.ExecuteReader(CommandBehavior.SequentialAccess))

                    {

                        while (rdr.Read())

                        {

                            Console.WriteLine("\t{0}\t{1}", rdr[0], rdr[1]);

                        }

                    }

                }

            }

            catch (Exception ex)

            {

                Console.WriteLine(ex.Message);

            }

        }

    }

}

[返回页首]

LINQ to SQL

此示例中的代码使用 LINQ 查询以 Categories 对象的形式返回数据，这些对象将作为仅包含 CategoryID 和 CategoryName 属性的匿名类型提取。此示例基于 Northwind 数据上下文。有关更多信息，请参见入门。

Option Explicit On

Option Strict On

Imports System

Imports System.Collections.Generic

Imports System.Linq

Imports System.Text

Imports Northwind

Class LinqSqlSample

    Public Shared Sub ExecuteQuery()

        Using db As NorthwindDataContext = New NorthwindDataContext()

            Try

                Dim query = From category In db.Categories _

                                Select New With _

                                { _

                                    .categoryID = category.CategoryID, _

                                    .categoryName = category.CategoryName _

                                }

                For Each categoryInfo In query

                    Console.WriteLine(vbTab & "{0}" & vbTab & "{1}", _

                            categoryInfo.categoryID, categoryInfo.categoryName)

                Next

            Catch ex As Exception

                Console.WriteLine(ex.Message)

            End Try

            End Using

    End Sub

End Class

在 C# 中：

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using Northwind;

    class LinqSqlSample

    {

        public static void ExecuteQuery()

        {

            using (NorthwindDataContext db = new NorthwindDataContext())

            {

                try

                {

                    var query = from category in db.Categories

                                select new

                                {

                                    categoryID = category.CategoryID,

                                    categoryName = category.CategoryName

                                };

                    foreach (var categoryInfo in query)

                    {

                        Console.WriteLine("vbTab {0} vbTab {1}",

                            categoryInfo.categoryID, categoryInfo.categoryName);

                    }

                }

                catch (Exception ex)

                {

                    Console.WriteLine(ex.Message);

                }

            }

        }

    }