How to use DataAdapter with SqlTransaction in C#?

Question

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;
}

Solution 1

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;

Solution 2

using (SqlConnection cn = new SqlConnection(ConnectionString.GetConnection()))         
{             
    cn.Open();
    SqlTransaction transction = cn.BeginTransaction();
    SqlDataAdapter sda= new SqlDataAdapter("Select * From TableName", cn);
    sda.SelectCommand.Transaction = transction; 
    sda.Fill(ds, "TableName");
    transction.Commit();                 
}