Sunday, 4 May 2014

How to Store and Read C# Class Object in Sql Server

Description:

There is no build-in sql datatype to store C# class object. But we can save C# class object either by Xml or Binary column. We can't have more control with C# class properties when we store in binary column. So the better way is Xml column. We can use XQuery in Sql Server table to query by class object's properties. In this article I am going write about how to Store/Save/Insert and Retrieve/Read C# class object into Sql database table and how to write XQuery with Sql query to work with C# class object properties.

Summary:


Store/Insert C# Class Object into Sql Server Table as XMl value

You can store/insert/save C# class object into Sql Server Table by using below C# functions. Here we are using XmlSerializer to convert C# class object into XML string.
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Xml.Serialization;

namespace SQLSamples
{
    public class UserDetail
    {
        public string UserName { get; set; }
        public string MailID { get; set; }
        public string City { get; set; }
    }

    class Program
    {
        static void Main(string[] args)
        {
            UserDetail userDetail = new UserDetail { UserName = "Morgan", 
               MailID = "Morgan@Domain.Com", City = "London" };

            InsertClassObjectIntoSQLTable(1, userDetail);
        }

        static void InsertClassObjectIntoSQLTable(int userID,UserDetail userDetail)
        {
            using (SqlConnection sqlconnection = new SqlConnection(@"Data Source=.\SQLExpress; 
Initial Catalog=MorganDB; Integrated Security=SSPI;"))
            {
                sqlconnection.Open();

                // create table if not exists 
                string createTableQuery = @"Create Table [UserTable] (ID int, [UserObject] xml)";
                SqlCommand command = new SqlCommand(createTableQuery, sqlconnection);
                command.ExecuteNonQuery();

                // Convert C# class object into xml string 
                string xmlData = ConvertObjectToXMLString(userDetail);

                string insertQuery = @"Insert Into [UserTable] (ID,[UserObject])
                                                 Values(1,@UserObject)";

                // Insert XMl Value into Sql Table by SqlParameter
                SqlCommand insertCommand = new SqlCommand(insertQuery, sqlconnection);
                SqlParameter sqlParam =insertCommand.Parameters.AddWithValue("@UserObject",xmlData);
                sqlParam.DbType = DbType.Xml;
                insertCommand.ExecuteNonQuery();
            }
        }

        static string ConvertObjectToXMLString(object classObject)
        {
            string xmlString = null;
            XmlSerializer xmlSerializer = new XmlSerializer(classObject.GetType());
            using (MemoryStream memoryStream = new MemoryStream())
            {
                xmlSerializer.Serialize(memoryStream, classObject);
                memoryStream.Position = 0;
                xmlString = new StreamReader(memoryStream).ReadToEnd();
            }
            return xmlString;
        }
    }
}

Retrieve/Read C# Class Object from Sql Server Table

You can retrieve/read C# class object from Sql Server Table by using below C# functions. Here we are reading Xml value from Sql server and converting XML string into C# class object using XmlSerializer.
static void Main(string[] args)
{
    UserDetail userDetail = ReadClassObjectFromSqlServer(1);
    Console.WriteLine(userDetail.UserName);
}

public static UserDetail ReadClassObjectFromSqlServer(int userID)
{
    UserDetail userDetail = null;
    using (SqlConnection sqlconnection = new SqlConnection(@"Data Source=.\SQLExpress; 
Initial Catalog=MorganDB; Integrated Security=SSPI;"))
    {
        sqlconnection.Open();

        string selectQuery = string.Format(@"Select [UserObject] From [UserTable] Where ID={0}"
                            , userID);

        // Read Xml Value from Sql Table 
        SqlCommand selectCommand = new SqlCommand(selectQuery, sqlconnection);
        SqlDataReader reader = selectCommand.ExecuteReader();
        if (reader.Read())
        {
            string xmlValue = reader[0].ToString();
            userDetail = (UserDetail)ConvertXmlStringtoObject<UserDetail>(xmlValue);
        }
    }

    return userDetail;
}

static T ConvertXmlStringtoObject<T>(string xmlString)
{
    T classObject;

    XmlSerializer xmlSerializer = new XmlSerializer(typeof(T));
    using (StringReader stringReader = new StringReader(xmlString))
    {
        classObject = (T)xmlSerializer.Deserialize(stringReader);
    }
    return classObject;
}

XQuery to filter C# Objects by its properties

In some of the times, we might have a need to filter c# class objects by its properties from sql server itself. To achieve this we can use XQuery with Sql query to query Xml nodes.

Select all UserDetail class objects
Use [MorganDB]
Select * From [UserTable]
UserDetail class object Xml structure:
<UserDetail>
  <UserName>Morgan</UserName>
  <MailID>Morgan@Domain.Com</MailID>
  <City>London</City>
</UserDetail>
XQuery to filter by UserDetail's Property City:
Use [MorganDB]
Select * From [UserTable] Where UserObject.exist('/UserDetail[City="London"]')=1

How to Store and Retrieve C# Class Object into Sql Server table

XQuery to select only UserDetail's Property values:
Use [MorganDB]
Select ID,
   UserObject.value('(/UserDetail/UserName)[1]','nvarchar(250)') as UserName,
   UserObject.value('(/UserDetail/MailID)[1]','nvarchar(250)') as MailID,
   UserObject.value('(/UserDetail/City)[1]','nvarchar(250)') as City
 From [UserTable] 

How to Retrieve/Read C# Class Object from Sql Server table


Advertisements
Advertisements

2 comments:

  1. hey...how can i store this xml data in to mysql

    ReplyDelete
  2. I am looking for way to store a sequence of items in a single column.

    Relational databases are designed specifically to store one value per row / column combination. In order to store more than one value, but I don't like to serialize a list into a single value for storage, then deserialize it upon retrieval.

    Please let me know any approach available to store a sequence of items in a single column.

    Even i dont like to create another table to store that list, but this is exactly what relational databases.

    I am looking for something like this

    https://mariadb.com/kb/en/sql-99/collection-data-types/

    Thanks in advance

    RM

    ReplyDelete