Bind Variables Using ODP.NET

[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= aram1 and col2 = aram2
select * from testTbl2 where col1= aram1 and col2 = aram2
select * from testTbl2 where col1= aram1 and col2 = aram2

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