Getting last ID value of identity column from SQL Server for the most recently inserted record using ASP.Net C# & VB.Net

For the relational database like SQL Server, Oracle, and Microsoft Access/Jet we have to maintain a primary key to establish relation among entities. To do that we can use business code if applicable, but in some cases we have to use Identity Column to identify an entity or record uniquely. These values are generated by the RDBMS as rows are added to a table. To set the identity column of a table, different RDBMS has been exposed different ways like for SQL Server you can set the identity property of a column, for Oracle you can create a Sequence, and for Microsoft Access you can create an AutoNumber column.

Good to Know:

1. C# or VB.Net DataTable DataColumn, can also be considered to generate auto increment values by setting the AutoIncrement property to true. But you might end up with duplicate values for separate instances of a DataTable, if multiple client applications or sessions are independently generating automatically incrementing values. So our recommendation is to use server side identity value rather than instance or session specific value.

2. Use the NewGuid method of a Guid object to generate a GUID, or globally unique identifier, on the client computer that can be copied to the server as each new row is inserted. The NewGuid method generates a 16-byte binary value that is created using an algorithm that provides a high probability that no value will be duplicated. In a SQL Server database, a GUID is stored in a uniqueidentifier column which SQL Server can automatically generate using the Transact-SQL NEWID() function. But our recommendation is not to use NewGuid method since using a GUID as a primary key can adversely affect performance.

3. From your application you can also use GUID, that can be used across all computers and networks wherever a unique identifier is required. Such an identifier has a very low probability of being duplicated. But our recommendation is not to use .net built in GUID method since using a GUID as a primary key also can adversely affect performance. This is basically used as a prefix or post-fix while uploading files to avoid file overwriting. For more about on Asp.Net GUID please follow the below Link:
How to Create and Use a GUID in C# VB.Net Application

List of getting SQL Server Identity Column Values:

SQL Server Function Name Short Description
SELECT Scope_Identity() Returns the last identity value within the current execution scope/session. SCOPE_IDENTITY is highly recommended for almost every cases.
SELECT @@IDENTITY Contains the last identity value generated in any table in the current scope/session. If the affected table has Triggers then @@IDENTITY may not return the identity value as per expectation.
SELECT IDENT_CURRENT(‘table_name’) Returns the last identity value generated for a specific table in any session and any scope. You have to specify the table name. It’s a mandatory argument.

Detail Examination:
Check out the below video tutorial more attentively. Hope it will help you a lot.
Getting last ID value of identity column from SQL Server for the most recently inserted record using ASP.Net C# & VB.Net

So far i have described all about SQL Server. Now time to get last ID value of identity column from SQL Server using C# and VB.Net:

To do the example create the below table:

CREATE TABLE [dbo].[Person](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[PersonName] [varchar](max) NOT NULL,

Add connection string into web.config file:

    <add name="DBConnection" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=TESTDB;Trusted_Connection=yes;" providerName="System.Data.SqlClient"/>
    <!--<add name="BONConnection" connectionString="Data;Initial Catalog=DBNAME;User Id=UserName;Password=YourPassword;" providerName="System.Data.SqlClient" />-->

Note: If you use windows authentication then use the first key. Otherwise use second key line. Update server name, Database name, User name, Password as per your settings.

Add below namespaces:


C# Code:

    protected void Page_Load(object sender, EventArgs e)
        if (!IsPostBack)
            string consString = ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
            Int64 ID;

            using (SqlConnection conn = new SqlConnection(consString))
                string insert_query = "Insert Into Person (PersonName) Values (@PersonName);" +
                "Select Scope_Identity()";

                using (SqlCommand cmd = new SqlCommand(insert_query, conn))
                    cmd.Parameters.AddWithValue("@PersonName", "Scott Mathew");
                    ID = Convert.ToInt64(cmd.ExecuteScalar());

            Response.Write("The Last Identity Value is: " + ID);

VB.Net Code:

    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            Dim consString As String = ConfigurationManager.ConnectionStrings("DBConnection").ConnectionString
            Dim ID As Int64

            Using conn As New SqlConnection(consString)
                Dim insert_query As String = "Insert Into Person (PersonName) Values (@PersonName);" & "Select Scope_Identity()"

                Using cmd As New SqlCommand(insert_query, conn)
                    cmd.Parameters.AddWithValue("@PersonName", "Scott Mathew")
                    ID = Convert.ToInt64(cmd.ExecuteScalar())
                End Using
            End Using

            Response.Write("The Last Identity Value is: " & ID)
        End If

    End Sub

Note: For simplicity i am avoiding transaction management. But strongly recommend to use above codes under a transaction.

Still confusion. Please download below code sample:
Download Code Example C#        Download Code Example VB.Net

Posted in .Net,, C#, Database, SQL Server, VB.Net

Leave a Reply

Your email address will not be published. Required fields are marked *