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 C# Class Object into Sql Server
- Retrieve C# Class Object from Sql Server
- XQuery to filter C# Objects by its properties
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]