<narayan.k.verma_at_gmail.com> wrote in message
news:1146532223.630036.20540_at_v46g2000cwv.googlegroups.com...
> I create the following table:
> CREATE GLOBAL TEMPORARY TABLE TT_EmpTable
> (
> EmpId INT NOT NULL,
> EmpName VARCHAR2(30) NOT NULL)
> ON COMMIT PRESERVE ROWS;
>
> Then I am executing the following .NET code from a console application:
>
> using System;
> using System.Data;
> using System.Data.OracleClient;
>
> namespace TempTableTest
> {
> class MainClass
> {
> [STAThread]
> static void Main(string[] args)
> {
> OracleConnection connection = new OracleConnection("Data
> Source=orcl;User ID=userid;Password=password;");
> OracleCommand command = connection.CreateCommand();
> string query = "begin insert into TT_EmpTable(EmpId, EmpName)
> values(1, 'Test Employee'); OPEN :pCUROUT FOR SELECT * from
> TT_EmpTable;end;";
> command.CommandText = query;
> OracleParameter op = new OracleParameter("pCUROUT",
> OracleType.Cursor, 0, ParameterDirection.Output, false, 0, 0,
> String.Empty, DataRowVersion.Default, DBNull.Value);
> op.DbType = DbType.Object;
> command.Parameters.Add(op);
>
> OracleDataAdapter adapter = new OracleDataAdapter(command);
> DataSet ds = new DataSet();
> connection.Open();
> adapter.Fill(ds);
> connection.Close();
> connection.Dispose();
>
> Console.WriteLine(ds.Tables[0].Rows.Count); //first time it should
> show 1 record
>
> connection = new OracleConnection("Data Source=orcl;User
> ID=userid;Password=password;");
> command = connection.CreateCommand();
> query = "begin insert into TT_EmpTable(EmpId, EmpName) values(2,
> 'Another test employee'); OPEN :pCUROUT FOR SELECT * from
> TT_EmpTable;end;";
> command.CommandText = query;
> op = new OracleParameter("pCUROUT", OracleType.Cursor, 0,
> ParameterDirection.Output, false, 0, 0, String.Empty,
> DataRowVersion.Default, DBNull.Value);
> op.DbType = DbType.Object;
> command.Parameters.Add(op);
>
> adapter = new OracleDataAdapter(command);
> ds = new DataSet();
> connection.Open();
> adapter.Fill(ds);
> connection.Close();
> connection.Dispose();
>
> Console.WriteLine(ds.Tables[0].Rows.Count); //second time it should
> also show 1 record
>
> Console.ReadLine();
> }
> }
> }
>
> my problem is that I am seeing two rows the second time around. I was
> expecting only one row as when I did the first connection.Close that
> should have closed the session and dropped the row for the next call.
> But apparently the row added in the first call is also appearing in the
> second call. This issue is baffling me for a couple of days with no
> solution.
>
> Any pointers/guidance welcome.
>
> Thanks a lot,
> Narayan
>
> P.S. It does not matter if I let DataAdapter handle the opening and
> closing of connection.
>
If you do it in sqlPlus do you get the same behavior? My guess is no. That
would indicate a problem with the ADO architecture.
Jim
Received on Mon May 01 2006 - 21:21:31 CDT
