What I do:
public static DataTable FillDataTable(String StoredProcedure, List<sqlparameter> sqlParameters, String ConnectionName)
{
DataTable tabel = new DataTable("GeneralTable");
SqlTransaction transaction = null;
using (SqlCommand cmd = BaseDatabaseData(StoredProcedure, sqlParameters, ConnectionName))
{
try
{
using (transaction = cmd.Connection.BeginTransaction("SampleTransaction"))
{
cmd.Transaction = transaction;
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(tabel);
transaction.Commit();
}
}
catch (Exception ex)
{
String Message = ex.Message;
transaction.Rollback();
throw new Exception(ex.Message, ex.InnerException);
}
finally
{
if (cmd.Connection.State.Equals(ConnectionState.Open))
{
cmd.Connection.Close();
cmd.Connection.Dispose();
}
}
}
return tabel;
}
</sqlparameter>
I know I use the MSSql connection and not the Oracle one. I believe that setting up a transaction will work with the Oracle connection as with the mssql connection.
Method BaseDatabaseData returns me an open SqlConnection