locked
How to call a stored procedure and read the results using new EF code first ? RRS feed

  • Question

  • Hi All,

    I need to invoke a stored procedure with some parameters and read the result back. This stored procedure is used to insert a record and provide the result whether the insert was a success or not.

    Currently, I am using MapToStoredProcedures -> Insert to invoke the SP. The records are getting inserted. But I am not able to get a solution to read the response from the SP.

    Can anyone please let me know how to capture the response from the SP ?

    ---Sameer S Panicker

    Wednesday, June 29, 2016 10:49 PM

Answers

  • Hi Sameer S Panicker,

    >> My SP accepts 2 parameters and returns a Result. 

    According to your description, your stored procedure has two parameters and returns a result. I create a simple demo as below for your reference.

    #Stored Procedure

    USE [EFDemo]
    GO
    
    /****** Object: SqlProcedure [dbo].[InsertCodeText2] Script Date: 7/1/2016 10:41:06 AM ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE PROCEDURE [dbo].[InsertCodeText2]
    	@code varchar(50) = '111',
    	@text varchar(50)
    AS
    BEGIN
    	SET NOCOUNT ON;
    	INSERT INTO CodeText
               (Code)
    	VALUES
               (@code);
    
    	SELECT SCOPE_IDENTITY() as codeTextId;
    END
    

    #DbContext

    namespace EFCallSpwithMap
    {
        using System.Data.Entity;
    
        public partial class EFDemoContext : DbContext
        {
            public EFDemoContext()
                : base("name=EFDemoContext")
            {
            }
    
            public virtual DbSet<CodeText> CodeTexts { get; set; }
    
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                modelBuilder
                  .Entity<CodeText>()
                  .MapToStoredProcedures(s =>
                     s.Insert(i => i.HasName("InsertCodeText2")
                                   .Parameter(b => b.Code, "code")
                                   .Parameter(b => b.Text, "text")
                                   ));
                modelBuilder
                  .Entity<CodeText>()
                  .MapToStoredProcedures(s =>
                    s.Insert(i => i.Result(b => b.Id, "codeTextId")));
    
                modelBuilder.Entity<CodeText>()
                    .Property(e => e.Code)
                    .IsUnicode(false);
    
                modelBuilder.Entity<CodeText>()
                    .Property(e => e.Text)
                    .IsUnicode(false);
            }
        }
    }
    

    #Model

    namespace EFCallSpwithMap
    {
        using System.ComponentModel.DataAnnotations;
        using System.ComponentModel.DataAnnotations.Schema;
    
        [Table("CodeText")]
        public partial class CodeText
        {
            public int Id { get; set; }
    
            [StringLength(50)]
            public string Code { get; set; }
    
            [StringLength(50)]
            public string Text { get; set; }
        }
    }
    

    #Usage (console app): we could obtain the value id after SaveChanges

    using System;
    
    namespace EFCallSpwithMap
    {
        class Program
        {
            static void Main(string[] args)
            {
                using (var db = new EFDemoContext())
                {
                    CodeText ct = new CodeText() { Code = "ddd", Text = "xxx" };
                    db.CodeTexts.Add(ct);
                    db.SaveChanges();
                    Console.Write(ct.Id);
                    Console.ReadKey();
                }
            }
        }
    }
    

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, July 1, 2016 2:46 AM

All replies

  • Hi Sameer,

    You can update your stored procedure and include the @@RowCount, here is an example

    insert into table (x,y) values (1,2)
    Go
    Select @@RowCount

    if the insertion succeeds, the return will be 1 otherwise it will be 0

    here is a documentation on @@ROWCOUNT


    Thanks, Mahmoud


    Wednesday, June 29, 2016 11:06 PM
  • Hi Sameer S Panicker,

    >>Can anyone please let me know how to capture the response from the SP ?

    We could use output parameters to achieve your requirement. I create a simple demo as below for your reference.

    #Stored Procedure

    USE [EFDemo]
    GO
    
    /****** Object: SqlProcedure [dbo].[InsertCodeText] Script Date: 6/30/2016 10:11:47 AM ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    
    ALTER PROCEDURE [dbo].[InsertCodeText]
    	@code varchar(50) = '111',
    	@text varchar(50),
    	@codeTextId int OUTPUT
    
    AS
    BEGIN
    	SET NOCOUNT ON;
    	INSERT INTO CodeText
               (Code,Text)
    	VALUES
               (@code, @text);
    
    	SELECT @codeTextId = SCOPE_IDENTITY();
    END
    

    #Usage:

    using (var db = new EFDemoContext())
                {
                    var idParameter = new SqlParameter();
                    idParameter.ParameterName = "@codeTextId";
                    idParameter.Direction = ParameterDirection.Output;
                    idParameter.SqlDbType = SqlDbType.Int;
                    var authors = db.Database.ExecuteSqlCommand("InsertCodeText @code, @text, @codeTextId OUT",
                        new SqlParameter("@code", "code1"),
                        new SqlParameter("@text", "text1"),
                        idParameter);
                    Console.WriteLine(idParameter.Value);
                    Console.ReadKey();
                }

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, June 30, 2016 2:27 AM
  • Is there a way to use MapToStoredProcedures and get the result back ???
    Thursday, June 30, 2016 1:57 PM
  • Yes Sameer,

    modelBuilder 
      .Entity<Blog>() 
      .MapToStoredProcedures(s => 
        s.Insert(i => i.Result(b => b.BlogId, "generated_blog_identity")));
    CREATE PROCEDURE [dbo].[Blog_Insert]  
      @Name nvarchar(max),  
      @Url nvarchar(max)  
    AS  
    BEGIN 
      INSERT INTO [dbo].[Blogs] ([Name], [Url]) 
      VALUES (@Name, @Url) 
     
      SELECT SCOPE_IDENTITY() AS generated_blog_id 
    END
    More details at https://msdn.microsoft.com/en-us/data/dn468673.aspx


    Please mark as answer if you find it helpful

    Thanks, Mahmoud


    Thursday, June 30, 2016 2:36 PM
  • Currently my request object has below properties - 

    Prop1,Prop2

    My Response object has only 1 property - 

    Result

    My SP accepts 2 parameters and returns a Result. 

    How can I use MapToStoredProcedure method to dump the result from SP into Result property.

    I tried doing it, but couldnt get it.

    Thursday, June 30, 2016 4:27 PM
  • My SP accepts 2 parameters and returns a Result. 

    How can I use MapToStoredProcedure method to dump the result from SP into Result property.

    I tried doing it, but couldnt get it.

    Map it yourself then you got the results back so use a datareader and read the results and populate the object.

    Thursday, June 30, 2016 7:54 PM
  • Hi Sameer S Panicker,

    >> My SP accepts 2 parameters and returns a Result. 

    According to your description, your stored procedure has two parameters and returns a result. I create a simple demo as below for your reference.

    #Stored Procedure

    USE [EFDemo]
    GO
    
    /****** Object: SqlProcedure [dbo].[InsertCodeText2] Script Date: 7/1/2016 10:41:06 AM ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE PROCEDURE [dbo].[InsertCodeText2]
    	@code varchar(50) = '111',
    	@text varchar(50)
    AS
    BEGIN
    	SET NOCOUNT ON;
    	INSERT INTO CodeText
               (Code)
    	VALUES
               (@code);
    
    	SELECT SCOPE_IDENTITY() as codeTextId;
    END
    

    #DbContext

    namespace EFCallSpwithMap
    {
        using System.Data.Entity;
    
        public partial class EFDemoContext : DbContext
        {
            public EFDemoContext()
                : base("name=EFDemoContext")
            {
            }
    
            public virtual DbSet<CodeText> CodeTexts { get; set; }
    
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                modelBuilder
                  .Entity<CodeText>()
                  .MapToStoredProcedures(s =>
                     s.Insert(i => i.HasName("InsertCodeText2")
                                   .Parameter(b => b.Code, "code")
                                   .Parameter(b => b.Text, "text")
                                   ));
                modelBuilder
                  .Entity<CodeText>()
                  .MapToStoredProcedures(s =>
                    s.Insert(i => i.Result(b => b.Id, "codeTextId")));
    
                modelBuilder.Entity<CodeText>()
                    .Property(e => e.Code)
                    .IsUnicode(false);
    
                modelBuilder.Entity<CodeText>()
                    .Property(e => e.Text)
                    .IsUnicode(false);
            }
        }
    }
    

    #Model

    namespace EFCallSpwithMap
    {
        using System.ComponentModel.DataAnnotations;
        using System.ComponentModel.DataAnnotations.Schema;
    
        [Table("CodeText")]
        public partial class CodeText
        {
            public int Id { get; set; }
    
            [StringLength(50)]
            public string Code { get; set; }
    
            [StringLength(50)]
            public string Text { get; set; }
        }
    }
    

    #Usage (console app): we could obtain the value id after SaveChanges

    using System;
    
    namespace EFCallSpwithMap
    {
        class Program
        {
            static void Main(string[] args)
            {
                using (var db = new EFDemoContext())
                {
                    CodeText ct = new CodeText() { Code = "ddd", Text = "xxx" };
                    db.CodeTexts.Add(ct);
                    db.SaveChanges();
                    Console.Write(ct.Id);
                    Console.ReadKey();
                }
            }
        }
    }
    

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, July 1, 2016 2:46 AM
  • Thanks Cole. This is what I was looking for. Thank you very much.
    Friday, July 1, 2016 5:34 PM
  • Hey Cole,

    I tried ur solution but I am receiving an error 

    A result binding for the property 'Err' was not found on the modification function 'sp_Insert_MA'. Ensure that the property is database generated.

    My SP return this ->
    DECLARE @Err INTEGER
    .....
    .....
    SELECT Rslt = @Err 

    I tried with Rslt as well, still same result.

    Regards,

    Sameer 

    Friday, July 1, 2016 7:37 PM
  • Hi Sameer,

    You are trying to return another value @err as well as CodeTextId

    In your stored procedure modify the select statment to 

    Select SCOPE_IDENTITY() as codeTextId, @Err as Reslt

    In the OnModelCreating function add this

    modelBuilder
                  .Entity<CodeText>()
                  .MapToStoredProcedures(s =>
                    s.Insert(i => i.Result(b => b.Error, "Reslt")));
    And your model class should have a new property "Error"
        [Table("CodeText")]
        public partial class CodeText
        {
            public int Id { get; set; }
    
            [StringLength(50)]
            public string Code { get; set; }
    
            [StringLength(50)]
            public string Text { get; set; }
            public string Error {get; set;}
        }
    }

    Please let me know if it works


    Thanks, Mahmoud

    Friday, July 1, 2016 9:30 PM