The first task
we'll do is to load the drop down list in the page load event with the list of
records in the UserAccounts table. We'll also add an extra entry in the list
to allow the user to select the option of creating a new user.
1.
Double click on the web form in Design view to create the Page_Load
event in the code behind.
2.
Add the following code to the Page_Load event.
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
LoadUserDropDownList();
}
}3.
The LoadUserDropDownList is a custom method that you must create.
private void LoadUserDropDownList()
{
using (OrderDBContainer db = new OrderDBContainer())
{
ddlUsers.DataSource = from u in db.UserAccounts
orderby u.LastName
select new { Name = u.LastName + ", " + u.FirstName, Id = u.Id };
ddlUsers.DataTextField = "Name";
ddlUsers.DataValueField = "Id";
ddlUsers.DataBind();
ddlUsers.Items.Insert(0, new ListItem("Create New User", ""));
}
}This method creates and an instance of the OrderDBContainer
class which was created when you created the OrderDB.edmx file. This object acts
similar to a connection object in ADO.NET. You use the OrderDBContainer to
"connect" to the database and manipulate the entities defined within
it. The drop down list's DataSource source property is set to the results of a
LINQ query. The Entity Framework will translate this syntax into a SQL
statement. The syntax for writing LINQ queries takes some time to get used to
because it's backwards from SQL. The FROM clause comes first and the SELECT
clause comes last. In this example, I'm selecting all the records from the
UserAccounts table and ordering them by their last name. In the select clause
I'm creating a dynamically generated object with two properties called Name and
Id. The Name is what will be displayed to the user in the drop down list.
I'm concatenating the Last and First name and separating them by a comma.
The DataTextField is then set to "Name" which is the
property in the dynamically created object. The DataValueField is then set to
"Id". The next line binds the data to the drop down list. The call
to the database doesn't actually get made until this line is executed. The
last line adds a new item to the list in the first position. The text of the
item is "Create New User" and this will be used to determine if the
user is adding or updating an existing user.
Set this page as
the startup page and run the project. There are no records in the table yet so
all you'll see is the "Create New User" entry in the drop down list.
If you were to turn on SQL Server Profiler you would see the SQL statement that
the Entity Framework executed against the database to retrieve the records.
SELECT
[Project1].[Id] AS [Id],
[Project1].[C1] AS [C1]
FROM ( SELECT
[Extent1].[Id] AS [Id],
[Extent1].[LastName] AS [LastName],
[Extent1].[LastName] + N', ' + [Extent1].[FirstName] AS [C1]
FROM [dbo].[UserAccounts] AS [Extent1]
) AS [Project1]
ORDER BY [Project1].[LastName] ASC
