Transactions, Datareaders and Commands

  • Thread starter Thread starter nevin
  • Start date Start date
N

nevin

Hi all,

I have a transaction open, run a query which inserts data under the
trabsaction and returns a row of data into a SqlDataReader which I put into
some variables, all no problem.
I then close the DataReader making sure it's just dr.Close() rather than
dr.Close(CommandBehavior.CloseConnection) to avoid the connection closing.
Now I create a SqlCommand using the still open transaction to just insert a
record into another table using ExecuteNonQuery()
Finally I commit the transaction.

The data inserted using the ExecuteNonQuery() is ok but the one using the
ExecuteDataReader() isn't.
Surely I should be able to mix a reader and straight data inserts this way?

Anyone have any ideas?
Thanks
Nevin.
 
I am not sure what your problem is. Is it that the record does not get inserted? What are you trying to do? Insert a record using ExecuteReader

Tu-Thac
www.ongtech.co

----- nevin wrote: ----

Hi all

I have a transaction open, run a query which inserts data under th
trabsaction and returns a row of data into a SqlDataReader which I put int
some variables, all no problem
I then close the DataReader making sure it's just dr.Close() rather tha
dr.Close(CommandBehavior.CloseConnection) to avoid the connection closing
Now I create a SqlCommand using the still open transaction to just insert
record into another table using ExecuteNonQuery(
Finally I commit the transaction

The data inserted using the ExecuteNonQuery() is ok but the one using th
ExecuteDataReader() isn't
Surely I should be able to mix a reader and straight data inserts this way

Anyone have any ideas
Thank
Nevin
 
Yes the ExecuteReader runs a storedprocedure which inserts and returns the
effected rows. The row the storeprocedure inserts while using the datareader
does not get updated.
If it's executed in isolation it will update fine and return the rows I want
but if I close the DataReader and then use a command to insert some other
data into a different table but under the same open connection and
transaction the first insert fails to commit although it does run without
any errors.


Tu-Thach said:
I am not sure what your problem is. Is it that the record does not get
Click to expand...
inserted? What are you trying to do? Insert a record using ExecuteReader?
 
Here it is in summary:

SqlTransaction tx = conn.BeginTransaction();
SqlCommand cmdGro = new SqlCommand("spw_Subscribe", conn, tx);
cmdGro.CommandType = CommandType.StoredProcedure;
cmdGro.Parameters.Add("@groupID", groupID);
// more params added

SqlDataReader dr = cmdGro.ExecuteReader();
if (dr.Read())
{
int status = dr.GetInt32(0); // first col on returned data
switch (status)
{
case 0:
int subID = dr.GetInt32(1);
string groupname =
dr.GetString(dr.GetOrdinal("gro_Name"));
string groupbox =
dr.GetString(dr.GetOrdinal("gro_MailBox"));
// gets loads more values from the datareader here

// if i committed the transaction here the data from
the cmd.ExecuteReader will be correctly updated
dr.Close();

SqlCommand cmd = new SqlCommand("spw_InsertPost",
conn, tx);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@id", gMessageInfo.MessegeID);
// loads more params added here

cmd.ExecuteNonQuery();
tx.Commit();

// now data input by cmd will be ok but cmdGro will not
be in the db.

break;
} //switch
}if dr.read()
 
Can you also post the stored procedures to get an idea of what is going on?
It is possible that your second stored proc uses transactions?
 
Well the first is an insert and then it returns a select.
The second is a simple an insert as there is possible. No Transactions, no
cursors, no temp tables nothing weird at all.

Each one works fine on its own and on its own under the transaction but not
together under the transaction


Angel Saenz-Badillos said:
Can you also post the stored procedures to get an idea of what is going on?
It is possible that your second stored proc uses transactions?

--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.

nevin said:
Here it is in summary:

SqlTransaction tx = conn.BeginTransaction();
SqlCommand cmdGro = new SqlCommand("spw_Subscribe", conn, tx);
cmdGro.CommandType = CommandType.StoredProcedure;
cmdGro.Parameters.Add("@groupID", groupID);
// more params added

SqlDataReader dr = cmdGro.ExecuteReader();
if (dr.Read())
{
int status = dr.GetInt32(0); // first col on returned data
switch (status)
{
case 0:
int subID = dr.GetInt32(1);
string groupname =
dr.GetString(dr.GetOrdinal("gro_Name"));
string groupbox =
dr.GetString(dr.GetOrdinal("gro_MailBox"));
// gets loads more values from the datareader here

// if i committed the transaction here the data from
the cmd.ExecuteReader will be correctly updated
dr.Close();

SqlCommand cmd = new SqlCommand("spw_InsertPost",
conn, tx);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@id", gMessageInfo.MessegeID);
// loads more params added here

cmd.ExecuteNonQuery();
tx.Commit();

// now data input by cmd will be ok but cmdGro will not
be in the db.

break;
} //switch
}if dr.read()

rows
I want some
other not dr.Close()
rather to the
one using
Click to expand...
Click to expand...
 
What I'm also after is the confirmation that what I'm doing is possible..it
certainly seems simple enough.
I can accept I'm doing something stupid and try and work around that if I
know that it is possible to do what I want.
Running a query through a datareader, closing the dr (not the con nor the
tx) running a new command under same tx then committing should work fine
right ?

Angel Saenz-Badillos said:
Can you also post the stored procedures to get an idea of what is going on?
It is possible that your second stored proc uses transactions?

--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.

nevin said:
Here it is in summary:

SqlTransaction tx = conn.BeginTransaction();
SqlCommand cmdGro = new SqlCommand("spw_Subscribe", conn, tx);
cmdGro.CommandType = CommandType.StoredProcedure;
cmdGro.Parameters.Add("@groupID", groupID);
// more params added

SqlDataReader dr = cmdGro.ExecuteReader();
if (dr.Read())
{
int status = dr.GetInt32(0); // first col on returned data
switch (status)
{
case 0:
int subID = dr.GetInt32(1);
string groupname =
dr.GetString(dr.GetOrdinal("gro_Name"));
string groupbox =
dr.GetString(dr.GetOrdinal("gro_MailBox"));
// gets loads more values from the datareader here

// if i committed the transaction here the data from
the cmd.ExecuteReader will be correctly updated
dr.Close();

SqlCommand cmd = new SqlCommand("spw_InsertPost",
conn, tx);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@id", gMessageInfo.MessegeID);
// loads more params added here

cmd.ExecuteNonQuery();
tx.Commit();

// now data input by cmd will be ok but cmdGro will not
be in the db.

break;
} //switch
}if dr.read()

rows
I want some
other not dr.Close()
rather to the
one using
Click to expand...
Click to expand...
 
nevin said:
What I'm also after is the confirmation that what I'm doing is possible..it
certainly seems simple enough.
I can accept I'm doing something stupid and try and work around that if I
know that it is possible to do what I want.
Running a query through a datareader, closing the dr (not the con nor the
tx) running a new command under same tx then committing should work fine
right ?
Click to expand...


Yes, it should work fine.

David
 
Back
Top