ADO.NET Entity Framework Tutorial and Basics

Basic ADO.NET Entity Framework Operations

Simplest stuff first; now that you're set up, you can start by creating a form to traverse through all the payrolls. To do this, you'll need a combobox that will enumerate the author names, a label, and a textbox to display the details of that author's payroll if it exists (keep in mind that some authors may not be on the payroll). Start by creating a form like this:

Name it PayrollView and give the controls appropriate names. For now, you'll just populate the combobox with the author names. In the form's code, at the top of the class, add this:

PublishingCompanyEntities publishContext;
Payroll currentPayroll;

And in the form load event, instantiate the publishContext object.

publishContext = new PublishingCompanyEntities();

In the form closing event, always dispose it.

publishContext.Dispose();

This PublishingCompanyEntities object—publishContext—is very important; it serves as the basis of all the ADO.NET Entity queries that you will be using. To watch it at work at its most basic level, populate the combobox with a list of authors. In the form's load event, add this:

//This is a simple ADO.NET Entity Framework query!
authorList.DataSource = publishContext.Author;
authorList.DisplayMember = "FirstName";

In the code above, authorList is the name of the combobox. Press F5 and watch the form load. You should see the combobox with the author names in it! Loop through that list for a bit and marvel at your handiwork.

Behind the scenes, when you set the DataSource and DisplayMember of the combobox to the publishContext.Author property, the publishContext performed the query against the database and returned the results for the combobox to use. You didn't have to open a connection or create a command; the housework was taken care of by the publishContext object.

Now, you can populate the textboxes that represent the payroll properties for each author. Handle the combobox's SelectedIndexChanged event. Add this code to the event:

Author selectedAuthor = (Author)authorList.SelectedItem;
int selectedAuthorID  = selectedAuthor.AuthorID;

//Uses Linq-to-Entities
IQueryable<Payroll> payrollQuery =
   from p in publishContext.Payroll
   where p.Author.AuthorID == selectedAuthorID
   select p;
List<Payroll> selectedPayroll = payrollQuery.ToList();

if (selectedPayroll != null && selectedPayroll.Count > 0)
{
   currentPayroll = selectedPayroll.First();
}
else
{
   currentPayroll = null;
}

PopulateFields();

In the code above, you do the following:

1. Get the current Author object from the combobox by looking at the SelectedItem property.
2. Use a LINQ-to-Entities query against publishContext to filter the Payrolls on the AuthorID.
3. The return type for the LINQ-to-Entities query is IQueryable<>, which you convert to a List<>.
4. Check whether it has values and get the first row from the returned results because you only want one author's payroll.
5. Assign this value to the currentPayroll object which then is used in your common PopulateFields method.

The PopulateFields method, shown below, simply reads the properties of the Payroll object and places the value in corresponding labels/textboxes.

private void PopulateFields()
{
   if (currentPayroll != null)
   {
      payrollIDLabel.Text  = currentPayroll.PayrollID.ToString();
      salaryUpDown.Value   = (decimal)currentPayroll.Salary;
      addButton.Enabled    = false;
      deleteButton.Enabled = true;
      updateButton.Enabled = true;
   }
   else
   {
      payrollIDLabel.Text  = "Not on payroll";
      salaryUpDown.Value   = 0;
      addButton.Enabled    = true;
      deleteButton.Enabled = false;
      updateButton.Enabled = false;
   }
}

Run the application again, and when you select different authors from the combobox, you should get their corresponding salaries. You'll also see that the "Add" button is disabled for authors with payrolls, and enabled for authors without payrolls.

Coding the Update and Delete buttons

Because this is a simple example, the only property that the user can modify is the author's Salary. In the Update event, set the currentPayroll's Salary property to be the value of the numeric up-down control. Then, simply call the SaveChanges method on publishContext. Here is the Update button's click event:

currentPayroll.Salary = Convert.ToInt16(salaryUpDown.Value);
int rowsAffected = publishContext.SaveChanges();
MessageBox.Show(rowsAffected.ToString() +
                " changes made to the table");

The SaveChanges method is akin to the dataadapter's Update method in regular ADO.NET; it will go through the collection of objects for 'marked' entities, and then update them in the database.

In the Delete button's click event, use the publishContext's DeleteObject method before calling SaveChanges().

publishContext.DeleteObject(currentPayroll);
publishContext.SaveChanges(true);
currentPayroll = null;
PopulateFields();

You called the DeleteObject method, passing it the current Payroll object, which marks it for deletion. You then called the SaveChanges method that performs the deletion.

Run the form again. Update a few salaries and try deleting one or two payrolls. Note that this method will delete the payroll associated with an author; it will not delete the author itself.

Coding the Add button

The Add button's click event will require a little more code. You first must create a brand new Payroll object, assign it values, and then call the AddToPayroll() method of the publishContext. The AddTo<EntityName> methods are generated by the Entity Framework based on the entities it generated from the database—all entities have one. It will perform the INSERT against the database and return the PayrollID of the new row in the table.

Payroll newPayroll    = new Payroll();
Author selectedAuthor = (Author)authorList.SelectedItem;
newPayroll.Author     = selectedAuthor;
newPayroll.Salary     = Convert.ToInt16(salaryUpDown.Value);
publishContext.AddToPayroll(newPayroll);
//INSERT against database and get new PayrollID
publishContext.SaveChanges();
//newPayroll.PayrollID now matches the database table
currentPayroll = newPayroll;
PopulateFields();

Because PopulateFields is called right at the end, you will see that after you add a new Payroll to the database, the PayrollID label has been filled with the new value. Again, the Entity Framework has taken care of the new ID and assigned it to newPayroll.PayrollID for you to use.

Run your form and, if you haven't deleted any authors yet, do so now. Once you delete the author, their ID label will say "Not on payroll" and their salary will be 0. Modify the salary and click "Add". Your form now displays authors and their payrolls, and allows you to add, update, and delete payrolls. Have a play with it and marvel at your handiwork again before you continue.

On the next page, you will use the same concepts learned here, but with a little more efficiency.