Get the identity of the inserted row

When creating stored procedures you will want the identity of the newly inserted row returned.

It’s quite simple, just use SCOPE_IDENTITY.

SQL Example:

	
CREATE PROCEDURE CreateClient
    -- Add the parameters for the stored procedure here
    @ClientName nvarchar(MAX), @ClientAcronym nvarchar(MAX)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    INSERT INTO Clients
    VALUES(@ClientName, @ClientAcronym)

    SELECT SCOPE_INDENTITY()
END
GO

C# Example

public int Create(Client entity)
{
    using (SqlConnection connection = new SqlConnection(Settings.SQL_CONNECTION_STRING))
    {
        try
        {
            connection.Open();

            using (SqlCommand cmd = new SqlCommand("CreateClient", connection))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@ClientName", entity.Username));
                cmd.Parameters.Add(new SqlParameter("@ClientAcronym", entity.Email));

                var id = cmd.ExecuteScalar();

                return Convert.ToInt32(id);
            }
        }
        catch (Exception ex)
        {
            // TODO: Log error

            return -1;
        }
    }
}

Remember, ExecuteScalar is important here. It gives you back your identity.

Advertisements

Published by

Alan Feekery

Developer, Gamer, Musician, Cyclist and big Motorsport fan... enjoys the odd cup of coffee :)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s