OleDb Parameters Array Format

Question

Hi in the OleDB documentation (linked below) they have a parameter called OleDbParameter[] parametersin a functions, a loop then iterates over that array to insert the parameters into the SQL.

I can't find in the documentation how you are supposed to format that array?

Docs: https://learn.microsoft.com/en-us/dotnet/api/system.data.oledb.oledbcommand.parameters?view=dotnet-plat-ext-3.1

`how you are supposed to format that array?` ← What do you mean by format? — Igor, May 07 '20 at 17:02
If I ran the function `CreateMyOleDbCommand` from the documentation, what would I need to put into the `OleDbParameter[] parameters` parameter. For example what would I need to do to put UK and London into that SQL query in the example in the docs — Oliver Guy, May 07 '20 at 17:07
It is an array of OleDbParameter. You declare a variable of that type with the size you need then you build the parameters one by one and insert them in the array, then pass the array to the function — Steve, May 07 '20 at 17:10
^-- What Steve said and keep in mind that the order of the parameters in the array must match the order they appear in the sql statement. — Igor, May 07 '20 at 17:11
By the way that example is wrong and not compilable. You cannot add an array to a Parameters collection with Add, but you should use AddRange (and this makes the following loop useless) — Steve, May 07 '20 at 17:13

Steve · Accepted Answer · 2020-05-07T18:15:29.393

The code in the Microsoft Example is wrong and not compilable. I have already sent a feedback about it. Here a more correct version with annotations about what to change.

public void CreateMyOleDbCommand(OleDbConnection connection,
                  string queryString, OleDbParameter[] parameters)
{
    OleDbCommand command = new OleDbCommand(queryString, connection);

    // This destroys the commandtext set by querystring in the constructor.
    // command.CommandText =
    //    "SELECT CustomerID, CompanyName FROM Customers WHERE Country = ? AND City = ?";

    // This cannot work, you can't add an array with Add but only one element
    // command.Parameters.Add(parameters);
    command.Parameters.AddRange(parameters);

    // After AddRange this loop is pointless and creates possible errors with
    // too many parameters added to the collection
    // for (int j=0; j<parameters.Length; j++)
    //{
    //    command.Parameters.Add(parameters[j]) ;
    //}

    string message = "";
    for (int i = 0; i < command.Parameters.Count; i++)
    {
        message += command.Parameters[i].ToString() + "\n";
    }
    Console.WriteLine(message);
}

Now to call this function you have

string cmdText = @"SELECT CustomerID, CompanyName 
                   FROM Customers 
                   WHERE Country = ? AND City = ?";
OleDbParameter[] parameters = new OleDbParameter[]
{
    new OleDbParameter{ ParameterName = "?",OleDbType = OleDbType.VarWChar, Value = "UK" },
    new OleDbParameter{ ParameterName = "?",OleDbType = OleDbType.VarWChar, Value = "London"},
};
CreateMyOleDbCommand(myConnection, cmdText, parameters);

OleDb Parameters Array Format

1 Answers1