John,
Yes, it can be done. I have done it successfully. Look at the code snippet
below. You may have to make some modification since you have multiple tables
in the dataset. My recommendation would be--loop through the dataset for
each table, create a new DataTable with the GetChanges() method on the table
and pass it to the function. The new table will be created with the default
name "Table" which is an important requirement.
I am sure it will work. Good luck.
Prodip Saha
A Freelance Developer
public bool UpdateEntries(DataTable dtTable)
{
try
{
OleDbCommand updateCmd=CreateUpdateCommand();
OleDbCommand insertCmd=CreateInsertCommand();
OleDbCommand deleteCmd=CreateDeleteCommand();
int
retVal=ExecuteNonQuery(DatabaseConnectionString,updateCmd,insertCmd,deleteCm
d,dtTable,true);
if (retVal >0)
{
return true;
}
else
{
return false;
}
}
catch(Exception ex)
{
throw new Exception("User Defined Error:Unable to update entries.",ex);
}
}
private OleDbCommand CreateUpdateCommand(int EntryListType)
{
OleDbCommand cmd = new OleDbCommand();
cmd.Connection=new OleDbConnection(Database Connection String);
cmd.Connection.Open();
cmd.CommandTimeout=your custom time out in sec;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText="stored proc name";
OleDbCommandBuilder.DeriveParameters(cmd);
foreach(OleDbParameter paramCurrent in cmd.Parameters)
{
if (paramCurrent.Direction==ParameterDirection.Input)
{
paramCurrent.SourceColumn=paramCurrent.ParameterName;
}
}
if(cmd.Connection.State==ConnectionState.Open)
{
cmd.Connection.Close();
}
return cmd;
}
private OleDbCommand CreateInsertCommand(int EntryListType)
{
OleDbCommand cmd = new OleDbCommand();
cmd.Connection=new OleDbConnection(Database Connection String);
cmd.Connection.Open();
cmd.CommandTimeout=your custom time out in sec;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText="stored proc name";
OleDbCommandBuilder.DeriveParameters(cmd);
foreach(OleDbParameter paramCurrent in cmd.Parameters)
{
if (paramCurrent.Direction==ParameterDirection.Input)
{
paramCurrent.SourceColumn=paramCurrent.ParameterName;
}
}
if(cmd.Connection.State==ConnectionState.Open)
{
cmd.Connection.Close();
}
return cmd;
}
private OleDbCommand CreateDeleteCommand()
{
OleDbCommand cmd = new OleDbCommand();
cmd.Connection=new OleDbConnection(Database Connection String);
cmd.Connection.Open();
cmd.CommandTimeout=your custom time out in sec;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText="stored proc name";
OleDbCommandBuilder.DeriveParameters(cmd);
foreach(OleDbParameter paramCurrent in cmd.Parameters)
{
if (paramCurrent.Direction==ParameterDirection.Input)
{
paramCurrent.SourceColumn=paramCurrent.ParameterName;
paramCurrent.SourceVersion=DataRowVersion.Original;
}
}
if(cmd.Connection.State==ConnectionState.Open)
{
cmd.Connection.Close();
}
return cmd;
}
public int ExecuteNonQuery(string
connectionString,OleDbCommand updateCommand,OleDbCommand
insertCommand,OleDbCommand deleteCommand,DataTable dataTable,bool
useTransaction)
{
int retval=0;
OleDbTransaction txn=null;
OleDbConnection cn=null;
//Check if the DataTable has updated records. If so, an update command is
required.
DataTable updatedTable=dataTable.GetChanges(DataRowState.Modified);
if ((updatedTable !=null) && (updateCommand==null))
{
throw new Exception("Update Failed. Data has been modified but no update
command is specified.");
}
//Check if the DataTable has inserted records. If so, an insert command
is required.
DataTable insertedTable=dataTable.GetChanges(DataRowState.Added);
if ((insertedTable !=null) && (insertCommand==null))
{
throw new Exception("Update Failed. Data has been inserted but no insert
command is specified.");
}
//Check if the DataTable has deleted records. If so, a delete command is
required.
DataTable deletedTable=dataTable.GetChanges(DataRowState.Deleted);
if ((deletedTable !=null) && (deleteCommand==null))
{
throw new Exception("Update Failed. Data has been deleted but no delete
command is specified.");
}
try
{
//create & open a OleDbConnection, and dispose of it after we are done.
cn = new OleDbConnection(connectionString);
cn.Open();
//Create a data adapter
OleDbDataAdapter da = new OleDbDataAdapter();
//Assign commands to the adapter
if (updatedTable !=null)
{
updateCommand.Connection=cn;
da.UpdateCommand=updateCommand;
}
if (insertedTable !=null)
{
insertCommand.Connection=cn;
da.InsertCommand=insertCommand;
}
if (deletedTable !=null)
{
deleteCommand.Connection=cn;
da.DeleteCommand=deleteCommand;
}
//Create a new transaction if useTransaction is true
if (useTransaction==true)
{
txn = cn.BeginTransaction();
//Set the Transaction property of the DataAdapter's Commands.
if (updatedTable !=null)
{
da.UpdateCommand.Transaction = txn;
}
if (insertedTable !=null)
{
da.InsertCommand.Transaction = txn;
}
if (deletedTable !=null)
{
da.DeleteCommand.Transaction = txn;
}
}
//Submit the changes.
retval=da.Update(dataTable);
//Commit the changes and close the connection.
if (txn !=null)
{
txn.Commit();
}
return retval;
}
catch(Exception ex)
{
try
{
if (txn !=null)
{
txn.Rollback();
}
}
catch (OleDbException OleDbex)
{
throw OleDbex;
}
throw ex;
}
finally
{
if (cn !=null)
{
if (cn.State ==ConnectionState.Open)
{
cn.Close();
}
}
}
}
