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!!!!!!!!!!!!!!!!!!


Integrating Silverlight 2.0 with Sharepoint MOSS2007

Silverlight is a revolutionary technology and its much in demand these days,Sharepoint is an old player but very extensive and offer a lot of features which make it alluring for medium to large enterprise. Sharepoint already provide a lot of out-of the box features but as silverlight is catching up every now and then you will have a requirement from the client to integrate silverlight with sharepoint. So in this article i will tell you how to integrate silverlight with sharepoint.Although there can be various ways of doing so i m following the webparts approach as it seems simple to me there are other simple approaches in which we can add a contentweb part in which we can give the source of the iframe as an hosted xap on silverlight live streaming server’s but being a developer this approach seems to me a little creepy and also there are very few resources which clearly tell how to integrate silverlight 2.0 into Sharepoint or MOSS2007.

  1. Create a silverlight 2.0 application and copy the xap and project .dll file in a separate folder.
  2. Configure MIME Types on the sharepoint server to accept .xap,.xaml,.xbap file extensions.
  3. Change the web.config of the application or website where you want silverlight to be integrated.
  4. On the sharepoint server create a WebPart project.
  5. Copy the resources i.e xap and the project dll file in the directory specified in the web part project silverlightcontrol source
  6. Deploy the webpart project to the required site where you want silverlight to show up.
  7. Edit the page where you want to add the silverlight control and click add webpart and you are done.

 

  1. Create Silverlight 2.0 application
    • Create a silverlight 2.0 application and build that solution and copy and past its xap file and the project dll file into another folder whose content we will copy later into the wwwroot and website name of the sharepoint site where we want to deploy this silverlight application.
  2. Configure MIME types in IIS on the sharepoint server
    • If you don’t have mime types configured on your iis server on the machine where you have your sharepoint installed, then first step is to configure the mime types on the IIS.
    • For IIS 6 you can either right click on whole of the website section or on specific website where you want the mime types to be configured.After right clicking go to the http header section from there select the mime types.

    • After selecting mime types click on new if the silverlight mime types are not present.

    • Add all the three mime types shown in the above figure and then click ok.Now your IIS is configured to accept and render xaml, xap and xbap

3.Change the web.config file of the sharepoint website for which you want to integrate silverlight.

    • This is very important step because if you leave this step in between even if you do all other things correctly then also nothing will get rendered in the final webpart when you deploy the webpart and include it on your page.
    • So we have to follow a little hacky approach in order to get the configuration file correct.
    • So first step is open your visual studio and create a new asp.net 2.0 application

    • We are creating asp.net 2.0 application because sharepoint is using mostly asp.net 2.0 references and it’s not having any web.config enteries related to silverlight and ajax features of the 3.5 framework.
    • It is recommended to make a copy of the web.config so that if any error occurs you can restore the original version.
    • Now we should open the web.config of the sharepoint website in the same solution.web.config of the sharepoint website can be found at “c:inetpubwwwrootwssVirtualDirecotriesyourwebsitenameweb.config”
    • Open this web.config and copy and paste the content in the original asp.net 2.0 web application web.config.
    • Now right click on the web project and click properties and change the target framework to .net framework 3.5 and then save the web.config file for the sharepoint website.

    • Now you have to add the following two entries in your web.config, first one is in the <system.web><compilation><assemblies> section add the below line
<add assembly="System.Web.Silverlight, Version=2.0.5.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>
    • Next entry to be added is in the <system.web><pages><controls> section just add the line as given below
<add tagPrefix="asp" namespace="System.Web.UI.SilverlightControls" assembly="System.Web.Silverlight, Version=2.0.5.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>
    • After adding these two entries just save the web.config file and you are done with the web.config changes.

4.Create a webpart project on the sharepoint server

    • For creating a web part project in Visual Studio 2008 you need to install  “Windows SharePoint Services 3.0 Tools: Visual Studio 2008 Extensions, Version 1.2” which can be downloaded from here
    • After installing WSS3.0 tools Visual studio extensions you will have a new Sharepoint project type in C# section,from there choose webpart project and give your project some name.

    • Delete the default webpart folder which will be created and click on the add new item and then select webpart from the list.

    • Add two references to this project one is System.Web.Extensions and the other one is System.Web.Silverlight

 

    • In the yourwebpartname.cs file make changes so that it should look similar in code as given below
using System;
using System.Runtime.InteropServices;
using System.Web.UI;
using System.Web.UI.SilverlightControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Serialization;

using Microsoft.SharePoint;
using Microsoft.SharePoint.WebControls;
using Microsoft.SharePoint.WebPartPages;

namespace TangramWP
{
    [Guid("6b31932f-7245-4d78-912c-56033008e775")]
    public class TangramSilverlightWP : System.Web.UI.WebControls.WebParts.WebPart
    {
        private Silverlight silverlightControl = null;
        public TangramSilverlightWP()
        {
        }
        protected override void OnLoad(EventArgs e)
        {
            base.OnLoad(e);
            ScriptManager scriptManager = ScriptManager.GetCurrent(this.Page);
            if(scriptManager==null)
            {
                scriptManager = new ScriptManager();
                this.Controls.AddAt(0,scriptManager);
            }
        }
        protected override void CreateChildControls()
        {
            base.CreateChildControls();
            silverlightControl = new Silverlight();
            silverlightControl.ID = "TangramSilverlight";
            silverlightControl.Source = "/Bin/Tangram.xap";
            silverlightControl.Width = new Unit(500);
            silverlightControl.Height = new Unit(400);
            Controls.Add(silverlightControl);
        }
    }
}

