Auto-magically add MySQL parameters in C#

Question

At the moment, I'm creating a new method for each mysql query with parameters I want to get performed. An example:

public DataTable RetreiveAllLinks(int id, int categoryid)
{
    const string request =
    @"
        SELECT * 
        FROM links
        WHERE id=?id,categoryid=?categoryid
        ORDER by id DESC
    ";
    using (var query = new MySqlCommand(request))
    {
        query.Parameters.AddWithValue("?id", id);
        query.Parameters.AddWithValue("?categoryid", categoryid);
        return _connection.RetreiveData(query);
    }
}

This is really getting on my nerves, because I always end up with 10-30 queries, when two queries can do it for me, a simple method for retrieving non-parameter query, and a method for parameters. For example

public DataTable NonParameterCommand(string r)
{
    var request = r;
    using (var query = new MySqlCommand(request))
    {
        return _connection.RetreiveData(query);
    }
}

What I want to do, is some regex'ing, where i would say e.g.

var pPattern = "^[\\?][\\w]";

and then a method with request and a list as parameters, and the list should be in the same order as the request parameters. So if the request is

`"SELECT * FROM test WHERE id=?id,categoryid=?categoryid"` 

then my list would look like

var _dictionary = new Dictionary<string, string>();
_dictionary.Add(_parameter, _value);

and my method

ParameterCommand(string r, Dictionary dic)

but how exactly?

Answer 1

If you are using a dictionary, then the key would already be the parameter name. Why parse the query to extract them?

You could do:

public DataTable CommandWithParams(string sql, Dictionary<string, object> parameters)
{
    using (var query = new MySqlCommand(sql))
    {
        foreach (var param in parameters)
        {
            query.Parameters.AddWithValue(param.Key, param.Value);
        }
        return _connection.RetreiveData(query);
    }
}

Calling it:

var parameters = new Dictionary<string, object>()
{
    { "?param1", value1 },
    { "?param2", value2 }
};

var result = CommandWithParams(query, parameters);

But maybe you'd like to use a List instead of a Dictionary?
(I wouldn't recommend it, passing the parameters values based on their position would be error-prone)