In Part 1 we've talked about how to insert data to the database with Entity Framework. In this part I'm going to demonstrate how to fetch the data from the database and populate the form fields with Entity Framework.
STEP 1: Setting up the Form
To get started let's go ahead and fire-up visual studio and add a new WebForm. For the simplicity of this demo I just set up the form like this:
<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server">
<asp:DropDownList ID="ddlUser" runat="server" AppendDataBoundItems="true">
<asp:ListItem Value="0">--Select--</asp:ListItem>
</asp:DropDownList>
<br />First Name:
<br /><asp:TextBox ID="tbFirstName" runat="server" />
<br />Last Name:
<br /><asp:TextBox ID="tbLastName" runat="server" />
<br />Contact Number:
<br /><asp:TextBox ID="tbContactNumber" runat="server" />
</asp:Content>
As you can see there's nothing fancy in the mark-up above. What we want to do here is we are going to populate the DropDownList "ddlUser" with the list of names from the database and then populate the remaining textbox with the details based on the selected name from the dropdownlist. And since we only want to display the information in the page so I set the ReadOnly attribute in the TextBox to true.
STEP 2: Creating the View Model
If you remember in Part 1 particularly in STEP 4, we had an Object Manager Class for each table which is responsible for handling any operations in the database and since we are going to fetch the list of names from the database then we will add a method in UserManager class.
As a recap, Entity Framework will generate the business objects and manage Data Access within the application. As a result, the class SysUser is automatically created by EF and it features all the fields in the database table as properties of the class.
I don't want to use this class for listing all names of the users. It would be wasteful as EF will bring back all information in the table inclusing the LastName, ContactNumber, SysUserLogIn and Password. All we need for populating the DropDownList is the SysUserID and FirstName. So I decided to create a separate class that just holds these properties:
namespace WebAppDemo.Model.ViewModel {
public class UserName {
public int SysUserID { get; set; }
public string FirstName { get; set; }
}
}
Also I created a separate class for listing the user detail information and create the necessary fields that I need to use like below:
namespace WebAppDemo.Model.ViewModel {
public class UserDetail {
public int SysUserID { get; set; }
public string FirstName { get; set; }
public string Lastname { get; set; }
public string ContactNumber { get; set; }
}
}
Both those classes above are stored under Model -> ViewModel folder within the application, as you can probably guess from the namespaces they have ;).
Now since we already have the fields we need for populating the DropDownList and the form then we can start adding new methods for fecthing the list of names and user details in the UserManager class. Here's the code block below:
using System.Collections.Generic;
using System.Linq;
using WebAppDemo.Model.DB;
using WebAppDemo.Model.ViewModel;
namespace WebAppDemo.Model {
public class UserManager {
private DeveloperReportEntities dre = new DeveloperReportEntities();
public IEnumerable<UserName> GetUserFirstName() {
var user = from o in dre.SysUsers
select new UserName {
SysUserID = o.SysUserID,
FirstName = o.FirstName
};
return user.ToList();
}
public IEnumerable<UserDetail> GetUserDetail(int userID) {
var user = from o in dre.SysUsers
where o.SysUserID == userID
select new UserDetail {
SysUserID = o.SysUserID,
FirstName = o.FirstName,
LastName = o.LastName,
ContactNumber = o.ContactNumber
};
return user.ToList();
}
}
}
The code above is the LINQ syntax for querying data. As you can see we query the SysUser object based on the parameter we passed on the GetUserFirstName() method. Once we invoke the LINQ ToList() function, this LINQ query will issue a parameterize SQL query to the database in which the SQL Server can understand and then bring back the results to the Entity Model.
The GetUserFirstName is a method that gets all the User FirstName along with the SysUserID. This method returns an IEnumrable of UserName class. The GetUserDetail() is a method that gets the user detail information based on the ID passed on to the query. This method returns a List of UserDetail class.
One of the cool things about EF is we don’t need to worry about how the query is being constructed because EF will take care of that for you including mapping of the data types from your table columns, mapping relationships between tables, etcetera and etcetera. Always keep in mind that EF is an ORM (Object Relational Mapper) and so we don’t need to deal directly with databases, tables and columns but instead we deal with the objects that is in the Entity and query the data against it using LINQ syntax.
STEP 3: Populating the DropDownList
Now lets proceed to the code behind file of the WebForm and populate the DropDownList. Here's the code block for binding the dropdownlist control in the webform. Typically we do this at Page_Load event within Not IsPostBack block like below:
using System;
using WebAppDemo.Model;
namespace WebAppDemo {
public partial class WebForm2 : System.Web.UI.Page {
private void BindUserNames() {
UserManager userMgr = new UserManager();
ddlUser.DataSource = userMgr.GetUserFirstName();
ddlUser.DataTextField = "FirstName";
ddlUser.DataValueField = "SysUserID";
ddlUser.DataBind();
}
protected void Page_Load(object sender, EventArgs e) {
if (!IsPostBack)
BindUserNames();
}
}
}
running the code above will display something like this in the browser:
STEP 4: Populating the Form with the User Details
Now we already have the list of first names in the DropDownList, the next step is to populate the TextBox in the form with the other user information based on the selected item from the DropDownList. Here's the code block below:
private void PopulateFormFieds(int userID) {
UserManager userMgr = new UserManager();
var result = userMgr.GetUserDetail(userID);
if (result.Count > 0) {
var user = result.First();
tbFirstName.Text = user.FirstName;
tbLastName.Text = user.LastName;
tbContactNumber.Text = user.ContactNumber;
}
else {
//NO RECORDS FOUND.
tbFirstName.Text = string.Empty;
tbLastName.Text = string.Empty;
tbContactNumber.Text = string.Empty;
}
}
protected void ddlUser_SelectedIndexChanged(object sender, EventArgs e) {
PopulateFormFieds(Convert.ToInt32(ddlUser.SelectedItem.Value));
}
As you can see the code above is very straight forward. The PopulateFormFieds() is aboviously a method for populating the TextBox with the user detail information. The first line in the method creates an instance of the UserManager class and call the method GetUserDetail and pass along the userID as the parameter. We then store the results in the result variable and check if the result count is greater than 0. If it is greater than 0 then that means there are data returned based on the query we issued and fill the TextBox with the corresponding field data.
Here's the output below after selecting an item from the DropDownList:
That's it! I hope someone find this post useful.
Technorati Tags:
Entity Framework