ADO.NET Entity Framework Tutorial and Basics

Introducing the ADO.NET Entity Framework

Database development with the .NET framework has not changed a lot since its first release. Many of us usually start by designing our database tables and their relationships and then creating classes in our application to emulate them as closely as possible in a set of Business Classes or (false) "Entity" Classes, and then working with them in out ADO.NET code. However, this process has always been an approximation and has involved a lot of groundwork.

This is where the ADO.NET Entity Framework comes in; it allows you to deal with the (true) entities represented in the database in your application code by abstracting the groundwork and maintenance code work away from you. A very crude description of the ADO.NET Entity Framework would be "It allows you to deal with database concepts in your code."

Of course, it's much more than that, but in this tutorial, instead of starting with a lengthy spiel about the Entity Framework and how it differs from LINQ to Sql, you will code first, talk later.

Contents

In this tutorial, you will go over the following:

  • Page 1: Set up the environment and database, generating the Entity Data Model
  • Page 2: Basic ADO.NET Entity Framework operations with a form for Payrolls
  • Page 3: Adding a little efficiency using another form for Authors
  • Page 4: The case for using stored procedures in the Entity Framework
  • Page 5: Using stored procedures to perform SELECT operations against the database in the Articles form
  • Page 6: Using stored procedures for the INSERT, UPDATE, and DELETE operations in the Articles form
  • Page 7: More information and conclusion

Setting Up Your Environment

For this ADO.NET Entity Framework tutorial, you will need the following:

  • SP1 for .NET Framework 3.5/Visual Studio 2008 (which you can download here.)
  • Some C# knowledge, because the code samples here are in C#
  • A little prior knowledge of ADO.NET and SQL
  • Approximately 250ml to 350ml of trimethylxanthine, otherwise associatedly known as coffee

Setting Up the Database

You can either create your own project, or refer to the project files attached to this article (bottom of page), but I would recommend starting your own and glancing at the attached project files if you need to. Before you start coding, though, you will need to create the database and its objects that will be used and referred to in this tutorial. The DatabaseScript.zip file contains a .sql script that you need to run against your SQL Express or SQL Server database; this script will generate the database for a theoretical publishing company, inventively named PublishingCompany, and the tables and stored procedures required.

Note: You don't need to use SQL Server. You can use any database you'd like, but then you will need to modify the script to work with the SQL implementation for your database. For the purposes of this tutorial, I will continue to refer to SQL Server as the database.

Generating an Entity Data Model in Your Visual Studio Project

Once you are satisfied that the database has been created and you have had a look through all of the tables and its fields, start by creating a new Windows Forms Application project. I suggest the name of the solution to be SodiumHydroxide. I chose this name because I'm hoping that this project will serve as a good base for your learning. (Chemistry pun alert!)

The very first step is to generate your Entity Data Model from the database that you created earlier; this will serve to be at the core of all your ADO.NET Entity Framework operations. To do this, right-click on the project and add a new item. Add an "ADO.NET Entity Data Model" and call it PublisherModel.edmx to correspond to your database.

The Entity Data Model Wizard shows up and you now can use this to query your database and generate the model diagram, as long as you supply it with the right credentials. In the Wizard, click "Generate from Database" and click Next.

Supply it with the right server name, authentication, credentials, and the database name PublishingCompany.

Yes, I do like to name various entities on my home network after arcane Mesoamerican civilizations. Finally, "Save entity connections settings in App.Config as" should be PublishingCompanyEntities.

In the next dialog box, choose all of the options—tables, views, and stored procedures—and the model will be generated for you. You should end up with this:

This is a graphical representation of the Entity Data Model (EDM) that's generated by the wizard. Note that it isn't exactly a table mapping in the database, but it looks close. You'll also see that the Author entity has an article reference and payroll reference, even though you haven't actually created fields in the Author table; this relationship was derived from the foreign key constraint by the EDM generator.

