Posted by: Cirilo Meggiolaro | 02/7/2009

Tip of the day #116 – Storing classes instances to the database

Store serialized versions of object instances to the database or persist the instance in some other way is common in workflow applications.

Let’s check today how to serialize and persist a class instance to a SQL Server table and how to retrieve the instance from the database and deserialize the object.

How to…

We are going to use the following class as our main object:

[Serializable]
public class Customer
{
    public Guid ID { get; set; }
    public string Name { get; set; }
    public DateTime DOB { get; set; }
    public bool IsActive { get; set; }
}

You may have noticed the Serializable attribute added to that class. It marks the class as “allowed” for serialization.

To store the serialized version of the customer class I created a simple table with an integer column named ID that stores a sequential instance id and an image data type column that stores the instance itself.

Picture 1 - Customer instance table

Picture 1 - Customer instance table

Steps to persist the instance in the database

  1. Add the Serializable attribute to the class you want to serialize;
  2. Serialize the class using a BinaryFormatter (available under the long named System.Runtime.Serialization.Formatters.Binary namespace) object and store the array of bytes into a MemoryStream object (available under the System.IO namespace);
  3. Save the array of bytes to a Image data type column in the database using a regular SQL statement;

Code snippet

Once you have added the Serializable attribute to the class, your next task is to serialize the customer object. Take a look on the Serialize method:

private byte[] Serialize(Customer cust)
{
    /// Checks if the customer object is a valid instance.
    if (cust == null)
        throw new ArgumentNullException(“Customer cannot be null.”);

    /// Declares and instantiates a stream object
    /// that holds the serialized object.

    MemoryStream stream = new MemoryStream();

    /// Declares and instantiates a binary formatter
    /// that serializes the customer object instance.
    BinaryFormatter binFormatter = new BinaryFormatter();

    try
    {
        /// Serializes the customer object storing it
        /// in the stream object.
        binFormatter.Serialize(stream, cust);

        /// Resets the stream position.
        stream.Seek(0, 0);

        /// Returns the array of bytes.
        return stream.ToArray();
    }
    finally
    {
        /// Releases resources
        if (stream != null)
        {
            stream.Close();
            stream.Dispose();
            stream = null;
        }

        binFormatter = null;
    }
}

There aren’t secrets for this method. Using a BinaryFormatter and a MemoryStream object, the customer instance is easily “transformed” to an array of bytes. Once we have the array of bytes we may save it to the image column I described previously:

private void SaveCustomerInstance(byte[] customerInstance)
{
    /// Checks if the array of bytes is not null or empty
    if ((customerInstance == null) || ((customerInstance != null) && (customerInstance.Length == 0)))
        throw new ArgumentNullException(“CustomerInstance cannot be null or empty.”);

    /// Creates the SQL statement
    string sql = “INSERT INTO CUSTOMER_INSTANCE (id, instance) VALUES (@ID, @Instance)”;

    /// Creates SqlParameter collection.
    SqlParameter[] parms = new SqlParameter[2];

    /// Adds SqlParameter to the collection.
    parms[0] = new SqlParameter(“@ID”, SqlDbType.Int);
    parms[0].Value = 1;

    parms[1] = new SqlParameter(“@Instance”, SqlDbType.Image);
    parms[1].Value = customerInstance;

    /// Declares and instantiates a SqlConnection object.
    
using (SqlConnection conn = new SqlConnection(@”Data Source=INSTANCENAME;Initial Catalog=DBName;Integrated Security=true;”))
    {
        /// Declares and instantiates a SqlCommand object.
        
using (SqlCommand cmd = new SqlCommand(sql, conn))
        {
            /// Adds Sql parameters.
            cmd.Parameters.AddRange(parms);

            /// Opens connection.
            conn.Open();

            /// Executes Sql statement.
            cmd.ExecuteNonQuery();

            /// Closes connection
            conn.Close();
        }
    }
}

It’s done! Our customer instance is stored in the database. Query the table to check the instance saved there. You may have something similar to the following:

Picture 1 - The customer instance persisted to the database.

Picture 2 - The customer instance persisted to the database.

