DbConnection、DbCommand 和 DbException (ADO.NET)
在建立 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.
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
// Takes a DbConnection and creates a DbCommand to retrieve data
// from the Categories table by executing a DbDataReader.
static void DbCommandSelect(DbConnection connection)
{
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.");
}
}
執行命令範例
這個範例會使用 DbConnection 物件做為引數。 如果 DbConnection 有效，則連接會開啟，接著再建立及執行 DbCommand。 CommandText 會設定為 SQL INSERT 陳述式，以對 Northwind 資料庫的 Categories 資料表執行插入作業。 程式碼會假設 Northwind 資料庫位於資料來源，而用於 INSERT 陳述式的 SQL 語法則對指定的提供者有效。 發生在資料來源的錯誤會由 DbException 程式碼區塊處理，所有其他的例外則是在 Exception 區塊中處理。
' 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
// 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')";
int 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.");
}
}
使用 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.
}