Binary Serialization -- how to actually insert into database?

26 Oct 2006 4:54 PM

matt

hey all,

ive read some good articles on the tenents of serialization and when &
why youd want to do it, as well as the types. great!

now id like to put it to practice. however....none of the articles i
found had database examples -- all were files. how do i insert a memory
stream into the database? (im currently usesing oracle, so this would
be into a table w/ a CLOB column).

heres what i have so far:

    DataTable someData = GetData();

    MemoryStream stream = new MemoryStream();

    BinaryFormatter formatter = new BinaryFormatter();
    formatter.Serialize(stream, someData);

....thanks for the tips or links.

matt

26 Oct 2006 5:57 PM

Patrick Steele

In article <1161881651.151886.219***@f16g2000cwb.googlegroups.com>,
m***@mailinator.com says...
Show quote

> hey all,
>
> ive read some good articles on the tenents of serialization and when &
> why youd want to do it, as well as the types. great!
>
> now id like to put it to practice. however....none of the articles i
> found had database examples -- all were files. how do i insert a memory
> stream into the database? (im currently usesing oracle, so this would
> be into a table w/ a CLOB column).
>
> heres what i have so far:
>
>
>     DataTable someData = GetData();
>
>     MemoryStream stream = new MemoryStream();
>
>     BinaryFormatter formatter = new BinaryFormatter();
>     formatter.Serialize(stream, someData);
>
>
> ...thanks for the tips or links.
>

Use the MemoryStream.ToArray() method to get a byte[]. You should be
able to stick that in your CLOB column.

--
Patrick Steele
http://weblogs.asp.net/psteele

26 Oct 2006 6:28 PM

matt

Patrick Steele wrote:

> Use the MemoryStream.ToArray() method to get a byte[]. You should be
> able to stick that in your CLOB column.

thanks. im guessing thats what i have to do...clob takes in characters
up to 4gb. theres a BLOB datatype that takes a binary object up to 4gb
as well. but ive not serialized before so im not up to speed on which
to use.

i see theres also a .ToString() method on the stream. any idea what the
difference is between working w/ a byte array vs a string? i know i can
pass the string into oracle's CLOB; i dont know yet about the array.

thanks,
matt

26 Oct 2006 7:41 PM

matt

ah.. looks like the byte array is what meshes w/ the binary data
stream, so BLOB is what should be used. tests work, good to go.

thanks,
matt

26 Oct 2006 10:42 PM

Dave Sexton

Hi Matt,

> i see theres also a .ToString() method on the stream. any idea what the
> difference is between working w/ a byte array vs a string? i know i can
> pass the string into oracle's CLOB; i dont know yet about the array.

MemoryStream inherits ToString from System.Object, but it doesn't override the
inherited behavior. Therefore, ToString will only return the full Type name
of the object - in this case, "System.IO.MemoryStream". To retrieve the
contents of a MemoryStream you should use the ToArray method, as suggested by
Patrick.

And yes you are correct that BLOB corresponds with binary data, which is the
contents of your MemoryStream and the output of the ToArray method, so it
doesn't make sense to use a character data type in your database.

--
Dave Sexton

<m***@mailinator.com> wrote in message

Show quote

news:1161887313.837108.311990@m73g2000cwd.googlegroups.com...
> Patrick Steele wrote:
>> Use the MemoryStream.ToArray() method to get a byte[]. You should be
>> able to stick that in your CLOB column.
>
> thanks. im guessing thats what i have to do...clob takes in characters
> up to 4gb. theres a BLOB datatype that takes a binary object up to 4gb
> as well. but ive not serialized before so im not up to speed on which
> to use.
>
> i see theres also a .ToString() method on the stream. any idea what the
> difference is between working w/ a byte array vs a string? i know i can
> pass the string into oracle's CLOB; i dont know yet about the array.
>
>
> thanks,
> matt
>

28 Oct 2006 8:10 AM

Tommaso Caldarola

m***@mailinator.com wrote:

> hey all,
>
> now id like to put it to practice. however....none of the articles i
> found had database examples -- all were files. how do i insert a memory
> stream into the database? (im currently usesing oracle, so this would
> be into a table w/ a CLOB column).

make sense to store binary data in database?

The database is the right place in order to do data warehouse, reporting and so
on not a container of all.

28 Oct 2006 4:24 PM

Dave Sexton

Hi Tommaso,

> The database is the right place in order to do data warehouse, reporting and
> so on not a container of all.

Unless it's a relational database, of course.

Just to cite a few examples, ASP.NET session state or the ASP.NET 2.0 provider
infrastructure data such as membership and roles are commonly stored today in
Microsoft Sql Server, a well known RDBMS. This shows that databases aren't
only for data warehousing and reporting.

