how to pass objects (byte array) in query if query is not parameterized?

Question

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?

you can construct your query and use parameters at same time — Renatas M., Apr 17 '12 at 11:57
@Reniuz thats an excellent idea. Not that I have not thought about it, but how to implement? Some sample code I can see? — nawfal, Apr 17 '12 at 11:59
possible duplicate of [Parameterize a SQL IN clause](http://stackoverflow.com/questions/337704/parameterize-a-sql-in-clause) — nawfal, Jun 28 '14 at 06:18

score 1 · Accepted Answer · edited Jan 13 '15 at 08:48

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

edited Jan 13 '15 at 08:48

Eli

4,576
1
27
39

answered Apr 17 '12 at 12:18

Renatas M.

11,694
1
43
62

Fantastic, this is exactly what I had in my mind. But didnt think this `"@param"+i.ToString();` part would be as silly. I didnt put too much mind into it, instead was curious to know the standard practice in such scenarios. Is this the accepted practice in such cases? – nawfal Apr 17 '12 at 12:33
Well I cant see anything wrong in here...maybe you can change to myGuid.ToString in this place to make 'unpredictable' parameter :) – Renatas M. Apr 17 '12 at 13:06
haha, fine. thanks. well cant go ahead with tostring since in my db the Guid field is binary representation of Guids and not char representation. Finally, anyway thankssss... – nawfal Apr 17 '12 at 13:09

nawfal · Answer 2 · 2012-06-24T09:29:56.923

@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 \

how to pass objects (byte array) in query if query is not parameterized?

2 Answers2

Linked