Datagrid and transactions

[luigiida]

Hi,
I use a datgrid to visualize the content of a table and to perform update and delete operations.
The problem is that I don't manage transactions: if a userA modifies or deletes a record, the userB in the same moment will not do operations in the selected table in the right way.
Attached you'll find the code:
Can anyone help me?
Thanks a lot,
Luigi

private string selectedTable="";


private void Page_Grid(object source,DataGridPageChangedEventArgs e)
{
// Set CurrentPageIndex to the page the user clicked.
grid.CurrentPageIndex = e.NewPageIndex;
DataSet ds = new DataSet();
selectedTable=ListBox2.SelectedItem.ToString();
OleDbConnection conn = new OleDbConnection(strConnect);
OleDbDataAdapter da = new OleDbDataAdapter("select * from "+selectedTable, strConnect);
da.Fill(ds, selectedTable);
conn.Close();
grid.DataSource = ds.Tables[selectedTable];
//((TextBox)e.Item.Cells[1].Controls[0])
grid.DataBind();
}
private void OnDelete(object source,System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
string delete="";
DataSet ds = new DataSet();
DataSet ds1 = new DataSet();
grid.EditItemIndex = e.Item.ItemIndex;
grid.DataSource = ds.Tables[selectedTable];
int i=0;
ArrayList cols = new ArrayList();
selectedTable=ListBox2.SelectedItem.ToString();
OleDbConnection conn = new OleDbConnection(strConnect);

OleDbDataAdapter da = new OleDbDataAdapter("select * from "+selectedTable, strConnect);
da.Fill(ds, selectedTable);

conn.Close();
if ((selectedTable!="Address&quot&&(selectedTable!="Company&quot&&(selectedTable!="Relation&quot&&(selectedTable!="AlternateName&quot&&(selectedTable!="Profile&quot)
{
outError.Text=&quot;<b>You can't delete records of this table</b>&quot;;
return;
}

foreach(DataColumn myColumn in ds.Tables[selectedTable].Columns)
{
cols.Add(myColumn.ToString());
}

string ID=e.Item.Cells[2].Text;
if (selectedTable == &quot;Company&quot
/*Cancello tutti i record in ogni tablella legati in qualche modo al CompanyID che voglio elmininare*/
{
delete=&quot;delete * from Company where CompanyID=&quot;+ID;
OleDbCommand myCommand1 = new OleDbCommand(delete,conn);
conn.Open();
myCommand1.ExecuteNonQuery();
conn.Close();

delete=&quot;delete * from Address where CompanyID=&quot;+ID;
OleDbCommand myCommand2 = new OleDbCommand(delete,conn);
conn.Open();
myCommand2.ExecuteNonQuery();
conn.Close();

delete=&quot;delete * from Profile where CompanyID=&quot;+ID;
OleDbCommand myCommand3 = new OleDbCommand(delete,conn);
conn.Open();
myCommand3.ExecuteNonQuery();
conn.Close();

delete=&quot;delete * from Relation where CompanyID=&quot;+ID;
OleDbCommand myCommand4 = new OleDbCommand(delete,conn);
conn.Open();
myCommand4.ExecuteNonQuery();
conn.Close();

delete=&quot;delete * from AlternateName where CompanyID=&quot;+ID;
OleDbCommand myCommand5 = new OleDbCommand(delete,conn);
conn.Open();
myCommand5.ExecuteNonQuery();
conn.Close();

}
else
{

if (selectedTable==&quot;Profile&quot
{
ID=e.Item.Cells[2].Text;
delete=&quot;delete * from &quot;+selectedTable+&quot; where CompanyID=&quot;+ID;
}
if (selectedTable==&quot;AlternateName&quot
{
ID=e.Item.Cells[2].Text;
string alternatename=e.Item.Cells[3].Text;
delete=&quot;delete * from &quot;+selectedTable+&quot; where CompanyID=&quot;+ID+&quot; AND AlternateName='&quot;+alternatename+&quot;'&quot;;
}
if (selectedTable==&quot;Relation&quot
{

ID=e.Item.Cells[2].Text;
string name=e.Item.Cells[3].Text;
string relation=e.Item.Cells[4].Text;
delete=&quot;delete * from &quot;+selectedTable+&quot; where CompanyID=&quot;+ID+&quot; AND RelationID=&quot;+relation+&quot; AND Name='&quot;+name+&quot;'&quot;;
}
if (selectedTable==&quot;Address&quot
{
ID=e.Item.Cells[2].Text;
delete=&quot;delete * from &quot;+selectedTable+&quot; where AddressID=&quot;+ID;
}
outError.Text=delete;
OleDbCommand myCommand = new OleDbCommand(delete,conn);
conn.Open();
myCommand.ExecuteNonQuery();
conn.Close();
}

grid.EditItemIndex = -1;
conn = new OleDbConnection(strConnect);
OleDbDataAdapter da1 = new OleDbDataAdapter(&quot;select * from &quot;+selectedTable, strConnect);
da1.Fill(ds1, selectedTable);
conn.Close();
grid.DataSource=ds1.Tables[selectedTable];
grid.EditItemIndex = -1;
grid.DataBind();

}//fine onDelete


private void OnEdit(object source,System.Web.UI.WebControls.DataGridCommandEventArgs e)
{

DataSet ds = new DataSet();
selectedTable=ListBox2.SelectedItem.ToString();
OleDbConnection conn = new OleDbConnection(strConnect);
OleDbDataAdapter da = new OleDbDataAdapter(&quot;select * from &quot;+selectedTable, strConnect);
da.Fill(ds, selectedTable);
conn.Close();
grid.DataSource = ds.Tables[selectedTable];
//((TextBox)e.Item.Cells[1].Controls[0])
grid.EditItemIndex = e.Item.ItemIndex;
grid.DataBind();
}

private void OnUpdate(object source,System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
selectedTable=ListBox2.SelectedItem.ToString();
DataSet ds = new DataSet();
DataSet ds1 = new DataSet();
grid.EditItemIndex = e.Item.ItemIndex;
grid.DataSource = ds.Tables[selectedTable];
string update=&quot;update &quot;+selectedTable+&quot; set&quot;;
int i=1;
ArrayList cols = new ArrayList();
selectedTable=ListBox2.SelectedItem.ToString();
OleDbConnection conn = new OleDbConnection(strConnect);
OleDbDataAdapter da = new OleDbDataAdapter(&quot;select * from &quot;+selectedTable, strConnect);
da.Fill(ds, selectedTable);
conn.Close();
foreach(DataColumn myColumn in ds.Tables[selectedTable].Columns)
{
cols.Add(myColumn.ToString());
}
int arraylen=cols.Count;

while (i<arraylen)
{
if (i>0)
{
// update += &quot; &quot;+cols.ToString()+&quot; = &quot;+(e.Item.Cells).Text;
update += &quot; &quot;+cols.ToString()+&quot; = '&quot;+((TextBox)e.Item.Cells[i+2].Controls[0]).Text+&quot;'&quot;;

}
if ((i<arraylen-1) && (i>0))
{
update+=&quot;,&quot;;
}
if (i == (arraylen-1))
{
update += &quot; where &quot;+cols[0]+&quot; = &quot;+((TextBox)e.Item.Cells[2].Controls[0]).Text;
}
i++;
}
outError.Text=update;
OleDbConnection myConnection = new OleDbConnection(strConnect);
OleDbCommand myCommand = new OleDbCommand(update,myConnection);
myConnection.Open();
myCommand.ExecuteNonQuery();
myConnection.Close();
outError.Text =update;
grid.EditItemIndex = -1;
conn = new OleDbConnection(strConnect);
OleDbDataAdapter da1 = new OleDbDataAdapter(&quot;select * from &quot;+selectedTable, strConnect);
da1.Fill(ds1, selectedTable);
conn.Close();
grid.DataSource=ds1.Tables[selectedTable];
grid.EditItemIndex = -1;
grid.DataBind();
}

private void OnCancel(object source,System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
grid.EditItemIndex = -1;
DataSet ds = new DataSet();
selectedTable=ListBox2.SelectedItem.ToString();
OleDbConnection conn = new OleDbConnection(strConnect);
OleDbDataAdapter da = new OleDbDataAdapter(&quot;select * from &quot;+selectedTable, strConnect);
da.Fill(ds, selectedTable);
conn.Close();
grid.DataSource = ds.Tables[selectedTable];
grid.DataBind();
}