Binary Serialization -- how to actually insert into database?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

matt@mailinator.com - 26 Oct 2006 17:54 GMT

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

Reply to this Message

Patrick Steele - 26 Oct 2006 18:57 GMT

> hey all,
>
[quoted text clipped - 16 lines]
>
> ...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.

Signature

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

Reply to this Message

matt@mailinator.com - 26 Oct 2006 19:28 GMT

> 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

Reply to this Message

matt@mailinator.com - 26 Oct 2006 20:41 GMT

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

Reply to this Message

Dave Sexton - 26 Oct 2006 23:42 GMT

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.

Signature

Dave Sexton

>> Use the MemoryStream.ToArray() method to get a byte[]. You should be
>> able to stick that in your CLOB column.
[quoted text clipped - 10 lines]
> thanks,
> matt

Reply to this Message

Tommaso Caldarola - 28 Oct 2006 09:10 GMT

> 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.

Reply to this Message

Dave Sexton - 28 Oct 2006 17:24 GMT

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/con
figaspnet_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_msche
ma_3gab.asp?frame=true

Signature

Dave Sexton

>> hey all,
>>
[quoted text clipped - 7 lines]
> The database is the right place in order to do data warehouse, reporting and
> so on not a container of all.

Reply to this Message

pirho - 02 Nov 2006 21:20 GMT

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.

Reply to this Message

Dave Sexton - 02 Nov 2006 21:40 GMT

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".

Signature

Dave Sexton

> Talk about timely...
>
[quoted text clipped - 37 lines]
>
> Very new to the serialization stuff, so any ideas would be appreciated.

Reply to this Message

pirho - 02 Nov 2006 22:12 GMT

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.

> Hi,
>
[quoted text clipped - 56 lines]
> >
> > Very new to the serialization stuff, so any ideas would be appreciated.

Reply to this Message

Binary Serialization -- how to actually insert into database?

Free Magazines