Serializing an Object into a database Column

I'm trying to serialize a custom object into my database, Heres the code:

/// <summary>
/// Creates new user session
/// </summary>
/// <returns>Guid</returns>
public static Guid InsertNewUserSession(CustomPrincipal principal, string connString)
{
Guid token = Guid.NewGuid();
using (SqlConnection connection = new SqlConnection(connString))
{
MemoryStream memoryStream = new MemoryStream();
BinaryFormatter binaryFormatter = new BinaryFormatter();
binaryFormatter.Serialize(memoryStream, principal);
SqlCommand command = new SqlCommand("upInsertNewUserSession", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@Token", token);
byte[] buff = memoryStream.GetBuffer();
command.Parameters.AddWithValue("@User", buff);
command.Parameters.AddWithValue("@Length", buff.Length);

// make sure query succeeds, close connection either way
try
{
connection.Open();
command.ExecuteNonQuery();
}
finally
{
connection.Close();
}
}
return token;
}

The problem with this is when it gets to the database the object is just a bunch of empty bytes, 0x000000000 ... etc.

So what i'm wondering is if I'm doing anything wrong And if there is a better way to do this... Because currently I don't know how many bytes the object is going to be so I just have the database has binary(8000).

Thanks for any tips!

Answer this question

Serializing an Object into a database Column

ShikhaVaid
Hi sontek,

It occured to me only two mintues ago (as my machine was booting up) that without a catch in your Try block you weren't able to catch any exception. I was just going to test your code. Glad you got it resolved. A catch statement would surely have made it much easier. I didn't know it was optional and was surpirsed at its ommission yesterday, but I didn't immediately consider the potential for an exception to occur and for you to not know either that it occurred or what it was.

// make sure query succeeds, close connection either way
try
{
connection.Open();
command.ExecuteNonQuery();
}

catch (exception ex){

system.diagnostics.debuf.writeline(ex.tostring)}
finally
{
connection.Close();
}

Martin.
Robert Kozak

Have you examined the content of the buffer

It seems you need to close the memorystream before calling its GetBuffer() method.
Tigers21
When ommitting the catch block it'll just throw the exception
gdvl
I seem to remember the same problem (maybe), I am on a guest machine and can't test.

If its the same another option is to set the position in the stream to 0 prior to calling the get buffer method.

mystream.position = 0;
Northern Rob
And jumping in first this in the morning! The catch would presumably not have applied here!
kundalani
<quote>currently I don't know how many bytes the object is going to be so I just have the database has binary(8000).
</quote>

Was just doing something else and remebered your earlier post. I hope this may be of some help.

You can save a byte array to the db when setting the column type as Image. This will be more effective than encoding and decoding from Hex.

Depending on your custom ISecurityPrincipal members and some testing you should be able to better determine an appropriate size for the field.

VB Example provided for another post.

' providing a sample image as system.drawing.image

Dim tstImg As System.Drawing.Image

tstImg = System.Drawing.Image.FromFile(MapPath("\images\") & "farmH.jpg")

' regular

Dim cmd As New Data.SqlClient.SqlCommand

Dim con As New Data.SqlClient.SqlConnection

cmd.Connection = con

cmd.CommandText = "Insert Into TestImage(TestImage) values(@NewImage); Select @@IDENTITY"

con.ConnectionString = “Valid connection string”

Dim ImageParam As Data.SqlClient.SqlParameter = _

cmd.Parameters.Add("@NewImage", Data.SqlDbType.Image)

' ### Use stream to get byte array

Dim strm As New System.IO.MemoryStream

' using system.drawing.image

tstImg.Save(strm, System.Drawing.Imaging.ImageFormat.Jpeg)

Dim bytes() As Byte = strm.ToArray()

Dim length As Integer = bytes.Length

ImageParam.Value = bytes

Dim DBID As Integer

Try

con.Open()

DBID = cmd.ExecuteScalar

Catch ex As Exception

System.Diagnostics.Debug.WriteLine(ex.ToString)

End Try

' get the image from db by ItemId

Try

cmd.CommandText = "Select TestImage From TestImage Where (Id = " & DBID.ToString & " )"

Dim rdr As Data.SqlClient.SqlDataReader

rdr = cmd.ExecuteReader

If rdr.Read Then

bytes = rdr("TestImage")

End If

Catch ex As Exception

System.Diagnostics.Debug.WriteLine(ex.ToString)

con.Close()

con.Dispose()

cmd.Dispose()

End Try

Response.ContentType = "image/jpeg"

Me.Response.OutputStream.Write(bytes, 0, bytes.Length)
nwyork
The problem was saving it as binary, I solved the issue by converting it to a base64 string and saving it in the db as text
Serializing an Object into a database Column

Answer this question