Bind All DropDownLists On A Page Using A Common Method

Open SQL Server and create two tables namely State and City:



Create Procedure name is testSp with the following parameters. Here is the Procedure:

Create PROCEDURE testSp
    (@Para varchar(30) = '', @Id int = 0)
AS
Begin
If@ Para = 'Add'
Begin
Select Id, State from State
END
Else If@ Para = 'GetCity'
Begin
Select CityId, CityName from City where StateId = @Id
END
END

Now Open visual studio, file, new project and select web in template, then ASP.NET empty web application and give it any name.

Now in the solution explorer right click on the solution and add new project in the templates. Select visual C#, then class library and name it a sqlhelper which is a dll. Add reference of this sqlhelper dll into our project. In the class library there is a class Class1.cs inside it. Write the following code:

SqlHelper Code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.Collections;
namespace SqlHelper {
    public class Class1 {#
        region "Declare Variables"
        private static SqlConnection m_conn = null;
        public static SqlTransaction m_transaction;#
        endregion# region "Attach Parameters"
        private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters) {
            foreach(SqlParameter param in commandParameters) {
                if (Convert.ToString(param.Value) == "1/1/1999 12:00:00 AM") {
                    param.Value = DBNull.Value;
                }
                command.Parameters.Add(param);
            }
        }
        private static void AssignParameterValues(SqlParameter[] commandParameters, object[] parameterValues) {
            try {
                if ((commandParameters == null) || (parameterValues == null)) {
                    return;
                }
                for (int i = 0, j = commandParameters.Length; i < j; i++) {
                    try {
                        commandParameters[i].Value = parameterValues[i];
                    } catch {}
                }
            } catch {}
        }
        private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters) {
            command.Connection = connection;
            command.CommandText = commandText;
            command.Transaction = transaction;
            command.CommandType = commandType;
            if (commandParameters != null) {
                AttachParameters(command, commandParameters);
            }
            return;
        }#
        endregion# region "Execute Non Query"
        public static int ExecuteNonQuery(SqlConnection connection, SqlTransaction transaction, string spName, params object[] parameterValues) {
            if ((parameterValues != null) && (parameterValues.Length > 0)) {
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, transaction, spName);
                AssignParameterValues(commandParameters, parameterValues);
                return ExecuteNonQuery(connection, transaction, CommandType.StoredProcedure, spName, commandParameters);
            } else {
                return ExecuteNonQuery(connection, transaction, CommandType.StoredProcedure, spName);
            }
        }
        public static int ExecuteNonQuery(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters) {
            SqlCommand cmdExecute = new SqlCommand();
            PrepareCommand(cmdExecute, connection, transaction, commandType, commandText, commandParameters);
            int returnVal = cmdExecute.ExecuteNonQuery();
            return returnVal;
        }#
        endregion# region "Connection String"
        public static SqlConnection OpenConnection(string DBName) {
            //string connString = System.Configuration.ConfigurationManager.ConnectionStrings[APPID].ConnectionString;
            string connstr = ConfigurationSettings.AppSettings["Local"].ToString();
            m_conn = new SqlConnection(connstr);
            if ((m_conn.State == ConnectionState.Broken) || (m_conn.State == ConnectionState.Closed)) {
                m_conn.Open();
            }
            return m_conn;
        }
        public static SqlConnection OpenConnection() {
            //string connstr = ConfigurationSettings.AppSettings["connString"].ToString();
            string connstr = System.Configuration.ConfigurationManager.ConnectionStrings[ConfigurationSettings.AppSettings["APPID"].ToString()].ToString();
            m_conn = new SqlConnection(connstr);
            if ((m_conn.State == ConnectionState.Broken) || (m_conn.State == ConnectionState.Closed)) {
                m_conn.Open();
            }
            return m_conn;
        }
        public static void CloseConnection(Object obj) {
            m_conn = (SqlConnection) obj;
            if (m_conn.State == ConnectionState.Open) {
                m_conn.Close();
                m_conn.Dispose();
            }
        }#
        endregion# region "Transaction"
        public static void StartTransaction() {
            m_transaction = m_conn.BeginTransaction();
        }
        public static void CommitTransaction() {
            m_transaction.Commit();
        }
        public static void RollBackTransaction() {
            m_transaction.Rollback();
        }#
        endregion# region "Execute Dataset"
        public static DataSet ExecuteDataset(SqlConnection cn, SqlTransaction t, string spName, params object[] parameterValues) {
            if ((parameterValues != null) && (parameterValues.Length > 0)) {
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(cn, t, spName);
                AssignParameterValues(commandParameters, parameterValues);
                return ExecuteDataset(cn, t, CommandType.StoredProcedure, spName, commandParameters);
            } else {
                return ExecuteDataset(cn, t, CommandType.StoredProcedure, spName);
            }
        }
        public static DataSet ExecuteDataset(SqlConnection connection, SqlTransaction t, CommandType commandType, string commandText, params SqlParameter[] commandParameters) {
            SqlCommand cmdExecute = new SqlCommand();
            PrepareCommand(cmdExecute, connection, t, commandType, commandText, commandParameters);
            SqlDataAdapter daExecute = new SqlDataAdapter(cmdExecute);
            DataSet dstExecute = new DataSet();
            daExecute.Fill(dstExecute);
            cmdExecute.Parameters.Clear();
            return dstExecute;
        }
        public static DataSet ExecuteDataset(SqlConnection connection, SqlTransaction t, CommandType commandType, string commandText, string datatableName, params SqlParameter[] commandParameters) {
            SqlCommand cmdExecute = new SqlCommand();
            PrepareCommand(cmdExecute, connection, t, commandType, commandText, commandParameters);
            SqlDataAdapter daExecute = new SqlDataAdapter(cmdExecute);
            DataSet dstExecute = new DataSet();
            daExecute.Fill(dstExecute, datatableName);
            cmdExecute.Parameters.Clear();
            return dstExecute;
        }#
        endregion# region "Execute Scalar"
        public static object ExecuteScalar(SqlConnection connection, SqlTransaction transaction, string spName, params object[] parameterValues) {
            if ((parameterValues != null) && (parameterValues.Length > 0)) {
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, transaction, spName);
                AssignParameterValues(commandParameters, parameterValues);
                return ExecuteScalar(connection, transaction, CommandType.StoredProcedure, spName, commandParameters);
            } else {
                return ExecuteScalar(connection, transaction, CommandType.StoredProcedure, spName);
            }
        }
        public static object ExecuteScalar(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters) {
            SqlCommand cmdExecute = new SqlCommand();
            PrepareCommand(cmdExecute, connection, transaction, commandType, commandText, commandParameters);
            object returnVal = cmdExecute.ExecuteScalar();
            cmdExecute.Parameters.Clear();
            return returnVal;
        }#
        endregion# region "Class SqlHelper"
        public sealed class SqlHelperParameterCache {
            private SqlHelperParameterCache() {}
            private static Hashtable paramCache = Hashtable.Synchronized(new Hashtable());
            private static SqlParameter[] DiscoverSpParameterSet(SqlConnection connection, SqlTransaction transaction, string spName, bool includeReturnValueParameter) {
                SqlCommand cmdExecute = new SqlCommand();
                cmdExecute.CommandType = CommandType.StoredProcedure;
                cmdExecute.CommandText = spName;
                cmdExecute.Connection = connection;
                // AlternativeDeriveParameters adParams = new AlternativeDeriveParameters();
                //cmdExecute.Transaction=transaction;
                SqlCommandBuilder.DeriveParameters(cmdExecute);
                if (!includeReturnValueParameter) {
                    cmdExecute.Parameters.RemoveAt(0);
                }
                SqlParameter[] discoveredParameters = new SqlParameter[cmdExecute.Parameters.Count];
                cmdExecute.Parameters.CopyTo(discoveredParameters, 0);
                return discoveredParameters;
            }
            private static SqlParameter[] CloneParameters(SqlParameter[] originalParameters) {
                SqlParameter[] clonedParameters = new SqlParameter[originalParameters.Length];
                for (int i = 0, j = originalParameters.Length; i < j; i++) {
                    clonedParameters[i] = (SqlParameter)((ICloneable) originalParameters[i]).Clone();
                }
                return clonedParameters;
            }
            public static void CacheParameterSet(string connectionString, string commandText, params SqlParameter[] commandParameters) {
                string hashKey = ConfigurationSettings.AppSettings["ConnString"].ToString() + ":" + commandText;
                paramCache[hashKey] = commandParameters;
            }
            public static SqlParameter[] GetCachedParameterSet(SqlConnection connection, string commandText) {
                string hashKey = ConfigurationSettings.AppSettings["ConnString"].ToString() + ":" + commandText;
                SqlParameter[] cachedParameters = (SqlParameter[]) paramCache[hashKey];
                if (cachedParameters == null) {
                    return null;
                } else {
                    return CloneParameters(cachedParameters);
                }
            }
            public static SqlParameter[] GetSpParameterSet(SqlConnection connection, SqlTransaction transaction, string spName) {
                return GetSpParameterSet(connection, transaction, spName, false);
            }
            public static SqlParameter[] GetSpParameterSet(SqlConnection connection, SqlTransaction transaction, string spName, bool includeReturnValueParameter) {
                //string connectionString = "";
                string hashKey = connection.ConnectionString + ":" + spName + (includeReturnValueParameter ? ":include ReturnValue Parameter" : "");
                SqlParameter[] cachedParameters;
                cachedParameters = (SqlParameter[]) paramCache[hashKey];
                if (cachedParameters == null) {
                    cachedParameters = (SqlParameter[])(paramCache[hashKey] = DiscoverSpParameterSet(connection, transaction, spName, includeReturnValueParameter));
                }
                return CloneParameters(cachedParameters);
            }
        }#
        endregion
    }
}

