• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 740
  • Last Modified:

Problems with object deserialization from MSSQL

I have an object that I'm serializing and storing in an MSSQL database as a varbinary.

When I retieve the varbinary and try to deserialize it, I'm having problems. I believe its something to do with the length of the byte array as the length is different going into the database and coming out.

I've tried to force the length of the array in both input and output, but that gives me an "End of Stream encountered before parsing was completed." error.

Without that, sometimes the deserialization just decides to say that it can't convert a byte[] to the object, and sometimes it gives a different error altogether which I can't replicate at the moment because its stopped working again LOL.

Serialisation code:
        MemoryStream ms = new MemoryStream();
        BinaryFormatter b=new BinaryFormatter();
        b.Serialize(ms, this);

        SqlHelp.ExecuteSp("user_InsertSaveSearchObject", saveName,   UserSessionManager.UserSession.IsDating(), UserSessionManager.UserSession.MemberID, WhereClause, ms.GetBuffer());

ms.Close();

Deserialisation code:
            BinaryFormatter b = new BinaryFormatter();
            MemoryStream ms = new MemoryStream((Byte[])sr.dataSet.Tables[0].Rows[0]["searchObject"]);

            ms.Seek(0, 0);
            UserSessionManager.UserSession.theMemberSearch = new MemberSearch();
            UserSessionManager.UserSession.theMemberSearch = (MemberSearch)b.Deserialize(ms);
            ms.Close();

Please help!
0
rhianwen
Asked:
rhianwen
  • 6
  • 5
1 Solution
 
appariCommented:
try changing

SqlHelp.ExecuteSp("user_InsertSaveSearchObject", saveName,   UserSessionManager.UserSession.IsDating(), UserSessionManager.UserSession.MemberID, WhereClause, ms.GetBuffer());

to

SqlHelp.ExecuteSp("user_InsertSaveSearchObject", saveName,   UserSessionManager.UserSession.IsDating(), UserSessionManager.UserSession.MemberID, WhereClause, ms.ToArray());
0
 
rhianwenAuthor Commented:
Unfortunately with ms.ToArray() I'm still getting the "Unable to cast object of type 'System.Byte[]' to type 'MemberSearch'." error on deserialisation
0
 
appariCommented:
did you check if you have same byte array before and after saving to database? may be the sp saving data into your db or reading from the db has some problem?
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
appariCommented:
also try changing

ms.Seek(0, 0);

to
ms.Position = 0;
0
 
rhianwenAuthor Commented:
I know that the information in the byte arrays is the same when its being input as output - but the length is different. When it comes out, the byte array has a whole bunch of extra items all with value 0 on the end...
0
 
appariCommented:
while reading the data from the varbinary column try trimming it by adding rtrim(yourvarbinarycolumn) instead of yourvarbinarycolumn in your sql.
0
 
rhianwenAuthor Commented:
Ok I tried adding rtrim to the SP, and apparently you can't rtrim a varbinary - it just ends up as nothing! Using ms.Position doesn't help either, it still tells me that it can't cast from the Byte[]
0
 
rhianwenAuthor Commented:
Update:  it seems the length is not the only problem.

I got desperate and changed the code and DB to store the length of the incoming array so that I could work on getting an outcoming array of the same size to deserialize.

Code now looks like this:

    BinaryFormatter b = new BinaryFormatter();
            Byte[] secondArray = new Byte[Convert.ToInt32(sr.dataSet.Tables[0].Rows[0]["objectLength"])];
           
            Byte[] theArray = (Byte[])sr.dataSet.Tables[0].Rows[0]["searchObject"];

            System.Array.ConstrainedCopy(theArray, 0, secondArray, 0, Convert.ToInt32(sr.dataSet.Tables[0].Rows[0]["objectLength"]));
       
            MemoryStream ms = new MemoryStream(secondArray);
            ms.Flush();
            ms.Position = 0;

            UserSessionManager.UserSession.theMemberSearch = new MemberSearch();
            UserSessionManager.UserSession.theMemberSearch = (MemberSearch)b.Deserialize(ms);
            ms.Close();


But even though the array being deserialized is now the same length (and same content) as the original serialized array, I get a End of Stream encountered before parsing was completed error.

Also fyi, the array deserializes correctly if I do it immediately after the serialization, but not when I get it out of the database
0
 
appariCommented:
>>the array deserializes correctly if I do it immediately after the serialization
means either you are not saving the data properly in to the db, may be the data is truncated?
try to save the serialized data to a file stream save it , read it back from filestream and try to deserialize. if you can do it means data is not saved properly to the DB.
and can you show the user_InsertSaveSearchObject sp script? andalso SqlHelp.ExecuteSp code.
0
 
