Bind Variables Using ODP.NET

microsoft.public.dotnet.framework.adonet

24 Feb 2006 12:37 PM

vm.shinde

Dear All,
   I am Trying to use Bind Variables using ODP.NET and calculate the
performance

The Scenario at my end is:
1. Win Form application that creates Sql statements to be executed.
2. Oracle database 10g.
3. Using ODP for Data Access

The connection String
        ConnectionString="User ID=username;Data
Source=dbTest;Password=Password;enlist=false;";

i am executing 3 diffrent simple SQL statements one after another and
preparing them only once

the SQL's and code as foloows

select * from testTbl1 where col1= :param1 and col2 = :param2
select * from testTbl2 where col1= :param1 and col2 = :param2
select * from testTbl2 where col1= :param1 and col2 = :param2

cmd1 = new OracleCommand(CreateSQLStatement(0),conn);
cmd1.CommandType=CommandType.Text;
cmd1.Prepare();

cmd2 = new OracleCommand(CreateSQLStatement(1),conn);
cmd2.CommandType=CommandType.Text;
cmd2.Prepare();

cmd3 = new OracleCommand(CreateSQLStatement(2),conn);
cmd3.CommandType=CommandType.Text;
cmd3.Prepare();
//----------------------

for(i=0;i<NoOfExecution;i++)
{
    OracleParameter param1 = new OracleParameter();
    param1.ParameterName="param1";
    param1.OracleDbType=OracleDbType.Varchar2;
    OracleParameter param2 = new OracleParameter();
    param2.ParameterName="param2";
    param2.OracleDbType=OracleDbType.Int32;

    param1.Value=i.ToString();
    param2.Value=i.ToString();

    switch(i%3)
    {
    case 0:
    {
        cmdTemp = (OracleCommand)cmd1.Clone();
        cmd1.Parameters.Add(param1);
        cmd1.Parameters.Add(param2);
        reader=cmd1.ExecuteReader();
        while(reader.Read());
        reader.Close();
        cmd1 = (OracleCommand)cmdTemp.Clone();
        break;
    }
    case 1:
    {
        cmdTemp=(OracleCommand)cmd2.Clone();
        cmd2.Parameters.Add(param1);
        cmd2.Parameters.Add(param2);
        reader=cmd2.ExecuteReader();
        while(reader.Read());
        reader.Close();
        cmd2 = (OracleCommand) cmdTemp.Clone();
        break;
    }
    case 2:
    {
        cmdTemp =(OracleCommand)cmd3.Clone();
        cmd3.Parameters.Add(param1);
        cmd3.Parameters.Add(param2);

        reader=cmd3.ExecuteReader();
        while(reader.Read());
        reader.Close();
        cmd3=(OracleCommand) cmdTemp.Clone();
        break;
    }
    }
}

but the SQL trace shows that the Every time the SQl got parsed.
this effects the Performance

I have tried above code with NDP (.Net native providers) it parses only
once.

Suggest me if i am missing something in case of ODP.

thanks and Regards

Vivek