Friday, 17 March 2017

How to call Stored Procedure in Entity Framework 6 (Code-First)


    I have the following classes:
     public class LoanProgramInfoes
    {
      //  [Key]
       // [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int LoanProgramInfoId { get; set; }
        public int LoanProgramID { get; set; }
        public string LoanProgramCode { get; set; }
        public string LoanProgramName { get; set; }
     
    }

My context class is as in the following. In this class, I overrode the OnModelCreating method to map the Identity column with the LoanProgramInfoes entity.

 public class DbContextDemo:DbContext
    {
        public DbContextDemo()
            : base("PortalContext")
        {
        }
      
        public virtual DbSet<LoanProgramInfoes> LoanProgramInfoDbSet { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            Database.SetInitializer<DbContext>(null);

              modelBuilder.Entity<LoanProgramInfoes>().HasKey(x => x.LoanProgramInfoId);
            modelBuilder.Entity<LoanProgramInfoes>()
                .Property(x => x.LoanProgramInfoId)
                .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

        }

    }

 Now i am calling SP using entity frame work 

 using (var context = new DbContextDemo())

            {
 using (var dbContextTransaction = context.Database.BeginTransaction())
                {
                    try

                    {
                     var searchdata = context.Database.SqlQuery<LoanProgramInfoes>(
                    "proc_GetLoanProgramInfo @LoanProgramInfoId,@LoanProgramID",
                    new SqlParameter("@LoanProgramInfoId", loanProgramInfoId),
                    new SqlParameter("@LoanProgramID", loanProgramId))
    .Select(x => new LoanProgramInfoes()
                        {
                        LoanProgramID = x.LoanProgramID,
                        LoanProgramInfoId = x.LoanProgramInfoId,
                        LoanProgramCode = x.LoanProgramCode,
                        LoanProgramName = x.LoanProgramName
                       

                   }).ToList();
                  foreach (var itemvalue in searchdata)
                        {
                                Console.WriteLine("LoanProgramID  {0}  of LoanProgramCode {1}  and its name -                                 {2}",  itemvalue.LoanProgramID, itemvalue.LoanProgramCode,                                                               itemvalue.LoanProgramName);

                        }
               dbContextTransaction.Commit();
             }
              catch (Exception ex)
                    {
                        Console.WriteLine(ex);
                        dbContextTransaction.Rollback();
                        

                    }
       }
}

Like that you can call for update and delete .
In case of select query no need to use Transaction, you can remove it.

1 comment:

  1. Betfair Hotel Casino & Spa - MapyRO
    Find your 논산 출장안마 way around 의왕 출장안마 the casino, catch a few of the action on TV, 시흥 출장안마 or play 아산 출장샵 on your cell phone. Betfair Casino - Las Vegas - MapyRRO.com - Las Vegas 대구광역 출장마사지 - MapyROC

    ReplyDelete