Session State Modes on MSDN:
http://msdn2.microsoft.com/en-us/library/ms178586.aspx

Configuring ASP.NET 2.0 Application Services to Use SQL Server 2000 / 2005 on
MSDN:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnaspp/html/configaspnet_sql.asp

> make sense to store binary data in database?

Serialization into Sql Server, for example, provides relational storage for
the binary data, which is otherwise usually controlled by a file-numbering
system outside of the database. This method of relational persistence isn't
enforced by simple database constraints that ensure the integrity of the data,
but instead only by your code - if done correctly. If each file, commonly
graphical images, are to be saved in reference to a primary key in a database
then it makes sense in some circumstances to just store the data in the
database row along with the related data.

Controlling the serialization of an object and storing it in the database is a
flexible alternative to other persistence mechanism and can be used in
WinForms and web apps to persist complex object graphs without having to write
some proprietary xml schema or file-numbering system that introduces the
possibility for more bugs and requires you to author I/O, which databases
handle for you efficiently, behind the scenes.

Xml object graphs could be used instead to provide a more flexible alternative
to binary serialization. Sql Server supports xpath to query the serialized
structure, in place.

Using XPath Queries on MSDN:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/xmlsql/ac_mschema_3gab.asp?frame=true

--
Dave Sexton

Show quote

"Tommaso Caldarola" <ilbecch***@lcimitero.it> wrote in message
news:4543105f$1_2@x-privat.org...
> m***@mailinator.com wrote:
>> hey all,
>>
>> now id like to put it to practice. however....none of the articles i
>> found had database examples -- all were files. how do i insert a memory
>> stream into the database? (im currently usesing oracle, so this would
>> be into a table w/ a CLOB column).
>
> make sense to store binary data in database?
>
> The database is the right place in order to do data warehouse, reporting and
> so on not a container of all.
>

2 Nov 2006 8:20 PM

pirho

Talk about timely...

I'm actually trying to do this with SQL. I'm trying to store business
objects for auditing purposes, so that I don't have to mess around with the
format of various different types, I want to serialize and store the entire
object.

I'm getting a deserialize error from the following code:

    Private Shared Function DeHydrateAnObject(ByVal this As Object) As String
        Dim UE As New UnicodeEncoding
        Dim m As System.IO.MemoryStream = New System.IO.MemoryStream
        Dim b As
System.Runtime.Serialization.Formatters.Binary.BinaryFormatter = New
System.Runtime.Serialization.Formatters.Binary.BinaryFormatter
        b.Serialize(m, this)
        Return UE.GetString(m.ToArray)
    End Function

    Public Shared Function HydrateAnObject(ByVal this As String, ByVal
strType As String) As Object
        Dim UE As New UnicodeEncoding
        Dim m As System.IO.MemoryStream = New
System.IO.MemoryStream(UE.GetBytes(this))
        Dim b As
System.Runtime.Serialization.Formatters.Binary.BinaryFormatter = New
System.Runtime.Serialization.Formatters.Binary.BinaryFormatter
        'Dim thisType As Type = Type.GetType(strType)
        Return CType(b.Deserialize(m), Product)
    End Function

For now, it's hard coded as a "Product"

My question is, for SQL 2000, what database type should I use?
The field is NText right now, but I get the following on the deserialize

Binary stream does not contain a valid BinaryHeader, 0 possible causes,
invalid stream or object version change between serialization and
deserialization

Very new to the serialization stuff, so any ideas would be appreciated.

2 Nov 2006 8:40 PM

Dave Sexton

Hi,

You will want to use varbinary or image, depending on the size of your
objects. varbinary can go up to 8000 bytes, IIRC. The large character types
text and ntext are not binary, they are text, so unless you're using a
SoapFormatter or XmlSerializer they won't be of much use to you here.

If you could use the added flexibility of searching the object graph from
within the database itself using Sql Server's built-in xml support then using
the text column type and serializing the object with an XmlSerializer might be
a better choice for you.

Just out of curiosity, why did you choose the term "dehydrate" over
"serialize" and "hydrate" over "deserialize"?

IMO, your code will make more sense if you describe the methods for what they
are actually doing using framework idioms such as "serialize" and
"deserialize".

--
Dave Sexton

Show quote

