I have created a user control "Any Drop Down List" anyDDL. Its use to show drop download list form database very quickly.
Lets create that user control.
I'm using Visual Studio 2005, Framework 2.0, SQL Server 2005, ASP.net, C# language to build it.
Start Visual Studio,
Click on Create New Web Site.
Select ASP.net web site.
Click on OK button.
Now,
click on Web Site menu or right click on project name in Solution Explorer.
Select menu named "Add New Item".
It will show a add item dialog box as bellow.
Select
Web User Control there and right down its name
anyDDL, click on OK button.
Now add a Drop Down List form toolbox.
Rename that drop down list with
"thisDDL".
now add some following code in code behind file.
using System;
using System.Data;
using System.Data.SqlClient;
/// This Drop down list can handle all kind of Values automaticaly.
///saCode 14Sep2007
public partialclass userControls_anyDDL : System.Web.UI.UserControl
{
#region private variables
private string _SQL;
private string _WhereSQL="";
private String _allowSelectionByUserValue;
#endregion
#region Public Propertys
///SQL Stored Procedure Name Comes here.
public string SQLstring
{
get { return _SQL; }
set { _SQL =value; }
}
/// SQL where Condition
public string WhereValue
{
get { return _WhereSQL; }
set { _WhereSQL =value; }
}
/// Selected Value of Drop Down List Box
public string SelectedValue
{
get{ return ddlThis.SelectedValue;}
set{ddlThis.SelectedValue = value;}
}
/// Somthing like "---Other---" or "--Select--"
public string allowSelectionByUserValue
{
get { return _allowSelectionByUserValue; }
set { _allowSelectionByUserValue = value; }
}
#endregion
public void ReBindData()
{
BindWithData();
}
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
BindWithData();
}
}
private void BindWithData()
{
if (SQLstring !=null)
{
string _strConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString;
SqlConnection _objCon = new SqlConnection(_strConnectionString);
SqlCommand _objCmd = new SqlCommand();
System.Data.DataSet _objDataSet = new System.Data.DataSet();
_objCmd.Connection = _objCon;
_objCmd.CommandType = System.Data.CommandType.StoredProcedure;
_objCmd.CommandText = "uspAnyDll";
SqlParameter _paramSelectType = new SqlParameter("@selectType", System.Data.SqlDbType.VarChar);
SqlParameter _paramWhereValue = new SqlParameter("@WhereValue", System.Data.SqlDbType.VarChar);
_paramSelectType.Value = _SQL;
_paramWhereValue.Value = _WhereSQL;
_objCmd.Parameters.Add(_paramSelectType);
_objCmd.Parameters.Add(_paramWhereValue);
SqlDataAdapter _objAdapter = new SqlDataAdapter(_objCmd);
_objAdapter.Fill(_objDataSet);
ddlThis.DataSource = _objDataSet;
ddlThis.DataTextField = "Value";
ddlThis.DataValueField = "ID";
ddlThis.DataBind();
if (allowSelectionByUserValue != null)
{
System.Web.UI.WebControls.ListItem
_listItem = new System.Web.UI.WebControls.ListItem(allowSelectionByUserValue, "0");
_listItem.Selected = true;
ddlThis.Items.Add(_listItem);
}
if (this.SelectedValue != null)
{
ddlThis.SelectedValue = this.SelectedValue;
}
}
else
{
//Throw Expression
}
}
}
Now we have to create stored procedure named
"uspAnyDll" to get data form SQL Server.
-- =============================================
-- Author: <sa>
-- Create date: <15Sep2007>
-- Description: <saCode>
-- =============================================
CREATE PROCEDURE uspAnyDll
--ALTER PROCEDURE uspAnyDll @selectType varChar(50),
@WhereValue varChar(50)
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
--you can change this name and SQL as you database requirement.
--but don't change Field name , ID and Value both are static typed in that user control.
IF @selectType='GroupData'
SELECT GroupID AS [ID], Name AS [Value] FROM tblGroupWHERE RecordStatusID=1
IF @selectType='User Information'
SELECT UserID AS [ID], UserName AS [Value] FROM tblUser WHERE CompanyID=Convert(int, @WhereValue)
IF @selectType='State'
SELECT StateID AS [ID], StateName AS [Value] FROM tblState
END TRY
BEGIN CATCH
SELECT 'Error! : ' + @@ERROR
END CATCH
END
you can write down you own query here but don't change it's title "
ID" and "
Value", because those are static in user control code.
Thats all.
User Control is ready to run. open default.aspx page and drag this control from Solution Explorer.
It will display a drop down list control. See it property panel.
Here is the description for each property:
allowSelectionByUserValue : Some selection message. like "---Select---"
SelectedValue : any selected value if required.
SQLstring : SQL Server stored procedure parameter comes here.
WhereValue : any where condition to pass in that stored procedure.
See, here I have create three different select query in uspAnyDll.
I drag three times anyDDL, and pass
SQLstring property as "
GroupData', 'User Information' and 'State'. this are actually sql stored porcedure parameters.
and just press F5.
See the result.
Now, using this user control, you can get quickly bind data.
Thursday, September 27, 2007
saAction
Just click on icon, add URL and save it.
Just click on icon, add URL and save it.
Click on that menu.
