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
