CODEWITHSUNDEEP
c#oracle-database.net-4.5
Raghul Raman
Raghul Raman

Reputation: 199

pass parameters to dynamic sql c#

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.

Upvotes: 1

Views: 2695

Answers (1)

Sudhakar Tillapudi
Sudhakar Tillapudi

Reputation: 26209

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();
}

Upvotes: 1

Related Questions