ADO.NET 代码示例

  • 项目

更新：November 2007

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

  • ADO.NET 数据提供程序：

    • .NET Framework Data Provider for SQL Server (System.Data.SqlClient)

    • .NET Framework Data Provider for OLE DB (System.Data.OleDb)

    • .NET Framework Data Provider for ODBC (System.Data.Odbc)

    • .NET Framework Data Provider for Oracle (System.Data.OracleClient)

  • ADO.NET 实体框架：

    • LINQ to Entities

    • Typed ObjectQuery

    • EntityClient data provider (System.Data.EntityClient)

  • LINQ to SQL

ADO.NET 数据提供程序示例

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

SqlClient

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

Option Explicit On
Option Strict On

Imports System
Imports System.Data
Imports System.Data.SqlClient

Public Class Program
    Public Shared Sub Main()

        Dim connectionString As String = GetConnectionString()
        Dim queryString As String = _
         "SELECT CategoryID, CategoryName FROM dbo.Categories;"

        Using connection As New SqlConnection(connectionString)
            Dim command As SqlCommand = connection.CreateCommand()
            command.CommandText = queryString
            Try
                connection.Open()
                Dim dataReader As SqlDataReader = _
                 command.ExecuteReader()
                Do While dataReader.Read()
                    Console.WriteLine(vbTab & "{0}" & vbTab & "{1}", _
                     dataReader(0), dataReader(1))
                Loop
                dataReader.Close()

            Catch ex As Exception
                Console.WriteLine(ex.Message)
            End Try
        End Using
    End Sub

    Private Shared Function GetConnectionString() As String
        ' To avoid storing the connection string in your code,  
        ' you can retrieve it from a configuration file.
        Return "Data Source=(local);Initial Catalog=Northwind;" _
           & "Integrated Security=SSPI;"
    End Function
End Class

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

