how to select by a binary column?

  • Thread starter Thread starter Emmanuel Touzery
  • Start date Start date
E

Emmanuel Touzery

Hello,

I have a binary column in a .NET (C#) in-memory DataTable, and I wish
to make a selection based (among others) on this column. It's easy to
do such a thing on eg MS-SQL, but I want to do it in .NET's in-memory
data. I tried to format the byte array in many ways (including the
0x0001 format of MS-SQL, but also 000-0001, {000-001}, [000-001], with
or without quotes, but no way worked.

Any idea how to do that? Please don't question why I would want to do
that ;O)

If I wasn't clear:

DataRow[] matches = dsMyDataSet.Select("Column1 = " + value + " and
Column2 = " + value2);

where value2 is in fact a byte[], and Column2 contains a base64binary
in my in-memory table.

Thank you,

emmanuel
 
Emmanuel said:
DataRow[] matches = dsMyDataSet.Select("Column1 = " + value + " and
Column2 = " + value2);

where value2 is in fact a byte[], and Column2 contains a base64binary
in my in-memory table.
Click to expand...

note that this binary[] is in fact a GUID, that I'm storing on server as
binary so that it works with both MS-SQL and Oracle and others. But I
could convert it to some GUID type in memory, but it doesn't seem to
exist. It seems my only options are base64Binary and hexBinary.

emmanuel
 
Hi Emmanuel,
Emmanuel Touzery said:
Emmanuel said:
DataRow[] matches = dsMyDataSet.Select("Column1 = " + value + " and
Column2 = " + value2);

where value2 is in fact a byte[], and Column2 contains a base64binary
in my in-memory table.
Click to expand...

note that this binary[] is in fact a GUID, that I'm storing on server as
binary so that it works with both MS-SQL and Oracle and others. But I
could convert it to some GUID type in memory, but it doesn't seem to
exist. It seems my only options are base64Binary and hexBinary.
Click to expand...

There is System.Guid data type.
 
Hello,

Thanks for the quick answer! I'm really stuck here...
Hi Emmanuel,
Emmanuel said:
DataRow[] matches = dsMyDataSet.Select("Column1 = " + value + " and
Column2 = " + value2);

where value2 is in fact a byte[], and Column2 contains a base64binary
in my in-memory table.
Click to expand...

note that this binary[] is in fact a GUID, that I'm storing on server as
binary so that it works with both MS-SQL and Oracle and others. But I
could convert it to some GUID type in memory, but it doesn't seem to
exist. It seems my only options are base64Binary and hexBinary.
Click to expand...


There is System.Guid data type.
Click to expand...
In my XML Schema for the DataSet (.resx), I have:

<xs:element name="colName" minOccurs="0"
type="xs:base64Binary"></xs:element>

I tried putting

<xs:element name="colName" minOccurs="0" type="xs:System.Guid"></xs:element>

or

<xs:element name="colName" minOccurs="0" type="System.Guid"></xs:element>

(also without the System.)

and the .resx doesn't build anymore. Also, the option is not in the
combo in visual studio (.NET 1.1).

emmanuel
 
Miha said:
note that this binary[] is in fact a GUID, that I'm storing on server as
binary so that it works with both MS-SQL and Oracle and others. But I
could convert it to some GUID type in memory, but it doesn't seem to
exist. It seems my only options are base64Binary and hexBinary.
Click to expand...


There is System.Guid data type.
Click to expand...

rereading your post, I think we misunderstood: the data i want to filter
for is a byte[] that I can convert to System.Guid if i want to.
My problem is that: if the type IN THE DATASET is binary, I can't seem
to do a select on it (unless I'm wrong??). But if I could let .NET know
that it's actually a Guid inside the dataset, maybe i could make a select?

emmanuel

PS: for instance, the select:
"myCol = 00000000-0000-0000-0000-00000000010f"
fails with "Syntax error: Missing operand after 'f' operator."
(this number was obtained by Guid.ToString())

and
"myCol = '00000000-0000-0000-0000-00000000010f'"
fails with "Cannot perform '=' operation on System.Byte[] and
System.String."
 
Hi Emmanuel,

IMO you should append a column of type Guid to your datatable, copy binary
Guids to System.Guids into that new column.
Then you'll be able to filter with:
"myCol='00000000-0000-0000-0000-00000000010f'"

Is this feasible for you?

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

Emmanuel Touzery said:
Miha said:
note that this binary[] is in fact a GUID, that I'm storing on server as
binary so that it works with both MS-SQL and Oracle and others. But I
could convert it to some GUID type in memory, but it doesn't seem to
exist. It seems my only options are base64Binary and hexBinary.
Click to expand...


There is System.Guid data type.
Click to expand...

rereading your post, I think we misunderstood: the data i want to filter
for is a byte[] that I can convert to System.Guid if i want to.
My problem is that: if the type IN THE DATASET is binary, I can't seem
to do a select on it (unless I'm wrong??). But if I could let .NET know
that it's actually a Guid inside the dataset, maybe i could make a select?

emmanuel

PS: for instance, the select:
"myCol = 00000000-0000-0000-0000-00000000010f"
fails with "Syntax error: Missing operand after 'f' operator."
(this number was obtained by Guid.ToString())

and
"myCol = '00000000-0000-0000-0000-00000000010f'"
fails with "Cannot perform '=' operation on System.Byte[] and
System.String."
Click to expand...
 
Hello,
Hi Emmanuel,

IMO you should append a column of type Guid to your datatable, copy binary
Guids to System.Guids into that new column.
Then you'll be able to filter with:
"myCol='00000000-0000-0000-0000-00000000010f'"

Is this feasible for you?
Click to expand...

well, it's not very elegant :O)
but it's also not feasible for me: I'm using SqlCommandBuilder to apply
my changes back to the server, adding a column etc would confuse it
completely i guess.

So you're basically saying that if my column type was Guid on the
server, it would work? And there's no way to make it work with binary?

emmanuel
 
well, it's not very elegant :O)
:-)

but it's also not feasible for me: I'm using SqlCommandBuilder to apply
my changes back to the server, adding a column etc would confuse it
completely i guess.
Click to expand...

No, it would not. It will ignore the column (it builds commands based on
select statament).
So you're basically saying that if my column type was Guid on the
server, it would work? And there's no way to make it work with binary?
Click to expand...

I am saying that you should do it in *memory* IOW in DataTable instance.
Once you have a DataColumn with guids inside, you can filter it.
 
Hello,
No, it would not. It will ignore the column (it builds commands based on
select statament).
Click to expand...

really? great!
I am saying that you should do it in *memory* IOW in DataTable instance.
Once you have a DataColumn with guids inside, you can filter it.
Click to expand...

OK. Well, I'm not a fan of the idea, but that's better than the
alternatives (storing the GUIDs as char[36] on the server, or not
supporting ORACLE). So I think that's what we'll do.

thanks for the patience and good advices!

emmanuel
 
Back
Top