Thursday, December 22, 2011

How To - Get connection string from web.config in ASP.NET

Sample web.config



  
  


Get Connection string at runtime

In order to retrieve a specific connection string from the web.config, first we need a reference to System.Configuration. In .NET 4, the reference has already been added for us, but we still need to add "using System.Configuration;" in our page. In previous .NET versions, you needed to add the reference manually by going to the references folder in the solution explorer, right click and select Add Reference. From the .NET tab, select System.Configuration. After having done the above, we can get the connection string required by using the following code.
private const string CONNECTIONSTRING_NAME = "LocalSqlServer";
string myConnectionString = ConfigurationManager.ConnectionStrings[CONNECTIONSTRING_NAME].ConnectionString;

Using connection string expression syntax

We can bind the connection string directly to the connection string property of the sqldatasource using ASP.NET expressions. This is done by using the following syntax


How To - Cascading dropdowns using AJAX Callbacks

Filtering data for the users is very important for 2 reasons

  1. Results in a more user friendly experience
  2. Reduces data, therefore reduces load time.  

One of the most common scenarios is to have multiple dropdownlists which filter each other. For example: categories and sub-categories.
The below code uses the MS database adventureworks to retrieve data.

MS AJAX Toolkit

In the following example, we will be using the AJAX Control Toolkit. In order to start using the toolkit, you will first need to add the toolkit reference to the project, then add the following line at the top of the aspx page
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="ajaxToolkit"  %>
In order to use any control or extender from the toolkit, you will need to drop a scriptmanager in the page (this can be in the masterpage as well)

In the aspx file, copy the following code
Category
Sub Category
and in the webservice use the following code
 [System.Web.Script.Services.ScriptService]
    public class WebService1 : System.Web.Services.WebService
    {
        [WebMethod]
        public CascadingDropDownNameValue[] GetCategories(string knownCategoryValues, string category)
        {
            DataTable dt = SqlHelper.ExecuteDataset(
            ConfigurationManager.ConnectionStrings["AdventureWorksConnectionString"].ConnectionString,
            CommandType.Text, "SELECT * FROM [Production].[ProductCategory]").Tables[0];

            List values = new List();

            foreach (DataRow dr in dt.Rows)
            {
                values.Add(new CascadingDropDownNameValue((string)dr["Name"], 
                dr["ProductCategoryID"].ToString()));
            }
            return values.ToArray();
        }

        [WebMethod]
        public CascadingDropDownNameValue[] GetSubCategories(string knownCategoryValues, string category)
        {
            StringDictionary kv = CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues);

            int categoryId;
            if (!kv.ContainsKey("category") || !Int32.TryParse(kv["category"], out categoryId))
            {
                return null;
            }

            DataTable dt = SqlHelper.ExecuteDataset(
            ConfigurationManager.ConnectionStrings["AdventureWorksConnectionString"].ConnectionString,
            CommandType.Text, "SELECT * FROM [Production].[ProductSubcategory] WHERE ProductCategoryID = "
             + categoryId).Tables[0];

            List values = new List();

            foreach (DataRow dr in dt.Rows)
            {
                values.Add(new CascadingDropDownNameValue((string)dr["Name"],
                dr["ProductSubcategoryID"].ToString()));
            }
            return values.ToArray();
        }
    }
Do not forget to uncomment the part [System.Web.Script.Services.ScriptService] or else it will not work.

Devexpress

Another way to implement this is to use the devexpress combobox (ASPxCombobox). Aspx:
code behind
protected void ddlSubCategory_Callback(object sender, DevExpress.Web.ASPxClasses.CallbackEventArgsBase e)
{
      if (string.IsNullOrEmpty(e.Parameter)) return;
      dsSubCategory.SelectParameters["ProductCategoryID"].DefaultValue = e.Parameter;
      ddlSubCategory.DataBind();
}
Personally, I prefer using the DevExpress controls as it involves much less tedious work and you do not need to create a webservice. Obviously this comes with a price tag, so if you have a limited budget, the ASP.net toolkit still offers a very good solution.