"pirho" <pi***@discussions.microsoft.com> wrote in message
news:3962F0D4-E2E3-4684-B474-8295C4F8AF9C@microsoft.com...
> Talk about timely...
>
> I'm actually trying to do this with SQL. I'm trying to store business
> objects for auditing purposes, so that I don't have to mess around with the
> format of various different types, I want to serialize and store the entire
> object.
>
> I'm getting a deserialize error from the following code:
>
>    Private Shared Function DeHydrateAnObject(ByVal this As Object) As String
>        Dim UE As New UnicodeEncoding
>        Dim m As System.IO.MemoryStream = New System.IO.MemoryStream
>        Dim b As
> System.Runtime.Serialization.Formatters.Binary.BinaryFormatter = New
> System.Runtime.Serialization.Formatters.Binary.BinaryFormatter
>        b.Serialize(m, this)
>        Return UE.GetString(m.ToArray)
>    End Function
>
>    Public Shared Function HydrateAnObject(ByVal this As String, ByVal
> strType As String) As Object
>        Dim UE As New UnicodeEncoding
>        Dim m As System.IO.MemoryStream = New
> System.IO.MemoryStream(UE.GetBytes(this))
>        Dim b As
> System.Runtime.Serialization.Formatters.Binary.BinaryFormatter = New
> System.Runtime.Serialization.Formatters.Binary.BinaryFormatter
>        'Dim thisType As Type = Type.GetType(strType)
>        Return CType(b.Deserialize(m), Product)
>    End Function
>
>
> For now, it's hard coded as a "Product"
>
> My question is, for SQL 2000, what database type should I use?
> The field is NText right now, but I get the following on the deserialize
>
> Binary stream does not contain a valid BinaryHeader, 0 possible causes,
> invalid stream or object version change between serialization and
> deserialization
>
> Very new to the serialization stuff, so any ideas would be appreciated.

2 Nov 2006 9:12 PM

pirho

Dave,

The DAO code where I work is all stored in one huge class file.
I didn't want any confusion between my serialization code and the .NET
method(s) #1

and #2, I've been using nHibernate on other projects, so the Dehydrate thing
is a little bit burned into my retina at the moment :-)

Thanks for the answer so quick. Helps to know I'm on the right track.

Show quote

"Dave Sexton" wrote:

> Hi,
>
> You will want to use varbinary or image, depending on the size of your
> objects. varbinary can go up to 8000 bytes, IIRC. The large character types
> text and ntext are not binary, they are text, so unless you're using a
> SoapFormatter or XmlSerializer they won't be of much use to you here.
>
> If you could use the added flexibility of searching the object graph from
> within the database itself using Sql Server's built-in xml support then using
> the text column type and serializing the object with an XmlSerializer might be
> a better choice for you.
>
> Just out of curiosity, why did you choose the term "dehydrate" over
> "serialize" and "hydrate" over "deserialize"?
>
> IMO, your code will make more sense if you describe the methods for what they
> are actually doing using framework idioms such as "serialize" and
> "deserialize".
>
> --
> Dave Sexton
>
> "pirho" <pi***@discussions.microsoft.com> wrote in message
> news:3962F0D4-E2E3-4684-B474-8295C4F8AF9C@microsoft.com...
> > Talk about timely...
> >
> > I'm actually trying to do this with SQL. I'm trying to store business
> > objects for auditing purposes, so that I don't have to mess around with the
> > format of various different types, I want to serialize and store the entire
> > object.
> >
> > I'm getting a deserialize error from the following code:
> >
> >    Private Shared Function DeHydrateAnObject(ByVal this As Object) As String
> >        Dim UE As New UnicodeEncoding
> >        Dim m As System.IO.MemoryStream = New System.IO.MemoryStream
> >        Dim b As
> > System.Runtime.Serialization.Formatters.Binary.BinaryFormatter = New
> > System.Runtime.Serialization.Formatters.Binary.BinaryFormatter
> >        b.Serialize(m, this)
> >        Return UE.GetString(m.ToArray)
> >    End Function
> >
> >    Public Shared Function HydrateAnObject(ByVal this As String, ByVal
> > strType As String) As Object
> >        Dim UE As New UnicodeEncoding
> >        Dim m As System.IO.MemoryStream = New
> > System.IO.MemoryStream(UE.GetBytes(this))
> >        Dim b As
> > System.Runtime.Serialization.Formatters.Binary.BinaryFormatter = New
> > System.Runtime.Serialization.Formatters.Binary.BinaryFormatter
> >        'Dim thisType As Type = Type.GetType(strType)
> >        Return CType(b.Deserialize(m), Product)
> >    End Function
> >
> >
> > For now, it's hard coded as a "Product"
> >
> > My question is, for SQL 2000, what database type should I use?
> > The field is NText right now, but I get the following on the deserialize
> >
> > Binary stream does not contain a valid BinaryHeader, 0 possible causes,
> > invalid stream or object version change between serialization and
> > deserialization
> >
> > Very new to the serialization stuff, so any ideas would be appreciated.
>
>
>