How to use SQLBinary?

I have a varbinary field in my table. (It has the potential of being rather large.)

I need to read this value, change it and write it back.

I have gotten as far as using the GetSQLBinary method of the SqlDataReader. What I don't know is what do I actually have at that
point. I would assume that somewhere this information is going to be presented as an array of something or perhaps as a BitArray?

How do I actually manipulate the contents of the varbinary column in my application code?

Thanks

-------------------------------------------
Roy Chastain
KMSystems, Inc.

Jon
Mon Jul 26 07:54:37 CDT 2004

Roy Chastain <roy@kmsys.com> wrote:
> I have a varbinary field in my table. (It has the potential of being
> rather large.)
>
> I need to read this value, change it and write it back.
>
> I have gotten as far as using the GetSQLBinary method of the
> SqlDataReader. What I don't know is what do I actually have at that
> point. I would assume that somewhere this information is going to be
> presented as an array of something or perhaps as a BitArray?
>
> How do I actually manipulate the contents of the varbinary column in
> my application code?

You can convert a SqlBinary to a byte array, change the array, and then
use it as the value of the parameter to an update command.

If you need to change the size, you'll need to create a new byte array,
of course.

--
Jon Skeet - <skeet@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

Miha
Mon Jul 26 07:58:11 CDT 2004


"Roy Chastain" <roy@kmsys.com> wrote in message
news:ciu9g09i5hfv0veep6tujbs5tgnp7kniro@4ax.com...
> I have a varbinary field in my table. (It has the potential of being
rather large.)
>
> I need to read this value, change it and write it back.
>
> I have gotten as far as using the GetSQLBinary method of the
SqlDataReader. What I don't know is what do I actually have at that
> point. I would assume that somewhere this information is going to be
presented as an array of something or perhaps as a BitArray?

No, actually you get byte array: byte[]
>
> How do I actually manipulate the contents of the varbinary column in my
application code?

Get byte array and then do whatever you want with it :-)

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

Roy
Mon Jul 26 08:31:29 CDT 2004

Thanks to both Jon and Miha, but I need a little more info.

I assume the Value property is where I get the Byte Array or can I just assign the SqlBinary to a Byte Array since there is an
implicit Conversion routine in the SqlBinary class?

The .Length property is in bytes. Does that mean that if I need the exact bit length I need to keep that value in a separate
column in the table.


Thanks


On Mon, 26 Jul 2004 08:41:32 -0400, Roy Chastain <roy@kmsys.com> wrote:

>I have a varbinary field in my table. (It has the potential of being rather large.)
>
>I need to read this value, change it and write it back.
>
>I have gotten as far as using the GetSQLBinary method of the SqlDataReader. What I don't know is what do I actually have at that
>point. I would assume that somewhere this information is going to be presented as an array of something or perhaps as a BitArray?
>
> How do I actually manipulate the contents of the varbinary column in my application code?
>
>Thanks
>
>-------------------------------------------
>Roy Chastain
>KMSystems, Inc.

-------------------------------------------
Roy Chastain
KMSystems, Inc.

Jon
Mon Jul 26 08:43:18 CDT 2004

Roy Chastain <roy@kmsys.com> wrote:
> Thanks to both Jon and Miha, but I need a little more info.
>
> I assume the Value property is where I get the Byte Array or can I
> just assign the SqlBinary to a Byte Array since there is an
> implicit Conversion routine in the SqlBinary class?

