CODEWITHSUNDEEP
c#sqloracle-databaseodp.net
Sam Chapman
Sam Chapman

Reputation: 25

How to create a parameterized query?

I've been stuck with this problem for a while and have gone through several tutorials without success.

How do you use paramaterized queries in C# with the Oracle.ManagedDataAccess.dll rather than the old Oracle.DataAccess.dll?

The code I have tried is:

string query = @"SELECT Count(*) FROM @table WHERE @custNM = '@nm'";

OracleCommand mySearch = new OracleCommand(query, newConn);
mySearch.Prepare();

mySearch.Parameters.Add("@table", "cust");
mySearch.Parameters.Add("@custNM", "nm");
mySearch.Parameters.Add("@nm", "SAM");

I have also tried it without the mySearch.Prepare section and wit : instead of @ and without the @ prefixing the query string. Another thing I tried was removing the @/: at the start of the Add string e.g.

mySearch.Parameters.Add("nm", "SAM");

My end goal is to use variables to pass values instead of just string text for the statement, if that's easier to do.

Upvotes: 2

Views: 2263

Answers (1)

Patrick Hofman
Patrick Hofman

Reputation: 157136

First, you should use : as the parameter identifier instead of @, which is commonly used by SQL Server.

Second, You can't supply the table name or field names using parameters. It should either be hard-coded or written using dynamic SQL.

Third, you shouldn't escape parameters. They don't need that.

The best thing possible now is this:

string query = @"SELECT Count(*) FROM cust WHERE nm = :nm";

OracleCommand mySearch = new OracleCommand(query, newConn);
mySearch.Parameters.Add(":nm", "SAM");

Upvotes: 4

Related Questions