Right click on web application in solution explorer and add new web form on the aspx page; include two dropdown lists one for state as ddlState and other for city as ddlCity. Here is the aspx page code:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="WebApplication12.WebForm1" %>
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">

    <head runat="server">
        <title></title>
    </head>

    <body>
        <form id="form1" runat="server">
            <div>
                <table>
                    <tr>
                        <td>
                            Select State
                        </td>
                        <td>
                            <asp:DropDownList ID="ddlState" runat="server" onselectedindexchanged="ddlState_SelectedIndexChanged" AutoPostBack="true">
                            </asp:DropDownList>
                        </td>
                    </tr>
                    <tr>
                        <td>
                            Select City
                        </td>
                        <td>
                            <asp:DropDownList ID="ddlCity" runat="server">
                                <asp:ListItem Value="0" Selected="True" Text="Select"></asp:ListItem>
                            </asp:DropDownList>
                        </td>
                    </tr>
                </table>
            </div>
        </form>
    </body>

    </html>

Now on the cs side write the following code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using SqlHelper;
namespace WebApplication12 {
    public partial class WebForm1: System.Web.UI.Page {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Local"].ToString());
        SqlParameter[] param;
        DataSet ds = null;
        protected void Page_Load(object sender, EventArgs e) {
            if (!Page.IsPostBack)
                BindState();
        }
        public void BindALLDDL(ref DropDownList ddl, DataSet ds, string textField, string valueField) {
            try {
                ddl.DataSource = ds;
                ddl.DataValueField = ds.Tables[0].Columns[valueField].ToString();
                ddl.DataTextField = ds.Tables[0].Columns[textField].ToString();
                ddl.DataBind();
                ddl.Items.Insert(0, "Select");
            } catch (Exception ex) {
                throw ex;
            }
        }
        private void BindState() {
            try {
                param = new SqlParameter[1];
                param[0] = new SqlParameter("@Para", SqlDbType.VarChar);
                param[0].Value = "Add";
                ds = Class1.ExecuteDataset(con, null, CommandType.StoredProcedure, "testSp", param);
                BindALLDDL(ref ddlState, ds, "State", "Id");
            } catch (Exception ex) {
                throw ex;
            }
        }
        protected void ddlState_SelectedIndexChanged(object sender, EventArgs e) {
            try {
                if (ddlState.SelectedIndex > 0) {
                    BindCity();
                }
            } catch (Exception ex) {
                throw ex;
            }
        }
        private void BindCity() {
            try {
                param = new SqlParameter[2];
                param[0] = new SqlParameter("@Para", SqlDbType.VarChar);
                param[0].Value = "GetCity";
                param[1] = new SqlParameter("@Id", SqlDbType.Int);
                param[1].Value = Convert.ToInt32(ddlState.SelectedItem.Value);
                ds = Class1.ExecuteDataset(con, null, CommandType.StoredProcedure, "testSp", param);
                BindALLDDL(ref ddlCity, ds, "CityName", "CityId");
            } catch (Exception ex) {
                throw ex;
            }
        }
    }
}

