EF Code First Approach Stored Procedure With Parameter

Introduction

 
This article shows how to access a SQL Server database with the Entity Framework Code First Approach and later we will also look at how to use a Stored Procedure with the Fluent API.
 
Step 1: Create an ASP.Net web application
 
 
Employee.cs
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.ComponentModel.DataAnnotations;  
  4. using System.ComponentModel.DataAnnotations.Schema;  
  5. using System.Linq;  
  6. using System.Web;  
  7. namespace CodeFirst_SP_WithParameterApp  
  8. {  
  9.     public class Employee   
  10.     {  
  11.         public Employee() {}  
  12.         [Key]  
  13.         [DatabaseGenerated(DatabaseGeneratedOption.Identity)]  
  14.         public int Id   
  15.         {  
  16.             get;  
  17.             set;  
  18.         }  
  19.         public string FirstName   
  20.         {  
  21.             get;  
  22.             set;  
  23.         }  
  24.         public string LastName   
  25.         {  
  26.             get;  
  27.             set;  
  28.         }  
  29.     }  
  30. }  
Employeecontext.cs
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Data.Entity;  
  4. using System.Linq;  
  5. using System.Web;  
  6. namespace CodeFirst_SP_WithParameterApp  
  7. {  
  8.     public class EmployeeContext: DbContext   
  9.     {  
  10.         public EmployeeContext(): base("EmployeeConn")   
  11.         {  
  12.             Database.SetInitializer < EmployeeContext > (new CreateDatabaseIfNotExists < EmployeeContext > ());  
  13.         }  
  14.         public DbSet < Employee > Employees  
  15.         {get;set;}  
  16.         protected override void OnModelCreating(DbModelBuilder modelBuilder) {  
  17.             modelBuilder.Entity < Employee > ()  
  18.             .MapToStoredProcedures(p = > p.Insert(sp = > sp.HasName("sp_InsertStudent").Parameter(pm = > pm.FirstName, "FirstName").Result(rs = > rs.Id, "Id")));  
  19.         }  
  20.     }  
  21. }  
Web.config
  1. <connectionStrings>  
  2.     <add name="EmployeeConn"    
  3.   connectionString="Data Source=WIN-B4KJ8JI75VF;Initial Catalog=EmployeeDB;Integrated Security=true"    
  4.   providerName="System.Data.SqlClient"/>  
  5. </connectionStrings>    
Webform1.aspx
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.UI;  
  6. using System.Web.UI.WebControls;  
  7. namespace CodeFirst_SP_WithParameterApp  
  8. {  
  9.     public partial class WebForm1: System.Web.UI.Page  
  10.     {  
  11.         protected void Page_Load(object sender, EventArgs e)   
  12.         {  
  13.             EmployeeContext empContext = new EmployeeContext();  
  14.             Employee emp = new Employee()   
  15.             {  
  16.                 FirstName = "Haney",  
  17.                 LastName = "Jow"  
  18.             };  
  19.             empContext.Employees.Add(emp);  
  20.             empContext.SaveChanges();  
  21.         }  
  22.     }  
  23. }    

Summary

 
In this article, we saw how to access a SQL Server database with the Entity Framework Code First Approach and how to use a Stored Procedure with the Fluent API. Happy coding.