Using the SqlDataAdapter's Update Method to submit changes to the DataBase

An interesting feature of ADO.NET is using the SqlDataAdapter's Update Method. This method will iterate through all the DataRows of your DataSet and, depending on the values in the RowState, assign that row to the appropriate Stored Procedure for processing. Additionally it will automatically map the DataSet's Columns to the corresponding Stored Procedure's parameters. Notice in the Code below that the only parameter is the modified DataSet. This is the only place in all of the code where it will be directly referenced, in the Update method. Of the five methods below, SubmitChangesToPerformance(), SelectPerformanceCommand(), DeletePerformanceCommand(), InsertPerformanceCommand() and UpdatePerformanceCommand() only SubmitChangesToPerformance()is public and it is the only one to take an argument. What this means is that if this codes is in a DataAccess Layer, then only the SubmitChangesToPerformance() is exposed.

What this means is that after you have done all your modification to your DataSet, inserts, updates and deletes, when submitting the DataSet to your DataAccess Layer, all the rows in the Sql Datbase Table will be modified by executing a single line of code..

MyDataAccess.SubmitChangesToPerformance(MyDataSet);

Before moving on, the SqlDataAdapter's Update Method also works with DataTables. If your DataSet is made up of several related DataTables you can pass each DataTable object to their appropriate Update method.

MyDataAccess.SubmitChangesToPerformance(MyDataSet.Tables["Performance"]);
MyDataAccess.SubmitChangesToPerformers(MyDataSet.Tables["Performers"]);
MyDataAccess.SubmitChangesToConcerts(MyDataSet.Tables["Concerts"]);

To accomplish this we'll use an example of an update procedure, (scroll down)...

public void SubmitChangesToPerformance(DataSet ds) << The DataSet with all its modified
                                                   rows is passed in as the only argument
{
    SqlDataAdapter da = new SqlDataAdapter();   //Create an SqlDataAdapter
 SqlCon.Open();     //Open an SqlConnection so we can...
 SqlTransaction tx = SqlCon.BeginTransaction(); //...attach a transaction to it.

 //Assign the SqlCommand Objects to their 
 //appropriate SqlDataAdapter's counter parts...
 da.SelectCommand = SelectPerformanceCommand();
 da.DeleteCommand = DeletePerformanceCommand();
 da.InsertCommand = InsertPerformanceCommand();
 da.UpdateCommand = UpdatePerformanceCommand();

 //Now assign the Transaction object to the SqlDataAdapter >
 da.DeleteCommand.Transaction = tx;
 da.InsertCommand.Transaction = tx;
 da.UpdateCommand.Transaction = tx;

 try
 {
  da.Update(ds); << This is where all the action take place,           in this one method.
  tx.Commit();
 }
 catch(SqlException SqlEx) //On Sql Exception, roll back and throw an exception
 {
  tx.Rollback();
  throw SqlEx;
 }
 catch(Exception Ex) //On Any other Exception, roll back and throw an exception
 {
  tx.Rollback();
  throw Ex;
 }
 finally
 {
  SqlCon.Close();
 }
}
 

This first step is to set up the Stored Procedures. Nothing unusual here, you simply want an Insert, Update and Delete Stored procedure to correspond with matching Insert, Update and Delete SqlCommand objects you will write. This has the added advantage a insuring that the DataSet's DataTable Column names match the Sql Table's Column Names. ( You can name your columns anything you want, but if you do you will be stuck using the DataTableMapping object to get this system to work. That will involve a lot of ugly code.) So a simple Stored Procedure would be...

CREATE PROCEDURE dbo.sp_PerformUpdate
(
 @PerformID int,
 @CompID int,
 @ConcertHallID int,
 @PerfDate varchar(50),
 @Version varchar(50),
 @Recording varchar(50),
 @Length varchar(50),
 @HandHNotes text,
 @Original_CompID int, << Note the @Original_... Parameters and how they are used.
 @Original_ConcertHallID int,
 @Original_PerfDate varchar(50),
 @Original_Version varchar(50)
)
AS
 SET NOCOUNT OFF;
UPDATE tblPerformance SET CompID = @CompID, ConcertHallID = @ConcertHallID ,
 PerfDate = @PerfDate, Version = @Version, Recording = @Recording, Length = @Length ,
 HandHNotes = @HandHNotes
WHERE PerformID = @PerformID AND CompID = @Original_CompID AND ConcertHallID = @Original_ConcertHallID
 AND PerfDate = @Original_PerfDate AND Version = @Original_Version
GO

