I have a function that takes a Dictionary<String, Object>
as an argument. It uses this Dictionary
to create parameters for a query string.
private static TableAdapters dataSource = new TableAdapters();
private void findScriptsQueryButton_Click(object sender, EventArgs e)
{
string fillBy = findByComboBox.SelectedItem.ToString();
string server = serverComboBox.SelectedItem.ToString();
Dictionary<String, Object> parameters = new Dictionary<String, Object>()
{
{"findBy", findByTextBox.Text}
};
dataGridView.DataSource = dataSource.GetDataSource(fillBy, server, parameters).Tables[0];
}
public class TableAdapters
{
public DataSet GetDataSource(string fillBy, string server, Dictionary<String, Object> parameters)
{
string query = GetQuery[fillBy];
SqlConnection connection = new SqlConnection();
connection.ConnectionString = GetSqlConnection[server];
SqlDataAdapter adapter = new SqlDataAdapter(query, connection);
foreach (KeyValuePair<String, Object> p in parameters)
{
adapter.SelectCommand.Parameters.AddWithValue("@" + p.Key, (p.Value.ToString().Length == 0) ? DBNull.Value : p.Value);
}
DataSet dataSet = new DataSet();
dataSet.Tables.Add(new DataTable());
adapter.Fill(dataSet.Tables[0]);
return dataSet;
}
}
My primary problem with my code is:
- I have to create a new dictionary of parameters every time before I call the function.
- The parameter names are hard coded into both the dictionaries and the query strings.
The number of parameters for a query string in this particular program can be 1-11. Is there a better way for me to pass parameters to this function where I don't have to hard code the names? Mind you I don't want to go off parameter number either, because then I've become dependent on ensuring I pass the parameters to the function in the right order.
For brevity here is an example of one of the queries from the query dictionary that this particular instance of the function calls:
private static readonly Dictionary<String, String> GetQuery = new Dictionary<String, String>()
{
{"Target VDN",
"SELECT * FROM table_name WHERE column_name = ISNULL(@findBy, column_name) "
},
// ...
}
In this case I am only dealing with one parameter but as I mentioned some of these queries in the dictionary can take up to 11 parameters.