DbConnection、DbCommand 和 DbException
建立 DbProviderFactory 和 DbConnection之後，您就可以使用命令和數據讀取器從數據源擷取數據。
擷取數據範例
此範例會將 DbConnection 對象當做自變數。 建立 DbCommand，藉由將 CommandText 設定為 SQL SELECT 語句，以從 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($"{reader[0]}. {reader[1]}");
}
}
catch (Exception ex)
{
Console.WriteLine($"Exception.Message: {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 {rows} rows.");
}
// Handle data errors.
catch (DbException exDb)
{
Console.WriteLine($"DbException.GetType: {exDb.GetType()}");
Console.WriteLine($"DbException.Source: {exDb.Source}");
Console.WriteLine($"DbException.ErrorCode: {exDb.ErrorCode}");
Console.WriteLine($"DbException.Message: {exDb.Message}");
}
// Handle all other exceptions.
catch (Exception ex)
{
Console.WriteLine($"Exception.Message: {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 類別是代表數據源擲回之所有例外狀況的基類。 您可以在例外狀況處理程序代碼中使用它來處理不同提供者擲回的例外狀況，而不需要參考特定的例外狀況類別。 下列代碼段示範如何使用 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.
}