Design issue with a try....catch....finally block

[Julia B]

Hi, I'm wondering if anyone can give me any ideas/good practice/advice.

I've got a web form which a user inputs lots of data into, then presses
submit. The submit button uses two classes to input data into 2 tables. I
want to put a try....catch...finally block into the data input sections of
the classes or submit.on_click event to ensure that, if there's a problem
with data entry into the database, that the system doesn't crash and the
connection remains open & locked.

However, my dilemma is where do I put the try....catch....finally? If I put
one in each of the subs in the two classes I have an issue if the second sub
fails. I want both or none of them to succeed, i.e. if the first one enters
data correctly and the second one fails, then the system crashes but I've got
a data problem.

If I put the try....catch....finally in the webform, I've got the problem
that the connection objects are declared and used in the classes, so I could
not close or dispose of them from the webform (correct me if I'm wrong
here!!!).

So I'm a bit stuck. What's the best way to design something like this? Has
anyone any advice.

Thanks
Julia

 

[Guest]

Hi, I'm wondering if anyone can give me any ideas/good practice/advice.

I've got a web form which a user inputs lots of data into, then presses
submit. The submit button uses two classes to input data into 2 tables. I
want to put a try....catch...finally block into the data input sections of
the classes or submit.on_click event to ensure that, if there's a problem
with data entry into the database, that the system doesn't crash and the
connection remains open & locked.

However, my dilemma is where do I put the try....catch....finally? If I put
one in each of the subs in the two classes I have an issue if the second sub
fails. I want both or none of them to succeed, i.e. if the first one enters
data correctly and the second one fails, then the system crashes but I've got
a data problem.

If I put the try....catch....finally in the webform, I've got the problem
that the connection objects are declared and used in the classes, so I could
not close or dispose of them from the webform (correct me if I'm wrong
here!!!).

So I'm a bit stuck. What's the best way to design something like this? Has
anyone any advice.

Thanks
Julia

What database are you using? SQL Server?

Use SQL Server Transactions

using (SqlConnection connection =
new SqlConnection(connectionString))
{
SqlCommand command = connection.CreateCommand();
SqlTransaction transaction = null;

try
{
// BeginTransaction() Requires Open Connection
connection.Open();

transaction = connection.BeginTransaction();

// Assign Transaction to Command
command.Transaction = transaction;

// Execute 1st sub using the opened Command
command.CommandText = "Insert ...";
command.ExecuteNonQuery();

// Execute 2nd sub using the same Command
command.CommandText = "Update...";
command.ExecuteNonQuery();

transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
finally
{
connection.Close();
}
}

 

[Julia B]

Really sorry, I should have been more specific. I'm using Microsoft Access.

Julia