Solved

How do i get "binary object" from database and then "deserialise" to its original form

Posted on 2008-06-17
5
234 Views
Last Modified: 2012-05-05
I have an application that passes an 'object' to various functions to collate data from various sources. Once all the data has been collected I'd like to store object on an SQL database..The Object is "serializable" and so thought about converting to a bytearray and storing as binary data...

But I'm struggling to get the data out of the database and convert it back into the its orignal form..
OR
I may not be send the data to the database correctly


Private Sub SetMessage

        'set up Message object and fill with some values

        Dim MSG As New RobotMessage

        Dim JOBNO As String = "ABC12345"

        MSG.Job.JobNumber = JOBNO

        MSG.RobotID = 987

 

 

        Dim mStream As New MemoryStream

        Dim formatter As New BinaryFormatter

        Dim DBBytes() As Byte

        Try

            formatter.Serialize(MStream, MSG)

 

            DBBytes = mStream.ToArray

 

        Catch ex As SerializationException

            msgbox "Failed to serialize. Reason: " & ex.Message

            Exit Sub

        Finally

 

        End Try

        Dim DBCon As SqlClient.SqlConnection = GetDBCon()

        Dim iCmd As New SqlClient.SqlCommand("ADDMessage", DBCon)

        iCmd.CommandType = CommandType.StoredProcedure

        Dim inP1 As New SqlClient.SqlParameter

        Dim inP2 As New SqlClient.SqlParameter

 

        inP1.ParameterName = "@JobNo"

        inP1.Value = JOBNO

        iCmd.Parameters.Add(inP1)

        inP2.ParameterName = "@JDATA"

        inP2.Value = DBBytes

        iCmd.Parameters.Add(inP2)

        Try

            DBCon.Open()

            iCmd.ExecuteNonQuery()

            DBCon.Close()

        Catch ex As Exception

            MsgBox(ex.Message)

        End Try

 

    End Sub

 

Private Sub GetMessage

        Dim DBCON As SqlClient.SqlConnection = GetDBCon()

        Dim DA As New SqlClient.SqlDataAdapter("SELECT Jobdata FROM [JOBRECORD] WHERE ID  = 9", DBCON)

        Dim JD As New DataSet

        Try

            DBCON.Open()

            DA.Fill(JD)

            DBCON.Close()

 

        Catch ex As Exception

 

        End Try

 

        Dim DBBytes() As Byte = JD.Tables(0).Rows(0).Item(0)

        Dim MS As New MemoryStream(DBBytes)

 

        Dim RM As RobotMessage

        Try

            Dim formatter As New BinaryFormatter

             ' Deserialize the message       

            'MS.Position = 0

            MS.Seek(0, SeekOrigin.Begin)

            'formatter.Deserialize(MS)

            RM = DirectCast(formatter.Deserialize(MS), RobotMessage)

        Catch ex As SerializationException

            MsgBox(ex.Message)

            exit sub

        Finally

 

        End Try

        ' display the object in ProgpertyGrid

       pgJob.SelectedObject = RM

    End Sub

 

 

*****************************************************

SQL StoredProcedure used to add data

USE [JOBHIST]

GO

/****** Object:  StoredProcedure [dbo].[ADDMessage]    Script Date: 06/17/2008 10:33:25 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

/*

Created: 05/06/08

Author: Ian Smith

History:

*/

 

CREATE PROCEDURE [dbo].[ADDMessage] 

	@JOBNo	char(10),

	@JDATA binary

AS

SET NOCOUNT ON

Declare	@tamid int

 

INSERT INTO JOBRECORD(JOBNO,ACTIONDATE,JOBDATA) VALUES( @JobNo ,getdate(), @JDATA);

SET NOCOUNT OFF

Open in new window

0
Comment
Question by:Smudger5201
  • 3
  • 2
5 Comments
 
LVL 25

Expert Comment

by:apeter
ID: 21802527
If you serialize and deserialize the  immediately, are you able to get the object ? Then we can find out , whether serialization or database is causing problem.
0
 

Author Comment

by:Smudger5201
ID: 21802605
apeter:

The the serialization works without going to the database...i.e when the same memory stream object is used for serialize and de-serialize...all is well..

