Send an Unspecified Number of Parameters to a Method

Question

I have a program that at the moment is hard coded to send a number of parameters that the user inputs into a DBF file. I cannot leave the parameters empty (DBF just doesn't seem to allow it) so now I am simply putting in a blank which is not necessarily a problem.

This is the method so far;

    public bool SendToDBF(string name, string town, string pcode)
    {

        int id, querytype, personID;
        string whatfile, netname, whatProgram, blank;

        id = 1;
        whatfile = "Compns";
        querytype = 1;
        netname = Environment.UserName;
        personID = 8948;
        whatProgram = "Sales";
        blank = "";

        try
        {
            string constr = ConfigurationManager.ConnectionStrings["dbfString"].ConnectionString;
            using (var dbfCon = new OleDbConnection(constr))
            {
                dbfCon.Open();
                var dbfCmd = new OleDbCommand("INSERT INTO fromsql VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", dbfCon);
                dbfCmd.Parameters.Clear();
                dbfCmd.Parameters.AddWithValue("@fq_uniqid", id);
                dbfCmd.Parameters.AddWithValue("@fq_whfile", whatfile);
                dbfCmd.Parameters.AddWithValue("@fq_what", querytype);
                dbfCmd.Parameters.AddWithValue("@fq_whonm", netname);
                dbfCmd.Parameters.AddWithValue("@fq_whoid", personID);
                dbfCmd.Parameters.AddWithValue("@fq_whoprog", whatProgram);
                dbfCmd.Parameters.AddWithValue("@param1", name);
                dbfCmd.Parameters.AddWithValue("@param2", town);
                dbfCmd.Parameters.AddWithValue("@param3", pcode);
                dbfCmd.Parameters.AddWithValue("@param4", blank);
                dbfCmd.Parameters.AddWithValue("@param5", blank);
                dbfCmd.Parameters.AddWithValue("@param6", blank);
                dbfCmd.Parameters.AddWithValue("@param7", blank);
                dbfCmd.Parameters.AddWithValue("@param8", blank);
                dbfCmd.Parameters.AddWithValue("@param9", blank);
                dbfCmd.Parameters.AddWithValue("@param10", blank);
                dbfCmd.Parameters.AddWithValue("@param11", blank);
                dbfCmd.Parameters.AddWithValue("@param12", blank);
                dbfCmd.Parameters.AddWithValue("@param13", blank);
                dbfCmd.Parameters.AddWithValue("@paraml1", blank);
                dbfCmd.Parameters.AddWithValue("@paraml2", blank);

                dbfCmd.ExecuteNonQuery();
                return true;
            }
        }
        catch (OleDbException ex)
        {
            MessageBox.Show("Error Updating MySQL: " + ex);
            return false;
        }
    }

What I would like is that the amount of paramaters sent to DBF is decided on how many fields the user fills in for the company, for example if they fill in just the name, town and post code then SendToDBF will take 3 params as well as the predefined parameters such as ID, Whatfile and QueryType.

Is there a way that I can make SendToDBF take an unspecified number of parameters, and fill in the rest that are not in putted by the user with blanks?

Build an SQL statement on demand. Just concatenate the statement yourself. This is very tricky code you have. — Patrick Hofman, Oct 26 '15 at 14:53
why not make a List or create a Class that handles the total number of fields and when you pass the class as a param you can the use the `AddWithValue` method to properly fill in the parameters.. and for the ones that are empty assign string.Empty as a param — MethodMan, Oct 26 '15 at 14:53
Dictionary would work too I have used that as well ..also @CBreeze `MessageBox.Show("Error Updating MySQL: " + ex);` change to `MessageBox.Show("Error Updating MySQL: " + ex.Message);` — MethodMan, Oct 26 '15 at 14:56
I would use the C# params keyword (public bool SendToDBF(params string[] parameters). The insert statement would then need to have a "?" per items in the parameters array. — Polyfun, Oct 26 '15 at 14:59
You can use the params keyword here. See: [http://stackoverflow.com/questions/9528276/does-c-sharp-support-a-variable-number-of-arguments-and-how](http://stackoverflow.com/questions/9528276/does-c-sharp-support-a-variable-number-of-arguments-and-how) — Fayyaz Naqvi, Oct 26 '15 at 15:01
This isn't exactly what you're asking, but I'd suggest considering optional parameters. So your method might have fifty parameters, but the invocation would look like `DoStuff(town: obj1, person: obj2)` instead of tons of nulls. I'd say this is better than using a dictionary, which is the typical way of working around languages not offering optional params. — Casey, Oct 26 '15 at 15:14

Mathias R. Jessen · Accepted Answer · 2015-10-26T16:18:24.377

4

If the type is always string you can use the params keyword in C#:

public static void TestParams(params string[] Params)
{
   foreach (string param in Params) 
    {
        Console.WriteLine(param);
    }
}

And then decide on the value name based on the index in Params

Example:

public static void TestParams(params string[] Params)
{
    // init dbf up here
    int maxParams = 12;
    for(int i = 1; i <= maxParams; i++) 
    {
        string paramName = "@param" + i;
        if(Params.Length >= i)
            dbfCmd.Parameters.AddWithValue(paramName, Params[i - 1]); // array index is zero-based
        else
            dbfCmd.Parameters.AddWithValue(paramName, blank);
    }
}

edited Oct 26 '15 at 16:18

answered Oct 26 '15 at 14:57

Mathias R. Jessen

157,619
12
148
206

_And then decide on the value name based on the index in `Params`_ - I'm not sure I understand this statement. Would you mind expanding a bit? – CBreeze Oct 26 '15 at 15:23
@CBreeze If, for example, you always expect them in the order *Name*,*Company*,*Country*,*param1*,*param2* etc., Name will always be `Params[0]` (if present), Company will always be `Params[1]` and so on. Added example – Mathias R. Jessen Oct 26 '15 at 15:46
@MathiasR.Jessen If I looped through the `Params` and added them one by one to the statement with `Parameters.AddWithValue()` then this wouldn't fix the issue with replacing the rest of the Parameters with blank would it? – CBreeze Oct 26 '15 at 15:52
@CBreeze No, but you could add second loop to add those blank values as well, counting from `Params.Length` to 12 if that's the number of columns you have – Mathias R. Jessen Oct 26 '15 at 15:54
Uh... if you know what all the params represent ahead of time then optional parameters are a much better solution. – Casey Oct 26 '15 at 17:08
@Casey Sure, but to a certain point. You really want a method signature with +12 almost indistiguishable arguments? – Mathias R. Jessen Oct 26 '15 at 17:11
@MathiasR.Jessen Well, more than I'd want one that relied on the position of strings in an array, yes. – Casey Oct 26 '15 at 19:01
@MathiasR.Jessen If you're trying to avoid that why not define an object with all the arguments and pass that in instead? That would take the noise out of the method signature and still be more predictable than relying on array position. – Casey Oct 26 '15 at 19:02

score 3 · Answer 2 · edited May 23 '17 at 11:59

I don't know the exact context of your problem, but you can:

Convert your parameter list to a dictionary with key / value (a Dictionary, or Dictionary) so you can pass the exact parameters to your method, so you don't get mixed up in the order of each parameter.

So you will have a

new Dictionary<string, string> { {"firstName": "John"}, {"age": "42"} }

and for the rest of the parameters, have an array with keys on which you can diff and populate the remaining with blank (i guess your procedure needs all the parameters)

or another alternative: use a default dictionary containing all parameters with default values for each column:

new Dictionary<string, string> { "status": "1" }

Then you would just merge the defaults with the parameter, then add all the keys and values to the command.

See: Merging dictionaries in C#

If the order is not important for the parameters (which i doubt) you can use the params keyword. See Why use the params keyword? for when to use.

score 0 · Answer 3 · edited Oct 26 '15 at 15:05

0

You can use params:

public bool SendToDBF(params  string[] parameters)
{

}

public bool SendToDBF(params  object[] parameters)
{

}

edited Oct 26 '15 at 15:05

Patrick Hofman

153,850
22
249
325

answered Oct 26 '15 at 14:57

Aizaz Ahmed

210
1
6

score 0 · Answer 4 · answered Oct 26 '15 at 15:03

I would build a dictionary of field names and values, and construct the SQL myself. You can pass in the dictionary from the method, or build it internally.

Dictionary<string, object> d = new Dictionary<string, object>();

d["personID"] = 1234;
// ...

The SQL can be built like this:

StringBuilder sb = new StringBuilder();
sb.Append("insert into ");
sb.Append(tableName);
sb.Append(" (");

StringBuilder sbValues = new StringBuilder();
sbValues.Append("values (");

bool first = true;
foreach (KeyValuePair<string, object> kvp in d)
{
    if (first)
    {
        first = false;
    }
    else
    {
        sb.Append(", ");
        sbValues.Append(", ");
    }

    sb.Append(kvp.Key);
    sbValues.Append("?");

    // put the value in a SQL parameter;
    dbfCmd.Parameters.AddWithValue("@" + kvp.Key, kvp.Value);
}

sb.Append(") ");
sbValues.Append(")");

sb.Append(sbValues.ToString());

string fullSql = sb.ToString();

It separates building the values string and the rest of the SQL statement, since you else need to go over the collection twice, which seems unnecessary.

using this method how I would I populate the rest of the statements with `empty` if there are more `?` arguments in the statement than in the dictionary? — CBreeze, Oct 26 '15 at 15:15
Hmm ok, thanks for your answer. I'll try to get my head around it. — CBreeze, Oct 26 '15 at 15:16

Send an Unspecified Number of Parameters to a Method

4 Answers4