Note the four parameters that start with @Original-. This is a optional concurrancy check and takes advantage of the DataSet's DataRow RowState feature. When you update or delete a DataRow in the DataSet the old values are saved with in a DataRow State of 'Original'. You can use that value to perform simple concurrency checks like here, but it is not needed by the Update method.

Now that the Stored Procedure is done, and we have a public SubmitChangesToPerformance() method that evokes the SqlDataAdapter's Update method, there is only one more piece of code to write, a method that returns the SqlCommand object. Think of this method as the 'glue' between the SubmitChangesToPerformance method and the Stored Procedure.

private SqlCommand UpdatePerformanceCommand()
{
 SqlCommand cmd = new SqlCommand("sp_PerformUpdate",SqlCon); //Create an SqlCommand that calls the sproc
 cmd.CommandType = CommandType.StoredProcedure;

 SqlParameterCollection pc = cmd.Parameters;  //Create an SqlParameter Collection
 SqlParameter param;

 //Reference each sproc param with correct Data Type and Row Version
 param = pc.Add("@PerformID",SqlDbType.Int,4,"PerformID"); << The DataDow's Column is also named 'PerformID'.
 param.SourceVersion = DataRowVersion.Current;          << We're asking for the DataDow's 'Current' version here.
 param = pc.Add("@CompID",SqlDbType.Int,4,"CompID");
 param.SourceVersion = DataRowVersion.Current;
 param = pc.Add("@ConcertHallID",SqlDbType.Int,4,"ConcertHallID");
 param.SourceVersion = DataRowVersion.Current;
 param = pc.Add("@PerfDate",SqlDbType.VarChar,50,"PerfDate");
 param.SourceVersion = DataRowVersion.Current;
 param = pc.Add("@Version",SqlDbType.VarChar,50,"Version");
 param.SourceVersion = DataRowVersion.Current;
 param = pc.Add("@Recording",SqlDbType.VarChar,50,"Recording");
 param.SourceVersion = DataRowVersion.Current;
 param = pc.Add("@Length",SqlDbType.VarChar,50,"Length");
 param.SourceVersion = DataRowVersion.Current;
 param = pc.Add("@HandHNotes",SqlDbType.Text,0,"HandHNotes");
 param.SourceVersion = DataRowVersion.Current;

 //Get Data Row's Original Version for the Concurrance Check params
 param = pc.Add("@Original_CompID",SqlDbType.Int,4,"CompID");
 param.SourceVersion = DataRowVersion.Original;       << We're asking for the DataDow's 'Original' version here.
 param = pc.Add("@Original_ConcertHallID",SqlDbType.Int,4,"ConcertHallID");
 param.SourceVersion = DataRowVersion.Original;
 param = pc.Add("@Original_PerfDate",SqlDbType.VarChar,50,"PerfDate");
 param.SourceVersion = DataRowVersion.Original;
 param = pc.Add("@Original_Version",SqlDbType.VarChar,50,"Version");
 param.SourceVersion = DataRowVersion.Original;

 return cmd;  //Return the SqlCommand Object
}

The first part of the method creates an SqlCommand that references the above Stored Procedure, "sp_PerformUpdate". It next creates an SqlParameter Collection where there will be a one to one matching between the Stored Procedure parameters and the DataSet's DatRow columns. Make sure the data types match between the DataSet Columns, Database Columns and the SqlParameter. The first 8 assignemts uses the Data Row's current data. This is the data that is going to update the Table's Columns. The second 4cassignments uses the DataRow's original version for concurrency checking. Implemented in the Stored Procedure's WHERE clause.

To make life easier on yourself, make the DataSet's DataColumn names and the Sql Table Column names and the Stored Procedure Parameter names all the same. This method works best if there is a one-to-one-to-one relationship between the Sql Table Columns, the DataSet's Data Columns and the Stored Procedure's parameters in both name and data types.

That's it! It really works. As the SqlDataAdapter's Update method iterates through the rows of the DataSet, when one has a DataRowState of "Modified" it will evoke UpdatePerformanceCommand(). That method will assign the DataSet's Columns to the Stored Procedure's parameter collection and then evoke the Procedure. That Sql Table's row will be updated with the values from the DataSet. Likewise when the Update method encounters DataRowStates of "Deleted" or "Added" it will evoke DeletePerformanceCommand() or InsertPerformanceCommand() respectively.

For the sake of completeness here is the code for Inserting and Deleting along with a little snipit about SqlDataAdapter's Fill method.

CREATE PROCEDURE dbo.sp_PerformInsert
(
 @CompID int,
 @ConcertHallID int,
 @PerfDate varchar(50),
 @Version varchar(50),
 @Recording varchar(50),
 @Length varchar(50),
 @HandHNotes text
)
AS
 SET NOCOUNT OFF;
