EF Code First Approach Stored Procedure With Parameter

Vijay Prativadi
4y
16.2k
0
3

Article

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

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Web;
namespace CodeFirst_SP_WithParameterApp
{
public class Employee
{
public Employee() {}
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int Id
{
get;
set;
}
public string FirstName
{
get;
set;
}
public string LastName
{
get;
set;
}
}
}

Employeecontext.cs

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Web;
namespace CodeFirst_SP_WithParameterApp
{
public class EmployeeContext: DbContext
{
public EmployeeContext(): base("EmployeeConn")
{
Database.SetInitializer < EmployeeContext > (new CreateDatabaseIfNotExists < EmployeeContext > ());
}
public DbSet < Employee > Employees
{get;set;}
protected override void OnModelCreating(DbModelBuilder modelBuilder) {
modelBuilder.Entity < Employee > ()
.MapToStoredProcedures(p = > p.Insert(sp = > sp.HasName("sp_InsertStudent").Parameter(pm = > pm.FirstName, "FirstName").Result(rs = > rs.Id, "Id")));
}
}
}

Web.config

<connectionStrings>
<add name="EmployeeConn"
connectionString="Data Source=WIN-B4KJ8JI75VF;Initial Catalog=EmployeeDB;Integrated Security=true"
providerName="System.Data.SqlClient"/>
</connectionStrings>

Webform1.aspx

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace CodeFirst_SP_WithParameterApp
{
public partial class WebForm1: System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
EmployeeContext empContext = new EmployeeContext();
Employee emp = new Employee()
{
FirstName = "Haney",
LastName = "Jow"
};
empContext.Employees.Add(emp);
empContext.SaveChanges();
}
}
}

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.