Nikolaos Kantzelis ASP.Net Blog

This blog will focus on ASP.NET Framework

Sponsors

About Me

Great Blogs

Using stored procedures with Entity Framework in an ASP.Net application


This is going to be the third post of a series of posts regarding ASP.Net and the Entity Framework and how we can use Entity Framework to access our datastore. You can find the first one here and the second one here.

I have a post regarding ASP.Net and EntityDataSource. You can read it here.I have 3 more posts on Profiling Entity Framework applications. You can have a look at them here,here and here.

In this post I will show you how to select,insert,update,delete data in the database using EF and stored procs. A lot of people use stored procedures when they use EF so they know exactly what hits their database.

I will be using Entity Framework version 4.0 that ships with Visual Studio 2010 and .Net 4.0.  

In this very first post we will create a simple website and use Entity Framework as our data access layer.

I assume that you have access to a version of SQL Server.If you do not, you can download and install the free SQL Server Express edition from here.

1) Launch Visual Studio 2010 (express edition will work fine). Create a new empty website and choose a suitable name for it. Choose C# as the development language.

2) Add a new item to your site, a web form. Leave the default name, Default.aspx

3) Add a new project to your solution, a class library project.Remove the class1.cs file from the project.

4) Create a database in the instance of your SQL Server and name it CompanyEmployees . In this post you will find the SQL Script that creates a table named Employees and a table called Companies.They have various columns and they also have a one to many relationship among them (Foreign Key constraint).

5) Add a new item to your class library project, a ADO.Net Entity Data model. Choose a suitable name for it, e.g CompanyEmployees.edmx.

6) Then the Wizard pops up. Choose "Generate from Database".Choose the database which you will base the model on , CompanyEmployees.Follow exactly the steps 5-9 that you can see in this post to finish the steps of the wizard.Now you have your entity data model. Let's create a stored procedure to select some data from the Employees table.

We will have a stored procedure that takes as an input parameter the country of an employee and return information about this employee.

7) In the query window type,

USE [CompanyEmployees]
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[GetEmployeesByCountry] @Country NVARCHAR(50)
AS
    BEGIN
        SET NOCOUNT ON ;
   
        SELECT  emp.EmpFirstName ,
                emp.EmpLastName ,
                emp.Email ,
                emp.City
        FROM    Employees emp
        WHERE   country = @Country
    END
GO
 

8) Execute the T-SQL and you have the GetEmployeesByCountry stored procedure.Now we want to import this new database object into our Entity Data Model.The stored procedure will become a method in our entity data model. We have to switch to the CompEmp.edmx file and our entity designer and right click on the designer and choose "Update model from database".Select the stored procedure and click "Finish" Have a look at the picture below

 

9) Now we will choose Model Browser. Select  Function Imports  and then choose Add Function Import... and choose the stored procedure GetEmployeesByCountry.

Have a look at the picture below


 

We have to create a custom type-New complex type since our stored procedure does not return a whole entity.

Custom type means basically that will create a custom class with just those properties defined in the procedure/method. When you click "OK", you will have imported the procedure in the model.

Have a look at the CompEmp.Designer.cs file to see the generated code for the procedure and the complex type.

10) Let's use the stored procedure now.The main idea is to have the dropdown list control filled in with all the countries and then as the user picks countries to pass this country as a parameter to the stored procedure and bind the resultset to the Gridview control

Add a dropdown list on the Default.aspx page.Add a GridView control on the page. In the Page_Load event handling routine type,

 

if (!IsPostBack)

 { 
  using (CompanyEmployeesEntities ctx = new CompanyEmployeesEntities())
  {
        

        var query = from myemp in ctx.Employees
                    select myemp.Country;

        DropDownList1.DataSource = query;

        DropDownList1.DataBind();
   }
 }

 11) In the type DropDownList1_SelectedIndexChanged event type,

 using (CompanyEmployeesEntities ctx = new CompanyEmployeesEntities())
        {
var query = ctx.GetEmployeesByCountry(DropDownList1.SelectedValue.ToString());

        GridView1.DataSource = query;
        GridView1.DataBind();
        }

12) Run your application and see all the countries in the dropdown list control. Choose one and see the employee's details for that country printed on the screen.Launch SQL Profiler and have a look at the stored procedure being called and executed.

In the following steps I will create the stored procedures I need to insert,delete,update data from the database.

You will need to bring the stored procedures into the model by hitting "Update model from database..." . Then you will need to map those functions to the Companies entity.

We know that the EF generates its own insert,update and delete statements. We can override the default behaviour by mapping functions to a specific entity. We always need to call the SaveChanges() method and the stored procedure is called instead of the native commands.

 Have a look at the picture below to see the complete mapping.


13) Now we will create another stored procedure to insert a record in the Companies table.In the Query window type,

 

USE [CompanyEmployees]
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[InsertCompany]
    @Companyname NVARCHAR(50)
AS
    BEGIN

        SET NOCOUNT ON ;

       

INSERT  INTO dbo.Companies
                ( CompanyName )
        VALUES  ( @CompanyName )
       

    END



GO

 