If you are like me, you probably want to know what's happening behind the scenes; you can right-click on the .edmx file in Solution Explorer and choose to view it with an XML Editor. Even if you aren't interested, I would encourage you to look at the XML anyways, because advanced Entity Framework operations will require you to directly edit the XML file, but not for this tutorial. As you can see, the EDM is essentially an XML file that's generated from the database schema, and which is understood by the Visual Studio designer to give you a graphical representation of your database entities.

On the next page, you will start working on the first form with basic Entity Framework operations.

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:

[ado_10.jpg]

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.

[ado_11.jpg]

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.

[ado_11b.jpg]

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

ADO.NET Entity Framework Tutorial and Basics

Adding a Little Efficiency

In the Payroll form, every action performed meant one database call to get the data. For the Authors form, you will use almost all of the same logic as the Payroll form, but take a slightly different approach; we will minimize the number of database calls made by getting all the authors in one call and saving the data changes together. It will feel a little clumsy doing this, but it's only to show you how you can take another approach for basic operations in the Entity Framework.

Create a new form for Authors that looks like this. It has a previous, next, first, last, update, and add new button. The "Clear for new Author" button will simply empty out the controls and "Create New" button will work off it by creating the Author object.

[ado_13.jpg]

Agreed, not the most intuitive interface, but this is just a tutorial. The "Send to Database" is the only button that will make a database call; the rest will manipulate the Author objects.

As before, create the PublishingCompanyEntities object and instantiate it in the form load event. This time, declare a List<Author> as well; this is what you will use to hold the authors. Also, add an indexing integer to hold your current 'position'.

At the top of the class:

PublishingCompanyEntities publishContext;
List<Author> authorList;
int currentAuthorIndex = 0;

In the form's load event:

publishContext = new PublishingCompanyEntities();
authorList     = new List<Author>();

Back in the PayrollView form, you got all the authors using publishContext.Author. This is actually an ADO.NET Entity Query Builder method expression. Query builder methods allow you to use various methods to filter the data you get back. You will read more on these methods later, but for now you should know that the result of most query builder method expression is an ObjectQuery<>, ObjectResult<>, or IQueryable<>. These classes represent the returned entity collections for the queries you perform. You will read more about these later. For the sake of variety, you will use an ObjectQuery<> next. You will get all the authors from the database, except for anyone named Mark, because you don't really care about Mark. For business logic reasons, of course.

ObjectQuery<Author> authorQuery =
   publishContext.Author.Where("it.FirstName <> 'Mark'");
authorList = authorQuery.ToList();
PopulateFields();

Now you have a list of Author objects that you can manipulate in your form. The PopulateFields method will look slightly different.

private void PopulateFields()
{
   Author currentAuthor = authorList[currentAuthorIndex];
   firstName.Text       = currentAuthor.FirstName;
   lastName.Text        = currentAuthor.LastName;
   authorIDLabel.Text   = currentAuthor.AuthorID.ToString();
}

You are traversing the List<>, an in-memory object. The previous, next, first, and last buttons are now easy to implement.

private void firstButton_Click(object sender, EventArgs e)
{
   currentAuthorIndex = 0;
   PopulateFields();
}

private void previousButton_Click(object sender, EventArgs e)
{
   if (currentAuthorIndex == 0)
   {
      MessageBox.Show("No previous author");
   }
   else
   {
      currentAuthorIndex -= 1;
      PopulateFields();
   }
}

private void nextButton_Click(object sender, EventArgs e)
{
   if (currentAuthorIndex == authorList.Count - 1)
   {
      MessageBox.Show("No next author");
      PopulateFields();
   }
   else
   {
      currentAuthorIndex += 1;
      PopulateFields();
   }
}

private void lastButton_Click(object sender, EventArgs e)
{
   currentAuthorIndex = authorList.Count - 1;
   PopulateFields();
}

Run the application (set AuthorView as the startup object) and ensure that the navigation buttons are working.

The Update button is easy too. Get the current author and set its values from the textboxes.

private void update_Click(object sender, EventArgs e)
{
   Author currentAuthor    = authorList[currentAuthorIndex];
   currentAuthor.FirstName = firstName.Text;
   currentAuthor.LastName  = lastName.Text;
}