Steps to retrieve the instance from the database

  1. Execute a regular SQL query to the database to retrieve the array of bytes stored in the Image data type column;
  2. Write the array of bytes into a MemoryStream object;
  3. Deserialize the class using a BinaryFormatter object;
  4. Convert the deserialized object to a Customer object.

The following method queries the database to retrieve the array of bytes stored in the customer_instance table:

private byte[] GetCustomerInstance(int instanceID)
{
    /// Checks if the id of the instance requested
    /// is within the allowed range.

    if (instanceID <= 0)
        throw new ArgumentOutOfRangeException(“Instance ID must be greater than zero.”);

    byte[] returnObject = null;

    /// Creates the SQL statement.
    string sql = “SELECT instance from CUSTOMER_INSTANCE where id = @ID”;

    /// Creates SqlParameter collection.
    SqlParameter[] parms = new SqlParameter[1];

    /// Adds SqlParameter to the collection.
    parms[0] = new SqlParameter(“@ID”, SqlDbType.Int);
    parms[0].Value = instanceID;

    /// Declares and instantiates a SqlConnection object.
    using (SqlConnection conn = new SqlConnection(@”Data Source=INSTANCENAME;Initial Catalog=DBName;Integrated Security=true;”))
    {
        /// Declares and instantiates a SqlCommand object.
        using (SqlCommand cmd = new SqlCommand(sql, conn))
        {
            /// Adds Sql parameters.
            cmd.Parameters.AddRange(parms);

            /// Opens connection.
            conn.Open();

            /// Executes Sql statement.
            SqlDataReader reader = cmd.ExecuteReader();

            if ((reader != null) && (reader.HasRows))
            {
                reader.Read();

                /// Converts the image column to an array of bytes.
                returnObject = (byte[])reader[“instance”];
            }

            /// Closes connection.
            conn.Close();
        }
    }

    /// Returns the array of bytes.
    return returnObject;
}

Once we have the array of bytes, it is just a matter to deserialize using the BinaryFormatter and the MemoryStream object:

private Customer Deserialize(byte[] customerInstance)
{
    /// Checks if the array of bytes is not null or empty
    if ((customerInstance == null) || ((customerInstance != null) && (customerInstance.Length == 0)))
        throw new ArgumentNullException(“CustomerInstance cannot be null or empty.”);

    /// Creates the return object.
    Customer returnObject = null;

    /// Declares and instantiates a stream object
    /// that holds the serialized object.

    MemoryStream stream = new MemoryStream();

    /// Declares and instantiates a binary formatter
    /// that deserializes the customer object instance.

    BinaryFormatter binFormatter = new BinaryFormatter();

    try
    {
        /// Writes the array of bytes to the stream.
        stream.Write(customerInstance, 0, customerInstance.Length);

        /// Resets the stream position.
        stream.Seek(0, 0);

        /// Deserializes the stream, assigning it to the return object.
        returnObject = (Customer)binFormatter.Deserialize(stream);

        /// Returns the customer object.
        return returnObject;
    }
    finally
    {
        /// Releases resources
        if (stream != null)
        {
            stream.Close();
            stream.Dispose();
            stream = null;
        }

        binFormatter = null;
    }
}

Executing the code

The following code shows how to create the instance and persist to the database:

/// Creates a new customer object.
Customer cust = new Customer()
{
    ID = Guid.NewGuid(),
    Name = “George Smith”,
    DOB = new DateTime(1970, 05, 28),
    IsActive = true
};

/// Serializes the customer object.
byte[] customerInstance = Serialize(cust);

/// Save customer instance to the database.
SaveCustomerInstance(customerInstance);

To retrieve the instance and display the customer data we need to get the customer instance and deserialize. The following code snippet shows that:

/// Gets the serialized customer instance from the database.
byte[] customerInstance = GetCustomerInstance(1);

/// Deserializes the customer object.
Customer cust = Deserialize(customerInstance);

if (cust != null)
{
    /// Displays the customer information.
    Console.WriteLine(“Customer Id: {0}”, cust.ID);
    Console.WriteLine(“Customer Name: {0}”, cust.Name);
    Console.WriteLine(“Customer Date of Birth: {0}”, cust.DOB);
    Console.WriteLine(“Customer is active: {0}”, cust.IsActive);
}

Advertisements

Responses

  1. This thing is awesome, beautiful!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: