Serialize an object into SQL Server using C#

Question

I have the following code which writes to a SQL Server database:

private void InsertResult(Result results, string messageId)
{
   object chkresult = (results);

   MemoryStream memStream = new MemoryStream();
   StreamWriter sw = new StreamWriter(memStream);

   sw.Write(chkresult);

   SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["Reporting"].ConnectionString);

   using (conn)
   {
      using (SqlCommand cmd = new SqlCommand())
      {
         conn.Open();

         cmd.CommandText = "Results_Ins";
         cmd.CommandType = CommandType.StoredProcedure;
         cmd.Parameters.Add("@Id", SqlDbType.VarChar, 50).Value = messageId;
         cmd.Parameters.Add("@result", SqlDbType.VarBinary, Int32.MaxValue);
         cmd.Parameters["@result"].Value = memStream.GetBuffer();
         cmd.Parameters.Add("@RowCount", SqlDbType.Int).Direction = ParameterDirection.Output;

         cmd.Connection = conn;

         try
         {
             cmd.ExecuteNonQuery();
         }
         catch (Exception err)
         {
             // handle the error
             //messageInsert = false;
         }
         finally
         { 
             conn.Close(); 
         }
      }
   }

   //return rowCount;
}

When I execute this code, I get 0x being stored in the Result column. I'm not sure this is correct, and therefore I need some help.

Basically, I need to store a complete object into a single column in a SQL Server database table.

Damien_The_Unbeliever · Accepted Answer · 2013-07-04 14:06:01Z

1

You either need to call Flush or cause sw to be Closed or Disposed in order to ensure that it's finished writing to the memStream.

Also, instead of GetBuffer, you should use ToArray. GetBuffer returns an internal buffer which may be larger than the amount of bytes that have actually been written.

You should also change this:

catch (Exception err)
{
   // handle the error
   //messageInsert = false;
}

to only catch specific exceptions for which you have a strategy to deal with them.

And, even now, I don't think it's going to do what you wanted (which you haven't really spelled out). All that your sw.Write call does is:

Writes the text representation of an object to the text string or stream by calling the ToString method on that object.

If you want actual serialization to occur, you're going to need to write code that causes it to happen - after picking whether you want to perform binary or xml serialization.

edited Jul 4, 2013 at 14:06

answered Jul 4, 2013 at 13:53

Damien_The_Unbeliever

241k28 gold badges358 silver badges470 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

CSharpNewBee Over a year ago

Hi Damien, so making the suggestive changes has now stored ServiceReference1.Result in th field. So, the object (which was from a webservice) appears to be saved now. have you any examples on how to convert this back?

Damien_The_Unbeliever Over a year ago

@CSharpNewBee - note my most recent addition at the bottom - all that you're currently storing is the result of calling ToString() on results - which by default is just the type name. You need to decide what type of serialization you actually want to perform (I've linked to the two standard .NET ones) and then write the code to do that.

CSharpNewBee Over a year ago

I see, our messages passed each other :-) So, i am only storing the name to the object. perhaps, i need to do some more reading, as i thought this method would write the object.

Collectives™ on Stack Overflow

Serialize an object into SQL Server using C#

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related