How can I pass an unkown number of arguments to C#'s "Database.Open("DatabaseName").Query()" method?

Question

I have tried Googling to find a solution, but all I get is totally irrelevant results or results involving 2 dimensional arrays, like the one here: http://social.msdn.microsoft.com/Forums/vstudio/en-US/bb4d54d3-14d7-49e9-b721-db4501db62c8/how-does-one-increment-a-value-in-a-two-dimensional-array, which does not apply.

Say I have this declared:

var db = Database.Open("Content");
var searchTerms = searchText.Split('"').Select((element, index) => index % 2 == 0 ? element.Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries) : new string[] { element }).SelectMany(element => element).ToList();
int termCount = searchTerms.Count;

(Note: All that you really need to know about searchTerms is that it holds a number of search terms typed into a search bar by the user. All the LINQ expression is doing is ensuring that text wrapped in qoutes is treated as a single search term. It is not really necessary to know all of this for the purpose of this question.)

Then I have compiled (using for loops that loop for each number of items in the searchTerms list) a string to be used as a SELECT SQL query.

Here is an example that shows part of this string being compiled with the @0, @1, etc. placeholders so that my query is parameterized.

searchQueryString = "SELECT NULL AS ObjectID, page AS location, 'pageSettings' AS type, page AS value, 'pageName' AS contentType, ";

for (int i=0; i<termCount; i++)
{
    if(i != 0)
    {
        searchQueryString += "+ ";
    }

    searchQueryString += "((len(page) - len(replace(UPPER(page), UPPER(@" + i + "), ''))) / len(@" + i + ")) ";
}

searchQueryString += "AS occurences ";

(Note: All that you really need to know about the above code is that I am concatenating the incrementing value of i to the @ symbol to dynamically compile the placeholder value.)

All of the above works fine, but later, I must use something along the lines of this (only I don't know how many arguments I will need until runtime):

foreach (var row in db.Query(searchQueryString, searchTerms[0]))
{
    @row.occurences
}

(For Clarification: I will need a number of additional arguments (i.e., in addition to the searchQueryString argument) equal to the number of items in the searchTerms list AND they will have to be referencing the correct index (effectively referencing each index from lowest to highest, in order, separated by commas, of course.)

Also, I will, of course need to use an incrementing value to reference the appropriate index of the list, if I can even get that far, and I don't know how to do that either. Could I use i++ somehow for that?

I know C# is powerful, but maybe I am asking too much?

Answer 1

Well, for how complex the question probably seemed, the answer ended up being something pretty simple. I suppose it was easy to overlook because I had never done this before and others may have thought it too obvious to be what I was looking for. However, I had NEVER tried to pass a variable length of parameters before and had no clue if it was even possible or not (okay, well I guess I knew it was possible somehow, but could have been very far from my method for all I knew).

In any case, I had tried:

foreach (var row in db.Query(searchQueryString, searchTerms)) //searchTerms is a list of strings.
{
    //Do something for each row returned from the sql query.
}

Assuming that if it could handle a variable length number of arguments (remember each argument passed after the first in the Database.Query() method is treated as fillers for the placeholders in the query string (e.g., @0, @1, @2, etc.) it could accept it from a list if it could from an array.

I couldn't really have been any more wrong with that assumption, as passing a list throws an error. However, I was surprised when I finally broke down, converted the list to an array, and tried passing the array instead of the list.

Indeed, and here is the short answer to my original question, if I simply give it an array it works easily (a little too easily, which I suppose is why I was so sure it wouldn't work):

string[] searchTermsArray = searchTerms.ToArray();

foreach (var row in db.Query(searchQueryString, searchTermsArray)) //searchTermsArray is an array of strings.
{
    //Do something for each row returned from the sql query.
}

The above snippet of code is really all that is needed to successfully answer my original question.

Answer 2

I'm not sure I quite understand what you need from the question, but it looks like you're substituting a series of placeholders in the SQL with another value. If that's the case, you can use String.Format to replace the values like this:

        object val = "a";
        object anotherVal = 2.0;
        var result = string.Format("{0} - {1}", new[] { val, anotherVal });

This way, you can substitute as many values as you need by simply creating the arguments array to be the right size.

If you're creating a SQL query on the fly, then you need to be wary of SQL injection, and substituting user-supplied text directly into a query is a bit of a no-no from this point of view. The best way to avoid this is to use parameters in the query, which automatically then get sanitised to prevent SQL injection. You can still use a 'params array' argument though, to achieve what you need, for example:

    public IDataReader ExecuteQuery(string sqlQuery, params string[] searchTerms)
    {
        var cmd = new SqlCommand { CommandText = sqlQuery };

        for (int i = 0; i < searchTerms.Length; i++)
        {
           cmd.Parameters.AddWithValue(i.ToString(), searchTerms[i]);
        }

        return cmd.ExecuteReader();
    }

obviously, you could also build up the sql string within this method if you needed to, based on the length of the searchTerms array

Answer 3

Use params keyword for variable numbers of parameters. With params, the arguments passed to a any function are changed by the compiler to elements in a temporary array.

static int AddParameters(params int[] values)
{
    int total = 0;
    foreach (int value in values)
    {
      total += value;
    }
    return total;
}

and can be called as

int add1 = AddParameters(1);
int add2 = AddParameters(1, 2);
int add3 = AddParameters(1, 2, 3);
int add4 = AddParameters(1, 2, 3, 4);

//-----------Edited Reply based on comments below--- You can use something like this to be used with SQL

void MYSQLInteractionFunction(String myConnectionString)
 {
    String searchQueryString = "SELECT NULL AS ObjectID, page AS location, 'pageSettings' AS type, page AS value, 'pageName' AS contentType, ";
    SqlConnection myConnection = new SqlConnection(myConnectionString);
    SqlCommand myCommand = new SqlCommand(searchQueryString, myConnection);
    myConnection.Open();

    SqlDataReader queryCommandReader = myCommand.ExecuteReader();
    // Create a DataTable object to hold all the data returned by the query.
    DataTable dataTable = new DataTable();

    // Use the DataTable.Load(SqlDataReader) function to put the results of the query into a DataTable.
    dataTable.Load(queryCommandReader);

    Int32 rowID = 0; // or iterate on your Rows - depending on what you want
    foreach (DataColumn column in dataTable.Columns)
    {
       myStringList.Add(dataTable.Rows[rowID][column.ColumnName] + " | ");
       rowID++;
     }
     myConnection.Close();

     String[] myStringArray = myStringList.ToArray();
     UnlimitedParameters(myStringArray);
}

static void UnlimitedParameters(params string[] values)
{
    foreach (string strValue in values)
    {
        // Do whatever you want to do with this strValue
     }
 }