how to pass objects (byte array) in query if query

问题:

I have this query to be run:

string query = @"SELECT *
                 FROM   hint 
                 WHERE  addedSessionId IN (x, y, z, ............)";


if (_connSource.State != ConnectionState.Open)
    _connSource.Open();

MySqlCommand cmd = new MySqlCommand(query, _connSource);
MySqlDataReader r = cmd.ExecuteReader();

List<Hint> lstHint = new List<Hint>();
while (r.Read())
{
    Hint h = new Hint(Convert.ToInt32(r[0]), Convert.ToInt32(r[1]), 
                      Convert.ToString(r[2]), Convert.ToInt32(r[3]), 
                      Convert.ToInt32(r[4]));
    h.addedSessionId = (Guid)r[5];

    lstHint.Add(h);
}

r.Close(); //important

In the above code, how do I pass values x, y, z etc into the query itself? x, y, z etc are not int or string but byte arrays. In my code they are .net Guid fields, but I'm saving it in db as binary values by converting it to byte arrays.

I can achieve what I want by doing this in parameterized way as shown below:

string query = @"SELECT *
                 FROM   hint 
                 WHERE  addedSessionId = @newSessionId";


if (_connSource.State != ConnectionState.Open)
    _connSource.Open();

List<Hint> lstHint = new List<Hint>();
foreach (List<Guid> myGuid in lstGuid)
{
    MySqlCommand cmd = new MySqlCommand(query, _connSource);
    cmd.Parameters.AddWithValue("newSessionId", myGuid.ToByteArray());
    MySqlDataReader r = cmd.ExecuteReader();

    while (r.Read())
    {
        int id = Convert.ToInt32(r[0]);

        if (IsThisEntryAlreadyAdded(id, lstHint))
            continue;

        Hint h = new Hint(id, Convert.ToInt32(r[1]),
                          Convert.ToString(r[2]), Convert.ToInt32(r[3]),
                          Convert.ToInt32(r[4]));
        h.addedSessionId = (Guid)r[5];

        lstHint.Add(h);
    }

    r.Close(); //important
}

The problem with the 2nd approach is that its terribly slower comparatively. Here not only query has to be run to database a lot more times, but also each time I need to ensure if that particular entry is not already added by running this IsThisEntryAlreadyAdded function.

My question is how to pass objects (in my case byte arrays) in non-parametrized queries? If its impossible, my question is is there an alternative to make my query faster?

回答1:

Here is sample(not tested just pseudo code) how I imagine this could be done:

string query = @"SELECT * FROM   hint  WHERE  addedSessionId IN (";
MySqlCommand cmd = new MySqlCommand(query, _connSource);
int i = 0;
foreach (List<Guid> myGuid in lstGuid)
{
    query = string.Format("{0}@param{1}", query, i);
    cmd.Parameters.AddWithValue(string.Format("@param{0}", i), myGuid.ToByteArray());
    i++;
    if(i != lstGuid.Count) query = string.Format("{0},", query);
}
query = string.Format("{0})", query);
cmd.CommandText = query;
//Here you have command with constructed query and params

回答2:

@Reinuz's answer answers my question, but in my scenario its painful. When I have very large number of values for IN ( clause, I can exceed the maximum packet allowed size and hence the query wont run at all.

So here is what I ended up with, which is much faster for large table:

string query = @"SELECT * 
                 FROM   hint 
                 WHERE  addedSessionId IN (" + GetStringValuesFromGuidList()  + ")";

void GetStringValuesFromGuidList()
{
    string guids = null;
    for (int i = 0; i < lstGuid.Count; i++)
    {
        guids += "'" + UTF8Encoding.Default.GetString(lstGuid[i].ToByteArray()).Replace("\\", "\\\\").Replace("'", "''") + "'";
        if (i < lstGuid.Count - 1)
            guids += ", ";
    }

    return guids;
}

I convert the Guids to its string representation and then pass it to query where MySQL can do an equivalent string search.

For limited number of entries, parameterized query (Reinuz's answer) is the best. For very large values, go for string search

Edit: The UTF8Encoding one should use here depends on the character set of the database whether its unicode etc. I had success with .Default in most cases. Furthermore the string representation of binary will have reserved characters. So escape it with ' or \