Automatically configure MySQL options in C #

Question

At the moment, I am creating a new method for each mysql query with the parameters that I want to execute. 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 nervous because I always get 10-30 requests, when two requests can do this for me, a simple method for receiving a nonparametric request and a method for parameters. for instance

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 kind of regular expression where I would say, for example.

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

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

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

then my list will 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

, . ?

:

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

:

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

var result = CommandWithParams(query, parameters);

, , ?
( , ) ​​