Transaction problem (Open DataReader exception)

[juhaka]

Hi!

I have done a database handler class (named MyDBConnection). My base idea was, that via this class I could execute all my database jobs (INSERT, SELECT and UPDATE prosedures). (see code, this is very stripped version, but here should be all essential parts.)


class MyDBConnection
{
SqlConnection conn = null;

public MyDBConnection(string connStr)
{
conn = new SqlConnection(connStr);
}

public void openConnection()
{
conn.open();
}

public SqlCommand getCommand(CommandType cmdType, string cmdStr)
{
SqlCommand comm = new SqlCommand(cmdStr);
comm.CommandType = cmdType;
return comm;
}

public SqlDataReader executeCommand(SqlCommand comm)
{
//SqlTransaction trans = this.conn.BeginTransaction();
comm.Connection = this.conn;
//comm.CommandTimeout = 10;
//comm.Transaction = trans;

switch(comm.CommandType)
{
case CommandType.TableDirect:
{
throw new NotSupportedException();
}
case CommandType.Text:
{
throw new NotSupportedException();
}
case CommandType.StoredProcedure:
{
SqlDataReader sdr = null;
try
{
sdr = comm.ExecuteReader();
//trans.Commit();
return sdr;
}
catch(Exception ex)
{
//trans.Rollback();
return null;
}
break;
}
default:
{
break;
}
}//end of switch
}

public void closeConnection()
{
this.conn.Close();
}
}



public class MyClass
{
public MyClass()
{
}

public void insertToDB()
{
MyDBConnection conn = new MyDBConnection("MyConnectionSritng");
conn.openConnection();
SqlCommand comm = conn.getCommand(CommandType.StoredProcedure, "SOME_INSERT_PROCEDURE");
//adding parameters to the procedure
SqlParameter param = comm.Parameters.Add("@Name", SqlDbType.VarChar, 20);
param.Direction = ParameterDirection.Input;
...and so on...

foreach(....)
{

comm.Parameter["@Name"].Value = "MyFirstName"
...and so on
try
{
SqlDataReader sdr = conn.executeCommand(comm);
if(sdr != null)
{
Console.Writeline("Insert succeed.");
sdr.Close();
}
else
{
Console.Writeline("Inser failed.");
}
}
catch(Exception ex)
{
Console.Writeline("Insert failed. " + ex.Message);
}
}//end of foreach
conn.closeConnection();
}
}


This worked fine, until I added transactions.
If I use transactions, I'll get an error message: "Insert failed: There is already an open DataReader associated with this Connection which must be closed first".
How it is possible, that datareaders stay open, when I use transactions, but works fine, if I comment out transactions???
There must be something what I not understood about transactions....but what?

br. Juha Ka

 

[juhaka]

Hi, again!

I solved the problem by myself. The SqlDataReader object has to be closed before executing commit() -command. So, I made a wrapper class, to where the data from SqlDataReader object is copied. Then I close SqlDataReader object, then commit the database and then the wrapper class object is returned.
Still I wonder SqlDataReader class; it is a SqlDataREADER class. It can't update a database, and if a data is changed in the database, the same SqlDataReader object can't be refreshed. Why then the database connection (SqlConnection) object is lock until the reader object is closed?!! Weird!

Juha Ka