OleDbCommand - Variable number of parameters

Hi, I hope someone can point me in the right direction…

I have an array which contains 1 or more dates. I want to retrieve all of the records from my database that match any of the dates.

However I don’t see how I can pass a variable number of parameters to my OleDbCommand object. I could build the query as a string but that would leave me vulnerable to SQL injection. Alternatively, I could build it as a string, adding the required number of @Variables, then do a second pass to insert them all. Both are rather hacky solutions but what alternative do I have?

Actually, the 2nd option to dynamically build your “IN LIST()” is the way to fly. And it can be done in one pass. Here is a trip down memory lane:


        protected override void bindCommandText(StringBuilder whereClause)
        {
            if (SelectedIdList.Count==0)
            {
                throw new InvalidOperationException("Cannot search without any items specified.");
            }
            List<string> paramNames = new List<string>();
            string paramName;
            StringBuilder listString = new StringBuilder();
            for (int i=0; i<SelectedIdList.Count; i++)
            {
                if (i>0)
                {
                    listString.Append(", ");
                }
                paramName = "listParam" + i.ToString();
                listString.Append("@" + paramName);
                paramNames.Add(paramName);
            }
            whereClause.AppendFormat(InListClause, listString.ToString());
            affixParams(paramNames);
        }

You could probably replace that in ~3 lines of insane linq these days.

Thanks, I’ll give it a try.