The problem seems to be getting the Object out of the Memory stream , into the datbase (via byte array) and then out again...
0
 
LVL 25

Accepted Solution

by:
apeter earned 500 total points
ID: 21806283
Looks like we have to read the bytes from Dataset, "JD" a lilttel different. Below is the C# code kindly translate to VB.NET. Please use SqlDataReader instead of Dataset since it light weight. First get the length and then get then read the stream.

int contentLength = Convert.ToInt32(reader.GetBytes(0, 0, null, 0, int.MaxValue));
    byte[] buffer = new byte[contentLength];
    reader.GetBytes(0, 0, buffer, 0, contentLength);
0
 

Author Closing Comment

by:Smudger5201
ID: 31468292
apeter

Cracked it....although I need to change the seerialise and deserialize routines..The SQL SP was not storing the binary data correctly so I had to re-write...then using you retrieval method I can no get the binary data and then deserialize to its original form

Many Thanks
0
 

Author Comment

by:Smudger5201
ID: 21811707
Heres the completed code for both serialize and deserialize

    Private Sub SerializeMsg()

        Dim MSG As New TAMmessage.RobotMessage

        Dim JOBNO As String = "ABC123/02"

        MSG.Job.JobNumber = JOBNO

        MSG.RobotID = 987

        Dim mStream As New MemoryStream

        Dim formatter As New BinaryFormatter

        Dim DBBytes As Byte()

        Try

            formatter.Serialize(mStream, MSG)

            DBBytes = mStream.ToArray

        Catch ex As SerializationException

            Console.WriteLine("Failed to serialize. Reason: " & ex.Message)

            Exit Sub

        Finally
 

        End Try

        Dim DBCon As SqlClient.SqlConnection = GetDBCon()
 

        Dim iCmd As New SqlClient.SqlCommand("Insert into JOBRECORD(JOBNO,ACTIONDATE,JOBDATA) Values(@JOBNO,@DATE,@JDATA)", DBCon)
 

        Dim inP1 As SqlClient.SqlParameter = iCmd.Parameters.Add("@JOBNO", SqlDbType.Text)

        inP1.Value = JOBNO.ToString
 

        Dim inp2 As SqlClient.SqlParameter = iCmd.Parameters.Add("@DATE", SqlDbType.DateTime)

        inp2.Value = Now
 

        Dim inP3 As SqlClient.SqlParameter = iCmd.Parameters.Add("@JDATA", SqlDbType.Binary)

        inP3.Value = DBBytes

        inP3.Size = DBBytes.Length
 

        Try

            DBCon.Open()

            Dim res As Integer = iCmd.ExecuteNonQuery

            DBCon.Close()

        Catch ex As Exception

            MsgBox(ex.Message)

            Exit Sub

        End Try
 

    End Sub
 
 

    Private Sub DeserializeMsg()
 
 

        Dim DBCON As SqlClient.SqlConnection = GetDBCon()

        Dim sCmd As New SqlClient.SqlCommand("SELECT Jobdata FROM [JOBRECORD] WHERE ID  = 24", DBCON)

        Dim reader As SqlClient.SqlDataReader

        Dim DBBytes As Byte()
 

        Try

            DBCON.Open()

            reader = sCmd.ExecuteReader

            While reader.Read

                Dim contentLength As Integer = Convert.ToInt32(reader.GetBytes(0, 0, Nothing, 0, Integer.MaxValue))

                DBBytes = New Byte(contentLength - 1) {}

                reader.GetBytes(0, 0, DBBytes, 0, contentLength)

            End While
 

        Catch ex As Exception
 

        Finally

            reader.Close()

            DBCON.Close()

        End Try
 
 

        Dim MS As New MemoryStream(DBBytes, True)

        Dim RM As RobotMessage
 

        Try

            Dim formatter As New BinaryFormatter

            ' Deserialize 

            RM = DirectCast(formatter.Deserialize(MS), TAMmessage.RobotMessage)

        Catch ex As SerializationException

            MsgBox(ex.Message)

            Exit Sub

        Finally
 

        End Try

        ' display the object in ProgpertyGrid

        pgjob.SelectedObject = RM.Job

    End Sub

Open in new window

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now