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