Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • Code-First Stored Procedure Entity Framework 6.0

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 2
    • 0
    • 1.08k
    Comment on it

    Code-First Stored Procedure Entity Framework 6.0

     

    This blog shows configuration of code first making use of stored procedure for the entities. For usage of stored procedure, the OnModelCreating method of DBContext should be overriden,this method will contain code for mapping stored procedure.

     

    protected override void OnModelCreating(DbModelBuilder modelBuilder)  
    {  
        modelBuilder.Entity<DefinedEntity>().MapToStoredProcedures();  
    }  

     

    The MapToStoreProcedures method configures stored procedures for Insert,Delete and Update. This method has two overloaded methods.

     

    The first overloaded method is parameterless MapToStoredProcedures() which will create three stored procedure for Insert,Delete,Update named as <type_name>_Insert, <type_name>_Update and <type_name>_Delete. 
    (For e.g : student_Insert , student_Update, student_Delete)

     

    The second overloaded method is as follows :

    MapToStoredProcedures(Action<ManyToManyModificationStoredProceduresConfiguration<TEntityType,TTargetEntityType>>)

     

    The above declared method takes an action as input. The functionality provided by this method is customization of Stored Procedure name, parameter, schema name and so on.

     


    Example to create stored procedure in code first entity framework 6.0

     

    Define a student Model

     

    using System.Web.Mvc;
    using System.ComponentModel.DataAnnotations;
    using System.ComponentModel.DataAnnotations.Schema;
    using System;
    
    namespace Student_Management_System.Models
    {
        public class Student
        {
            [Key]
            public int student_id { get; set; }
             
            [Required]
            [Display(Name = "First Name")]
            public string Fname { get; set; }
    
            [Required]
            [Display(Name = "Last Name")]
            public string Lname { get; set; }
    
            [Required]
            public string Email { get; set; }
        }
    }

     

    Definition of studentcontext class

     

    using System.Data.Entity;
    using Student_Management_System.Models;
    using System.Diagnostics;
    
    namespace Student_Management_System.DAL
    {
        public class studentcontext : DbContext
        {
            public studentcontext() : base("StudentConnString") {
                Database.Log = message => Trace.WriteLine(message);
            }
            public DbSet<Student> students { get; set; }
    
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
    		    
                modelBuilder.Entity<Student>()
                    .MapToStoredProcedures(s => s.Insert(u => u.HasName("InsertStudent", "dbo"))  //for Insert InsertStudent stored procedure is created 
                                                    .Update(u => u.HasName("UpdateStudent", "dbo"))  //for Update UpdateStudent stored procedure is created 
                                                    .Delete(u => u.HasName("DeleteStudent", "dbo"))  //for Delete DeleteStudent stored procedure is created 
                    );
            }
        }
    }

     

     

    After creating studentcontext class run the migration steps :

     

     

    Step 1 : enable-migrations

     

    Step 2 : add-migration 

    Initial_SP is the file created in Migrations folder by the add migration command. This file will contain definition for all the Stored Procedures.

     

    namespace Student_Management_System.Migrations
    {
        using System;
        using System.Data.Entity.Migrations;
        
        public partial class Initial_SP : DbMigration
        {
            public override void Up()
            {
                CreateStoredProcedure(
                    "dbo.InsertStudent",
                    p => new
                        {
                            Fname = p.String(),
                            Lname = p.String(),
                            Email = p.String(),
                        },
                    body:
                        @"INSERT [dbo].[Students]([Fname], [Lname], [Email])
                          VALUES (@Fname, @Lname, @Email)
                          
                          DECLARE @student_id int
                          SELECT @student_id = [student_id]
                          FROM [dbo].[Students]
                          WHERE @@ROWCOUNT > 0 AND [student_id] = scope_identity()
                          
                          SELECT t0.[student_id]
                          FROM [dbo].[Students] AS t0
                          WHERE @@ROWCOUNT > 0 AND t0.[student_id] = @student_id"
                );
                
                CreateStoredProcedure(
                    "dbo.UpdateStudent",
                    p => new
                        {
                            student_id = p.Int(),
                            Fname = p.String(),
                            Lname = p.String(),
                            Email = p.String(),
                        },
                    body:
                        @"UPDATE [dbo].[Students]
                          SET [Fname] = @Fname, [Lname] = @Lname, [Email] = @Email
                          WHERE ([student_id] = @student_id)"
                );
                
                CreateStoredProcedure(
                    "dbo.DeleteStudent",
                    p => new
                        {
                            student_id = p.Int(),
                        },
                    body:
                        @"DELETE [dbo].[Students]
                          WHERE ([student_id] = @student_id)"
                );
                
            }
            
            public override void Down()
            {
                DropStoredProcedure("dbo.DeleteStudent");
                DropStoredProcedure("dbo.UpdateStudent");
                DropStoredProcedure("dbo.InsertStudent");
            }
        }
    }
    

     

    Step 3 : update-database

    This command will be creating stored procedure named "InsertStudent","UpdateStudent","DeleteStudent" in database. The definition of these stored procedure is as follows :-

     

    //For Insert
    CREATE PROCEDURE [dbo].[InsertStudent]
        @Fname [nvarchar](max),
        @Lname [nvarchar](max),
        @Email [nvarchar](max)
    AS
    BEGIN
        INSERT [dbo].[Students]([Fname], [Lname], [Email])
        VALUES (@Fname, @Lname, @Email)
        
        DECLARE @student_id int
        SELECT @student_id = [student_id]
        FROM [dbo].[Students]
        WHERE @@ROWCOUNT > 0 AND [student_id] = scope_identity()
        
        SELECT t0.[student_id]
        FROM [dbo].[Students] AS t0
        WHERE @@ROWCOUNT > 0 AND t0.[student_id] = @student_id
    END
    
    
    //For Update
    
    CREATE PROCEDURE [dbo].[UpdateStudent]
        @student_id [int],
        @Fname [nvarchar](max),
        @Lname [nvarchar](max),
        @Email [nvarchar](max)
    AS
    BEGIN
        UPDATE [dbo].[Students]
        SET [Fname] = @Fname, [Lname] = @Lname, [Email] = @Email
        WHERE ([student_id] = @student_id)
    END
    
    //For Delete
    
    CREATE PROCEDURE [dbo].[DeleteStudent]
        @student_id [int]
    AS
    BEGIN
        DELETE [dbo].[Students]
        WHERE ([student_id] = @student_id)
    END

     

    Now these stored procedure can be used in controller action :-

     

    using System.Net;
    using System.Web;
    using System.Web.Mvc;
    using Student_Management_System.DAL;
    using Student_Management_System.Models;
    
    namespace Student_Management_System.Controllers
    {
        public class StudentsController : Controller
        {
            private studentcontext db = new studentcontext();
    
            Student stu = new Student();
          
            public ActionResult Index()
            {
                stu.Fname = "suraj";
                stu.Lname = "rana";
                stu.Email = "s@gmail.com";
    
                db.students.Add(stu);   //For add internally "InsertStudent" stored procedure is called
                db.SaveChanges();
    
                return View(db.students.ToList());
            }
         }
    }

     

    The output that for add command internally "InsertStudent" stored procedure is called can be logged by using following code within studentcontext.

     

    public studentcontext() : base("StudentConnString") {
                Database.Log = message => Trace.WriteLine(message);
            }

     

    The following is the SQL logging output of the code above:

     

     

    Code-First Stored Procedure Entity Framework 6.0

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: