ADO.NET 代码示例

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

ADO.NET 数据提供程序：
- SqlClient （Microsoft.Data.SqlClient）
- OleDb （System.Data.OleDb）
- Odbc （System.Data.Odbc）
- OracleClient （System.Data.OracleClient）
ADO.NET 实体框架：
- LINQ to Entities
- 类型化 ObjectQuery
- EntityClient （System.Data.EntityClient）
LINQ 到 SQL

ADO.NET 数据提供程序示例

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

SqlClient

若要使用 ADO.NET 访问 SQL Server，请使用 Microsoft.Data.SqlClient NuGet 包。以下 C# 和Visual Basic项目面向 .NET 10 和 .NET Framework 4.8.1 并引用此包。使用 .NET 10 SDK 构建项目。

示例查询 Northwind 示例数据库中的 dbo.Products 表。 SqlCommand 会筛选出 UnitPrice 大于 5 的产品。 SqlParameter 将价格指定为 Money。 SqlDataReader 按单价降序读取产品 ID、单价和产品名称。 using 块（在 Visual Basic 中为 Using）释放读取器、命令和 SqlConnection。

在运行任一项目之前，请在启动该示例的进程中设置 SQL_CONNECTION_STRING 环境变量。例如，在启用了集成身份验证的 Windows 上，请使用以下 PowerShell 命令。将 <server> 替换为你的服务器名称，并使用客户端信任的证书配置服务器。

$env:SQL_CONNECTION_STRING = 'Server=<server>;Database=Northwind;Integrated Security=true;Encrypt=true;TrustServerCertificate=false'

有关其他身份验证选项，请参阅连接字符串语法。

重要

Microsoft 建议使用最安全的可用身份验证流。如果要连接到 Azure SQL，建议使用 Azure 资源的托管标识这种身份验证方法。

若要使用驱动程序的 Microsoft Entra ID 身份验证模式（包括托管标识），还需安装 Microsoft.Data.SqlClient.Extensions.Azure NuGet 包。有关设置说明，请参阅Microsoft Entra ID身份验证。

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

class Program
{
    static void Main()
    {
        string connectionString = Environment.GetEnvironmentVariable("SQL_CONNECTION_STRING")
            ?? throw new InvalidOperationException("Set the SQL_CONNECTION_STRING environment variable.");

        const string queryString =
            "SELECT ProductID, UnitPrice, ProductName FROM dbo.Products "
            + "WHERE UnitPrice > @pricePoint ORDER BY UnitPrice DESC;";

        using (SqlConnection connection = new SqlConnection(connectionString))
        using (SqlCommand command = new SqlCommand(queryString, connection))
        {
            command.Parameters.Add("@pricePoint", SqlDbType.Money).Value = 5m;
            connection.Open();

            using (SqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    Console.WriteLine($"{reader.GetInt32(0)}\t{reader.GetDecimal(1)}\t{reader.GetString(2)}");
                }
            }
        }
    }
}

Option Explicit On
Option Strict On

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

Module Program
    Sub Main()
        Dim connectionString As String = Environment.GetEnvironmentVariable("SQL_CONNECTION_STRING")
        If String.IsNullOrWhiteSpace(connectionString) Then
            Throw New InvalidOperationException("Set the SQL_CONNECTION_STRING environment variable.")
        End If

        Const queryString As String =
            "SELECT ProductID, UnitPrice, ProductName FROM dbo.Products " &
            "WHERE UnitPrice > @pricePoint ORDER BY UnitPrice DESC;"

        Using connection As New SqlConnection(connectionString),
              command As New SqlCommand(queryString, connection)
            command.Parameters.Add("@pricePoint", SqlDbType.Money).Value = 5D
            connection.Open()

            Using reader As SqlDataReader = command.ExecuteReader()
                While reader.Read()
                    Console.WriteLine($"{reader.GetInt32(0)}{vbTab}{reader.GetDecimal(1)}{vbTab}{reader.GetString(2)}")
                End While
            End Using
        End Using
    End Sub
End Module

从任一代码片段项目目录运行 dotnet run --framework net10.0。在已安装 .NET Framework 4.8.1 的 Windows 上，请使用 dotnet run --framework net481。

OleDb

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

using System;
using System.Data.OleDb;
using System.Runtime.Versioning;

