Extending the DataAdapter with a Helper Class [C#]

One of the coolest things about ADO.NET is the ability to supply a DataAdapter (either OleDb. ODBC or Sql) with a select Command, and have it generate its own update, insert and delete logic. What's more, you can supply Stored Procedures for all these and just call the Update method on the DataAdapter, passing in a DataTable or a DataSet.

The DA will iterate over the rows, check their current status via the RowState property, and handle updates, inserts and deletes to your database all in one fell swoop. This is great for situations like a Webservice handling your server side, because you can send over to your WebService only the changes - not the whole DataSet. When you call the DataSet's GetChanges method, you receive a new DataSet with the same structure as the original, but it contains only the modified rows - a nice bandwidth savings.

When this "update" version of the DataSet is passed to a DataAdapter on the receiving (Server - side) end, it can be configured to handle all the inserts, updates and deletes automatically. What I've done here is experiment with a little "DataAdapterHandler" helper class that uses the stored proc names to call the DeriveParameters method of the CommandBuilder class to fill in everything - in fact, as long as your sprocs have parameter names that are the same as the column names they'll update (e.g., "FirstName" would be "@FirstName"), all you need to do is slice off the "@" sign from the param name and you can tell the DA what the source column is for that parameter. It will then proceed to build the commands automatically.

You can get a lot more sophisticated than this, but I think this, by itself, is a very handy little helper class when you are working with say, a one-table DataSet that you're displaying in an updateable Grid. The downloadable source code has an overload on SubmitChanges to handle passing in a DataSet instead of a DataTable, and also sports a nice WinForms front end that hooks into the Northwind Database to demonstrate how easy it is to use.

What I've accomplished with this is very simple.  When you click the "Update" button on your "whatever" front end , all you need to do is issue this single line of code, and all your inserts, updates and deletes are "good to go":

PAB.Data.DAHandler.SubmitChanges(myDataSet, strConn, "NewUpdateCommand", "NewInsertCommand", "NewDeleteCommand")

Here's the code for the helper class:

using System;
using System.Collections;
using System.Data;
using System.Diagnostics;
using System.Data.SqlClient;
namespace PAB.Data
{
public class DAHandler
{
    public static SqlCommand UpdateCmd;
    public static SqlCommand InsertCmd;
    public static SqlCommand DeleteCmd;

public static int SubmitChanges( DataSet ds, string connectionString, string spUpDateName, string spInsertName, string spDeleteName)
{
  return SubmitChanges( ds.Tables[0], connectionString, spUpDateName, spInsertName, spDeleteName);

}

public static int SubmitChanges( DataTable tbl, string connectionString, string spUpDateName, string spInsertName, string spDeleteName)
{

    int res = 0;
try {
SqlDataAdapter da = new SqlDataAdapter();
if (!string.IsNullOrEmpty(spUpDateName)) {
da.UpdateCommand = CreateUpdateViaSPCommand(spUpDateName, connectionString);
    UpdateCmd = da.UpdateCommand;
}
if (!string.IsNullOrEmpty(spInsertName)) {
da.InsertCommand = CreateInsertViaSPCommand(spInsertName, connectionString);
    InsertCmd = da.InsertCommand;
}
if (!string.IsNullOrEmpty(spDeleteName)) {
da.DeleteCommand = CreateDeleteViaSPCommand(spDeleteName, connectionString);
    DeleteCmd = da.DeleteCommand;
}
da.ContinueUpdateOnError = false;
 res= da.Update(tbl);
                 
} catch  {
throw;
 
}
return res;
}

private static SqlCommand CreateUpdateViaSPCommand(string spName, string strConn)
{
SqlConnection cn = new SqlConnection(strConn);
SqlCommand cmd = new SqlCommand(spName, cn);
cmd.CommandType = CommandType.StoredProcedure;
SqlCommandBuilder cb = new SqlCommandBuilder();
cn.Open();
SqlCommandBuilder.DeriveParameters(cmd);
cn.Close();
 
foreach (SqlParameter parm in cmd.Parameters) {
 
// Use the param name to name the source column for the DA
                parm.SourceVersion = DataRowVersion.Current;
parm.SourceColumn = Convert.ToString(parm.ParameterName).Remove(0, 1);

}
return cmd;
}



private static SqlCommand CreateInsertViaSPCommand(string spName, string strConn)
{
SqlConnection cn = new SqlConnection(strConn);
SqlCommand cmd = new SqlCommand(spName, cn);
cmd.CommandType = CommandType.StoredProcedure;
 
cn.Open();
SqlCommandBuilder.DeriveParameters(cmd);
cn.Close();
 
foreach (SqlParameter parm in cmd.Parameters) {
parm.SourceVersion = DataRowVersion.Current;
// Use the param name from DeriveParameters to provide the source column
parm.SourceColumn = Convert.ToString(parm.ParameterName).Remove(0, 1);
}
return cmd;
}


private static SqlCommand CreateDeleteViaSPCommand(string spName, string strConn)
{
SqlConnection cn = new SqlConnection(strConn);
SqlCommand cmd = new SqlCommand(spName, cn);
cmd.CommandType = CommandType.StoredProcedure;
 
cn.Open();
SqlCommandBuilder.DeriveParameters(cmd);
cn.Close();
 
foreach (SqlParameter  parm in cmd.Parameters) {
parm.SourceVersion = DataRowVersion.Current;
parm.SourceColumn = Convert.ToString(parm.ParameterName).Remove(0, 1);

}
return cmd;
}
}
}

In the "Test Harness" windows form app in my demo, you have two buttons, "Load" and "Update". The Northwind Employees table will be shown in the Grid. You can edit any row, add a new row, or delete a row. When you press the "Update" button, all the insert, update and delete operations will be  performed and a messagebox will come up showing the number of rows affected.

Download the Visual Studio 2010 Demo solution.

By Peter Bromberg   Popularity  (3405 Views)