Tuesday, July 28, 2009

Sending List of Complex objects to Sql server 2005 and persisting it in the database

There are situations when you have to do multiple inserts on a single event there are multiple ways to handle this situation.The simplest and which most of the developers do and make a blunder is making individual calls to the database.

One common example can be a grid which have facility of multiple Insert in such a situation on a single add button click you have to make hundreds of insert or even thousands,there are more genuine scenarios such as, suppose you are making a Silverlight CMS system and you have to save the state of the page containing various sections such as Header,MenuBar,MainContent and Footer which have all the things customizable, in such a scenario you have to save hundreds of settings on a single save button click and you again have to show the user his settings when he again loads the website.

For such situations involving hundreds or thousands of db inserts and updates making individual calls over the network is not at all good,instead i will say that its the worst approach.

There are other approaches,most common of them is sending a list of comma separated values.Ideally this can be said to be a workaround if you are sending just an array or a list of one or two field objects.But for complex objects its the worst solution which i have ever seen and this solution very well persist over the internet.

But its has various disadvantages first one is that there is complex string manipulation associated with this approach,you will be sending something like this “5,6,9,8,45,78,5,6” and then splitting this string on the comma’s(,).Another disadvantage is that this approach is sql injection prone and above all string manipulation is never considered a good design paradigm.

So here in this article i will discuss about the way which i think is the best way to persist changes to the database in such a situation.

We will be making use of xml support of the sql server 2005 and xml serialization of objects in C# to achieve our goal.

Example Description:-This example is an application in Silverlight 2.0 with WCF,Linq,

[Serializable]
    [DataContract]
    public class WebsiteContainer
    {
        [DataMember]
        public int WebsiteContainerID { get; set; }

        [DataMember]
        public int UserID { get; set; }
        [DataMember]
        public double LogoHeight { get; set; }
        [DataMember]
        public double LogoWidth { get; set; }
        [DataMember]
        public double LogoTop { get; set; }
        [DataMember]
        public double LogoLeft { get; set; }
        [DataMember]
        public string LogoFileName { get; set; }
        [DataMember]
        public string BgColor { get; set; }
        [DataMember]
        public string Text { get; set; }
        [DataMember]
        public double TextFontSize { get; set; }
        [DataMember]
        public string TextFontFamily { get; set; }
        [DataMember]
        public double TextLeft { get; set; }
        [DataMember]
        public double TextTop { get; set; }
        [DataMember]
        public string TextColor { get; set; }
        [DataMember]
        public double TextScale { get; set; }
        [DataMember]
        public double Padding { get; set; }
        [DataMember]
        public int ContainerTypeID { get; set; }
        [DataMember]
        public int WebsiteID { get; set; }
        [DataMember]
        public double ContainerHeight { get; set; }
        [DataMember]
        public double ContainerWidth { get; set; }

    }

 Its important to note that you have to place a serializable attribute on top of the class declaration so that this class’s object can be serialized to XML while sending it to Sql server.

Now the second step is to write the C# code to serialize this object to XML to transmit over the network.

  public static string SerializeObject<T>(List<T> objects)
        {
            StringBuilder sb = new StringBuilder();
            foreach (var obj in objects)
            {
                try
                {
                    XmlWriterSettings writerSettings = new XmlWriterSettings();
                    writerSettings.OmitXmlDeclaration = true;
                    writerSettings.Indent = true;
                    
                    using (XmlWriter xmlWriter = XmlWriter.Create(sb, writerSettings))
                    {
                        XmlSerializer xs = new XmlSerializer(typeof(T));
                        XmlSerializerNamespaces ns = new XmlSerializerNamespaces();
                        ns.Add(String.Empty, String.Empty);
                        xs.Serialize(xmlWriter, obj, ns);
                    }
                }
                catch
                {
                    return string.Empty;
                }
            }
            return sb.ToString();
        }

Above function will take a list of type T and then iterate through each object inside the list and serialize the object and append the serialized object string to a stringbuilder ‘sb’

Here i would like to mention the real thing which you might miss is that , by default the writer settings are set to bring namespaces and xml dtd information which will result in error saying “XML parsing: line 1, character 841, text/xmldecl not at the beginning of input

To rectify this error you have to add the writerSetting.OmitXmlDeclaration = true and also add empty namespaces using the XmlSerializerNamespaces this will give you a clean xml which can be parsed by the sql server 2005

Finally we return the string builder object after converting it to string.

After this step we have a serialized string of the whole list of objects as shown in given figure

