Now that we are aware of the scenario, let us begin
implementing the application.
Populating DropDownList with Users
Our first task is to populate the DropDownList with the
names of the users. Check out the following code which achieves this task.
Listing 4 – Populating DropDownList
private void PopulateDDLNames()
{
SqlConnection myConnection = newSqlConnection(ConnectionString);
SqlDataAdapter ad = newSqlDataAdapter("SELECT * FROM tblUsers", myConnection);
DataSet ds = new DataSet();
ad.Fill(ds);
ddlNames.DataSource = ds;
ddlNames.DataTextField = "Name";
ddlNames.DataValueField = "UserID";
ddlNames.DataBind();
}The PopulateDDLNames method is called in the Page_Load
event.
Listing 5 – Page_Load Event
protected void Page_Load(object sender, EventArgse)
{
if (!Page.IsPostBack)
{
BindData();
}
}
private void BindData()
{
PopulateDDLNames();
}Now if you run the application, your DropDownList with be
populated with the data from the database. Note that in a production
application this would be an excellent place to add some data caching, perhaps
with SQL Cache Invalidation.
Implementing the Display Button Click Event
The display button click event is used to display the tasks
of a particular user. The user selection is based on the DropDownList control.
Take a look at the display button click event.
Listing 6 – Display Button Click Event
protected void Btn_DisplayTasks_Click(objectsender, EventArgs e)
{
int userID =Convert.ToInt32(ddlNames.SelectedValue);
MyGridView.DataSource = GetUserTasks(userID);
MyGridView.DataBind();
}As you may have noticed, the Btn_DisplayTasks calls the
GetUserTasks method which returns the tasks based on the user id.
Implementation of the GetUserTasks Method
The GetUserTasks method is responsible for returning the
tasks of a particular user. Take a look at the implementation of the method
below:
Listing 7 – GetUserTasks Method
private DataSet GetUserTasks(int userID)
{
Session["UserID"] = userID;
string sproc = "usp_GetUserTasks";
SqlConnection myConnection = newSqlConnection(ConnectionString);
SqlCommand myCommand = new SqlCommand(sproc,myConnection);
myCommand.CommandType =CommandType.StoredProcedure;
myCommand.Parameters.AddWithValue("@UserID",userID);
SqlDataAdapter ad = newSqlDataAdapter(myCommand);
DataSet ds = new DataSet();
try
{
myConnection.Open();
ad.Fill(ds);
}
catch (Exception ex)
{
string exception = ex.Message;
}
finally
{
myConnection.Close();
myCommand.Dispose();
}
if (ds != null)
return ds;
else
return null;
}Analysis
The GetUserTasks method simply uses the userID as the
parameter to the stored procedure. The stored procedure returns all the tasks
based on that userID and puts the results in the DataSet container. At the end
of the method we simply return the DataSet to the presentation layer.
