How to use SQLBinary?

[Roy Chastain]

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

 

[Jon Skeet [C# MVP]]

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.

 

[Miha Markic [MVP C#]]

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

 

[Roy Chastain]

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

 

[Jon Skeet [C# MVP]]

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.

 

[Miha Markic [MVP C#]]

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.

 

[Roy Chastain]

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

 

[Jon Skeet [C# MVP]]

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]

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

Thanks

 

[Jon Skeet [C# MVP]]

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.

 

[Miha Markic [MVP C#]]

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

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

 

[Cor Ligthert]

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?