There's an explicit conversion from SqlBinary to byte[], and an
implicit conversion the other way (which I don't think you need). So,
you can do:

byte[] data = (byte[]) reader.GetSqlBinary(...);

(That will throw an exception if the value is a DB Null, of course.)

> The .Length property is in bytes. Does that mean that if I need the
> exact bit length I need to keep that value in a separate
> column in the table.

I suspect so, yes - I can't say I'm an expert on this, but I wouldn't
have thought the database would let you upload only a certain number of
bits rather than bytes.

--
Jon Skeet - <skeet@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

Miha
Mon Jul 26 08:48:08 CDT 2004


"Roy Chastain" <roy@kmsys.com> wrote in message
news:gh1ag09r7es77u06dqa9pdcas2459oji1r@4ax.com...
> Thanks to both Jon and Miha, but I need a little more info.
>
> I assume the Value property is where I get the Byte Array or can I just
assign the SqlBinary to a Byte Array since there is an
> implicit Conversion routine in the SqlBinary class?

I think that both ways would do the same.

>
> The .Length property is in bytes. Does that mean that if I need the exact
bit length I need to keep that value in a separate
> column in the table.

Yes.

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

Roy
Mon Jul 26 10:38:04 CDT 2004

Again thanks. I am down to one problem, I hope.

Once I update the in memory copy of the SqlBinary object, how do I get that back to the database.
I wanted to do an Update command through ExecuteNonQuery, but I am not sure how to represent the data.
I don't think I really want to convert a 6000 bit value into a string for the update command.

Do I need to use a DataSet?

Thanks


On Mon, 26 Jul 2004 09:31:29 -0400, Roy Chastain <roy@kmsys.com> wrote:

>Thanks to both Jon and Miha, but I need a little more info.
>
>I assume the Value property is where I get the Byte Array or can I just assign the SqlBinary to a Byte Array since there is an
>implicit Conversion routine in the SqlBinary class?
>
>The .Length property is in bytes. Does that mean that if I need the exact bit length I need to keep that value in a separate
>column in the table.
>
>
>Thanks
>
>
>On Mon, 26 Jul 2004 08:41:32 -0400, Roy Chastain <roy@kmsys.com> wrote:
>
>>I have a varbinary field in my table. (It has the potential of being rather large.)
>>
>>I need to read this value, change it and write it back.
>>
>>I have gotten as far as using the GetSQLBinary method of the SqlDataReader. What I don't know is what do I actually have at that
>>point. I would assume that somewhere this information is going to be presented as an array of something or perhaps as a BitArray?
>>
>> How do I actually manipulate the contents of the varbinary column in my application code?
>>
>>Thanks
>>
>>-------------------------------------------
>>Roy Chastain
>>KMSystems, Inc.
>
>-------------------------------------------
>Roy Chastain
>KMSystems, Inc.

-------------------------------------------
Roy Chastain
KMSystems, Inc.

Jon
Mon Jul 26 10:51:39 CDT 2004

Roy Chastain <roy@kmsys.com> wrote:
> Again thanks. I am down to one problem, I hope.
>
> Once I update the in memory copy of the SqlBinary object, how do I
> get that back to the database. I wanted to do an Update command
> through ExecuteNonQuery, but I am not sure how to represent the data.
> I don't think I really want to convert a 6000 bit value into a string
> for the update command.

Just use an update command with a parameter, and set the value of the
parameter to the byte array you want to upload.

> Do I need to use a DataSet?

No.

--
Jon Skeet - <skeet@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

Roy
Mon Jul 26 11:53:18 CDT 2004

I am really somewhat of a newbie in this area. Can you give me an example?

Thanks

On Mon, 26 Jul 2004 16:51:39 +0100, Jon Skeet [C# MVP] <skeet@pobox.com> wrote:

>Roy Chastain <roy@kmsys.com> wrote:
>> Again thanks. I am down to one problem, I hope.
>>
>> Once I update the in memory copy of the SqlBinary object, how do I
>> get that back to the database. I wanted to do an Update command
>> through ExecuteNonQuery, but I am not sure how to represent the data.
>> I don't think I really want to convert a 6000 bit value into a string
>> for the update command.
>
>Just use an update command with a parameter, and set the value of the
>parameter to the byte array you want to upload.
>
>> Do I need to use a DataSet?
>
>No.

-------------------------------------------
Roy Chastain
KMSystems, Inc.

Jon
Mon Jul 26 12:16:01 CDT 2004

Roy Chastain <roy@kmsys.com> wrote:
> I am really somewhat of a newbie in this area. Can you give me an
> example?

I don't have time for a complete sample right now, but there are plenty
of examples of using parameters in MSDN. Look at SqlParameter and
SqlCommand.Parameters for examples. They're not blob-specific, but the
principle is the same.

--
Jon Skeet - <skeet@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

Miha
Mon Jul 26 12:26:47 CDT 2004

Hi Roy,

Check out this article:
How To Read and Write BLOB Data by Using ADO.NET with Visual C# .NET
http://support.microsoft.com/default.aspx?scid=kb;en-us;309158&Product=adonet
There are also some variations (vb.net, etc)

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

"Roy Chastain" <roy@kmsys.com> wrote in message
news:vmdag0tdpoh2fm23rg1r5m5r4bro3g9a6c@4ax.com...
> I am really somewhat of a newbie in this area. Can you give me an
example?
>
> Thanks
>
> On Mon, 26 Jul 2004 16:51:39 +0100, Jon Skeet [C# MVP] <skeet@pobox.com>
wrote:
>
> >Roy Chastain <roy@kmsys.com> wrote:
> >> Again thanks. I am down to one problem, I hope.
> >>
> >> Once I update the in memory copy of the SqlBinary object, how do I
> >> get that back to the database. I wanted to do an Update command
> >> through ExecuteNonQuery, but I am not sure how to represent the data.
> >> I don't think I really want to convert a 6000 bit value into a string
> >> for the update command.
> >
> >Just use an update command with a parameter, and set the value of the
> >parameter to the byte array you want to upload.
> >
> >> Do I need to use a DataSet?
> >
> >No.
>
> -------------------------------------------
> Roy Chastain
> KMSystems, Inc.

Cor
Mon Jul 26 12:36:22 CDT 2004

Roy,

Is this sample I once made something for you

It uses not the database itself however the dataset, but that is the same as
a database, it needs only an update or a select for whatever database

I hope this helps, the sample goes completly, it needs only a formproject, a
picturebox on it and 4 buttons. (When you use something else than a pic you
have to delete the picturebox part, that is by the way only for showing).

I hope this helps

Cior

Private abyt() As Byte
Private fo As New OpenFileDialog
Private sf As New SaveFileDialog
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
'Reading a pictur from file and put it in a bytearray
If fo.ShowDialog = DialogResult.OK Then
Dim fs As New IO.FileStream(fo.FileName, _
IO.FileMode.Open)
Dim br As New IO.BinaryReader(fs)
abyt = br.ReadBytes(CInt(fs.Length))
br.Close()
'just to show the sample without a fileread error
Dim ms As New IO.MemoryStream(abyt)
Me.PictureBox1.Image = Image.FromStream(ms)
End If
End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal _
e As System.EventArgs) Handles Button2.Click
'writing a picture from a bytearray to a file
If sf.ShowDialog = DialogResult.OK Then
Dim fs As New IO.FileStream(sf.FileName, _
IO.FileMode.CreateNew)
Dim bw As New IO.BinaryWriter(fs)
bw.Write(abyt)
bw.Close()
End If
End Sub

Private Sub Button3_Click(ByVal sender As System.Object, ByVal _
e As System.EventArgs) Handles Button3.Click
'writing a bytearray to a dataset and than to disk
Dim ds As New DataSet
ds.Tables.Add(New DataTable("Photo"))
ds.Tables(0).Columns.Add(New DataColumn("Sample"))
ds.Tables(0).Columns(0).DataType =
System.Type.GetType("System.Byte[]")
ds.Tables(0).Rows.Add(ds.Tables(0).NewRow)
ds.Tables(0).Rows(0)(0) = abyt
Dim sf As New SaveFileDialog
If sf.ShowDialog = DialogResult.OK Then
ds.WriteXml(sf.FileName, XmlWriteMode.WriteSchema)
End If
End Sub

Private Sub Button4_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button4.Click
'reading a picture from a dataset from disk and set it in the pic
box
Dim ds As New DataSet
If fo.ShowDialog = DialogResult.OK Then
ds.ReadXml(fo.FileName)
End If
abyt = CType(ds.Tables(0).Rows(0)(0), Byte())
Dim ms As New IO.MemoryStream(abyt)
Me.PictureBox1.Image = Image.FromStream(ms)
End Sub
> I am really somewhat of a newbie in this area. Can you give me an
example?
>
> Thanks
>
> On Mon, 26 Jul 2004 16:51:39 +0100, Jon Skeet [C# MVP] <skeet@pobox.com>
wrote:
>
> >Roy Chastain <roy@kmsys.com> wrote:
> >> Again thanks. I am down to one problem, I hope.
> >>
> >> Once I update the in memory copy of the SqlBinary object, how do I
> >> get that back to the database. I wanted to do an Update command
> >> through ExecuteNonQuery, but I am not sure how to represent the data.
> >> I don't think I really want to convert a 6000 bit value into a string
> >> for the update command.
> >
> >Just use an update command with a parameter, and set the value of the
> >parameter to the byte array you want to upload.
> >
> >> Do I need to use a DataSet?
> >
> >No.
>
> -------------------------------------------
> Roy Chastain
> KMSystems, Inc.