使用 DbDataAdapter 修改数据 (ADO.NET)
更新：November 2007
DbProviderFactory 对象的 CreateDataAdapter 方法为您提供 DbDataAdapter 对象，该对象强类型化为创建工厂时指定的基础数据提供程序。 然后您可以使用 DbCommandBuilder 创建命令，以插入、更新和删除数据源的 DataSet 中的数据。
使用 DbDataAdapter 检索数据
此示例演示如何基于提供程序名称和连接字符串来创建强类型的 DbDataAdapter。 代码使用 DbProviderFactory 的 CreateConnection 方法创建 DbConnection。 接下来，代码使用 CreateCommand 方法创建 DbCommand 以便通过设置其 CommandText 和 Connection 属性来选择数据。 最后，代码使用 CreateDataAdapter 方法创建 DbDataAdapter 对象并设置其 SelectCommand 属性。 DbDataAdapter 的 Fill 方法将数据加载到 DataTable 中。
Shared Sub CreateDataAdapter(ByVal providerName As String, _
ByVal connectionString As String)
' Create the DbProviderFactory and DbConnection.
Try
Dim factory As DbProviderFactory = _
DbProviderFactories.GetFactory(providerName)
Dim connection As DbConnection = _
factory.CreateConnection()
connection.ConnectionString = connectionString
Using connection
' Define the query.
Dim queryString As String = _
"SELECT CategoryName FROM Categories"
'Create the DbCommand.
Dim command As DbCommand = _
factory.CreateCommand()
command.CommandText = queryString
command.Connection = connection
' Create the DbDataAdapter.
Dim adapter As DbDataAdapter = _
factory.CreateDataAdapter()
adapter.SelectCommand = command
' Fill the DataTable
Dim table As New DataTable
adapter.Fill(table)
'Display each row and column value.
Dim row As DataRow
Dim column As DataColumn
For Each row In table.Rows
For Each column In table.Columns
Console.WriteLine(row(column))
Next
Next
End Using
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
End Sub
static void CreateDataAdapter(string providerName, string connectionString)
{
try
{
// Create the DbProviderFactory and DbConnection.
DbProviderFactory factory =
DbProviderFactories.GetFactory(providerName);
DbConnection connection = factory.CreateConnection();
connection.ConnectionString = connectionString;
using (connection)
{
// Define the query.
string queryString =
"SELECT CategoryName FROM Categories";
// Create the DbCommand.
DbCommand command = factory.CreateCommand();
command.CommandText = queryString;
command.Connection = connection;
// Create the DbDataAdapter.
DbDataAdapter adapter = factory.CreateDataAdapter();
adapter.SelectCommand = command;
// Fill the DataTable.
DataTable table = new DataTable();
adapter.Fill(table);
// Display each row and column value.
foreach (DataRow row in table.Rows)
{
foreach (DataColumn column in table.Columns)
{
Console.WriteLine(row[column]);
}
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
使用 DbDataAdapter 修改数据
此示例演示如何通过使用 DbCommandBuilder 生成更新数据源的数据所需的命令，从而使用 DbDataAdapter 来修改 DataTable 中的数据。 DbDataAdapter 的 SelectCommand 设置为从 Customers 表检索 CustomerID 和 CompanyName。 GetInsertCommand 方法用于设置 InsertCommand 属性，GetUpdateCommand 方法用于设置 UpdateCommand 属性，而 GetDeleteCommand 方法用于设置 DeleteCommand 属性。 代码向 Customers 表中添加一新行并更新数据源。 然后，代码通过搜索 CustomerID（为 Customers 表定义的主键）定位添加的行。 代码更改 CompanyName 并更新数据源。 最后，代码删除该行。
Shared Sub CreateDataAdapter(ByVal providerName As String, _
ByVal connectionString As String)
' Create the DbProviderFactory and DbConnection.
Try
Dim factory As DbProviderFactory = _
DbProviderFactories.GetFactory(providerName)
Dim connection As DbConnection = _
factory.CreateConnection()
connection.ConnectionString = connectionString
Using connection
' Define the query.
Dim queryString As String = _
"SELECT CustomerID, CompanyName FROM Customers"
'Create the select command.
Dim command As DbCommand = _
factory.CreateCommand()
command.CommandText = queryString
command.Connection = connection
' Create the DbDataAdapter.
Dim adapter As DbDataAdapter = _
factory.CreateDataAdapter()
adapter.SelectCommand = command
' Create the DbCommandBuilder.
Dim builder As DbCommandBuilder = _
factory.CreateCommandBuilder()
builder.DataAdapter = adapter
' Get the insert, update and delete commands.
adapter.InsertCommand = builder.GetInsertCommand()
adapter.UpdateCommand = builder.GetUpdateCommand()
adapter.DeleteCommand = builder.GetDeleteCommand()
' Display the CommandText for each command.
Console.WriteLine("InsertCommand: {0}", _
adapter.InsertCommand.CommandText)
Console.WriteLine("UpdateCommand: {0}", _
adapter.UpdateCommand.CommandText)
Console.WriteLine("DeleteCommand: {0}", _
adapter.DeleteCommand.CommandText)
' Fill the DataTable
Dim table As New DataTable
adapter.Fill(table)
' Insert a new row.
Dim newRow As DataRow = table.NewRow
newRow("CustomerID") = "XYZZZ"
newRow("CompanyName") = "XYZ Company"
table.Rows.Add(newRow)
adapter.Update(table)
' Display rows after insert.
Console.WriteLine()
Console.WriteLine("----List All Rows-----")
Dim row As DataRow
For Each row In table.Rows
Console.WriteLine("{0} {1}", row(0), row(1))
Next
Console.WriteLine("----After Insert-----")
' Edit an existing row.
Dim editRow() As DataRow = _
table.Select("CustomerID = 'XYZZZ'")
editRow(0)("CompanyName") = "XYZ Corporation"
adapter.Update(table)
' Display rows after update.
Console.WriteLine()
For Each row In table.Rows
Console.WriteLine("{0} {1}", row(0), row(1))
Next
Console.WriteLine("----After Update-----")
' Delete a row.
Dim deleteRow() As DataRow = _
table.Select("CustomerID = 'XYZZZ'")
For Each row In deleteRow
row.Delete()
Next
adapter.Update(table)
table.AcceptChanges()
' Display each row and column value after delete.
Console.WriteLine()
For Each row In table.Rows
Console.WriteLine("{0} {1}", row(0), row(1))
Next
Console.WriteLine("----After Delete-----")
Console.WriteLine("Customer XYZZZ was deleted.")
End Using
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
End Sub
static void CreateDataAdapter(string providerName, string connectionString)
{
try
{
// Create the DbProviderFactory and DbConnection.
DbProviderFactory factory =
DbProviderFactories.GetFactory(providerName);
DbConnection connection = factory.CreateConnection();
connection.ConnectionString = connectionString;
using (connection)
{
// Define the query.
string queryString =
"SELECT CustomerID, CompanyName FROM Customers";
// Create the select command.
DbCommand command = factory.CreateCommand();
command.CommandText = queryString;
command.Connection = connection;
// Create the DbDataAdapter.
DbDataAdapter adapter = factory.CreateDataAdapter();
adapter.SelectCommand = command;
// Create the DbCommandBuilder.
DbCommandBuilder builder = factory.CreateCommandBuilder();
builder.DataAdapter = adapter;
// Get the insert, update and delete commands.
adapter.InsertCommand = builder.GetInsertCommand();
adapter.UpdateCommand = builder.GetUpdateCommand();
adapter.DeleteCommand = builder.GetDeleteCommand();
// Display the CommandText for each command.
Console.WriteLine("InsertCommand: {0}",
adapter.InsertCommand.CommandText);
Console.WriteLine("UpdateCommand: {0}",
adapter.UpdateCommand.CommandText);
Console.WriteLine("DeleteCommand: {0}",
adapter.DeleteCommand.CommandText);
// Fill the DataTable.
DataTable table = new DataTable();
adapter.Fill(table);
// Insert a new row.
DataRow newRow = table.NewRow();
newRow["CustomerID"] = "XYZZZ";
newRow["CompanyName"] = "XYZ Company";
table.Rows.Add(newRow);
adapter.Update(table);
// Display rows after insert.
Console.WriteLine();
Console.WriteLine("----List All Rows-----");
foreach (DataRow row in table.Rows)
{
Console.WriteLine("{0} {1}", row[0], row[1]);
}
Console.WriteLine("----After Insert-----");
// Edit an existing row.
DataRow[] editRow = table.Select("CustomerID = 'XYZZZ'");
editRow[0]["CompanyName"] = "XYZ Corporation";
adapter.Update(table);
// Display rows after update.
Console.WriteLine();
foreach (DataRow row in table.Rows)
{
Console.WriteLine("{0} {1}", row[0], row[1]);
}
Console.WriteLine("----After Update-----");
// Delete a row.
DataRow[] deleteRow = table.Select("CustomerID = 'XYZZZ'");
foreach (DataRow row in deleteRow)
{
row.Delete();
}
adapter.Update(table);
// Display rows after delete.
Console.WriteLine();
foreach (DataRow row in table.Rows)
{
Console.WriteLine("{0} {1}", row[0], row[1]);
}
Console.WriteLine("----After Delete-----");
Console.WriteLine("Customer XYZZZ was deleted.");
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
处理参数
.NET Framework 数据提供程序以不同方式处理命名和指定参数和参数占位符。 此语法针对特定数据源量身定制，如下表所述。
数据提供程序 |
参数命名语法 |
|---|---|
SqlClient |
以 @参数名 格式使用命名参数。 |
OracleClient |
以 :参数名（或参数名）格式使用命名参数。 |
OleDb |
使用由问号 (?) 指示的位置参数标记。 |
Odbc |
使用由问号 (?) 指示的位置参数标记。 |
工厂模型对于创建参数化 DbCommand 和 DbDataAdapter 对象不具有帮助价值。 您需要编写分支代码来创建针对数据提供程序定制的参数。
.gif "安全说明") 安全说明： |
|---|
出于安全原因，建议不要通过采用字符串串联的形式构造直接 SQL 语句来完全避免提供程序特定的参数。 使用字符串串联代替参数会使您的应用程序容易受到 SQL 注入攻击。 |