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

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 = "[email protected]", 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>[email protected]</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

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]