Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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;
}
like image 983
Mythul Avatar asked May 10 '13 20:05

Mythul


People also ask

What does DataAdapter fill method do?

The Fill method of the DataAdapter is used to populate a DataSet with the results of the SelectCommand of the DataAdapter . Fill takes as its arguments a DataSet to be populated, and a DataTable object, or the name of the DataTable to be filled with the rows returned from the SelectCommand .

Which is faster DataReader or DataAdapter?

Answers. Datareaders are fast compare to DataAdapters/DataSets because of the following reason. DataReader offers better performance because it avoids the performance and memory overhead associated with the creation of the DataSet.

Which interface does the DataAdapter class implement?

The IDataAdapter interface allows an inheriting class to implement a DataAdapter class, which represents the bridge between a data source and a DataSet. For more information about DataAdapter classes, see Populating a DataSet from a DataAdapter.


1 Answers

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();                 
}
like image 196
Lahiru Avatar answered Oct 16 '22 11:10

Lahiru