Adding MySQL Parameters

Question

Is there a way to create parameters for a MySQLCommand object before creating the actual object?

Currently I have to set up generated commands like this:

public static MySqlCommand User(MySqlConnection MySQL, User u)
{
    var cmdVars = new List<string>();
    const string cmd = "SELECT * FROM `schema`.`table` WHERE 1=1 AND ";

    if (u.ID != null) cmdVars.Add("`ID` LIKE @0");
    if (u.Username != null) cmdVars.Add("`Username` LIKE @1");
    if (u.First != null) cmdVars.Add("`FirstName` LIKE @2");
    if (u.Last != null) cmdVars.Add("`LastName` LIKE @3");
    if (u.Phone != null) cmdVars.Add("`Phone` LIKE @4");
    if (u.Extension != null) cmdVars.Add("`Extension` LIKE @5");
    if (u.Email != null) cmdVars.Add("`Email` LIKE @6");

    var MySqlCmd = new MySqlCommand(cmd + string.Join(" AND ", cmdVars), MySQL);

    if (u.ID != null) MySqlCmd.Parameters.AddWithValue("@0", u.ID);
    if (u.Username != null) MySqlCmd.Parameters.AddWithValue("@1", u.Username);
    if (u.First != null) MySqlCmd.Parameters.AddWithValue("@2", u.First);
    if (u.Last != null) MySqlCmd.Parameters.AddWithValue("@3", u.Last);
    if (u.Phone != null) MySqlCmd.Parameters.AddWithValue("@4", u.Phone);
    if (u.Extension != null) MySqlCmd.Parameters.AddWithValue("@5", u.Extension);
    if (u.Email != null) MySqlCmd.Parameters.AddWithValue("@6", u.Email);

    return MySqlCmd;
}

Which, although works fine, is gross to look at as more parameters are added and requires a user to create a new User object just to search by one value.

I considered switching to the method shown in this question, but in use with MySQLConnector it becomes overly complicated to manage the values and doesn't really do anything for improved readability.

juergen d · Answer 1 · 2016-06-30T18:05:04.453

How about using a query like this

... WHERE (@0 is null or `ID` LIKE @0)
      AND (@1 is null or `Username` LIKE @1)
      AND (@2 is null or `FirstName` LIKE @2) ...

Then you can add all your parameters no matter if NULL or not.

Before executing you can run this

private void FillNullValuesWithDbNull(SqlParameterCollection parameters)
{ 
    foreach (IDataParameter param in parameters)
    {
        if (param.Value == null) param.Value = DBNull.Value;
    }
}

Uueerdo · Answer 2 · 2016-06-30T18:19:14.683

0

My suggestion would be instead of having a User instance passed to the function, create a new class called something like UserSearchCriteria; that class could have a much simpler design (0 parameter constructor, all properties with simple setters, etc...).

It wouldn't do much to shorten or "clean up" the function code, but would make using the function much simpler.

Actually, as far as the function goes, you can probably do something like this...

public static MySqlCommand User(MySqlConnection MySQL, UserSearchCriteria u)
{
    var cmdVars = new List<string>();
    const string cmd = "SELECT * FROM `schema`.`table` WHERE 1=1 AND ";

    var MySqlCmd = new MySqlCommand("", MySQL);

    if (u.ID != null) 
    {
        cmdVars.Add("`ID` LIKE @0");
        MySqlCmd.Parameters.AddWithValue("@0", u.ID);
    }
    if (u.Username != null)
    { 
        cmdVars.Add("`Username` LIKE @1");
        MySqlCmd.Parameters.AddWithValue("@1", u.Username);
    }

    // and the rest of the relevant properties

    MySqlCmd.CommandText = cmd + string.Join(" AND ", cmdVars);

    return MySqlCmd;
}

public void somefunction()
{
    var myCmd = User(someconnections, new UserSearchCriteria() {FirstName = "Joe"});
}

edited Jun 30 '16 at 18:19

answered Jun 30 '16 at 18:12

Uueerdo

15,723
1
16
21

except for there is a difference between `schema`.`table`` and `'schema`.`table'` – MethodMan Jun 30 '16 at 18:20
@MethodMan I am not sure what you are trying to say. – Uueerdo Jun 30 '16 at 18:21
I'm still not sure what you mean. There are no single quotes anywhere in what I posted and the "`" is what is used in MySQL to enclose schema, table, and field names. (_"figure it out"_ makes it look like you are trolling and cannot actually point out the problem.) – Uueerdo Jun 30 '16 at 18:37
never mind.. I did point out the problem.. look at the single tic marks they slant to the left.. the one on the keyboard next to the tilda sign `~` incorrect for quotes Its all good ... cmdVars.Add("`Username LIKE @1"); – MethodMan Jun 30 '16 at 18:42
As I said, ` is the correct delimiter for MySql schema, table, and field names, `'` is not. `"` can be used under some configurations, but I don't think `'` is recognized as anything other than a string delimiter by MySQL under any configuration. The `Username` in `Username LIKE @1` is a field name, not a string. Doing `'Username' LIKE @1` would always be false unless the value for parameter `@1` was: Username, %Username, Username%, or %Username%. – Uueerdo Jun 30 '16 at 18:44

Adding MySQL Parameters

2 Answers2