class Program
{
    static void Main()
    {
        string connectionString = GetConnectionString();
        string queryString = 
            "SELECT CategoryID, CategoryName FROM dbo.Categories;";
        using (SqlConnection connection = 
                   new SqlConnection(connectionString))
        {
            SqlCommand command = connection.CreateCommand();
            command.CommandText = queryString;

            try
            {
                connection.Open();

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

    static private string GetConnectionString()
    {
        // To avoid storing the connection string in your code, 
        // you can retrieve it from a configuration file.
        return "Data Source=(local);Initial Catalog=Northwind;"
            + "Integrated Security=SSPI";
    }
}

[Top]

OleDb

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

Option Explicit On
Option Strict On

Imports System
Imports System.Data
Imports System.Data.OleDb

Public Class Program
    Public Shared Sub Main()

        Dim connectionString As String = GetConnectionString()
        Dim queryString As String = _
            "SELECT CategoryID, CategoryName FROM Categories;"

        Using connection As New OleDbConnection(connectionString)
            Dim command As OleDbCommand = connection.CreateCommand()
            command.CommandText = queryString
            Try
                connection.Open()
                Dim dataReader As OleDbDataReader = _
                 command.ExecuteReader()
                Do While dataReader.Read()
                    Console.WriteLine(vbTab & "{0}" & vbTab & "{1}", _
                     dataReader(0), dataReader(1))
                Loop
                dataReader.Close()

            Catch ex As Exception
                Console.WriteLine(ex.Message)
            End Try
        End Using
    End Sub

    Private Shared Function GetConnectionString() As String
        ' To avoid storing the connection string in your code,  
        ' you can retrieve it from a configuration file.
        ' Assumes Northwind.mdb is located in c:\Data folder.
        Return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
           & "c:\Data\Northwind.mdb;User Id=admin;Password=;"
    End Function
End Class

using System;
using System.Data;
using System.Data.OleDb;

class Program
{
    static void Main()
    {
        string connectionString = GetConnectionString();
        string queryString =
            "SELECT CategoryID, CategoryName FROM Categories;";
        using (OleDbConnection connection =
                   new OleDbConnection(connectionString))
        {
            OleDbCommand command = connection.CreateCommand();
            command.CommandText = queryString;

            try
            {
                connection.Open();

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

    static private string GetConnectionString()
    {
        // To avoid storing the connection string in your code, 
        // you can retrieve it from a configuration file.
        // Assumes Northwind.mdb is located in the c:\Data folder.
        return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
            + "c:\\Data\\Northwind.mdb;User Id=admin;Password=;";
    }
}

[Top]

Odbc

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

Option Explicit On
Option Strict On

Imports System
Imports System.Data
Imports System.Data.Odbc

Public Class Program
    Public Shared Sub Main()

        Dim connectionString As String = GetConnectionString()
        Dim queryString As String = _
            "SELECT CategoryID, CategoryName FROM Categories;"

        Using connection As New OdbcConnection(connectionString)
            Dim command As OdbcCommand = connection.CreateCommand()
            command.CommandText = queryString
            Try
                connection.Open()
                Dim dataReader As OdbcDataReader = _
                 command.ExecuteReader()
                Do While dataReader.Read()
                    Console.WriteLine(vbTab & "{0}" & vbTab & "{1}", _
                     dataReader(0), dataReader(1))
                Loop
                dataReader.Close()

            Catch ex As Exception
                Console.WriteLine(ex.Message)
            End Try
        End Using
    End Sub

    Private Shared Function GetConnectionString() As String
        ' To avoid storing the connection string in your code,  
        ' you can retrieve it from a configuration file.
        ' Assumes Northwind.mdb is located in c:\Data folder.
        Return "Driver={Microsoft Access Driver (*.mdb)};" _
           & "Dbq=c:\Data\Northwind.mdb;Uid=Admin;Pwd=;"
    End Function
End Class

using System;
using System.Data;
using System.Data.Odbc;

class Program
{
    static void Main()
    {
        string connectionString = GetConnectionString();
        string queryString =
            "SELECT CategoryID, CategoryName FROM Categories;";
        using (OdbcConnection connection =
                   new OdbcConnection(connectionString))
        {
            OdbcCommand command = connection.CreateCommand();
            command.CommandText = queryString;

            try
            {
                connection.Open();

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

    static private string GetConnectionString()
    {
        // To avoid storing the connection string in your code, 
        // you can retrieve it from a configuration file.
        // Assumes Northwind.mdb is located in the c:\Data folder.
        return "Driver={Microsoft Access Driver (*.mdb)};"
            + "Dbq=c:\\Data\\Northwind.mdb;Uid=Admin;Pwd=;";
    }
}

[Top]

OracleClient

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

Option Explicit On
Option Strict On

Imports System
Imports System.Data
Imports System.Data.OracleClient

Public Class Program
    Public Shared Sub Main()

        Dim connectionString As String = GetConnectionString()
        Dim queryString As String = _
            "SELECT CUSTOMER_ID, NAME FROM DEMO.CUSTOMER"

        Using connection As New OracleConnection(connectionString)
            Dim command As OracleCommand = connection.CreateCommand()
            command.CommandText = queryString
            Try
                connection.Open()
                Dim dataReader As OracleDataReader = _
                 command.ExecuteReader()
                Do While dataReader.Read()
                    Console.WriteLine(vbTab & "{0}" & vbTab & "{1}", _
                     dataReader(0), dataReader(1))
                Loop
                dataReader.Close()

            Catch ex As Exception
                Console.WriteLine(ex.Message)
            End Try
        End Using
    End Sub

    Private Shared Function GetConnectionString() As String
        ' To avoid storing the connection string in your code,  
        ' you can retrieve it from a configuration file.
        Return "Data Source=ThisOracleServer;Integrated Security=yes;"
    End Function
End Class

using System;
using System.Data;
using System.Data.OracleClient;

class Program
{
    static void Main()
    {
        string connectionString = GetConnectionString();
        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);
            }
        }
    }

    static private string GetConnectionString()
    {
        // To avoid storing the connection string in your code, 
        // you can retrieve it from a configuration file.
        // Assumes Northwind.mdb is located in the c:\Data folder.
        return "Data Source=ThisOracleServer;Integrated Security=yes;";
    }
}

[Top]

实体框架示例

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

LINQ to Entities

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

[Top]

类型化 ObjectQuery

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

[Top]

EntityClient

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

[Top]

LINQ to SQL

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

[Top]

请参见

其他资源

ADO.NET 概述

在 ADO.NET 中检索和修改数据

创建客户端数据应用程序

Querying an Entity Data Model (Entity Framework Tasks)

How to: Execute a Query that Returns an Anonymous Type (Entity Framework)