We can perform CURD operation in Code First approach
using stored procedure.
Step(1): For creating Code First model using Stored
Procedure we have to override the OnModelCreating method of
DBContext .
We have to add this code
|
protected override void OnModelCreating(DbModelBuilder Builder)
{
Builder.Entity<yourEntity>().MapToStoredProcedures(your Link query);
}
|
The MapToStoreProcedures
method has two type of overloaded methods, First one is without a parameter.
This method uses the Code-First Entity framework method for the Stored Procedure. The second method takes parameters as an input and we can customize Stored Procedure name,
parameters, and so on.
We will understand this with an example
Step(2): Add a 2 class for “ Department.cs” and
“Student.cs” after that give the parameters name
“Department.cs” class is
like that
|
using
System;
using
System.Collections.Generic;
using
System.ComponentModel.DataAnnotations;
using
System.ComponentModel.DataAnnotations.Schema;
using
System.Linq;
using
System.Text;
using
System.Threading.Tasks;
namespace
CodeFirstStoredProcedureEntityFramework
{
public class Department
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int DepartmentId { get; set; }
public string Subject { get; set; }
public List< Student > Students{
get; set; }
}
}
|
“Student.cs” class is
like that
|
using
System;
using
System.Collections.Generic;
using
System.ComponentModel.DataAnnotations;
using
System.ComponentModel.DataAnnotations.Schema;
using
System.Linq;
using
System.Text;
using
System.Threading.Tasks;
namespace
CodeFirstStoredProcedureEntityFramework
{
public class Student
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int StudentId { get; set; }
public string StudentName { get; set;
}
public int DepartmentId { get; set; }
public Department Department { get;
set; }
}
}
|
Step(3): Now Add a
“Context” class, In this class we will use OnModelCreating method for mapping with StoredProcedure and “Student”
Entity.
|
using
System;
using
System.Collections.Generic;
using
System.Data.Entity;
using
System.Linq;
using
System.Text;
using
System.Threading.Tasks;
namespace
CodeFirstStoredProcedureEntityFramework
{
public class StoredProcedureEntitiesContext
: DbContext
{
public StoredProcedureEntitiesContext()
: base("name=Entities")
{
Database.Log = Console.WriteLine;
}
public DbSet<Department>
Departments { get; set; }
public DbSet< Student > Students
{ get; set; }
protected override void
OnModelCreating(DbModelBuilder Builder)
{
Builder.Entity< Student >()
.MapToStoredProcedures(s
=> s.Insert(u => u.HasName("Insert StudentDetail ",
"dbo"))
.Update(u => u.HasName("Update StudentDetail ",
"dbo"))
.Delete(u
=> u.HasName("Delete StudentDetail ", "dbo"))
);
}
}
}
|
Step(3): Enable
Migration
1.
Tools -> Nuget Package manager ->
package manager consol
In
console screen write this
enable-migrations -ContextTypeName
CodeFirstStoredProcedure.EntitiesContext
-MigrationsDirectory:EntitiesMigrations
After this Add Migration Configuration..
-> write this below command in console screen
Add-Migration -configuration CodeFirstStoredProcedure.EntitiesMigrations.Configuration InitialEntities
Purpose for Adding Migration command..
We add migration
command because its generates the Dbmigration class , and this Dbmigration class has
the definition of All the stored procedure.
Step(4): Add a class For writing stored procedure
|
namespace CodeFirstStoredProcedureEntityFramework.EntitiesMigrations
{
using
System;
using
System.Data.Entity.Migrations;
public partial
class StoredProcedure : DbMigration
{
public
override void Up()
{
CreateStoredProcedure(
"dbo.InsertStudentInfo",
p => new
{
StudentName =
p.String(),
DepartmentId =
p.Int(),
},
body:
@"INSERT [dbo].[StudentInfo]([StudentName], [DepartmentId])
VALUES (@Name, @DepartmentId)
DECLARE @StudentId int
SELECT @StudentId = [StudentId]
FROM [dbo].[StudentInfo]
WHERE @@ROWCOUNT > 0 AND [StudentId] = scope_identity()
SELECT t0.[StudentId]
FROM [dbo].[StudentInfo] AS t0
WHERE @@ROWCOUNT > 0 AND t0.[StudentId] = @StudentId"
);
CreateStoredProcedure(
"dbo.UpdateStudentInfo",
p => new
{
StudentId =
p.Int(),
StudentName =
p.String(),
DepartmentId =
p.Int(),
},
body:
@"UPDATE [dbo].[StudentInfo]
SET [StudentName] = @StudentName, [DepartmentId] = @DepartmentId
WHERE ([StudentId] = @StudentId)"
);
CreateStoredProcedure(
"dbo.DeleteStudentInfo",
p => new
{
StudentId = p.Int(),
},
body:
@"DELETE [dbo].[StudentInfo]
WHERE ([StudentId] = @StudentId)"
);
}
public
override void Down()
{
DropStoredProcedure("dbo.DeleteStudentInfo");
DropStoredProcedure("dbo.UpdateStudentInfo");
DropStoredProcedure("dbo.InsertStudentInfo");
}
}
}
|
Step(5): Update
Database
Update-Database -configuration:CodeFirstStoredProcedure.EntitiesMigrations.Configuration -Verbose
Update-Database -configuration:CodeFirstStoredProcedure.EntitiesMigrations.Configuration -Verbose
Step(6): After
Executing this stored procedure.
When you will execute this
Stored procedure this will create 3 stored procedure and the definition will be
like this.
|
CREATE PROCEDURE [dbo].[InsertStudentinfo]
@Name
[nvarchar](max),
@DepartmentId [int]
AS
BEGIN
INSERT
[dbo].Studentinfo([StudentName], [DepartmentId])
VALUES (@StudentName,
@DepartmentId)
DECLARE
@StudentId int
SELECT
@StudentId = [@StudentId]
FROM
[dbo].Studentinfo
WHERE @@ROWCOUNT
> 0 AND [@StudentId] = scope_identity()
SELECT
t0.[@StudentId]
FROM
[dbo].Studentinfo AS t0
WHERE @@ROWCOUNT
> 0 AND t0.[@StudentId] = @@StudentId
END
GO
CREATE PROCEDURE [dbo].[UpdateStudentinfo]
@StudentId [int],
@StudentName
[nvarchar](max),
@DepartmentId [int]
AS
BEGIN
UPDATE
[dbo].Studentinfo
SET
[Code] = [Name] = @StudentName, [StudentId] = @StudentId
WHERE ([StudentId]
= @StudentId)
END
GO
CREATE PROCEDURE [dbo].[DeleteStudentinfo]
@StudentId [int]
AS
BEGIN
DELETE
[dbo].[StudentInfo]
WHERE ([StudentId]
= @StudentId)
END
|
Step(7): Insert
Record from code
|
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace CodeFirstStoredProcedureEntityFramework
{
class
Program
{
static void Main(string[] args)
{
using (EntitiesContext context = new EntitiesContext())
{
Studentinfo Detail = new
Student();
Detail.Name = "Munesh
Sharma";
Detail.StudentId = 1;
context.Student.Add(Detail);
context.SaveChanges();
Console.ReadLine();
}
}
}
}
|
When you will run your
application it will update a record in database.
