none
How do I retrieve an identity value from a Stored Procedure with DBML? RRS feed

  • Question

  • Hi -

    I am trying to retrieve the identity value from a record that I just inserted that has an identity primary key. I am calling it from my context and I want the stored procedure to return a value.  I am trying to use LINQ to SQL Classes but continue to run into problems.  I then decided to write stored procs to avoid insert issues and it has been working well.  Now, I can't retrieve a value from a proc.  How do I retrieve the identity from the insert?

    Here's my code:

    DataClasses context = new DataClasses();

    var persId = context.proc_addIncidentPerson(_person.EmplID, null, null, _person.PSIncidentNo, null);

    // stored procedure

    ALTER PROCEDURE [dbo].[proc_addIncidentPerson](
        @emplId varchar(11) = NULL,
        @contactInformation varchar(200) = NULL ,
        @fullName varchar(30) = NULL,
        @psIncidentNo varchar(8),
        @vehicleInfoId int = NULL)
    AS
        INSERT INTO IncidentPerson(
            EmplID,
            ContactInformation,
            FullName,
            PSIncidentNo,
            VehicleInfoID)
        VALUES (
            @emplId,
            @contactInformation,
            @fullName,
            @psIncidentNo,
            @vehicleInfoId)

        SELECT SCOPE_IDENTITY()

    It returns a null or 0 value.  I can see that it is added to the table but it won't return correctly.  Any ideas?  This works in SQL 2000 and calling a stored procedure in VS 2005.

    Monday, March 22, 2010 7:44 PM

Answers

  • Hi Piper,

     

    Thank you very much for sending me the demo project.   It is strange that I do repro the problem when the returned identity value has no alias.  Column1 property of the proc_addIncidentPersonResult is null.  

     

    Then after I set the alias in the stored procedure as: SELECT SCOPE_IDENTITY() as IdentityID

     

    And the client entity class will be:

    ======================================================================

            public partial class proc_addIncidentPersonResult

            {

                   

                    private System.Nullable<decimal> _IdentityID;

                   

                    public proc_addIncidentPersonResult()

                    {

                    }

                   

                    [Column(Name="IdentityID", Storage="_IdentityID", DbType="Decimal(0,0)")]

                    public System.Nullable<decimal> IdentityID

                    {

                            get

                            {

                                    return this._IdentityID;

                            }

                            set

                            {

                                    if ((this._IdentityID != value))

                                    {

                                            this._IdentityID = value;

                                    }

                            }

                    }

            }

    ======================================================================

    By default, the property will have the same name as the returned alias in the stored procedure.  We can also specify the mapping by setting the [Column(Name=””)]. 

     

    Can you test it whether this method can solve the problem?  

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by runner11 Friday, March 26, 2010 6:10 PM
    Thursday, March 25, 2010 6:26 AM
    Moderator

