Solved

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

Posted on 2008-06-17
5
240 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

632 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