14) Add a new web form to the page.Name it StoredProcs.aspx. Add a button on the form. In the Button1_Click() event handling routine type,

 try
   {
    using (CompanyEmployeesEntities ctx = new CompanyEmployeesEntities())
      {
         Company mycompany = new Company()
          {

             CompanyName = "Ficticious Company"

          };

          ctx.AddToCompanies(mycompany);
          ctx.SaveChanges();
          Label1.Text = "Record Inserted";

      }

     }
     catch (Exception ex)
        {

        Response.Write(ex.InnerException.Message);
        }

 

15) Run your application and insert a new record. If you open SQL Profiler you will see that the InsertCompany stored procedure is used instead of the native sql commands.


16) Now we will create another stored procedure to update a record in the Companies table.In the Query window type,

USE [CompanyEmployees]
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[UpdateCompanies]
    @compID INT ,
    @compname NVARCHAR(50)
AS
    BEGIN
        SET NOCOUNT ON ;
        UPDATE  Companies
        SET     CompanyName = @compname
        WHERE   CompanyID = @compID

    END


GO

17) Add a button on the form. In the Button2_Click() event handling routine type

try
  {
    using (CompanyEmployeesEntities ctx = new CompanyEmployeesEntities())
    {

    var mycompany = ctx.Companies.Where(comp => comp.CompanyName == 
"Ficticious Company").First();

    mycompany.CompanyName = "My Fictitious Company";
    ctx.SaveChanges();
    Label1.Text = "Record Update";

     }

  }
        catch (Exception ex)
        {

            Response.Write(ex.InnerException.Message);
        }

18) Run your application and update the existing record. If you open SQL Profiler you will see that the UpdateCompanies stored procedure is used instead of the native sql commands.

19) Now we will create another stored procedure to delete a record in the Companies table.In the Query window type,

USE [CompanyEmployees]
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[DeleteCompanies] @compID INT
AS
    BEGIN

        DELETE  FROM dbo.Companies
        WHERE   CompanyID = @compID

    END

GO

20) Add a button on the form. In the Button3_Click() event handling routine type

try
 {
   using (CompanyEmployeesEntities ctx = new CompanyEmployeesEntities())
   {

 var mycompany = ctx.Companies.Where(comp => comp.CompanyID==1005)
.First();

      ctx.DeleteObject(mycompany);
      ctx.SaveChanges();
      Label1.Text = "Record Deleted";

            }

        }
        catch (Exception ex)
        {

            Response.Write(ex.InnerException.Message);
        }

 

 21) Run your application and delete an existing record.In this sample of mine I just hard coded the CompanyID. If you open SQL Profiler you will see that the DeleteCompanies stored procedure is used instead of the native sql commands.

Drop me an email if you want  the source code.

Hope it helps!!!

 

Comments

Gregor Suttie said:

More articles on stored procs in EF please - good work.

# March 2, 2011 3:47 PM

nikolaosk said:

nice to know that you like my posts,Gregor.There will be more posts on Entity Framework

# March 2, 2011 7:10 PM

Balakrishnan said:

This functionality will work in DotNet 3.5 framework? For me Return as complex type is disabled. What to do please let me know.

Balakrishnan

# September 26, 2011 10:16 AM

nikolaosk said:

@Balakrishnan : you have to upgrade to .net 4.0 and visual studio 2010

have a look at this article

thedatafarm.com/.../a-big-step-for-stored-procedures-in-ef4

# September 26, 2011 10:42 AM

akshamya_flexi said:

Can u plz mail me source code at hegde30@gmail.com and can you plz post some joining of multiple tables and display in view using mvc in c# plz.....

# December 16, 2011 12:39 AM

gotibandhu said:

This is best one article so far I have read online. I would like to appreciate you for

making it very simple and easy.  I have found another nice post related to this post over

the internet which also explained very well. For more details check out this url...

mindstick.com/.../3678ebe0-5c9b-4237-9465-347e15331723

Thanks

# January 5, 2012 10:14 AM

abh said:

I need ur source code

# January 12, 2012 9:56 AM

Doc said:

Thanks! Was able to use this!

# January 30, 2012 1:30 AM

Raja said:

It was a nice article for biginers.

can u send ur source code.

my mail id: rajakulai.s@shriramvalue.com

Thanks

Raja

# March 27, 2012 3:08 AM

Nigel said:

Great Read. This article made it simple and easy to understand

# April 18, 2012 2:54 PM

Dudeinco said:

Great stuff!  Being new to EF, but a long time programmer, stuffing stored procedures into the EF sounded like a pain in the rear before I read this.  It is downright amazing the simplicity of integrating stored procs into EF!

# May 10, 2012 11:33 AM

beef410dc said:

This post just helped me out a lot.  I was trying to update my SQL table based upon a checkbox selection, and I was having a lot of trouble getting it to work.

My solution was based upon your post.  I owe you a big thank you for taking the time and sharing this.  Thanks and I appreciate it.

# June 21, 2012 9:12 PM

Boris said:

Please send me sqlscript creating the database.

It is with teh data?

# July 25, 2012 4:18 AM

Yogesh said:

Plz send source code on yogesh.mau@gmail.com id.

Regards,

Yogesh

# February 26, 2013 1:01 AM