INSERT INTO tblPerformance (CompID, ConcertHallID, PerfDate, Version, Recording, Length, HandHNotes)
VALUES (@CompID, @ConcertHallID, @PerfDate, @Version, @Recording, @Length, @HandHNotes)

GO

CREATE PROCEDURE dbo.sp_PerformDelete
(
 @PerformID int,
 @Original_CompID int,
 @Original_ConcertHallID int,
 @Original_PerfDate varchar(50),
 @Original_Version varchar(50)
)
AS
 SET NOCOUNT OFF;
DELETE FROM tblPerformance
WHERE @PerformID = @PerformID AND CompID = @Original_CompID AND ConcertHallID = @Original_ConcertHallID
     AND PerfDate = @Original_PerfDate AND Version = @Original_Version

GO

--------------------------------------------------------------

private SqlCommand InsertPerformanceCommand()
{
 SqlCommand cmd = new SqlCommand("sp_PerformInsert",SqlCon); //Create an SqlCommand that calls the sproc
 cmd.CommandType = CommandType.StoredProcedure;

 SqlParameterCollection pc = cmd.Parameters;  //Create an SqlParameter Collection
 SqlParameter param;

 //Reference each sproc param with correct Data Type and Row Version
 param = pc.Add("@CompID",SqlDbType.Int,4,"CompID");
 param.SourceVersion = DataRowVersion.Current;
 param = pc.Add("@ConcertHallID",SqlDbType.Int,4,"ConcertHallID");
 param.SourceVersion = DataRowVersion.Current;
 param = pc.Add("@PerfDate",SqlDbType.VarChar,50,"PerfDate");
 param.SourceVersion = DataRowVersion.Current;
 param = pc.Add("@Version",SqlDbType.VarChar,50,"Version");
 param.SourceVersion = DataRowVersion.Current;
 param = pc.Add("@Recording",SqlDbType.VarChar,50,"Recording");
 param.SourceVersion = DataRowVersion.Current;
 param = pc.Add("@Length",SqlDbType.VarChar,50,"Length");
 param.SourceVersion = DataRowVersion.Current;
 param = pc.Add("@HandHNotes",SqlDbType.Text,0,"HandHNotes");
 param.SourceVersion = DataRowVersion.Current;

 return cmd;  //Return the SqlCommand Object
}


private SqlCommand DeletePerformanceCommand()
{
 SqlCommand cmd = new SqlCommand("sp_PerformDelete",SqlCon); //Create an SqlCommand that calls the sproc
 cmd.CommandType = CommandType.StoredProcedure;

 SqlParameterCollection pc = cmd.Parameters;  //Create an SqlParameter Collection
 SqlParameter param;

 //Reference each sproc param with correct Data Type and Row Version
 param = pc.Add("@PerformID",SqlDbType.Int,4,"PerformID");
 param.SourceVersion = DataRowVersion.Original;
 param = pc.Add("@Original_CompID",SqlDbType.Int,4,"CompID");
 param.SourceVersion = DataRowVersion.Original;
 param = pc.Add("@Original_ConcertHallID",SqlDbType.Int,4,"ConcertHallID");
 param.SourceVersion = DataRowVersion.Original;
 param = pc.Add("@Original_PerfDate",SqlDbType.VarChar,50,"PerfDate");
 param.SourceVersion = DataRowVersion.Original;
 param = pc.Add("@Original_Version",SqlDbType.VarChar,50,"Version");
 param.SourceVersion = DataRowVersion.Original;

 return cmd;  //Return the SqlCommand Object
}

If you have not used the SqlDataAdapter much it's actually quite simple to use and it packs in a lot of functionality. A very usefull method, besides the Update Method above, is the Fill Method. It will populate a DataSet's DataTable and do a few other usefull things. Here's a quick example...

DataSet MyDataSet = new DataSet();
..........
string strSqlCon = "data source=.........";
string strQuery = "SELECT * FROM tblXyz";
SqlDataAdapter MyAdapter = new SqlDataAdapter(strSqlCon,strQuery);
MyAdapter.Fill(MyDataSet);
.........
.........

Notice there is no SqlConnection or SqlCommand object. Since there is no SqlConnection object there is likewise no Open() or Close() methods. The SqlDataAdapter takes care of all of that. It creates (internally) the Command object, opens a connection to the DataBase, executes the query, populates the DataSet's DataTable then closes the connection. The SqlDataAdapter is heavily overloaded so I recomend you read David Sceppa's book, ADO.NET Core Reference

Much of this information I obtained from David Sceppa's excelent book, ADO.NET Core Reference and my personal experience using this method building the Handel and Haydn On-Line Database.