• How to Load controls dynamically from XML

    Posted on March 20, 2013 by in ASP.NET, C#, Dotnet

    We as programmers always try optimizing the way we do things.  It could be for better flexibility or performance or maybe we are too smart.  What I would like to explain in this article is to save page control values in database in form of XML and then load them back onto the controls. You may wonder why we would want to something like that.  May be these control values doesn’t represent application data, they are just used for display purpose.  You wouldn’t want to store these values in normalized way all the time.

    Database Connection:
    I am using AdventureWorks for this article.  Add following connectionstring element to web.config.

    <connectionStrings>
    <add name="Sql" connectionString="Data Source=(local);Initial Catalog=AdventureWorks;User=testuser;Password=testuser;" providerName="System.Data.SqlClient"/>
    </connectionStrings>
    

    Page Design
    I have added some basic controls to the page. A HTML table which would contain all the controls used for filtering purpose, actual filter controls and couple of buttons.

    <html xmlns="http://www.w3.org/1999/xhtml" >
    <head id="Head1" runat="server">
        <title></title>
        <link type="text/css" rel="Stylesheet" href="themes/TableTheme_1.css" />
    </head>
    <body>
    <form id="form1" runat="server">
    <div>
     <table ID="tblFilters" runat="server"  class="TableClass1">            
        <tr>
            <td><asp:Label ID= "lblProductName" runat ="server" Text="Product Name"/></td>
            <td><asp:TextBox ID= "tbxProductName" runat ="server"/></td>
        </tr>
         <tr>
            <td><asp:Label ID= "lblSubCategoryName" runat ="server" Text="SubCategory Name"/></td>
            <td><asp:TextBox ID= "tbxSubCategoryName" runat ="server"/></td>
        </tr>
        <tr>
            <td  colspan="2"><asp:LinkButton ID="btnSaveFilters" runat="server" Text="Save Filters" 
                    onclick="btnSaveFilters_Click" /></td>
        </tr>
         <tr>
            <td  colspan="2"><asp:LinkButton ID="btnReset" runat="server" Text="Reset Filters" 
                    onclick="btnResetFilters_Click" /></td>
        </tr>
    </table>
    </div>
    </form>
    </body>
    </html>
    

    Database Schema:
    Created a new table tblPageFilters to store page filter values. For this example, I am storing filters by page. Typically for a multi user environment, you would want to add another column “userid”.

    CREATE TABLE [dbo].[tblPageFilters](
    	[PageFilterID] [int] IDENTITY(1,1) NOT NULL,
    	[PageName] [varchar](100) NOT NULL,
    	[PageFiltersXML] [varchar](max) NOT NULL,
     CONSTRAINT [PK_tblPageFilters] PRIMARY KEY CLUSTERED 
    (
    	[PageFilterID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
    			ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    

    Created a new stored proc “sp_InsertPageFilters” which would insert or update page filters. It basically searches if there are any filters for a page. If it finds a match, it will update the filter xml. If not, it will create a new entry.

    CREATE PROCEDURE [dbo].[sp_InsertPageFilters]
    @PageName VARCHAR(100),
    @PageFiltersXML VARCHAR(MAX)
    AS
    BEGIN
    	DECLARE @PageFilterID INT
    	
    	SELECT @PageFilterID = PageFilterID FROM tblPageFilters 
    	WHERE PageName = @PageName
    
    	IF(@PageFilterID IS NOT NULL AND @PageFilterID > 0)
    	BEGIN
    		UPDATE tblPageFilters SET @PageFiltersXML = PageFiltersXML
    		WHERE PageFilterID = @PageFilterID
    	END
    	ELSE
    	BEGIN
    		INSERT INTO tblPageFilters (PageName, PageFiltersXML)
    		VALUES(@PageName, @PageFiltersXML)
    	END	                    
    END
    

    Source Code:

    NameSpaces:

    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Linq;
    using System.IO;
    using System.Text;
    using System.Xml;
    using System.Xml.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;

    When page is initially loaded, we fetch filters for that page from the database and will set them to filter controls on the page.

    protected void Page_Load(object sender, EventArgs e)
    {
        //if it is initial page_load, get filter values from database
        if (!Page.IsPostBack)
        {
            ResetFilterValues();
        }           
    }
    

    In ResetFilterValues, we call RetrievePageFilters to retrieve page filters for the current page. In RetrievePageFilters, we are using ADO.NET API to execute a simple select statement. If the resultset is not empty, we invoke  PopulateFilterValues which would populate filter controls on the page with those values.

    private void ResetFilterValues()
    {
        //get the page name
        string pageName = System.IO.Path.GetFileNameWithoutExtension(Page.AppRelativeVirtualPath);
    
        //fetch page filters
        string xml = DataHelper.RetrievePageFilters(pageName);
    
        //if there are any filters, load them
        if (!String.IsNullOrEmpty(xml))
        {
            PopulateFilterValues(xml);
        }
    }
    
    public static string RetrievePageFilters(string pageName)
    {
        //fetch the connection string from web.config
        string connString = ConfigurationManager.ConnectionStrings["Sql"].ConnectionString;
    
        //SQL statement to fetch entries from products            
        string sql = @"SELECT PageFiltersXML FROM tblPageFilters WHERE PageName = @PageName";
    
        DataTable dtFilters = new DataTable();
    
        //Open SQL Connection
        using (SqlConnection conn = new SqlConnection(connString))
        {
            conn.Open();
            //Initialize command object
            using (SqlCommand cmd = new SqlCommand(sql, conn))
            {                    
                cmd.Parameters.AddWithValue("@PageName", pageName);
    
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                //Fill the result set
                adapter.Fill(dtFilters);
            }
        }
        if (dtFilters.Rows.Count > 0)
            return dtFilters.Rows[0][0].ToString();
    
        return String.Empty;
    }
    
    private void PopulateFilterValues(string xml)
    {
        //Creates a new System.Xml.Linq.XDocument from xml string.
        XDocument filterValuesDoc = XDocument.Parse(xml);
    
        //Get a filtered collection of the descendant elements for this document
        //in document order. Only elements that have a matching "Filter" XName
        //are included in the collection.
        var filterControlList = from filterControl in filterValuesDoc.Descendants("Filter")
                       select new
                       {
                           ControlID = filterControl.Element("ID").Value,
                           ControlValue = filterControl.Element("Value").Value,                               
                       };
    
        Control ctrl;
        foreach (var filterControl in filterControlList)
        {
            //get reference to the control based on its ID
            ctrl = Page.FindControl(filterControl.ControlID);
    
            ////since we are interested in only textboxes in the example, check if the control type is textbox
            if (ctrl is TextBox)
            {
                //set the filter value to the control
                (ctrl as TextBox).Text = filterControl.ControlValue;
            }
        }
    }
    

    If user clicks on the “Save Filters” button, values from the filter controls are saved to the database. In the btnSaveFilters_Click button click event, we prepare an XML by recursively iterating through all the controls in tblFilters HTML table control. Once we have the filter XML, we persist it into tblPageFilters table by invoking SavePageFilters method. 

    protected void btnSaveFilters_Click(object sender, EventArgs e)
    {
        //get the page name
        string pageName = System.IO.Path.GetFileNameWithoutExtension(Page.AppRelativeVirtualPath);
    
        //Create page filter xml based on user input
        using (var sw = new StringWriter())
        {
            using (var writer = XmlWriter.Create(sw))
            {
                writer.WriteStartDocument();
                writer.WriteStartElement("PageFilters");
    
                //iterate recursively through all controls in filters table and prepare XML
                PrepareFilterXML(writer, tblFilters);
    
                writer.WriteEndElement();
                writer.WriteEndDocument();
            }
            //save filter values to DB
            DataHelper.SavePageFilters(pageName, sw.ToString());
    
    Response.Write("<p style='font-family: Verdana; font-size: medium; font-weight: bold; color: #FF0000'>Filters saved successful to database.</p>");
            Response.Write(Environment.NewLine);
        }
        
    }
    
    private void PrepareFilterXML(XmlWriter writer, Control ctrl)
    {
        TextBox tbx;
        foreach (Control filterControl in ctrl.Controls)
        {
            if (filterControl is TextBox)
            {
                tbx = filterControl as TextBox;
                writer.WriteStartElement("Filter");
    
                writer.WriteElementString("ID", tbx.ID.ToString());
                writer.WriteElementString("Value", tbx.Text);
                writer.WriteEndElement();
            }
            else
            {
                if (filterControl.HasControls())
                    PrepareFilterXML(writer, filterControl);
            }
        }
    }
    

    SavePageFilters method takes two arguments. One is the current page and the second one is the filter controls xml values. It calls [sp_InsertPageFilters] with these parameters using ADO.NET API.

    public static void SavePageFilters(string pageName, string pageFiltersXML)
    {
        //fetch the connection string from web.config
        string connString = ConfigurationManager.ConnectionStrings["Sql"].ConnectionString;
    
        //Open SQL Connection
        using (SqlConnection conn = new SqlConnection(connString))
        {
            conn.Open();
            //Initialize command object
            using (SqlCommand cmd = new SqlCommand("sp_InsertPageFilters", conn))
            {
                cmd.CommandType = CommandType.StoredProcedure;
    
                cmd.Parameters.AddWithValue("@PageName", pageName);
                cmd.Parameters.AddWithValue("@PageFiltersXML", pageFiltersXML);
    
                cmd.ExecuteNonQuery();
            }
        }
    }
    

    If user clicks on btnResetFilters_Click, we load these filter controls with values from database.

    protected void btnResetFilters_Click(object sender, EventArgs e)
    {
        ResetFilterValues();
    }
    

    Sample Page Filter Values XML:

    <?xml version="1.0" encoding="utf-16"?>
    <PageFilters>
      <Filter>
        <ID>tbxProductName</ID>
        <Value>test</Value>
      </Filter>
      <Filter>
        <ID>tbxSubCategoryName</ID>
        <Value>test</Value>
      </Filter>
    </PageFilters>
    

    Output:
    When page is initially load, you see blank values in filter controls.
    load controls dynamically from XML 1

    Enter “test product” as product name & “test sub category” as sub category name and hit on “Save Filters”.
    load controls dynamically from XML 2

    Reopen the page or click on “Reset Filters” and it would re-populate filters from database.

    Be Sociable, Share!
      Post Tagged with , , ,

    Written by

    Software architect with over 10 years of proven experience in designing & developing n-tier and web based software applications, for Finance, Telecommunication, Manufacturing, Internet and other Commercial industries. He believes that success depends on one's ability to integrate multiple technologies to solve a simple as well as complicated problem.

    View all articles by

    Email : [email protected]

    Leave a Reply