On 26-11-2013 14:58, Kjell Rilbe wrote:
> Please consider this code, which essentially creates a table, commits,
> fills data and does a select with joins, closes the query, commits, then
> drops the table in a new transaction.
>
> The problem is that without the conn.Close + conn.Open before starting
> the drop transaction, the drop fails saying the object is in use.
>
> Why is the Close/Open required?
>
...
> FbCommand cmd = conn.CreateCommand();
...
> cmd.CommandText = "select /*...*/ from \"Tmp\" inner join /*...*/ where
> /*...*/;";
> cmd.Parameters.Clear();
> using (FbDataReader rd =
> cmd.ExecuteReader(System.Data.CommandBehavior.SingleRow))
> {
> rd.Read();
> for (int colnum = 0; colnum < rd.FieldCount; colnum++)
> {
> string colname = rd.GetName(colnum);
> int count = rd.GetInt32(colnum);
> if (count > 0)
> result.Counts[colname] = count;
> }
> rd.Close();
> }
An rd.Close() is unnecessary when you use using. It will dispose the
object when it exits the block (which closed the reader).
The problem is that you did not close the command, which means it is
still prepared, which marks the table as used. Now when you close the
connection, the command will be closed as well.
So the solution is to wrap the command (and everything that uses the
command) in a using block as well.
> trans.Commit();
> conn.Close();
> conn.Open();
> trans = conn.BeginTransaction(new FbTransactionOptions() {
> TransactionBehavior = FbTransactionBehavior.ReadCommitted |
> FbTransactionBehavior.RecVersion | FbTransactionBehavior.Wait });
> FbCommand cmd = conn.CreateCommand();
> cmd.CommandType = System.Data.CommandType.Text;
> cmd.Transaction = trans;
> cmd.CommandText = "drop table \"Tmp\";";
> cmd.ExecuteNonQuery();
> trans.Commit();
BTW: You really should use using for your connections and for
transactions as well. This will rollback the transaction if no commit
was called when it exits the block (either because no commit() was in
the code or an exception occurred before the end of the block).
Basic rule: if something is IDisposable, then use using.
Mark
--
Mark Rotteveel
------------------------------------------------------------------------------
Rapidly troubleshoot problems before they affect your business. Most IT
organizations don't have a clear picture of how application performance
affects their revenue. With AppDynamics, you get 100% visibility into your
Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro!
http://pubads.g.doubleclick.net/gampad/clk?id=84349351&iu=/4140/ostg.clktrk
_______________________________________________
Firebird-net-provider mailing list
Firebird-net-provider@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
