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 Entity Framework:
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 範例資料庫。這段程式碼會建立 SqlCommand,以便從 Products 資料表中選取資料列,並且加入 SqlParameter,以便將結果限制為 UnitPrice 大於指定之參數值 (在此案例中為 5) 的資料列。SqlConnection 是在 using 區塊內部開啟,可確保程式碼結束時,系統會關閉和處置 (Dispose) 資源。然後,程式碼會使用 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 範例資料庫。這段程式碼會建立 OleDbCommand,以便從 Products 資料表中選取資料列,並且加入 OleDbParameter,以便將結果限制為 UnitPrice 大於指定之參數值 (在此案例中為 5) 的資料列。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 範例資料庫。這段程式碼會建立 OdbcCommand,以便從 Products 資料表中選取資料列,並且加入 OdbcParameter,以便將結果限制為 UnitPrice 大於指定之參數值 (在此案例中為 5) 的資料列。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]
Entity Framework 範例
下列程式碼清單將示範如何透過查詢 Entity Data Model (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 來執行 Entity SQL 查詢。這個查詢會傳回代表 Categories 實體類型之執行個體 (Instance) 的記錄清單。EntityDataReader 可用於存取結果集中的資料記錄。如需詳細資訊,請參閱 EntityClient Provider for Entity Framework。
[Top]
LINQ to SQL
這則範例中的程式碼會使用 LINQ 查詢來傳回資料當做 Categories 物件,而這些物件會投影成僅包含 CategoryID 和 CategoryName 屬性的匿名型別。這則範例是以 Northwind 資料內容為基礎。如需詳細資訊,請參閱使用者入門 (LINQ to SQL)。
[Top]
請參閱
其他資源
Querying an Entity Data Model (Entity Framework Tasks)
How to: Execute a Query that Returns an Anonymous Type (Entity Framework)