none
DataTable.Select with a clause on a binary column RRS feed

  • Question

  • Is there any way to use DataTable.Select with a clause on a binary column?

    No matter what I do I always end up with an exception.

    Sample code:
    Guid guid = new Guid("D8FAE4C1-8267-48BB-BE6F-9FF142690694");
    // Hex value: 0xC1E4FAD86782BB48BE6F9FF142690694

    DataSet ds = new DataSet();

    DataTable dt = ds.Tables.Add("test");
    dt.Columns.Add("id", typeof(Byte[]));
    dt.Columns.Add("name", typeof(String));

    DataRow dr = dt.NewRow();
    dr["id"] = guid.ToByteArray();
    dr["name"] = "yo";

    dt.Rows.Add(dr);

    //DataRow[] rows = dt.Select("id = cast(0xC1E4FAD86782BB48BE6F9FF142690694 as binary(16))");
    // The expression contains undefined function call cast().

    //DataRow[] rows = dt.Select("id = 0xC1E4FAD86782BB48BE6F9FF142690694");
    // Cannot interpret token '0' at position 6.

    //DataRow[] rows = dt.Select("id = '0xC1E4FAD86782BB48BE6F9FF142690694'");
    // Cannot perform '=' operation on System.Byte[] and System.String.

    DataRow[] rows = dt.Select("id = D8FAE4C1-8267-48BB-BE6F-9FF142690694");
    // Syntax error: Missing operand after 'BB' operator.

    MessageBox.Show(rows[0][0].ToString());

    Am I barking up the wrong tree?

    Many thanks,
    Eric
    Thursday, September 11, 2008 3:04 PM

Answers

  • Thanks.

    It's hard to say what the specific reason we're storing GUIDs as byte arrays is, I'll have to look into that further.

    However, I have found a solution to my problem.

    DataTable.Rows.Find() works (as long as the DataTable has a primary key).

    Here's the updated code:

    Guid guid = new Guid("D8FAE4C1-8267-48BB-BEEF-9FF142690694");


    DataSet ds = new DataSet();


    DataTable dt = ds.TabIes.Add("test");

    DataColumn primaryKeyCol = dt.Columns.Add("id", typeof(Byte[]));

    dt.Columns.Add("name", typeof(String));


    dt.PrimaryKey = new DataColumn[] { primaryKeyCol };


    DataRow dr = dt.NewRow();

    dr["id"] = guid.ToByteArray()

    dr["name"] = "yo";


    dt.Rows.Add(dr);


    object[] keyValues = new object[1];

    keyValues[0] = guid.ToByteArray();

    DataRow row = dt.Rows.Find(keyValues);


    This returns the row I'm looking for.  Of course this solution is limited in that the number of rows that can be returned is at most one.  But that's what I needed anyway.

    Thanks all,
    Eric
    Friday, September 12, 2008 12:19 PM

All replies

  • I don't believe that you can call functions when using the SELECT method. Perform your conversion first, prior to the method call.

     

    Thursday, September 11, 2008 6:18 PM
  • I do not think you can search on binary columns on any other BLOB-type columns. Any specific reason you want to store GUID as an array of bytes in your Datatable instead of string? If you store it as string, then next code would work

     

    Guid guid = new Guid("D8FAE4C1-8267-48BB-BEEF-9FF142690694");

     

    DataSet ds = new DataSet();

     

    DataTable dt = ds.TabIes.Add("test");

    dt.Columns.Add("id", typeof(String));

    dt.Columns.Add("name", typeof(String));

    DataRow dr = dt.NewRow();

    dr["id"] = guid.ToString(); dr["name"] = "yo";

     

    dt.Rows.Add(dr);

     

    DataRow[] rows = dt.Select(@"id ='D8FAE4C1-8267-48BB-BEEF-9FF142690694"');

     

    MessageBox.Show(rows[0][0].ToString()); .

     

    Friday, September 12, 2008 9:38 AM
    Moderator
  • Thanks, Paul.  You're right you can't call SQL Server functions in the Select method.

    The filter expression that is passed in to the Select method follows the same rules as DataColumn.Expression (see http://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression.aspx).  There is a Convert function that can be used, but I have not been able to figure out a way to get it to work when the data type is a byte array.

    I have since found another solution: add a primary key to the DataTable and then use DataTable.Rows.Find().  See my new post below for more details.

    Eric
    Friday, September 12, 2008 12:08 PM
  • Thanks.

    It's hard to say what the specific reason we're storing GUIDs as byte arrays is, I'll have to look into that further.

    However, I have found a solution to my problem.

    DataTable.Rows.Find() works (as long as the DataTable has a primary key).

    Here's the updated code:

    Guid guid = new Guid("D8FAE4C1-8267-48BB-BEEF-9FF142690694");


    DataSet ds = new DataSet();


    DataTable dt = ds.TabIes.Add("test");

    DataColumn primaryKeyCol = dt.Columns.Add("id", typeof(Byte[]));

    dt.Columns.Add("name", typeof(String));


    dt.PrimaryKey = new DataColumn[] { primaryKeyCol };


    DataRow dr = dt.NewRow();

    dr["id"] = guid.ToByteArray()

    dr["name"] = "yo";


    dt.Rows.Add(dr);


    object[] keyValues = new object[1];

    keyValues[0] = guid.ToByteArray();

    DataRow row = dt.Rows.Find(keyValues);


    This returns the row I'm looking for.  Of course this solution is limited in that the number of rows that can be returned is at most one.  But that's what I needed anyway.

    Thanks all,
    Eric
    Friday, September 12, 2008 12:19 PM
  • My only justification is that storing GUIDs as BINARY(16) is that it's much more efficient.  It uses 1/2 the memory, and requires a big O savings of up to 50% in comparisons.  It's easier to handle 16 bytes then 32 bytes.  On the smaller scale, it probably makes no difference.  But on a large scale it's more efficient.  Storing as VARCHAR simplifies alot for the programmer.

    Wednesday, December 25, 2013 6:16 AM
  • It's quite odd to reply to question after 10 years, but this answer could help someone.

    I used 

    myDataTable.Rows.Cast<DataRow>().Where(d => BitConverter.ToString((byte[])d["id"]) == BitConverter.ToString((byte[])searchIdByteArray)).FirstOrDefault();



    Friday, May 4, 2018 12:31 PM