// API is only supported on Windows
[SupportedOSPlatform("windows")]
static class Program
{
    static void Main()
    {
        const string connectionString =
        "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
            + "c:\\Data\\Northwind.mdb;...";

        // Provide the query string with a parameter placeholder.
        const string queryString =
            "SELECT ProductID, UnitPrice, ProductName from products "
                + "WHERE UnitPrice > ? "
                + "ORDER BY UnitPrice DESC;";

        // Specify the parameter value.
        const 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(connectionString))
        {
            // Create the Command and Parameter objects.
            OleDbCommand command = new(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{reader[0]}\t{reader[1]}\t{reader[2]}");
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            Console.ReadLine();
        }
    }

Option Explicit On
Option Strict On
Imports System.Data.OleDb
Imports System.Runtime.Versioning

Public Class Program
    <SupportedOSPlatform("Windows")>
    Public Shared Sub Main()

        ' The connection string assumes that the Access
        ' Northwind.mdb is located in the c:\Data folder.
        Dim connectionString As String = "..."

        ' Provide the query string with a parameter placeholder.
        Dim queryString As String =
            "SELECT ProductID, UnitPrice, ProductName from Products " _
            & "WHERE UnitPrice > ? " _
            & "ORDER BY UnitPrice DESC;"

        ' Specify the parameter value.
        Dim paramValue As Integer = 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 connection As New OleDbConnection(connectionString)

            ' Create the Command and Parameter objects.
            Dim command As 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()
                Dim dataReader As OleDbDataReader =
                 command.ExecuteReader()
                Do While dataReader.Read()
                    Console.WriteLine(
                        vbTab & "{0}" & vbTab & "{1}" & vbTab & "{2}",
                     dataReader(0), dataReader(1), dataReader(2))
                Loop
                dataReader.Close()

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

ODBC

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

using System;
using System.Data.Odbc;

static class Program
{
    static void Main()
    {
        const string connectionString =
            "Driver={Microsoft Access Driver (*.mdb)};...";

        // Provide the query string with a parameter placeholder.
        const string queryString =
            "SELECT ProductID, UnitPrice, ProductName from products "
                + "WHERE UnitPrice > ? "
                + "ORDER BY UnitPrice DESC;";

        // Specify the parameter value.
        const 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(connectionString))
        {
            // Create the Command and Parameter objects.
            OdbcCommand command = new(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{reader[0]}\t{reader[1]}\t{reader[2]}");
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            Console.ReadLine();
        }
    }
}

Option Explicit On
Option Strict On
Imports System.Data.Odbc

Public Class Program
    Public Shared Sub Main()

        ' The connection string assumes that the Access
        ' Northwind.mdb is located in the c:\Data folder.
        Dim connectionString As String = "..."

        ' Provide the query string with a parameter placeholder.
        Dim queryString As String =
            "SELECT ProductID, UnitPrice, ProductName from Products " _
            & "WHERE UnitPrice > ? " _
            & "ORDER BY UnitPrice DESC;"

        ' Specify the parameter value.
        Dim paramValue As Integer = 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 connection As New OdbcConnection(connectionString)

            ' Create the Command and Parameter objects.
            Dim command As 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()
                Dim dataReader As OdbcDataReader =
                 command.ExecuteReader()
                Do While dataReader.Read()
                    Console.WriteLine(
                        vbTab & "{0}" & vbTab & "{1}" & vbTab & "{2}",
                     dataReader(0), dataReader(1), dataReader(2))
                Loop
                dataReader.Close()

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

Oracle客户端

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

重要

Microsoft 建议使用最安全的可用身份验证流。如果要连接到 Azure SQL，建议使用 Azure 资源的托管标识这种身份验证方法。

using System;
using System.Data.OracleClient;

static class Program
{
    static void Main()
    {
        const string connectionString =
            "Data Source=ThisOracleServer;Integrated Security=yes;";
        const string queryString =
            "SELECT CUSTOMER_ID, NAME FROM DEMO.CUSTOMER";
        using (OracleConnection connection =
                   new(connectionString))
        {
            OracleCommand command = connection.CreateCommand();
            command.CommandText = queryString;

            try
            {
                connection.Open();

                OracleDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    Console.WriteLine($"\t{reader[0]}\t{reader[1]}");
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }
    }
}

Option Explicit On
Option Strict On
Imports Oracle.ManagedDataAccess.Client

Public Class Program
    Public Shared Sub Main()

        Dim connectionString As String =
            "..."

        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
End Class

实体框架示例

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

LINQ to Entities

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

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

Option Explicit On
Option Strict On

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

类型化 ObjectQuery

此示例中的代码使用 ObjectQuery<T> 以 Categories 对象的形式返回数据。有关详细信息，请参阅对象查询。

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

Option Explicit On
Option Strict On

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

EntityClient

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

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

Option Explicit On
Option Strict On

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

LINQ to SQL

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

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

Option Explicit On
Option Strict On

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

请参阅

上次更新时间为 2026-10-23