Visual Basic 复制代码
Public Sub BatchUpdate( _
ByVal dataTable As DataTable, ByVal batchSize As Int32)
' Assumes GetConnectionString() returns a valid connection string.
Dim connectionString As String = GetConnectionString()
' Connect to the AdventureWorks database.
Using connection As New SqlConnection(connectionString)
' Create a SqlDataAdapter.
Dim adapter As New SqlDataAdapter()
'Set the UPDATE command and parameters.
adapter.UpdateCommand = New SqlCommand( _
"UPDATE Production.ProductCategory SET " _
& "Name=@Name WHERE ProductCategoryID=@ProdCatID;", _
connection)
adapter.UpdateCommand.Parameters.Add("@Name", _
SqlDbType.NVarChar, 50, "Name")
adapter.UpdateCommand.Parameters.Add("@ProdCatID", _
SqlDbType.Int, 4, " ProductCategoryID ")
adapter.UpdateCommand.UpdatedRowSource = _
UpdateRowSource.None
'Set the INSERT command and parameter.
adapter.InsertCommand = New SqlCommand( _
"INSERT INTO Production.ProductCategory (Name) VALUES (@Name);", _
connection)
adapter.InsertCommand.Parameters.Add("@Name", _
SqlDbType.NVarChar, 50, "Name")
adapter.InsertCommand.UpdatedRowSource = _
UpdateRowSource.None
'Set the DELETE command and parameter.
adapter.DeleteCommand = New SqlCommand( _
"DELETE FROM Production.ProductCategory " _
& "WHERE ProductCategoryID=@ProdCatID;", connection)
adapter.DeleteCommand.Parameters.Add("@ProdCatID", _
SqlDbType.Int, 4, " ProductCategoryID ")
adapter.DeleteCommand.UpdatedRowSource = UpdateRowSource.None
' Set the batch size.
adapter.UpdateBatchSize = batchSize
' Execute the update.
adapter.Update(dataTable)
End Using
End Sub
// Connect to the AdventureWorks database.
using (SqlConnection connection = new
SqlConnection(connectionString))
{
// Create a SqlDataAdapter.
SqlDataAdapter adapter = new SqlDataAdapter();
// Set the UPDATE command and parameters.
adapter.UpdateCommand = new SqlCommand(
"UPDATE Production.ProductCategory SET "
+ "Name=@Name WHERE ProductCategoryID=@ProdCatID;",
connection);
adapter.UpdateCommand.Parameters.Add("@Name",
SqlDbType.NVarChar, 50, "Name");
adapter.UpdateCommand.Parameters.Add("@ProdCatID",
SqlDbType.Int, 4, "ProductCategoryID");
adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
// Set the INSERT command and parameter.
adapter.InsertCommand = new SqlCommand(
"INSERT INTO Production.ProductCategory (Name) VALUES (@Name);",
connection);
adapter.InsertCommand.Parameters.Add("@Name",
SqlDbType.NVarChar, 50, "Name");
adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
// Set the DELETE command and parameter.
adapter.DeleteCommand = new SqlCommand(
"DELETE FROM Production.ProductCategory "
+ "WHERE ProductCategoryID=@ProdCatID;", connection);
adapter.DeleteCommand.Parameters.Add("@ProdCatID",
SqlDbType.Int, 4, "ProductCategoryID");
adapter.DeleteCommand.UpdatedRowSource = UpdateRowSource.None;
// Set the batch size.
adapter.UpdateBatchSize = batchSize;
// Execute the update.
adapter.Update(dataTable);
}
}