Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

OracleParameter and IN Clause

Tags:

Is there a way to add a parameter to an IN clause using System.Data.OracleClient.

For example:

string query = "SELECT * FROM TableName WHERE UserName IN (:Pram)"; OracleCommand command = new OracleCommand(query, conn); command.Parameters.Add(":Pram", OracleType.VarChar).Value = "'Ben', 'Sam'"; 
like image 340
rc. Avatar asked Feb 12 '09 14:02

rc.


1 Answers

You can wrap it in OracleCommandExtension method:

public static class OracleCommandExtension {     public static OracleCommand AddParameterCollection<TValue>(this OracleCommand command, string name, OracleType type, IEnumerable<TValue> collection)     {         var oraParams = new List<OracleParameter>();         var counter = 0;         var collectionParams = new StringBuilder(":");         foreach (var obj in collection)         {             var param = name + counter;             collectionParams.Append(param);             collectionParams.Append(", :");             oraParams.Add(new OracleParameter(param, type) { Value = obj });             counter++;         }         collectionParams.Remove(collectionParams.Length - 3, 3);         command.CommandText = command.CommandText.Replace(":" + name, collectionParams.ToString());         command.Parameters.AddRange(oraParams.ToArray());         return command;     } } 
like image 66
argy Avatar answered Sep 24 '22 20:09

argy