Mythul Mythul - 2 months ago 8
SQL Question

How to use DataAdapter with SqlTransaction in C#?

I need to create 2 buttons, one for starting a transaction (

startButton
) and one for committing a transaction (
commitButton
).

I got stuck trying to implement it for the
SELECT
command.

When I press the
commitButton
I get the error that the transaction has already completed and is no longer usable.

public void startTransaction(IsolationLevel isolationLevel) {
connectSQL();
connection.Open();
transaction = connection.BeginTransaction(isolationLevel);
Console.WriteLine("Transaction started !");
}

public void commitTransaction() {
this.transaction.Commit();
connection.Close();
Console.WriteLine("Transaction commited !");
}

public DataTable readAllRezervari() {
try {
String selectSql = "SELECT * FROM Rezervari;";
SqlCommand sqlCommand = new SqlCommand(selectSql, connection, transaction);
rezervariDataAdapter.SelectCommand = sqlCommand;
rezervariDataAdapter.Fill(rezervariDataSet, "Rezervari");
}
catch (Exception e) {
Console.WriteLine("ERROR: " + e);
try {
transaction.Rollback();
}
catch (Exception e2) {
Console.WriteLine("ERROR: " + e2);
}
}
finally {
connection.Close();
}
rezervariDataTable = rezervariDataSet.Tables["Rezervari"];
return rezervariDataTable;
}

Answer

It appears you are trying to use Transaction Commit and Rollback as a way for the user to commit or rollback their changes. I always believed it was incase there was an issue with the code or the data you could prevent multiple changes from being partially commited resulting in bad data. I really know more vb.net than c# so forgive me if my format is off.

Here is a thought:commit all their data to a datatable and store it in a session variable or view state

something like

 rezervariDataTable  = session("reservari");

if the user hits commit button: It does the code to write the transaction to the database.

If the user hits rollback:

     reservariDataTable = Nothing;
Comments