CODEWITHSUNDEEP
c#.netoracle-databaseoledb
Crackerjack
Crackerjack

Reputation: 2154

Parameterized oracle query using oledb

Just wanted to verify if this is the correct syntax for performing a parameterized query in oracle using oledb:

OleDbCommand command = new OleDbCommand("SELECT DocumentName FROM Documents WHERE DocID = ?", connection);
command.Parameters.AddWithValue("@docid", DocIdTextBox.Text.Trim()); 
command.ExecuteReader();
using (OleDbDataReader reader = command.ExecuteReader())
{
    if (reader.HasRows)
    {
        reader.Read();
        string docName = Convert.ToString(reader["DocumentName"]);
    }   
}

Upvotes: 2

Views: 3069

Answers (1)

T.S.
T.S.

Reputation: 19340

Using OleDb connectivity is not recommended. OleDb will be deprecated by Microsoft. Microsoft recommends to use native db connectivity, provided by vendor, which is Odp.net in case of Oracle. You install Oracle client, then go into installation directory and find folder odp.net. In there you can find Oracle.DataAccess.dll. Copy this file into your directory and reference from your project. This library contains extensive oracle-specific objects and when you connect using ODP.net you get all the optimizations, including, in your case, executing parametarized query using bind variables.

OleDbCommand command = new OleDbCommand("SELECT DocumentName FROM Documents WHERE DocID = ?",  connection);
command.Parameters.AddWithValue("@docid", DocIdTextBox.Text.Trim());

If used with odp.net, you could properly form your statement with :1 instead of ? and call command.Parameters.Add(new OracleDataParameter.... In code difference is not big, difference is how Odp.net interprets these calls vs OleDb. It is actually interesting and much easier to see difference of Oledb vs SqlClient because you can easy profile SqlServer. Oracle doesn't give you such EASY option. YOu will see that parametrization with OleDb on SQLServer creates declare... but with SqlClient it executes sp_ExecuteSql, which is a better way.

// command.ExecuteReader(); - this line not needed

Another issue is that here you expect a single value and you could use ExecuteScalar instead of creating more expensive reader

using (OleDbDataReader reader = command.ExecuteReader())
{
    if (reader.HasRows)
    {
        reader.Read();
        string docName = Convert.ToString(reader["DocumentName"]);
    }   
}

Other than comments I have here, your syntax looks Ok.

Upvotes: 1

Related Questions