All replies

  • Hello,

     

    Glad to see you again!  J

     

    Such a stored procedure works fine at my side.   Because “SELECT SCOPE_IDENTITY()” does not have any returned alias name for the identity value, LINQ to SQL actually name it as Column1 in type of Nullable<decimal> and generates such a class to hold the returned data:

    ==================================================================================

                public partial class proc_addIncidentPersonResult

                {

                    private System.Nullable<decimal> _Column1;

                   

                    public proc_addIncidentPersonResult()

                    {

                    }

                   

                    [Column(Storage="_Column1", DbType="Decimal(0,0)")]

                    public System.Nullable<decimal> Column1

                    {

                        get

                        {

                            return this._Column1;

                        }

                        set

                        {

                            if ((this._Column1 != value))

                            {

                                this._Column1 = value;

                            }

                        }

                    }

                }

    ==================================================================================

     

    The identity value can be retrieved via

    ==================================================================================

    var persId = context.proc_addIncidentPerson(_person.EmplID, null, null, _person.PSIncidentNo, null);

    var id = persId.First().Column1;

    ==================================================================================

     

    If you have any questions, please feel free to let me know.

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, March 23, 2010 2:53 AM
    Moderator
  • I then decided to write stored procs to avoid insert issues and it has been working well. 
    I am wondering what insert issues you encountered? It seems such a simple stored procedure that LINQ would handle easily.
    Tuesday, March 23, 2010 8:15 AM
  • Lingzhi -

    Thank you for your response.  It is nice to know I can count on someone to help me out :-).  This looks like a great way of retrieving the value but I must have something set incorrectly since this doesn't return a value.  It is null.  When I go to the properties of the stored proc in the dbml, the return type displays "(Auto-gnerated Type)".  Is this correct? 

    I know once I get some of these basics down it will be much less frustrating.  The learning curve on these "new" features are, unfortunately, taking me too long to get something out to my customers. 

    I appreciated your help.

     

    Piper

    Tuesday, March 23, 2010 1:42 PM
  • John -

    Yes, that insert is quite simple and would probably work just fine.  I was using the EF in VS 2008 and was having many problems with my look up tables that had FK in my main table that I switched to LINQ to SQL  for dealing with the data layer.  I added all my tables and tried to use the save features but had problems.  It could be coming from other tables that I have in the dbml but I no longer have the luxury of trying to get it to work (I have spent about 1 month with the EF and now LINQ to SQL for accessing my data layer).  It could be FK type problems??  I, therefore, created stored procedures to call and access update, add, etc. so I can leverage LINQ and the stored procedures more easily.  If I can't get things working a little more smoothly soon, I will probably go back to the older way of accessing the data so I can get application out there.

     

    Piper

     

     

    Tuesday, March 23, 2010 1:53 PM
  • Hi Piper,

     

    If it is convenient, could you please send me the dbml file and demo database for further investigation?   My mail address is v-micsun@microsoft.com.   I will do my best to help!  

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, March 24, 2010 3:51 AM
    Moderator
  • Lingzhi -

     

    Thank you for taking a look into this.  I have sent the files you requested.  Please let me know if you do not receive it.  It is in a zipped file.

     

    Piper

    Wednesday, March 24, 2010 2:32 PM
  • Assuming that the Insert using a SPROC works that same as an insert using raw Linq to SQL, then when the object is inserted your object gets the identity value updated within it.  Did you try debugging/walkthru and look at your object after making the linq call? Took me awhile to pick this one up!

    Wednesday, March 24, 2010 5:18 PM
  • Hi Piper,

     

    Thank you very much for sending me the demo project.   It is strange that I do repro the problem when the returned identity value has no alias.  Column1 property of the proc_addIncidentPersonResult is null.  

     

    Then after I set the alias in the stored procedure as: SELECT SCOPE_IDENTITY() as IdentityID

     

    And the client entity class will be:

    ======================================================================

            public partial class proc_addIncidentPersonResult

            {

                   

                    private System.Nullable<decimal> _IdentityID;

                   

                    public proc_addIncidentPersonResult()

                    {

                    }

                   

                    [Column(Name="IdentityID", Storage="_IdentityID", DbType="Decimal(0,0)")]

                    public System.Nullable<decimal> IdentityID

                    {

                            get

                            {

                                    return this._IdentityID;

                            }

                            set

                            {

                                    if ((this._IdentityID != value))

                                    {

                                            this._IdentityID = value;

                                    }

                            }

                    }

            }

    ======================================================================

    By default, the property will have the same name as the returned alias in the stored procedure.  We can also specify the mapping by setting the [Column(Name=””)]. 

     

    Can you test it whether this method can solve the problem?  

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by runner11 Friday, March 26, 2010 6:10 PM
    Thursday, March 25, 2010 6:26 AM
    Moderator
  • Lingzhi -

    That did it!!  Thank you so much.  So I have a couple of questions about this.

    1. Since the DataClasses.designer.cs file gets generated, is there a problem making changes to it?  Are there any issues that I need to be aware of? Will this information stay or get regenerating at some point?

    2. What do you mean by also specify the mapping by setting the [Column(Name="")]?  Does that mean that I didn't  need to add that parameter and it would have still been IdentityID since in the stored procedure returns SCOPE_IDENTITY() AS IdentityID ?  Or is it best to specify the column name to ensure that the value is being saved into that column name?

    Thursday, March 25, 2010 1:19 PM
  • Hello Piper,

     

    It is not recommended to directly modify the .deginer.cs file.   This file is generated based on the .dbml.  If we modify the stored procedure at the database side, we need also change the .dbml by delete the original stored procedure and drag the new one into the designer.   Then the .designer.cs will be modified automatically.  

     

    The .designer.cs file will be automatically updated if the .dbml file is modified.   VS will then use MSLinqToSQLGenerator to generate the .designer.cs file.   So we will lost our custom modification in the .desginer.cs file.   Instead, we suggest you add some partial class outside the .designer.cs to add some custom modification.  

     

     

    By default, LINQ to SQL maps the property to the returned column by names.   Sometimes, our property name can be Identity or something else, which are not the same as the returned column name.   In such scenario, we can specify the column name to map with using [Column(Name=””)].  

     

    Please feel free to let me know if you have any questions.

     

    Have a nice weekend!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, March 26, 2010 9:54 AM
    Moderator
  • Lingzhi -

    Oh, I see.  I just deleted my old proc and added the new one and it made the changes for me.  Thank you again.  I truly appreciate your help.

     

    Piper

    • Marked as answer by runner11 Friday, March 26, 2010 6:10 PM
    • Unmarked as answer by runner11 Friday, March 26, 2010 6:10 PM
    Friday, March 26, 2010 6:10 PM
  • It's my pleasure, Piper!   :)

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, March 29, 2010 3:28 AM
    Moderator