Access DB Transaction on insert or updating
- by Raju Gujarati
I am going to implement the database access layer of the Window application using C#. The database (.accdb) is located to the project files. When it comes to two notebooks (clients) connecting to one access database through switches, it throws DBConcurrency Exception Error. My target is to check the timestamp of the sql executed first and then run the sql .
Would you please provide me some guidelines to achieve this ?
The below is my code
protected void btnTransaction_Click(object sender, EventArgs e)
{
string custID = txtID.Text;
string CompName = txtCompany.Text;
string contact = txtContact.Text;
string city = txtCity.Text;
string connString = ConfigurationManager.ConnectionStrings["CustomersDatabase"].ConnectionString;
OleDbConnection connection = new OleDbConnection(connString);
connection.Open();
OleDbCommand command = new OleDbCommand();
command.Connection = connection;
OleDbTransaction transaction = connection.BeginTransaction();
command.Transaction = transaction;
try
{
command.CommandText = "INSERT INTO Customers(CustomerID, CompanyName, ContactName, City, Country) VALUES(@CustomerID, @CompanyName, @ContactName, @City, @Country)";
command.CommandType = CommandType.Text;
command.Parameters.AddWithValue("@CustomerID", custID);
command.Parameters.AddWithValue("@CompanyName", CompName);
command.Parameters.AddWithValue("@ContactName", contact);
command.Parameters.AddWithValue("@City", city);
command.ExecuteNonQuery();
command.CommandText = "UPDATE Customers SET ContactName = @ContactName2 WHERE CustomerID = @CustomerID2";
command.CommandType = CommandType.Text;
command.Parameters.AddWithValue("@CustomerID2", custIDUpdate);
command.Parameters.AddWithValue("@ContactName2", contactUpdate);
command.ExecuteNonQuery();
adapter.Fill(table);
GridView1.DataSource = table;
GridView1.DataBind();
transaction.Commit();
lblMessage.Text = "Transaction successfully completed";
}
catch (Exception ex)
{
transaction.Rollback();
lblMessage.Text = "Transaction is not completed";
}
finally
{
connection.Close();
}
}
