Help using .NET Generics / Dictionary when replacing my arrays

Question

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 team will be something like

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

As you can see, I use arrays for the parameter name and parameter value, and both of them must "coincide" with each other and, of course, with the Sql command.

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

Answer 1

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

Each element contains a string with the name of the parameter and the value to be replaced. They are related to what are known as key-value pairs, and help to greatly simplify the mapping of names and values ​​for your request.

Something like that:

 var parameters = new Dictionary<string, object> { { "?FirstName", "Josef" }, { "?LastName", "Stalin" } }; MyConnection.MySqlLink(strConnection, strCommand, parameters, dtTable); 

Here you can see that using the dictionary is easier than managing two separate collections of parameters and values, respectively.

Inside your method, use a foreach loop with KeyValuePair<TKey, TValue> instead of a for loop with numeric indices, for example:

 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); } 

Answer 2

Since BoltClock has already reviewed the vocabulary answer - here is another alternative to considering; type of 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 - I like the approach LINQ-to-SQL takes here; TSQL string (for example)

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

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