Oracle transaction fails with create table

[SBGibson]

Hi guys, I've a big problem.
I'm using system.data.oracleclient to connect to an oracle database and modify his structure.
Hi do that inside a transaction but it commit everytime.
In fact if I put a command inside a transaction and the command text is an INSERT INTO or DELETE etc it works, when I launch rollback the modify is undone, but if the commandtext is CREATE TABLE, ALTER TABLE etc the modify is always done, without taking the transaction into consideration.
Any idea?

Stevie B. Gibson

 

[obislavu]

Ho do you launch the rollback ?
I suggest you to use stored procedures to update, delete or create tables instead to build these commands in the C# code.
In the code you do :
-connect to DB
-prepare the parameters for the stored procedures
-execute the stored procedure with the prepared parameters
-capture the return code of the store procedure execution
-disconnect from DB
The roolback should be puit in the stored procedure.
Example of calling a sp named sP_Delete which takes 4 parameters and returns the number of deleted records:

//  SQLConnection is a SqlConnection object stored somewhere
public int Delete()
{
	int vReturn=0;
	SqlDataAdapter vSqlDataAdapter=new SqlDataAdapter();
	if (SQLConnection.State==System.Data.ConnectionState.Closed) SQLConnection.Open();
	vSqlDataAdapter.SelectCommand = new SqlCommand("sp_Delete",SQLConnection);
	vSqlDataAdapter.SelectCommand.CommandType =CommandType.StoredProcedure;
	SqlParameter vSqlParameter= vSqlDataAdapter.SelectCommand.Parameters.Add("@ReturnValue", SqlDbType.Int);
	vSqlParameter.Direction = ParameterDirection.ReturnValue;
	vSqlParameter= vSqlDataAdapter.SelectCommand.Parameters.Add("@Username", Environment.UserName);
	vSqlParameter= vSqlDataAdapter.SelectCommand.Parameters.Add("@ItemID", m_ID);
	vSqlParameter= vSqlDataAdapter.SelectCommand.Parameters.Add("@SN", m_SN);
	vSqlParameter= vSqlDataAdapter.SelectCommand.Parameters.Add("@Model", m_Model);
	DataSet vDataSet = new DataSet();
	vSqlDataAdapter.Fill(vDataSet);
	vReturn= (Int32) vSqlDataAdapter.SelectCommand.Parameters["@ReturnValue"].Value;
	return(vReturn);
}

I used a DataSet object to capture the returned value just to be prepared in the case the stored procedures returns a record set.
-obislavu-

 

[chiph]

AFAIK, DDL (Create Table, Alter Table, etc) statements take place immediately, and do not respect transactions. This is because they affect the structure of the database, and not the values stored in the database.

Chip H.


If you want to get the best response to a question, please check out FAQ222-2244 first