Parameterized oracle query using oledb

Question

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"]);
    }   
}

If your intent is to verify: does it work?

Marc Gravell
– Marc Gravell

2013-05-07 22:33:22 +00:00
Commented May 7, 2013 at 22:33 — Marc Gravell
– Marc Gravell, Commented May 7, 2013 at 22:33
@Marc I don't have access to the db for testing.

Crackerjack
– Crackerjack

2013-05-07 23:09:56 +00:00
Commented May 7, 2013 at 23:09 — Crackerjack
– Crackerjack, Commented May 7, 2013 at 23:09

T.S. · Accepted Answer · 2013-07-16 19:24:35Z

1

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.

answered Jul 16, 2013 at 19:24

T.S.

19.6k11 gold badges70 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

sidbushes Over a year ago

As of October 2017, OLE DB is undeprecated, ref. Using the vendor's toolset (i.e. Oracle.DataAccess.dll) is probably still preferable though.

T.S. Over a year ago

@sidbushes This answer is from 2013 and is related to Oracle. The new MSOLEDBSQL driver is [quote] "Microsoft OLE DB Driver 18 for SQL Server ...". Im first adaptor of this - on the day of release. The purpose of it was to fix TLS1.2 connectivity to Sql Server. I retrofitted old supported apps using it while newer apps use Native SqlClient connectivity. You would only need to install it, change connection string and voila. The native data access is not just preferable but rather recommended by Microsoft. And current Oracle ODP you should use is Oracle.ManagedDataAccess.dll.

Collectives™ on Stack Overflow

Parameterized oracle query using oledb

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related