使用 DbDataAdapter 修改數據
DbProviderFactory 物件的 CreateDataAdapter 方法會提供一個 DbDataAdapter 對象，這個物件會在您建立處理站時指定給基礎數據提供者的強型別。 然後，您可以使用 DbCommandBuilder 來建立命令，以將資料從 DataSet 插入、更新或刪除至數據來源。
使用 DbDataAdapter 擷取數據
此範例示範如何根據提供者名稱和連接字串建立強型別 DbDataAdapter。 程式代碼會使用 DbProviderFactory 的 CreateConnection 方法來建立 DbConnection。 接下來，程式代碼會使用 CreateCommand 方法來建立 DbCommand，藉由設定其 CommandText 和 Connection 屬性來選取數據。 最後，程式代碼會使用 CreateDataAdapter 方法建立 DbDataAdapter 物件，並設定其 SelectCommand 屬性。
DbDataAdapter 的 Fill 方法會將數據載入到 DataTable中。
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.
const 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();
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);
}
}
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
使用 DbDataAdapter 修改數據
此範例示範如何使用 DbDataAdapter 來修改 DataTable 中的數據，方法是使用 DbCommandBuilder 來產生更新數據源數據所需的命令。
DbDataAdapter 的 SelectCommand 被設定為從 Customers 資料表中擷取 CustomerID 和 CompanyName。
GetInsertCommand 方法是用來設定 InsertCommand 屬性、GetUpdateCommand 方法用來設定 UpdateCommand 屬性，並使用 GetDeleteCommand 方法來設定 DeleteCommand 屬性。 程序代碼會將新的數據列新增至 Customers 數據表，並更新數據源。 然後，程式代碼會搜尋 CustomerID 來尋找新增的數據列，這是針對 Customers 數據表定義的主鍵。 它會變更 CompanyName 並更新數據源。 最後，程式代碼會刪除資料列。
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.
const 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: {adapter.InsertCommand.CommandText}");
Console.WriteLine($"UpdateCommand: {adapter.UpdateCommand.CommandText}");
Console.WriteLine($"DeleteCommand: {adapter.DeleteCommand.CommandText}");
// Fill the DataTable.
DataTable table = new();
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($"{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($"{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($"{row[0]} {row[1]}");
}
Console.WriteLine("----After Delete-----");
Console.WriteLine("Customer XYZZZ was deleted.");
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
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
處理參數
.NET Framework 數據提供者會以不同的方式處理命名和指定參數和參數佔位符。 此語法是針對特定數據源量身打造，如下表所述。
| 資料提供者 | 參數命名語法 |
|---|---|
SqlClient |
使用具名參數，格式為 @參數名稱。 |
OracleClient |
使用具名參數的格式 :parmname（或 parmname）。 |
OleDb |
使用問號所指示的位置參數標記 （?）。 |
Odbc |
使用問號所指示的位置參數標記 （?）。 |
處理站模型不適用於建立參數化 DbCommand 和 DbDataAdapter 物件。 您需要在您的程式碼中進行分支，以建立專為您的數據提供者量身打造的參數。
這很重要
基於安全性考慮，不建議使用字串串連來建構直接 SQL 語句來完全避免提供者特定的參數。 使用字串串連，而不是參數，讓您的應用程式容易受到SQL 插入式攻擊的攻擊。