.NET Framework 开发人员指南

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) 连接并检索数据。如果需要，所有数据提供程序都可以在单个的应用程序中使用。

下面的 SqlClient 示例假定您可以连接到 Microsoft SQL Server 7.0 或更高版本上的 Northwind 示例数据库，并使用 SqlDataReader 从 Categories 表返回记录列表。OleDb 和 Odbc 示例假定已与 Microsoft Access Northwind 示例数据库建立连接。OracleClient 示例假定已与 Oracle 服务器上的 DEMO.CUSTOMER 建立连接。还必须添加对 System.Data.OracleClient.dll 的引用。

有关 DataReader 的更多信息，请参见使用 DataReader 检索数据。

SqlClient

Visual Basic

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

OleDb

Visual Basic

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=;";
    }
}

Odbc

Visual Basic

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=;";
    }
}

OracleClient

Visual Basic

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

SqlClient

OleDb

Odbc

OracleClient

请参见

概念

其他资源