Note that this only modifies an existing author. Nothing has been sent to the database yet. You can do that now; in the click event for the "Send to database" button,

private void sendToDB_Click(object sender, EventArgs e)
{
   int rowsAffected = publishContext.SaveChanges(true);
   MessageBox.Show(rowsAffected.ToString() + " changes made.");
}

Try it out. Modify an author or authors and see whether your changes make it through to the database. A simple call to publishContext.SaveChanges() works because it still holds a reference to the same objects that were returned from the original ObjectQuery which is in your authorList as well.

Now, try adding a new author. The "Clear for new author" button should clear the fields to make it obvious (or not) that a new author is being created. The "Save New" button should actually create the Author object.

private void clearForNew_Click(object sender, EventArgs e)
{
   firstName.Text     = string.Empty;
   lastName.Text      = string.Empty;
   authorIDLabel.Text = "Not saved yet";
}

private void createNew_Click(object sender, EventArgs e)
{
   Author newAuthor    = new Author();
   newAuthor.FirstName = firstName.Text;
   newAuthor.LastName  = lastName.Text;
   newAuthor.AuthorID  = -1;    //To make it obvious that it's new
   authorList.Add(newAuthor);
   publishContext.AddToAuthor(newAuthor);
   //Set the index to the last, new item.
   currentAuthorIndex = authorList.Count - 1;
   PopulateFields();
}

The key here is to use the AddToAuthor method to add the new Author object to your publishContext 'database'. You also are adding it to the list of authors that you are holding.

Because the list and the publishContext reference the same new Author object, when you add a new author and click "Send to database", you'll notice that the new Author object gets the proper primary key ID instead of the -1 that we placed there. Same principle as before—the new identity is returned and given to the new Author object.

On the next page, you will look at the efficiency of the SQL generated behind the scenes for your queries.

ADO.NET Entity Framework Tutorial and Basics

The Case for Stored Procedures

The AuthorView form isn't that much different from the PayrollView form, but it does show you that you can deal with the data in different ways, depending on your preference. You did manage to cut down the database calls when navigating through the forms,, which is great. But, the profiler trace shows you queries that aren't exactly satisfactory. This is what I got in the profiler when I updated an author:

exec sp_executesql N'update [dbo].[Author]
set [FirstName] = @0, [LastName] = @1
where ([AuthorID] = @2)
',N'@0 nvarchar(6),@1 nvarchar(7),@2 
int',@0=N'Lerroy',@1=N'Jenkins',@2=2

If you've done a lot of enterprise development, you will probably be tearing the last few strands of your remaining hair follicles out right now. Dynamic SQL, that's just not acceptable. In fact, if you were to perform a lot of 'joining' (in a LINQ-to-SQL style) with your objects, you will see some extremely large and convoluted SELECT statements being run against the database in SQL Profiler. If you use an ObjectQuery, you can use its .ToTraceString() method to see the SQL statement that will be used. Go back to the AuthorView form and use a MessageBox to display the ToTraceString of the ObjectQuery:

private void AuthorView_Load(object sender, EventArgs e)
{
   publishContext = new PublishingCompanyEntities();
   authorList = new List<Author>();

   ObjectQuery<Author> authorQuery =
      publishContext.Author.Where("it.FirstName <> 'Mark'");
   authorList = authorQuery.ToList();
   MessageBox.Show(authorQuery.ToTraceString());
   PopulateFields();
}

But the query in this specific example isn't that bad:

SELECT [Extent1].[AuthorID] AS [AuthorID],
[Extent1].[FirstName] AS [FirstName], [Extent1].
[LastName] AS [LastName]FROM [dbo].[Author] AS
[Extent1]WHERE [Extent1].[FirstName] <>
'Mark'

To illustrate this point further (and show you another query method), go back to the AuthorView form and add another label. You will use this label to display the number of articles that this author has written. For this purpose, you will use an ObjectQuery method called Include. What is the Include method? You have seen that query methods will only retrieve what you ask; for instance, in your current query

