DataAdapters batch operations


Batch Operations with DataAdapters
DataAdapter

Image Credit:developerfusion.com

Before the arrival of Batch Operations with DataAdapters, the DataAdapter offered a single row data operation.
With the arrival of .Net Framework 2.0,it has become quite easier to perform operations with UpdateCommand, InsertCommand, or DeleteCommand, depending on the value of the RowState property for that row. Every row update involved a network round-trip to the database.

Types of DataAdapters

The OleDbDataAdapter object is suitable for use with any data source exposed by an OLE DB provider.

The SqlDataAdapter object is specific to SQL Server. Because it does not have to go through an OLE DB layer, it is faster than the OleDbDataAdapter class. However, it can only be used with SQL Server 7.0 or later.

The OdbcDataAdapter object is optimized for accessing ODBC data sources.

The OracleDataAdapter object is optimized for accessing Oracle databases.

public static void UpdaeBundle(DataTable dataTable,Int32 batchSize)
{
string ConnStr = “data source=abc; initial catalog=abc; user id=a; password=mypassword”
using (SqlConnection con = new SqlConnection(ConnStr))
{
da
SqlDataAdapter da = new SqlDataAdapter();

da.UpdateCommand = new SqlCommand(
“UPDATE Employees SET ”
+ “[email protected] WHERE [email protected];”,
con);
da.UpdateCommand.Parameters.Add(“@EmployeeName”,
SqlDbType.NVarChar, 25, “EmployeeName”);
da.UpdateCommand.Parameters.Add(“@EmployeeID”,
SqlDbType.Int, 4, “EmployeeID”);
da.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
da.InsertCommand = new SqlCommand(
“INSERT INTO Employees(EmployeeName) VALUES (@EmployeeName);”,
con);
da.InsertCommand.Parameters.Add(“@EmployeeName”,
SqlDbType.NVarChar, 25, “EmployeeName”);
da.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
da.DeleteCommand = new SqlCommand(
“DELETE FROM Employees”
+ “WHERE [email protected];”, con);
da.DeleteCommand.Parameters.Add(“@EmployeeID”,
SqlDbType.Int, 4, “EmployeeID”);
da.DeleteCommand.UpdatedRowSource = UpdateRowSource.None;
da.UpdateBatchSize = batchSize;

da.Update(dataTable);
}
}

Syed Adeel Ahmed
Syed Adeel Ahmed
Analyst, Programmer, Educationist and Blogger at Technofranchise
Computer Systems Engineer from Sir Syed University Of Engineering & Technology.I am passionate about all types of programming.

Published by

Syed Adeel Ahmed

Computer Systems Engineer from Sir Syed University Of Engineering & Technology.I am passionate about all types of programming.

Leave a Reply