As you can see BindAllDDL is the common method for binding all drop down list. Its parameters are the following: first is drop down list name, second is dataset, third is text field for drop down list, fourth is value field.

Now see the method bindState I have called it on page load to bind state from state table to drop down ddlState. In the method you can see I have created a SqlParameter array to pass the parameters to StoredProcedure, then I have filled dataset using ExecuteDataset method which is in our SqlHelper class.

Parameters pass to this method are Sqlconnection object, transaction is null, commandtype which is StoredProcedure, then the sp name which is testSp and last is parameter array which passes parameters to StoredProcedure. Then I have called BindALLDLL method and in that I have passed dropdownlist name which is ddlState, then the dataset ds, then textfield name which is State and value field name which is Id.

On similar basis I have bind City dropdown; bindCity is the method for it.

Do not forget to add connection string in the web.config file; here it is:

<connectionStrings>
   <add name="Local"
connectionString="Data Source=livingroom\SQLEXPRESS;Integrated Security=true;Initial Catalog=practisedatabase"/>
</connectionStrings>

Add this inside configuration section.

Therefore we can bind all drop down list on page using a common method, so in each we do not have to write dropdownlist name. datasource, ddl, datavaluefield, ddlname.datatextfield and ddlname.databind. I will come with more simple solution for the above in the next article.