Access an external database in a custom form control in Kentico CMS

 kentico

Access an external database in a custom form control in Kentico CMS

Kentico CMS is an extremely open and extendable platform for development. Often in a project companies will need an extra bit of functionality that is specific to them. This blog will detail how to add a new form control to be used within a Kentico CMS site. The control will extract data from a separate database and present the user with a list of check boxes to select.
 

Step 1 – Create the base form control

Kentico provides step by step instructions for creating form controls in their developer guide. This guide is a great place to start to make sure your form control has all of the correct properties and methods to work within the system. The guide can be found here:

http://devnet.kentico.com/docs/devguide/index.html?developing_form_controls.htm 

Completing the above process will create the base form and register it within Kentico


Step 2 – Configure the form control

For this blog, I am pulling back a checkbox list of counties to display to the user. Here is the layout of the form control to accomplish this:
 
<%@ Control Language="C#" AutoEventWireup="true" CodeFile="CountySelector.ascx.cs"
    Inherits="CountySelector" %>
<asp:CheckBoxList ID="cblCounties" runat="server">
</asp:CheckBoxList>

In the code behind, I will add code to get/set the selected items:
 
    /// <summary>
    /// Gets or sets field value
    /// </summary>
    public override Object Value
    {
        get
        {
            string strReturn = "";
            foreach (ListItem li in cblCounties.Items)
            {
                if (li.Selected)
                {
                    if (strReturn != "")
                    {
                        strReturn += ",";
                    }
                    strReturn += li.Value;
                }
            }
            return strReturn;
        }
        set
        {
            // Ensure checkbox list options
            EnsureItems();
            string strValues = System.Convert.ToString(value);

            if (strValues != "")
            {
                string[] strCounties = strValues.Split(',');
                foreach (string strCounty in strCounties)
                {
                    foreach (ListItem li in cblCounties.Items)
                    {
                        if (li.Value == strCounty)
                        {
                            li.Selected = true;
                        }
                    }
                }
            }
        }
    }
 

 
Step3 – Configure the database connection

Kentico provides a number of APIs to access data within the database. Unfortunately, these API’s do not work natively with external databases. In order to query data in an external database you will need to use the standard .NET SQL data provider to access an external SQL database.

In the web.config, add a new connection string:

Add connection string to Web.config

In the form control, connect to the external database using the SQLDataProvider. This code will use the new connection string to access the external data. I will put this code in the code behind for the form control.
 
    protected void EnsureItems()
    {
        if (cblCounties.Items.Count == 0)
        {
            string strConnString = System.Configuration.ConfigurationManager.ConnectionStrings["ExternalConnectionString"].ToString();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "[STORED PROCEDURE]";
            DataSet ds = new DataSet();
            SqlConnection conn = new SqlConnection(strConnString);
            conn.Open();
            cmd.Connection = conn;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            conn.Close();
            conn.Dispose();
            cblCounties.DataValueField = "CountyID";
            cblCounties.DataTextField = "Name";
            cblCounties.DataSource = ds;
            cblCounties.DataBind();
        }
    }
 

Step 4 – Add the form control to the User Settings table

Now that the control is created and registered in Kentico, we will add it to the User Settings table to allow user accounts to utilize it.

In CMSSiteManager/Development select System Tables. In System Tables, select User – Settings:

User Settings

On the Fields tab, click New attribute. Add the appropriate information for the field.

Add attribute


Step 5 – Test the form control

Adding a new field to the User Settings modifies the User Management screen in Kentico CMS by creating a Custom Fields tab. This is where admins can manage your custom settings.

Custom fields

On the Custom Fields tab, verify your control is displaying properly:

View custom form control

Confirm the form control properly updates and saves the information. You can also check directly in the database in the CMS_UserSettings table to confirm the control saved the data.

Saved custom form data Saved to database


Tip

After setting the custom setting, you can access the value using the API:
 
            //Get the user properties
            if (CMSContext.CurrentUser.UserSettings["FGNWCounties"] != null)
            {
                string strCounties = CMSContext.CurrentUser.UserSettings["FGNWCounties"].ToString();
                if (strCounties != "")
                {
                    cmd.Parameters["@CountyIDs"].Value = strCounties;
                }
                else
                {
                    cmd.Parameters["@CountyIDs"].Value = null;
                }
            }
            else
            {
                cmd.Parameters["@CountyIDs"].Value = null;
            }
 

Conclusion

The above process will create a dynamic form control that accesses data from an external data source. This functionality will allow you to quickly pull data from multiple sources to present to users. This same concept could be used with a Document Type or web part within the site.

Author

Wiz E. Wig, Mascot & Director of Magic
Wiz E. Wig

Director of Magic