Multiple Select Query Execute

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
samurae83
Posts: 3
Joined: Wed 12 Sep 2012 10:32

Multiple Select Query Execute

Post by samurae83 » Wed 12 Sep 2012 10:48

Hello.
I'm developing a SQL executor like SQLGate and Orange.

I'm having a problem executing mutiple select queries.
(eg, select * from dept; select * from emp;)
Two seperate grids should be displayed after the execution.

Right now, I'm adding the queries into the array and execute them one by one using the
foreach(string q in queries)
{
oracleCommand.CommandText = q;
....
}

Is there any better way to do this?

Thank you.

ps - I'm using C#.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Multiple Select Query Execute

Post by Shalex » Thu 13 Sep 2012 11:56

You can use the PL/SQL block:

Code: Select all

            OracleConnection conn = new OracleConnection("host=orcl1120;uid=scott;pwd=tiger");
            conn.Open();
            String commStr =@"BEGIN
                                OPEN :rcursor1 FOR SELECT * FROM dept c;
                                OPEN :rcursor2 FOR SELECT * FROM emp m;
                            END;";
            OracleCommand comm = new OracleCommand(commStr, conn);
            comm.Parameters.Add("rcursor1", OracleDbType.Cursor).Direction = System.Data.ParameterDirection.Output;
            comm.Parameters.Add("rcursor2", OracleDbType.Cursor).Direction = System.Data.ParameterDirection.Output;
            OracleDataReader reader = comm.ExecuteReader();
            do {
                while (reader.Read()) {
                    for (int i = 0; i < reader.FieldCount; i++) {
                        Console.Write(reader.GetValue(i) + "\t ");
                    }
                    Console.WriteLine();
                }
            } while (reader.NextResult());
            conn.Close();

Post Reply