DbConnection、DbCommand 和 DbException
在建立 DbProviderFactory 和 DbConnection 之後，接著就可以使用命令和資料讀取器從資料來源擷取資料。
擷取資料範例
這個範例會使用 DbConnection 物件做為引數。 然後會建立 DbCommand，並藉由設定 SQL SELECT 陳述式的 CommandText，從 Categories 資料表選擇資料。 程式碼假設 Categories 資料表位於資料來源。 接著會開啟連接，並使用 DbDataReader 擷取資料。
// Takes a DbConnection and creates a DbCommand to retrieve data
// from the Categories table by executing a DbDataReader.
static void DbCommandSelect(DbConnection connection)
{
const string queryString =
"SELECT CategoryID, CategoryName FROM Categories";
// Check for valid DbConnection.
if (connection != null)
{
using (connection)
{
try
{
// Create the command.
DbCommand command = connection.CreateCommand();
command.CommandText = queryString;
command.CommandType = CommandType.Text;
// Open the connection.
connection.Open();
// Retrieve the data.
DbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Console.WriteLine("{0}. {1}", reader[0], reader[1]);
}
}
catch (Exception ex)
{
Console.WriteLine("Exception.Message: {0}", ex.Message);
}
}
}
else
{
Console.WriteLine("Failed: DbConnection is null.");
}
}
' Takes a DbConnection and creates a DbCommand to retrieve data
' from the Categories table by executing a DbDataReader.
Private Shared Sub DbCommandSelect(ByVal connection As DbConnection)
Dim queryString As String = _
"SELECT CategoryID, CategoryName FROM Categories"
' Check for valid DbConnection.
If Not connection Is Nothing Then
Using connection
Try
' Create the command.
Dim command As DbCommand = connection.CreateCommand()
command.CommandText = queryString
command.CommandType = CommandType.Text
' Open the connection.
connection.Open()
' Retrieve the data.
Dim reader As DbDataReader = command.ExecuteReader()
Do While reader.Read()
Console.WriteLine("{0}. {1}", reader(0), reader(1))
Loop
Catch ex As Exception
Console.WriteLine("Exception.Message: {0}", ex.Message)
End Try
End Using
Else
Console.WriteLine("Failed: DbConnection is Nothing.")
End If
End Sub
執行命令範例
這個範例會使用 DbConnection 物件做為引數。 如果 DbConnection 有效，則連接會開啟，接著再建立及執行 DbCommand。 CommandText 會設定為 SQL INSERT 陳述式，以對 Northwind 資料庫的 Categories 資料表執行插入作業。 程式碼會假設 Northwind 資料庫位於資料來源，而用於 INSERT 陳述式的 SQL 語法則對指定的提供者有效。 發生在資料來源的錯誤會由 DbException 程式碼區塊處理，所有其他的例外則是在 Exception 區塊中處理。
// Takes a DbConnection, creates and executes a DbCommand.
// Assumes SQL INSERT syntax is supported by provider.
static void ExecuteDbCommand(DbConnection connection)
{
// Check for valid DbConnection object.
if (connection != null)
{
using (connection)
{
try
{
// Open the connection.
connection.Open();
// Create and execute the DbCommand.
DbCommand command = connection.CreateCommand();
command.CommandText =
"INSERT INTO Categories (CategoryName) VALUES ('Low Carb')";
var rows = command.ExecuteNonQuery();
// Display number of rows inserted.
Console.WriteLine("Inserted {0} rows.", rows);
}
// Handle data errors.
catch (DbException exDb)
{
Console.WriteLine("DbException.GetType: {0}", exDb.GetType());
Console.WriteLine("DbException.Source: {0}", exDb.Source);
Console.WriteLine("DbException.ErrorCode: {0}", exDb.ErrorCode);
Console.WriteLine("DbException.Message: {0}", exDb.Message);
}
// Handle all other exceptions.
catch (Exception ex)
{
Console.WriteLine("Exception.Message: {0}", ex.Message);
}
}
}
else
{
Console.WriteLine("Failed: DbConnection is null.");
}
}
' Takes a DbConnection and executes an INSERT statement.
' Assumes SQL INSERT syntax is supported by provider.
Private Shared Sub ExecuteDbCommand(ByVal connection As DbConnection)
' Check for valid DbConnection object.
If Not connection Is Nothing Then
Using connection
Try
' Open the connection.
connection.Open()
' Create and execute the DbCommand.
Dim command As DbCommand = connection.CreateCommand()
command.CommandText = _
"INSERT INTO Categories (CategoryName) VALUES ('Low Carb')"
Dim rows As Integer = command.ExecuteNonQuery()
' Display number of rows inserted.
Console.WriteLine("Inserted {0} rows.", rows)
' Handle data errors.
Catch exDb As DbException
Console.WriteLine("DbException.GetType: {0}", exDb.GetType())
Console.WriteLine("DbException.Source: {0}", exDb.Source)
Console.WriteLine("DbException.ErrorCode: {0}", exDb.ErrorCode)
Console.WriteLine("DbException.Message: {0}", exDb.Message)
' Handle all other exceptions.
Catch ex As Exception
Console.WriteLine("Exception.Message: {0}", ex.Message)
End Try
End Using
Else
Console.WriteLine("Failed: DbConnection is Nothing.")
End If
End Sub
使用 DbException 處理資料錯誤
DbException 類別是代表資料來源所擲回之所有例外狀況的基底類別 (Base Class)。 您可以將此類別運用於例外狀況處理程式碼中，如此可以處理由不同提供者所擲回的例外狀況，但不必參照特定的例外狀況類別。 下列程式碼片段示範如何使用 DbException，顯示由資料來源藉由 GetType、Source、ErrorCode 和 Message 等屬性所傳回的錯誤資訊。 輸出中會顯示錯誤類別、代表提供者名稱的來源、錯誤碼以及與錯誤相關聯的訊息。
Try
' Do work here.
Catch ex As DbException
' Display information about the exception.
Console.WriteLine("GetType: {0}", ex.GetType())
Console.WriteLine("Source: {0}", ex.Source)
Console.WriteLine("ErrorCode: {0}", ex.ErrorCode)
Console.WriteLine("Message: {0}", ex.Message)
Finally
' Perform cleanup here.
End Try
try
{
// Do work here.
}
catch (DbException ex)
{
// Display information about the exception.
Console.WriteLine("GetType: {0}", ex.GetType());
Console.WriteLine("Source: {0}", ex.Source);
Console.WriteLine("ErrorCode: {0}", ex.ErrorCode);
Console.WriteLine("Message: {0}", ex.Message);
}
finally
{
// Perform cleanup here.
}