Home > DeveloperSection > Forums > The underlying provider failed on an open while using stored procedure in code first
Arun Singh

Total Post:68

Points:476
Posted on    January-05-2013 9:00 AM

 ASP.NET MVC ASP.NET MVC 
Ratings:


 0 Reply(s)
 2583  View(s)
Rate this:

The underlying provider failed on an open while using stored procedure in code first

Hi,

I am trying to retrieve data from stored procedure in entity framework code first model. But while I try it gives me an error “The underlying provider failed on an open” for the inner list of data.

            using (ProductDbContext dbUserContext = new ProductDbContext())

            {

                listProducts = dbUserContext.Database.SqlQuery<Products>("SP_GetAllProducts");

                dbUserContext.Database.Initialize(force: false);

 

                var dbProdCmd = dbUserContext.Database.Connection.CreateCommand();

                dbProdCmd.Connection = dbUserContext.Database.Connection;

 

                 //((IObjectContextAdapter)dbUserContext).ObjectContext.Connection.Open();

               

                dbProdCmd.CommandText = "SP_GetAllProducts";

                dbProdCmd.CommandType = CommandType.StoredProcedure;

                try

                {

                    // dbUserContext.Database.Connection.Open();

                    ((IObjectContextAdapter)dbUserContext).ObjectContext.Connection.Open();

 

                    dbUserContext.Database.SqlQuery<Products>("SP_GetAllProducts");

 

                    var prodReader = dbProdCmd.ExecuteReader();

 

                    listProducts = ((IObjectContextAdapter)dbUserContext)

                   .ObjectContext

                   .Translate<Products>(prodReader, "Products", MergeOption.AppendOnly).ToList();

                    prodReader.Dispose();

                }

                finally

                {

                    //dbUserContext.Database.Connection.Close();

                    //((SqlConnection)dbUserContext.Database.Connection).Close();

 

                    ((IObjectContextAdapter)dbUserContext).ObjectContext.Connection.Close();

                }

                //((IObjectContextAdapter)dbUserContext).ObjectContext.ContextOptions.LazyLoadingEnabled = false;

                return View(listProducts);

            }

Stored Procedure:

CREATE PROCEDURE [dbo].[SP_GetAllProducts]

      -- Add the parameters for the stored procedure here

     

AS

BEGIN

      -- SET NOCOUNT ON added to prevent extra result sets from

      -- interfering with SELECT statements.

      SET NOCOUNT OFF;

 

    -- Insert statements for procedure here

      SELECT * FROM [DBO].[Products]

      SELECT * FROM [DBO].[prd1]

      SELECT * FROM [DBO].[prd2]

      SELECT * FROM [DBO].[prd3]

      SELECT * FROM [DBO].[prd4]

      SELECT * FROM [DBO].[prd5]

      SELECT * FROM [DBO].[prd6]

END

Model: Model structure is something like this:

using System.Collections.Generic;

using System;

using System.Data.Entity;

 

namespace Project.Models

{

    public class Products

    {

        public int Id { get; set; }

        public string Name { get; set; }

        public string Description { get; set; }

        public DateTime CreationDate { get; set; }

        public DateTime ModificationDate { get; set; }

 

        public virtual ICollection<T> prd1 { get; set; }

        public virtual ICollection<T> prd2 { get; set; }

        public virtual ICollection<T> prd3 { get; set; }

        public virtual ICollection<T> prd4 { get; set; }

        public virtual ICollection<T> prd5 { get; set; }

    }

 

    public class ProductContext : DbContext

    {

        public DbSet<T> prd { get; set; }

        public DbSet<T> prd1 { get; set; }

 

        public DbSet<T> prd2 { get; set; }

        public DbSet<T> prd3 { get; set; }

        public DbSet<T> prd4 { get; set; }

        public DbSet<T> prd5 { get; set; }

}

Please reply ASAP.

Thanks.



Don't want to miss updates? Please click the below button!

Follow MindStick