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!
rhianwenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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 Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Editors IDEs

From novice to tech pro — start learning today.