How to retrieve output parameter from stored procedure using Entity Framework 6

Question

Stored procedure:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE spInsertData
    @UNAME varchar(20),
    @Pass varchar(20),
    @Active bit,
    @City nvarchar(20),
    @Phone nvarchar(10)
AS
BEGIN
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    INSERT INTO Login 
    VALUES(@UNAME, @Pass, @Active, @City, @Phone) 

    SELECT SCOPE_IDENTITY() AS Id
END
GO

C# code-behind:

namespace AjaxJqueryDemo
{
    public partial class Reg : System.Web.UI.Page
    {
        TraineeEntities DB = new TraineeEntities();

        protected void btnReg_Click(object sender, EventArgs e)
        {
           int i = DB.spInsertData(uname.Text, Pass.Text,true, city.Text, pHno.Text);
        }
    }
}

How to fetch the scope identity so that I can ensure data is inserted?

I am not using gridview and neither am I interested in using it. How can I fetch it simply?

Please give answers with more detailed explanation so that I can understand..

If you had an entity User (or Trainee or whatever) and would be using Entity Framework to actually insert it, you would have this feature (getting back the newly inserted Id) automatically and for free - no effort on your part needed. Do you really need to use that stored procedure? Really?!?!?! — marc_s
– marc_s, Commented Jul 27, 2016 at 11:54
Assuming that for some (political?) reason you must use a stored proc (which as marc_s points out is not unnecessary), why are you SELECTing instead of RETURNing? Chances are your ID is an INT so you don't need all the overhead of a data set being returned. If, again for some unknown reason, you must SELECT it, then treat it in the same way you return any other data - as a set of columns and rows. — DavidG
– DavidG, Commented Jul 27, 2016 at 11:59
Please give me solution for this, i am new to Store Procedure, if possible then implement in my code so that i can understand easily. Thanks in advance, — Harry
– Harry, Commented Jul 27, 2016 at 12:14
Typo alert: it's a stored procedure - as in stored inside of SQL Server - it has nothing to do with a "store" .... and since that stored procedure is neither Holy, nor Royal, nor denotes a geographical place, there's absolutely no need to capitalize the leading letters, either.... — marc_s
– marc_s, Commented Jul 27, 2016 at 12:16
Instead of giving any solution you guys are just giving useless suggestions, i repeat that i am new this and couldn't find solution by Searching on Google... — Harry
– Harry, Commented Jul 29, 2016 at 4:40

Ankit Solanki · Accepted Answer · 2016-08-03 09:57:08Z

2

Please check bellow code it will give you better suggestion.

ALTER PROCEDURE dbo.YourProcedure
@params VARCHAR(32),
@Table1ID INT = NULL OUTPUT
AS
BEGIN
  SET NOCOUNT ON;

  BEGIN TRANSACTION;

  INSERT dbo.Table1...
  SET @Table1ID = SCOPE_IDENTITY();
  INSERT dbo.Table2...

  COMMIT TRANSACTION;
END
GO

answered Aug 3, 2016 at 9:57

Ankit Solanki

364 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to retrieve output parameter from stored procedure using Entity Framework 6

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related