Bind Variables Using ODP.NET

From: <vm.shinde_at_gmail.com>
Date: 24 Feb 2006 04:31:40 -0800
Message-ID: <1140784300.707497.113020_at_i39g2000cwa.googlegroups.com>

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 Received on Fri Feb 24 2006 - 13:31:40 CET