Den 2013-11-26 20:45 skrev Mark Rotteveel såhär:
> 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).
I added it just to see if it would help. :-)
> 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.
I looked for an Unprepare method, and didn't find it. Thought "oh well".
Didn't think to look for Close. I'll give that a try.
> So the solution is to wrap the command (and everything that uses the
> command) in a using block as well.
Or call cmd.Close explicitly?
>> 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.
I do have try/finally/catch for conn.Close and trans.Rollback, but
omitted it in the post for brevity and clarity re. the normal flow of
operations. My point was to understand why the table is in use in the
normal case. The exception/error case was not the problem.
I wasn't aware that using {...} will do a rollback on trans. Might use
that instead of try/catch then - looks a little slimmer in the code,
albeit possibly slightly less clear to read since it assumes that the
person studying the code knows that the end of the using block will
rollback the trans if still open.
Thanks,
Kjell
--
--------------------------------------
Kjell Rilbe
DataDIA AB
E-post: kj...@datadia.se
Telefon: 08-761 06 55
Mobil: 0733-44 24 64
------------------------------------------------------------------------------
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
