Monday, September 3, 2012

Quick Post: Passing Parameters for an SQL Statement with IN clause using SQLCommand (C#)

There are probably a variety of ways to solve this little problem as this Stack Overflow thread will show but I thought that showing how I solved this would still be worth it. All I do essentially is build a string, and based on how many parameters there are to insert into the SQL statement I insert the ‘?’ with a comma after it and once I exit the loop I insert one more ‘?’. Note that the number of times I run in the loop in one less than the actual length of the parameter array. Once I’m done building the string I then iterate through the parameter array and add each parameter in. This code is tested and working.

StringBuilder selectstring = new StringBuilder();             selectstring.Append("SELECT ROWID, * FROM tbl1 WHERE ROWID IN (");             Int64[] arr = {1,2,3,3,4};             for (int i = 0; i < arr.Length-1; i++)             {                 selectstring.Append("?,");             }             selectstring.Append("?)");                          sa.SelectCommand = new SQLiteCommand(selectstring.ToString(), conn);             foreach (Int64 a in arr)             {                 sa.SelectCommand.Parameters.Add(new SQLiteParameter(DbType.Int64, a));             }                          System.Data.DataSet ds = new System.Data.DataSet();             sa.Fill(ds);             conn.Close();

No comments:

Post a Comment