ObjectQuery<Author> authorQuery =
   publishContext.Author.Where("it.FirstName <> 'Mark'");

you won't have any information about the author's payroll or articles. You only have information about authors not named Mark. If you look at authorList in a quickwatch window after it has been populated, you will see that the Payroll and Article properties of each Author object have no values. The Include method will therefore load the entities associated with the author object, those that you ask for. Go back to the AuthorView form's load event, and modify the ObjectQuery like this to use the Include method.

ObjectQuery<Author> authorQuery =
   publishContext.Author.Where("it.FirstName <>
      'Mark'").Include("Article");

This means get all the authors, except Mark, and for each author, include their associated Article entities. In the PopulateField() method, you now can read the article count.

articleCountLabel.Text = currentAuthor.Article.Count.ToString();

Run the form and you should see the articles count label change for each author. But, did you notice the SQL trace string?

SELECT
[Project1].[AuthorID] AS [AuthorID],
[Project1].[FirstName] AS [FirstName],
[Project1].[LastName] AS [LastName],
[Project1].[C1] AS [C1],
[Project1].[C3] AS [C2],
[Project1].[C2] AS [C3],
[Project1].[ArticleID] AS [ArticleID],
[Project1].[Title] AS [Title],
[Project1].[Body] AS [Body],
[Project1].[AuthorID1] AS [AuthorID1]
FROM ( SELECT
   [Extent1].[AuthorID] AS [AuthorID],
   [Extent1].[FirstName] AS [FirstName],
   [Extent1].[LastName] AS [LastName],
   1 AS [C1],
   [Extent2].[ArticleID] AS [ArticleID],
   [Extent2].[Title] AS [Title],
   [Extent2].[Body] AS [Body],
   [Extent2].[AuthorID] AS [AuthorID1],
   CASE WHEN ([Extent2].[ArticleID] IS NULL)
      THEN CAST(NULL AS int) ELSE 1 END AS [C2],
   CASE WHEN ([Extent2].[ArticleID] IS NULL)
      THEN CAST(NULL AS int) ELSE 1 END AS [C3]
   FROM  [dbo].[Author] AS [Extent1]
   LEFT OUTER JOIN [dbo].[Article] AS [Extent2]
      ON [Extent1].[AuthorID] = [Extent2].[AuthorID]
   WHERE [Extent1].[FirstName] <> 'Mark'
)  AS [Project1]
ORDER BY [Project1].[AuthorID] ASC, [Project1].[C3] ASC

In case you're shaking your head and are about to give in to the temptation of criticizing large, faceless corporations for poor standards, you must keep in mind that in any given system, ease-of-use and the generic-ness is inversely proportional to the efficiency. So, to make it more convenient to query and load objects, there is a slight hit with your SQL statements.

However, this is where the ADO.NET Entity Framework shines through; you can tell it to use your own stored procedures instead of the generated SELECT, UPDATE, DELETE, and INSERT statements. Because stored procedures are generally more efficient than dynamic SQL and because you are given this flexibility with the Entity Framework, this is where the real 'power' of the framework is apparent.

On the next page, you will map stored procedures with the ADO.NET Entity Framework.

ADO.NET Entity Framework Tutorial and Basics

Using Stored Procedures with the ADO.NET Entity Framework

Any proper enterprise environment will have a DBA or a set of DBAs who guard and watch over their databases in the same way that a mother bear watches over her cub. If you inappropriately step between the DBAs and the DBOs, you will get mauled. This means that even with the ADO.NET Entity Framework, they will still want to retain ownership of the database and the objects in it. The ADO.NET Entity Framework allows you to use most of the application code as you did before, but with stored procedures that you or they may have written in the most 'optimal' way.

It'll take a bit of work to get this set up and running, but the effort pays off in the end. You are going to work on using stored procedures with the Article entities on a new form, first starting with a few tasks that you're already familiar with, and then get on to the good stuff.

  1. Creating the SELECT stored procedure(s)
  2. Layout of the ArticleView form
  3. Importing the stored procedure
  4. Using the stored procedure to get entities in our code
  5. Another stored procedure, and using it
  6. Navigation and update code
  7. Ta-daa!

Obviously, the first step is to create the SELECT stored procedure.

CREATE PROCEDURE GetArticle
   @ArticleID INT
AS
BEGIN
   SET NOCOUNT ON;
   SELECT ArticleID, Title, Body, AuthorID
   FROM Article
   WHERE ArticleID = @ArticleID
END
GO

Next, you need to import this newly created stored procedure into your EDM. There are two ways to do this: You can regenerate the EDM and import it via the designer, or you can go directly into the XML and edit it there. You will cover the visual method in this tutorial.

  1. Right-click anywhere in the Entity Designer view and click "Update Model from Database".
  2. Choose Tables and Stored procedures from the dialog box and click Finish.
  3. Next, open the Model Browser tab and search for the newly created stored procedure, GetArticle.
  4. Right-click on it and choose "Create Function Import".
  5. Set the return type as the Article Entities.

[ado_14.jpg]

[ado_15.jpg]

Stored procedures within the EDM are imported as functions, and a function that you import always returns a collection of entities. In future releases, this may change to allow a stored procedure to return a single entity. The result of a stored procedure goes into an ObjectResult<>, similar to how publishContext.Author's return type was ObjectQuery<>.

To see it working, quickly create an ArticleView form. Add a new form to the solution, ArticleView, with these controls.

[ado_16.jpg]

A little simple for now, but you'll expand it as you go along. In the code,

Top of the class:

PublishingCompanyEntities publishContext;
Article currentArticle;

In the form's load event:

private void ArticleView_Load(object sender, EventArgs e)
{
   publishContext = new PublishingCompanyEntities();
   currentArticle = new Article();
   ObjectResult<Article> articleQuery =
      publishContext.GetArticle(1);
   currentArticle = articleQuery.ToList().First();
   PopulateFields();
}

And your old friend:

private void PopulateFields()
{
   articleIDLabel.Text = currentArticle.ArticleID.ToString();
   titleText.Text      = currentArticle.Title;
   bodyText.Text       = currentArticle.Body;
}

This time, you will notice that you make an explicit call to the GetArticle stored procedure, passing it the ArticleID 1. Run the form and you'll see the first article loaded up. And, it's done using your GetArticle stored procedure. This is good because it means that you can optimize complicated queries if you need to and use stored procedures to help you.

However, in this particular case, when you introduce navigation buttons to the ArticleView form, you'll have to make a new stored procedure call for each button click event (for each ID). Avoid that situation and get all of the Articles in one go instead. Create a GetArticles (plural) stored procedure now.

CREATE PROCEDURE GetArticles
AS
BEGIN
   SET NOCOUNT ON;
   SELECT ArticleID, Title, Body, AuthorID
   FROM Article
END
GO

Import the GetArticles function as shown earlier. You then can use an ObjectResult<Article>, convert it ToList(), and assign it to a List<> object.

Top of the class:

PublishingCompanyEntities publishContext;
List<Article> articleList;
int currentArticleIndex = 0;

Form load:

publishContext = new PublishingCompanyEntities();
articleList = new List<Article>();
IEnumerable<Article> articleQuery =
   from ar in publishContext.GetArticles()
   select ar;
articleList = articleQuery.ToList();
PopulateFields();

I used a LINQ-to-Entities query instead of a method expression, hoping you would notice the flexibility available to you. You can introduce your filters into the expression and it won't affect the SP call. To illustrate, just as a test:

IEnumerable<Article> articleQuery =
   from ar in publishContext.GetArticles()
   where ar.ArticleID > 5
   select ar;

This will perform a GetArticles SP call and then filter the values returned afterwards. However, you're not interested in filtering it right now, so remove the where clause from the LINQ expression.

Again, there is a PopulateFields method in this form that changes slightly.

private void PopulateFields()
{
   Article currentArticle = articleList[currentArticleIndex];
   articleIDLabel.Text    = currentArticle.ArticleID.ToString();
   titleText.Text         = currentArticle.Title;
   bodyText.Text          = currentArticle.Body;
}

Run the form and make sure that the first article still shows.

Now, go back to the form designer and add the navigation buttons. Also, add an "Update" button. a "Clear for new" button. an "Add as new article" button. and a "Delete" button. Same principles as before—you navigate through the List<> for the navigation buttons, update an object's properties in the List<> for the Update button, clear the fields for the "Clear for new" button, and add a new object to the publishContext for "Add as new article".

[ado_17.jpg]

Based on work done in the past few pages, you must have an idea of what the various buttons will do now, so I'll simply list the code for the buttons here, and then you can get down to the main point of this task—using stored procedures for INSERT, UPDATE, and DELETE.

private void firstButton_Click(object sender, EventArgs e)
{
   currentArticleIndex = 0;
   PopulateFields();
}

private void previousButton_Click(object sender, EventArgs e)
{
   if (currentArticleIndex > 0)
   {
      currentArticleIndex -= 1;
      PopulateFields();
   }
   else
   {
      MessageBox.Show("No more articles to display");
   }
}

private void nextButton_Click(object sender, EventArgs e)
{
   if (currentArticleIndex == articleList.Count - 1)
   {
      MessageBox.Show("No more articles to display");
   }
   else
   {
      currentArticleIndex += 1;
      PopulateFields();
   }
}

private void lastButton_Click(object sender, EventArgs e)
{
   currentArticleIndex = articleList.Count - 1;
   PopulateFields();
}

private void updateButton_Click(object sender, EventArgs e)
{
   Article currentArticle = articleList[currentArticleIndex];
   currentArticle.Title = titleText.Text;
   currentArticle.Body = bodyText.Text;
}

private void clearForNewButton_Click(object sender, EventArgs e)
{
   articleIDLabel.Text = "-1";
   titleText.Text = string.Empty;
   bodyText.Text = string.Empty;
}

private void saveAsNew_Click(object sender, EventArgs e)
{
   Article newArticle = new Article();
   newArticle.Title = titleText.Text;
   newArticle.Body = bodyText.Text;
   newArticle.ArticleID = -1;
   publishContext.AddToArticle(newArticle);
   articleList.Add(newArticle);
   currentArticleIndex = articleList.Count - 1;
   PopulateFields();
}

private void deleteButton_Click(object sender, EventArgs e)
{
   Article currentArticle = articleList[currentArticleIndex];
   publishContext.DeleteObject(currentArticle);
   articleList.Remove(currentArticle);
   currentArticleIndex = 0;
   PopulateFields();
}

private void submitToDatabase_Click(object sender, EventArgs e)
{
   publishContext.SaveChanges();
   PopulateFields();
}

Note that although the code looks just like it did in the AuthorView form, when you do map your stored procedures, you won't have to change any of the code.

On the next page, you can (finally!) map the INSERT, UPDATE, and DELETE stored procedures.

ADO.NET Entity Framework Tutorial and Basics

.NET 3.5 SP1, Visual Studio 2008, C#, ADO.NET



About the Author

SM Altaf

Mendhak is a web developer and a Microsoft MVP who works with ASP.NET and PHP among the usual array[] of web technologies. He is also rumored to be a three eyed frog, but the evidence is lacking. He can be contacted via his website, www.mendhak.com.


Downloads

Comments

  • There are no comments yet. Be the first to comment!

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • This paper introduces IBM Java on the IBM PowerLinux 7R2 server and describes IBM's implementation of the Java platform, which includes IBM's Java Virtual Machine and development toolkit.

  • Targeted attacks and advanced threats are customized to infiltrate your unique IT infrastructure, evade conventional defenses, and remain hidden while stealing your corporate data. To detect these criminal intrusions, analysts and security experts agree that organizations should deploy advanced threat protection as part of an expanded security monitoring strategy. For this comparative analysis of breach detection systems, product analysis reports and comparative analysis reports are used to create the security …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds