Help with use of .NET Generics/Dictionary in replacing my Arrays

Mypage.cs

string[] strParameterName = new string[2] {"?FirstName", "?LastName"};
    string[] strParameterValue = new string[2] {"Josef", "Stalin"};        
    MyConnection.MySqlLink(strConnection, strCommand, strParameterName, strParameterValue, dtTable);

Myclass.cs

pu开发者_开发知识库blic static void MySqlLink(string strConnection, string strCommand, string[] strParameterName, string[] strParameterValue, DataTable dtTable)
{
    dtTable.Clear();
    MySqlConnection MyConnection = new MySqlConnection(strConnection);
    MySqlCommand MyCommand = new MySqlCommand(strCommand, MyConnection);

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

    MySqlDataAdapter MyDataAdapter = new MySqlDataAdapter(MyCommand);
    MyDataAdapter.Fill(dtTable);
}

And then my Sql Command will be something like

"SELECT * FROM MyTable WHERE FirstName=?FirstName AND LastName=?LastName"

As you can see, I am using arrays for the Parameter Name and Parameter Value and they both have to "match" with each other and ofcourse the Sql Command.

Someone recommended to me that I use .NET "Dictionary" instead of arrays. Now I have never used that before. Can someone show me a relative example of how I am to use .NET Dictionary here?

Instead of passing an array of names and array of values, you can pass a Dictionary<string, object> that holds parameter names for keys, and arbitrary objects (in your case, also strings) for the values you want to insert into your query.

Each element contains the string with your parameter name, and the value it is to be substituted with. These are associated through what's known as key-value pairs, and helps to make it much clearer your name-value mappings for your query.

Something like this:

var parameters = new Dictionary<string, object>
{
    { "?FirstName", "Josef" }, 
    { "?LastName", "Stalin" }
};

MyConnection.MySqlLink(strConnection, strCommand, parameters, dtTable);

You can see here that using a dictionary is easier than having to manage two separate collections of parameters and values respectively.

Within your method, use a foreach loop with KeyValuePair<TKey, TValue> instead of a for loop with numeric indices, like this:

public static void MySqlLink(string strConnection, string strCommand, Dictionary<string, object> parameters, DataTable dtTable)
{
    dtTable.Clear();
    MySqlConnection MyConnection = new MySqlConnection(strConnection);
    MySqlCommand MyCommand = new MySqlCommand(strCommand, MyConnection);

    foreach (KeyValuePair<string, object> param in parameters)
    {
        MyCommand.Parameters.AddWithValue(param.Key, param.Value);
    }

    MySqlDataAdapter MyDataAdapter = new MySqlDataAdapter(MyCommand);
    MyDataAdapter.Fill(dtTable);
}

Since BoltClock has already covered the dictionary answer - here's another alternative to consider; type information:

public static void MySqlLink(string strConnection, string strCommand,
    object args, DataTable dtTable)
{
    ....
        if (args != null)
        {
            foreach (PropertyInfo prop in args.GetType().GetProperties(
                System.Reflection.BindingFlags.Public
                | System.Reflection.BindingFlags.Instance))
            {
                MyCommand.Parameters.AddWithValue("?" + prop.Name,
                    Convert.ToString(prop.GetValue(args, null)));
            }
        }

with (at the caller)

var args = new {FirstName = "Josef",LastName = "Stalin"};

personally though - I like the approach that LINQ-to-SQL takes here; the TSQL string is (for example)

"SELECT * from Foo where Id = {0} and Status = {1}"

and you just pass in a params object[] of the parameters; the code finds the tokens and invents the argument names for each @p0 etc in the case of sql server.