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