Posted by: Cirilo Meggiolaro | 02/17/2009

Tip of the day #126 – Querying stored procedures with ADO.NET Entity Framework

Let’s check today how to execute a stored procedure that returns a list of customers using ADO.NET Entity Framework.

How to…

For this example I created a database with a table that stores customer information (a simple version for brevity) with the following columns: customer_id, customer_name, customer_dob, customer_active. I also created a simple stored procedure that lists all customers that are active (have the customer_active column equals true);

So to start our .NET application, create one console application (or any other that you want) and follow these steps:

  • Add a new ADO.NET Entity Data Model file from the Solution Explorer window, right-clicking and selecting add new item from the context menu;
  • It starts a wizard. The first step is to choose between create a model from the database or an empty one. Select the Generate from Database option and click next;
Picture 1 - Entity Data Model Wizard

Picture 1 - Entity Data Model Wizard

  • The second step is to create a connection to your database. Click the New Connection button and type the server and database names. Click the Test Connection button to make sure everything is ok and click ok. Back to the wizard, you are going to see on the bottom part of the screen a checkbox and a textbox where you can choose if you want to store the connection string in the config file and the key name. Click next to continue;
Picture 2 - Entity Data Model Wizard (Connection)

Picture 2 - Entity Data Model Wizard (Connection)

  • The next step is to select the objects you are going to retrieve. For this example I have selected the customer table and the GetActiveCustomer stored procedure;
Picture 3 - Entity Data Model Wizard (Object Selection)

Picture 3 - Entity Data Model Wizard (Object Selection)

  • Enter the model name that will be used from the code and click finish. Your model is going to be generated;
  • The customer entity is displayed on the edmx file;
Picture 4 - Entity Data Model Wizard

Picture 4 - Entity Data Model Wizard

  • Right click the edmx file and from the context menu select Add > Function Import;
Picture 5 - The context menu to import the stored procedure

Picture 5 - The context menu to import the stored procedure

  • The following screen will be displayed;
Picture 6 - Add Function Import Dialog

Picture 6 - Add Function Import Dialog

  • Select the stored procedure name, type a name to identify the stored procedure on the code and select the return type. For this example, I selected entities and from the combobox select customer. Click ok.
  • Now you may view the stored procedure on the browser model window;
Picture 7 - Model Browser Window

Picture 7 - Model Browser Window

  • You may use a simple syntax to loop through all items retrieved by the stored procedure as shown on the code snippet below:

/// Creates a new instance from the entity context.
using (SmoTestEntities ctx = new SmoTestEntities())
{
    /// Loops through the Customer collection retrieved
    /// by the GetActiveCustomers stored procedure.
    foreach (Customer c in ctx.GetActiveCustomers())
    {
        /// Displays the customer information.
        Console.WriteLine(“ID: {0} – Name: {1} – Date of Birth: {2}”,
                    c.customer_id, c.customer_name, c.customer_dob);
    }
}


Responses

  1. Nice tutorial. Easy to follow.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: