chandrasekar
New member
- Joined
- Jul 3, 2016
- Messages
- 1
- Programming Experience
- 3-5
Hello to everyone in this forum
I am in the process of developing a cusom application which uses a MS ACESS database (MSOFFICE 2007) as the back end and using c#.net (with VISUAL STUDIO 2010).
Let me briefly explain my problem.
I am trying to use transactions in the app. I have several tables in the database and using the VS Designer to create the data tables and tableadapters.
I have a situation in which mutiple tables need to be updated when a particular condition is avhieved. So I used transaction to correctly store the data.
That part of the code which does this task isexplained in the follwing pseudocode with relevant explanation.
As I am new to the transactions, I am unable to figure the error I am making. I searched several forums in the internet, but could not get a clear solution.
It would be of immense help if the experienced forum members can point out my mistake and give me a solution.
Thanks in advance for all the members who were patiently reading this long message.
I hope I made a clear description of my problem. In case if anyone needs any additional explanation, I can give to those who require. Pls. mail to rdcsekar@gmail.com with subject as "OLEDBTRANSACTION ROLLBACK IN C#" for any further explanation.
Thanks in advance for the solution
With great regards
chandrasekar
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{
// CREATE THE TRANSACTION
} }
*******************************************************************************************
SAMPLE************** AttachConnAndTrans(mytrans) TO ATTACH THE CONNECTION AND TRANSACTION OBJECT.
public void AttachConnAndTrans(OleDbTransaction tran)
{
this.Adapter.DeleteCommand.Connection = tran.Connection;
this.Adapter.DeleteCommand.Transaction = tran;
this.Adapter.InsertCommand.Connection = tran.Connection;
this.Adapter.InsertCommand.Transaction = tran;
this.Adapter.SelectCommand.Connection = tran.Connection;
this.Adapter.SelectCommand.Transaction = tran;
this.Adapter.UpdateCommand.Connection = tran.Connection;
this.Adapter.UpdateCommand.Transaction = tran;
foreach (OleDbCommand cmd in this.CommandCollection)
{
cmd.Connection = tran.Connection;
cmd.Transaction = tran;
}
}
}
***************************************************************************************
I am in the process of developing a cusom application which uses a MS ACESS database (MSOFFICE 2007) as the back end and using c#.net (with VISUAL STUDIO 2010).
Let me briefly explain my problem.
I am trying to use transactions in the app. I have several tables in the database and using the VS Designer to create the data tables and tableadapters.
I have a situation in which mutiple tables need to be updated when a particular condition is avhieved. So I used transaction to correctly store the data.
That part of the code which does this task isexplained in the follwing pseudocode with relevant explanation.
As I am new to the transactions, I am unable to figure the error I am making. I searched several forums in the internet, but could not get a clear solution.
It would be of immense help if the experienced forum members can point out my mistake and give me a solution.
Thanks in advance for all the members who were patiently reading this long message.
I hope I made a clear description of my problem. In case if anyone needs any additional explanation, I can give to those who require. Pls. mail to rdcsekar@gmail.com with subject as "OLEDBTRANSACTION ROLLBACK IN C#" for any further explanation.
Thanks in advance for the solution
With great regards
chandrasekar
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{
// CREATE THE TRANSACTION
try
{
{
// The database is MS-ACCESS 2007 mdb database****************
/ALL THE *****adp OBJECTS ARE DESIGNER GENERATED TABLEADAPTERS
OleDbConnection mycon = codadp.Getconnection();..........
if (mycon.State == ConnectionState.Closed) mycon.Open();
mytrans = mycon.BeginTransaction();
// aTTACH TRANSACTION OBJECT TO ALL THE TABLEADAPTER'S COMMAND OBJECTS. A partial class of these adapters are created and this method is added to all the required tableadapters
codadp.AttachConnAndTrans(mytrans);
itmadp.AttachConnAndTrans(mytrans);
maincostadp.AttachConnAndTrans(mytrans);
mainstkadp.AttachConnAndTrans(mytrans);
seccostadp.AttachConnAndTrans(mytrans);
secstkadp.AttachConnAndTrans(mytrans);
ITEMRow nrow = itmtab.NewITEMRow();
.......... FILL THE nrow DATAROW
itmtab.Rows.Add(nrow);
MainCostRow mcrow = maincosttab.NewMainCostRow();
MainStockRow msrow= mainstktab.NewMainStockRow();
SecondaryCostRow scrow= seccosttab.NewSecondaryCostRow();
SecondaryStockRow ssrow = secstktab.NewSecondaryStockRow();
.................. FILL THE DATAROWS
itmadp.Update(nrow);
maincosttab.Rows.Add(mcrow);
maincostadp.Update(mcrow);
mainstktab.Rows.Add(msrow); .......... THIS TABLE NEEDS A FIELD WITH ITS 'required' PROPERTY SET TO 'yes' but not supplied in the newly created row................ so updating this table will throw exception.
mainstkadp.Update(msrow);
seccosttab.Rows.Add(scrow);
seccostadp.Update(scrow);
secstktab.Rows.Add(ssrow);
secstkadp.Update(ssrow);
// WHEN EVERYTHING IS OK THEN COMMIT
mytrans.Commit();
/ALL THE *****adp OBJECTS ARE DESIGNER GENERATED TABLEADAPTERS
OleDbConnection mycon = codadp.Getconnection();..........
if (mycon.State == ConnectionState.Closed) mycon.Open();
mytrans = mycon.BeginTransaction();
// aTTACH TRANSACTION OBJECT TO ALL THE TABLEADAPTER'S COMMAND OBJECTS. A partial class of these adapters are created and this method is added to all the required tableadapters
codadp.AttachConnAndTrans(mytrans);
itmadp.AttachConnAndTrans(mytrans);
maincostadp.AttachConnAndTrans(mytrans);
mainstkadp.AttachConnAndTrans(mytrans);
seccostadp.AttachConnAndTrans(mytrans);
secstkadp.AttachConnAndTrans(mytrans);
ITEMRow nrow = itmtab.NewITEMRow();
.......... FILL THE nrow DATAROW
itmtab.Rows.Add(nrow);
MainCostRow mcrow = maincosttab.NewMainCostRow();
MainStockRow msrow= mainstktab.NewMainStockRow();
SecondaryCostRow scrow= seccosttab.NewSecondaryCostRow();
SecondaryStockRow ssrow = secstktab.NewSecondaryStockRow();
.................. FILL THE DATAROWS
itmadp.Update(nrow);
maincosttab.Rows.Add(mcrow);
maincostadp.Update(mcrow);
mainstktab.Rows.Add(msrow); .......... THIS TABLE NEEDS A FIELD WITH ITS 'required' PROPERTY SET TO 'yes' but not supplied in the newly created row................ so updating this table will throw exception.
mainstkadp.Update(msrow);
seccosttab.Rows.Add(scrow);
seccostadp.Update(scrow);
secstktab.Rows.Add(ssrow);
secstkadp.Update(ssrow);
// WHEN EVERYTHING IS OK THEN COMMIT
mytrans.Commit();
}
catch (Exception ex)
{
catch (Exception ex)
{
MessageBox.Show("incomplete transaction: " + ex.Message);//.... THIS MSG WAS DISPLAYED WHILE RUNNING THE APPLICATION , BUT PREVIOUS ROW UPDATES WERE NOT ROLLED BACK...........
try
{
{
mytrans.Rollback();
}
catch (Exception ex1)
{
catch (Exception ex1)
{
MessageBox.Show(" failed rollback. " + ex1.Message);// ...... JUST CURIOUS TO KNOW THE EXCEPTION thrown HERE BUT NO EXCEPTION WAS THROWN HERE WHILE RUNNING THE APP
}
}
*******************************************************************************************
SAMPLE************** AttachConnAndTrans(mytrans) TO ATTACH THE CONNECTION AND TRANSACTION OBJECT.
public void AttachConnAndTrans(OleDbTransaction tran)
{
this.Adapter.DeleteCommand.Connection = tran.Connection;
this.Adapter.DeleteCommand.Transaction = tran;
this.Adapter.InsertCommand.Connection = tran.Connection;
this.Adapter.InsertCommand.Transaction = tran;
this.Adapter.SelectCommand.Connection = tran.Connection;
this.Adapter.SelectCommand.Transaction = tran;
this.Adapter.UpdateCommand.Connection = tran.Connection;
this.Adapter.UpdateCommand.Transaction = tran;
foreach (OleDbCommand cmd in this.CommandCollection)
{
cmd.Connection = tran.Connection;
cmd.Transaction = tran;
}
}
}
***************************************************************************************