Storing Session In SQL Server

I have some C# code that iterates through the session state, serializes each object and stores the binary representation in an SQL table with an 'image' column. The problem is: it doesn't work. SQL server doesn't throw an error (at least ADO.NET doesnt propagate it); the table is just left unchanged. The SP works (I tested it with a few simple values); the MemoryStream and byte array are being populated correctly and bound to the parameter correctly.





What am I doing wrong? Anyone have a better approach? I know there is a builtin way of storing state in an SQL server, but I only need to do this once--namely, when a user is redirected from non-secure to secure pages--so I don't want to take that performance hit,








string uid = _session.SessionID;


object toSerialize;


BinaryFormatter formatter = new BinaryFormatter();


MemoryStream stream;





SqlConnection dbConn = SupportClasses.SqlUtilities.DBConn();


SqlCommand cmd;


SqlTransaction storeSession = null;


SqlParameter uidParam, keyParam, objParam;


try


{


dbConn.Open();


storeSession = dbConn.BeginTransaction();


foreach (string key in _session.Keys)


{


toSerialize = _session[ key ];


stream = new MemoryStream();


formatter.Serialize(stream, toSerialize);





cmd = new SqlCommand("store_session_object", dbConn, storeSession);


cmd.CommandType = CommandType.StoredProcedure;


uidParam = new SqlParameter("@uid", SqlDbType.VarChar);


uidParam.Value = uid;


cmd.Parameters.Add(uidParam);


keyParam = new SqlParameter("@object_key", SqlDbType.VarChar);


keyParam.Value = key;


cmd.Parameters.Add(keyParam);


objParam = new SqlParameter("@data", SqlDbType.Image);


objParam.Value = stream.ToArray();


cmd.Parameters.Add(objParam);





cmd.ExecuteNonQuery();


}


}


catch (SqlException ex)


{


SupportClasses.ErrorHandler.HandleSQLError(ex);


storeSession.Rollback();


}


catch (System.Runtime.Serialization.SerializationException exs)


{


// do something


}


finally


{


dbConn.Close();


}








For those interested, here is the code for the SP:





CREATE PROCEDURE store_session_object


@uid varchar(50),


@object_key varchar(25),


@data image


AS


INSERT INTO session_hold (uid, object_key, object)


VALUES (@uid, @object_key, @data)








[Cross-posted because issue spans several topics.]