pass parameters to dynamic sql c#

Question

I have a oracle table with dynamic queries, for example

FieldName    |  DynamicSQL
customerName |  Select customerName from customer where customerId = :customerId

And i am trying to read the dynamic sql and pass the customerId as parameter. My code looks like

string dynamicSQL = cmd.ExecuteScalar().ToString(); //dynamicSQL = "select customername from customer where  customerID= :customerID"

I will have the value of cutomerID in my program and to pass the customerID parameter to query above i split the string after ":" and then pass the customerID value as parameter.

string[] parameter = dynamicSQL.split (':') //paramteter[0]= "select customername...customerID=" parameter[1]=customerID
for (int i = 0; i <= parameter.Length - 1; i++) {
 if (parameter[i] == "customerID"){
  using (OracleCommand cmd1 = new OracleCommand(dynamicSQL, conn)){
  cmd1.parameters.add("customerID",oracletype.int32).value = customerID;
  customername = cmd1.executescalar().tostring();
    }
  }
}

Now there are lot of queries and parameter names like orderId. So if it is another paramater the i will have another case to check if the parameter[i] == orderID, that's one issue and another major issue is splitting the dynamic sql to get the parameter. Foe example, if i have the following dynamic sql

select customerName from customer c, order o where c.customerid = o.customerid and c.customerid = :customerID and o.orderid = :orderid

So how to pass value of customerid and orderid for the same command? Basically, if i have multiple parameters is there a way to add parameters in a more generic manner instead of hard coding parameter names and splitting it? I tried having all parameters (orderId, CustomerID) in an array but if the datatype is different i could not add parameters to command. I am working on .NET4.5 and C# code behind.

Can you change :customerID to @CustomerId, if so, then just pass that query to Command object and add parameter they will get picked anyway and you save your self from righting lot of detection code. otherwise I suggest atleast wrapper CustomerID as :customerid: to ensure you pick it correctly. your code will fail if for some reason your data has : as part of it. — Sumit Gupta
– Sumit Gupta, Commented Jan 22, 2014 at 16:19
For Oracle, make sure you set the value on the OracleCommand object BindByName to true. This is not set to true by default and ODP tries to match up parameters by position instead, which is probably not what you intend. — Maurice Reeves
– Maurice Reeves, Commented Jan 22, 2014 at 16:25
@SumitGupta: I don't think that is correct since it is Oracle syntax. — Patrick Hofman
– Patrick Hofman, Commented Jan 22, 2014 at 16:25
@SumitGupta: It's Oracle, so the query needs to be stored with :parameterName — Raghul Raman
– Raghul Raman, Commented Jan 22, 2014 at 16:38
Well I am not aware of ORacle Syntax, but if it is then just use Cmd object and still add parameter you don't need to extract them from query. — Sumit Gupta
– Sumit Gupta, Commented Jan 23, 2014 at 10:05

Sudhakar Tillapudi · Accepted Answer · 2014-01-22 16:22:31Z

1

Try This:

//dynamicSQL = "select customername from customer where  customerID= :customerID"

using (OracleCommand cmd1 = new OracleCommand(dynamicSQL, conn)){
cmd1.parameters.add(":customerID",oracletype.int32).value = customerID;
customername = cmd1.ExecuteScalar().ToString();
}

answered Jan 22, 2014 at 16:22

Sudhakar Tillapudi

26.3k5 gold badges43 silver badges70 bronze badges

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

Collectives™ on Stack Overflow

pass parameters to dynamic sql c#

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related