rhianwenAuthor Commented:
SP is
ALTER PROCEDURE [dbo].[user_InsertSaveSearchObject]
 @saveName nvarchar(50),
@profileType bit,
@ownerID bigint,
@searchContent text,
@searchObject varbinary(max),
@objectLength int

AS
BEGIN
   insert into savedSearch
      ( saveName,profileType, ownerID, dateModified, searchContent, searchObject, objectLength)
      values
      ( @saveName,@profileType, @ownerID, getdate(), @searchContent, @searchObject, @objectLength)
END




The SqlHelp.ExecuteSp code has been around since before my time and is quite big so bear with me...  (I've edited out the cases for all the other data types and just left the varbinary one to make it smaller)


 public static SqlResult ExecuteSp(string sp_name, params object[] parameters)
        {
            SqlHelp sqlhelp = new SqlHelp(sp_name);
            return sqlhelp.Call(parameters);
        }

        public SqlResult Call(params object[] parameters)
        {

            SqlResult result = new SqlResult();


            myConnection = new SqlConnection(GetConnectString());

            myCommand = new SqlCommand(this.ProcedureName, myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;

            SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand);

            try
            {
                myConnection.Open();

                GetProcedureParameter(parameters);

                myAdapter.Fill(result.dataSet, "Table");

                GetOutputValue(result);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                myAdapter.Dispose();
                myCommand.Dispose();
                myConnection.Close();
                myConnection.Dispose();
            }
            return result;
        }


        private void GetProcedureParameter(params object[] parameters)
        {
            DataTable dtSP = new DataTable();
            SqlCommand myCommand2 = new SqlCommand();

            if (HttpContext.Current.Cache["SQLHelp"] == null)
            {
                myCommand2.Connection = this.myConnection;
                //myCommand2.CommandText = "select * from INFORMATION_SCHEMA.PARAMETERS"
                //        + " where SPECIFIC_NAME='" +this.ProcedureName+ "' order by ORDINAL_POSITION";

                myCommand2.CommandText = "select * from INFORMATION_SCHEMA.PARAMETERS"
                        + " order by ORDINAL_POSITION";
                SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand2);
                myAdapter.Fill(dtSP);
                //set sp datatable cache expired 1 day
                HttpContext.Current.Cache.Insert("SQLHelp", dtSP, null, Cache.NoAbsoluteExpiration, new TimeSpan(1, 0, 0, 0), CacheItemPriority.Normal, null);
            }
            else
            {
                dtSP = (DataTable)HttpContext.Current.Cache["SQLHelp"];
            }


            DataRow[] rows = dtSP.Select("SPECIFIC_NAME='" + this.ProcedureName + "'");
            try
            {
                myParameter = new SqlParameter();
                myParameter.ParameterName = "@Value";
                myParameter.SqlDbType = SqlDbType.Int;
                myParameter.Direction = ParameterDirection.ReturnValue;

                myCommand.Parameters.Add(myParameter);
                int i = 0;
                foreach (DataRow row in rows)
                {
                    myParameter = new SqlParameter();

                    myParameter.ParameterName = row["PARAMETER_NAME"].ToString();
                    myParameter.Direction = row["PARAMETER_MODE"].ToString() == "IN" ? ParameterDirection.Input : ParameterDirection.Output;

                    if (parameters[i] == null)
                    {
                        if (myParameter.Direction == ParameterDirection.Input)
                        {
                            myParameter.Value = System.DBNull.Value;
                            continue;
                        }
                    }
                    switch (row["DATA_TYPE"].ToString())
                    {
                      case "varbinary":
                            try
                            {
                                if (myParameter.Direction == ParameterDirection.Input)
                                {
                                    MemoryStream ms = new MemoryStream();
                                    System.Runtime.Serialization.Formatters.Binary.BinaryFormatter b = new System.Runtime.Serialization.Formatters.Binary.BinaryFormatter();
                                    b.Serialize(ms, parameters[i]);

                                    Byte[] theObject = new Byte[ms.Length];
                                    theObject = ms.GetBuffer();

                                    myParameter.Value = theObject;
                                }
                            }
                            catch
                            {
                                myParameter.Value = "";
                            }
                            myParameter.SqlDbType = SqlDbType.VarBinary;
                            break;
                    }
                    i++;

                    myCommand.Parameters.Add(myParameter);
                }
            }
            catch (Exception e)
            {
                throw e;

            }
            finally
            {
                if (dtSP != null) dtSP.Dispose();
                if (myCommand2 != null) myCommand2.Dispose();
            }
        }
0
 
rhianwenAuthor Commented:
OH YAY!

I finally fixed it LOL

You made me realise that I was serializing the object TWICE (der!)

Thanks for all your help!
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now