创建 a DbProviderFactory 和 a DbConnection后，可以使用命令和数据读取器从数据源检索数据。
检索数据示例
此示例采用对象 DbConnection 作为参数。 创建一个 DbCommand，通过将 CommandText 设置为 SQL SELECT 语句，从“类别”表中选择数据。 代码假定“类别”表存在于数据源中。 打开连接并使用 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 被设置为执行向 Northwind 数据库的 Categories 表插入数据的 SQL INSERT 语句。 该代码假定 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 、GetTypeSource和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.
}