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?

FbConnection conn = new FbConnection(connstr);
FbTransaction 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 = "create table \"Tmp\" (/*...*/));";
cmd.ExecuteNonQuery();
trans.Commit();
trans = conn.BeginTransaction(new FbTransactionOptions() { 
TransactionBehavior = FbTransactionBehavior.ReadCommitted | 
FbTransactionBehavior.RecVersion | FbTransactionBehavior.Wait });
cmd.Transaction = trans;
cmd.CommandText = "insert into \"Tmp\" (/*...*/) values (/*...*/);";
cmd.Parameters.Add(new FbParameter("...", FbDbType.BigInt) { Direction = 
System.Data.ParameterDirection.Input, IsNullable = false });
cmd.Parameters.Add(new FbParameter("...", FbDbType.VarChar, 101) { 
Direction = System.Data.ParameterDirection.Input, IsNullable = false, 
Charset = FbCharset.Utf8 });
cmd.Prepare();
foreach (/*...*/)
{
     cmd.Parameters["..."].Value = /*...*/;
     /*...*/
     cmd.ExecuteNonQuery();
}
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();
}
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();


Regards,
Kjell

-- 
--------------------------------------
Kjell Rilbe
DataDIA AB
E-post: kj...@datadia.se
Telefon: 08-761 06 55
Mobil: 0733-44 24 64


------------------------------------------------------------------------------
Shape the Mobile Experience: Free Subscription
Software experts and developers: Be at the forefront of tech innovation.
Intel(R) Software Adrenaline delivers strategic insight and game-changing 
conversations that shape the rapidly evolving mobile landscape. Sign up now. 
http://pubads.g.doubleclick.net/gampad/clk?id=63431311&iu=/4140/ostg.clktrk
_______________________________________________
Firebird-net-provider mailing list
Firebird-net-provider@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider

Reply via email to