4

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

public 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?

Improve this question

2 Answers 2

Reset to default
2

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);
}
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Thank you, looks very clean! Question: Why not Dictionary<string, string> parameter?
@Rollo R: Because AddWithValue() can take any kind of object and not just a string. You could use <string, string>, I think, but I find it safer to just specify "any object type", in case in future you wish to pass other kinds of objects which calling ToString() on may not be the right thing to do.
@BoltClock: Thanks. One last thing, I store my T-SQL Queries in a class as a bunch of public static string variables so I can just use them anywhere. Is this a good practice, or is there a less cumbersome way of doing it? I have around 80 SQL Queries declared as public static strings..
@Rollo R: Well I'm not exactly sure - I've not heard of that practice. Typically I just declare them within my new SqlCommand calls but only because I have pretty few queries to run. Unless you're thinking about something like stored procedures although I don't think that's the case...
@BoltClock: Ok thanks anyway, your explanation is very clear. The database this app is using is MySql 4, it doesnt support Stored Procs unfortunately.
|
2

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.

Improve this answer

1 Comment

Wow this is excellent. I now have 3 choices atm, "classic" array, .net dictionary and type information. I have been starting to study LINQ and looks like it has a similar logic to my current approach but I bet it's a lot better! This current project uses MySql 4, so I'm kinda stuck with Connector/NET 5 :(

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.