Now the next step is to get this xml serialized object list into the stored proc.Given below is the code on how can you retrieve this list in sql server 2005

Create PROCEDURE [dbo].[SaveWebsite]
@WebsiteContainerXML XML

AS
BEGIN
DECLARE @WebsiteID INT

	SET NOCOUNT ON;
	
SET @WebsiteID = (SELECT TOP 1 WebsiteContainerValues.ID.value('.','INT') FROM @WebsiteContainerXML.nodes('/WebsiteContainer/WebsiteID') as WebsiteContainerValues(ID) )

		BEGIN TRANSACTION
		DELETE FROM dbo.WebsiteContainer WHERE WebsiteID = @WebsiteID	
		IF @@ERROR <> 0
 BEGIN
    ROLLBACK
    RAISERROR ('Error deleting records', 16, 1)
    RETURN
 END
		INSERT INTO dbo.WebsiteContainer (
			UserID,
			LogoHeight,
			LogoWidth,
			LogoTop,
			LogoLeft,
			LogoFileName,
			BgColor,
			[Text],
			TextFontSize,
			TextFontFamily,
			TextTop,
			TextLeft,
			TextColor,
			TextScale,
			Padding,
			ContainerTypeID,
			WebsiteID,
			ContainerHeight,
			ContainerWidth,
			IsRightAligned
			
		) 
		SELECT 
				WebsiteContainerTab.WebsiteContainerCol.value('UserID[1]','int') AS UserID,
                WebsiteContainerTab.WebsiteContainerCol.value('LogoHeight[1]','float') AS LogoHeight,  
                WebsiteContainerTab.WebsiteContainerCol.value('LogoWidth[1]','float') AS LogoWidth,  
                WebsiteContainerTab.WebsiteContainerCol.value('LogoTop[1]','float') AS LogoTop,  
                WebsiteContainerTab.WebsiteContainerCol.value('LogoLeft[1]','float') AS LogoLeft,  
                WebsiteContainerTab.WebsiteContainerCol.value('LogoFileName[1]','varchar(50)') AS LogoFileName,  
                WebsiteContainerTab.WebsiteContainerCol.value('BgColor[1]','varchar(50)') AS BgColor,  
                WebsiteContainerTab.WebsiteContainerCol.value('Text[1]','varchar(50)') AS TEXT,  
                WebsiteContainerTab.WebsiteContainerCol.value('TextFontSize[1]','float') AS TextFontSize,  
                WebsiteContainerTab.WebsiteContainerCol.value('TextFontFamily[1]','varchar(50)') AS TextFontFamily,  
                WebsiteContainerTab.WebsiteContainerCol.value('TextTop[1]','float') AS TextTop,  
                WebsiteContainerTab.WebsiteContainerCol.value('TextLeft[1]','float') AS TextLeft,  
				WebsiteContainerTab.WebsiteContainerCol.value('TextColor[1]','varchar(50)') AS TextColor,  
				WebsiteContainerTab.WebsiteContainerCol.value('TextScale[1]','float') AS TextScale,  
				WebsiteContainerTab.WebsiteContainerCol.value('Padding[1]','float') AS Padding,
				WebsiteContainerTab.WebsiteContainerCol.value('ContainerTypeID[1]','int') AS ContainerTypeID,  
				WebsiteContainerTab.WebsiteContainerCol.value('WebsiteID[1]','int') AS WebsiteID,  
				WebsiteContainerTab.WebsiteContainerCol.value('ContainerHeight[1]','float') AS ContainerHeight,  
				WebsiteContainerTab.WebsiteContainerCol.value('ContainerWidth[1]','float') AS ContainerWidth,
				WebsiteContainerTab.WebsiteContainerCol.value('IsRightAligned[1]','bit') AS IsRightAligned    
          FROM @WebsiteContainerXML.nodes('//WebsiteContainer') AS WebsiteContainerTab(WebsiteContainerCol) 
		

IF @@ERROR <> 0
 BEGIN

    ROLLBACK


    RAISERROR ('Error Inserting in WebContainers', 16, 1)
    RETURN
 END
 COMMIT 

END

You can refer the syntax to retrieve and iterate through the serialized list and then inserting into the database.

Important to make a note that you don’t have to manually iterate through to get the count and then again run some loop inside the stored proc for storing the above syntax will automatically iterate through all the nodes and insert each record.

Happy Programming!!!!!!!!!!!!!!!!!!

1 comments :

Anonymous said...

why not bulkinsert?

Post a Comment