5.Copy resources such as xap and the project dll inside the specified location inside the sharepoint website.

    • Now in the bin directory of your sharepoint website just copy your .xap file and the silverlight application which is in my case Tangram.dll.

6.Deploy the web part solution created

    • Right click on the solution and click properties in the debug section just change the “Start Browser with url” with the location of your sharepoint website.

    • Right click on the webpart solution which you have created and just click deploy to deploy the solution to the sharepoint website.

    • After successful deployment click on the edit page section in the site actions of your website and select the web part which you just deployed its generally in the miscellaneous section.

If all goes well and you have followed each step you will see the silverlight application rendered in your webpart.

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


Sharepoint Quick Tip:- Add stsadm.exe to your environment variables to execute it from anywhere

This is a very small but tremendously useful tip for the beginners of WSS 3.0 or MOSS 2007.Especially when you are using the free version WSS 3.0 many times you need to use this command line utility call stsadm.exe. As you need it many times every time you need to navigate to its directory which is “C:Program FilesCommon FilesMicrosoft Sharedweb server extensions12bin” So as a quick workaround what you can do to save your time is to set this path in environment variables section. Just right click on your MyComputer and select Properties   In the advanced section select environment variables and then below select the path.In the path put semi-colon after the last entry and add one more path as c:Program FilesCommon FilesMicrosoft Sharedweb server extensions12bin. After doing this just click OK and you are done. Now you can run stsadm.exe from anywhere in the command prompt as it’s available globally as an environment variable. This tip is meant for a sharepoint newbie.


Microsoft Charting Controls:- How to setup and start building your first chart application

If you need charting support in your asp.net application then you don’t need to look for any other 3rd party charting controls.Microsoft with its release of .net framework 3.5SP1 and Visual Studio 2008 SP1 provided the facility of charting within the development environment. You just have to download and install the right packages and then configure your configuration files correctly to setup your charts to see them up and running. Here in this article i will tell you where to download and install the required things for working with Microsoft Charts and how to configure you web.config in order to setup your charting controls. First of all these are the links to download the prerequisite to start playing with Microsoft Chart Controls

  1. Download Free Microsoft Chart Controls
  2. Download Visual Studio Tools Support For Chart Controls
  3. Download Microsoft Chart Control Samples
  4. Download Microsoft Chart Controls Documentation
  5. Microsoft Chart Control Forum

Now the second thing is adding the chart control to your toolbox.

  1. Just right click on your toolbox’s data tab and select Choose Item from the menu.
  2. In the dialogue box opened select the two dll’s marked in the image below, after adding these you are good to go with Microsoft Charting.

After adding these controls to the toolbox just drag and drop the control on to your page.It will automatically create a Register directive entry on your page if it does not create then you can do it manually, just add the below lines to your page in a Register directive.

<%@ Register Assembly="System.Web.DataVisualization, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
    Namespace="System.Web.UI.DataVisualization.Charting" TagPrefix="asp" %>

After dropping the chart control on to your page you will have the markup like this

<asp:Chart ID="Chart1" runat="server">
    <Series>
        <asp:Series Name="Series1">
        </asp:Series>
    </Series>
    <ChartAreas>
        <asp:ChartArea Name="ChartArea1">
        </asp:ChartArea>
    </ChartAreas>
</asp:Chart>

Now the third thing is configuring your web.config file in order to make chart controls work properly,otherwise you will receive errors complaining about the http get request and other similar errors.

<appSettings>
    <add key="ChartImageHandler" value="storage=file;timeout=20;dir=c:TempImageFiles;" />
</appSettings>

<httpHandlers>
...
    <add path="ChartImg.axd" verb="GET,HEAD" type="System.Web.UI.DataVisualization.Charting.ChartHttpHandler, System.Web.DataVisualization, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" validate="false" />
...
</httpHandlers>

<handlers>
...
    <add name="ChartImageHandler" preCondition="integratedMode" verb="GET,HEAD" path="ChartImg.axd" type="System.Web.UI.DataVisualization.Charting.ChartHttpHandler, System.Web.DataVisualization, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
...
</handlers>

These three web.config entries are sufficient to make your chart control work.

If for the first setting you don’t want to use the drive location which is currently configured as c:TempImageFiles you can replace it with web location of your website as instead of dir use url=”~tempImages”,this will use the tempimages folder created inside your website root.

Stay tuned for more articles on Microsoft Charting Controls

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


The base class includes the field 'ReportViewer1', but its type (Microsoft.Reporting.WebForms.ReportViewer) is not compatible with the type of control (Microsoft.Reporting.WebForms.ReportViewer)

If you are getting this error while using old reports from your legacy application then you can try out the following solutions

  1. Change in your Web.config all "Microsoft.ReportViewer.WebForms, Version=8.0.0.0,..." to " Microsoft.ReportViewer.WebForms, Version=9.0.0.0,..." and in controls "Register Assembly="Microsoft.ReportViewer.WebForms, Version=8.0.0.0,..." to "Register Assembly="Microsoft.ReportViewer.WebForms, Version=9.0.0.0,..."
  2. Clean your solution and delete the related dll files and then again rebuild the solution.

Generally doing step 1 will resolve the problem as in legacy applications its configured to use Version=8.0.0.0