C# Code Snippet - Get Identity Column Value from SQL Server

C# Code Snippet - Get Identity Column Value from SQL Server

This .Net C# code snippet connects to SQL server and executes a SQL statement and return the latest identity used within the scope of that user statement. To use this function simply provide open database connection and SQL statement. This function uses SqlClient name space to get the @@IDENTITY using ExecuteScalar method. Modify the exception handling section for your project requirements. Identity related data manipulation can be done more efficiently using stored procedure, this is an example showing how to read the identity value.

Identity Column
An Identity column is a column ( also known as a field ) in a database table that uniquely identifies every row in the table, and is made up of values generated by the database. An identity column has a name, initial seed and step. When a row is inserted into a table the column will take the value of the curent seed incremented by the step.
Note: An identity column is not guaranteed to be unique nor consecutive. You should always place a unique index on an identity column if your system requires uniqueness.

public long ExeSQLReturnLong(
        ref System.Data.SqlClient.SqlConnection _SqlConnection, 
        string _SQL)
{
    long _ReturnID = 0;
    object _ReturnObject = null;

    try
    {
        // Execute Query, and return last insert ID
        _SQL += ";SELECT @@IDENTITY AS 'Identity'";
        System.Data.SqlClient.SqlCommand _SqlCommand 
                        = new System.Data.SqlClient.SqlCommand(_SQL, _SqlConnection);

        _ReturnObject = _SqlCommand.ExecuteScalar();
        
        _SqlCommand.Dispose();
        _SqlCommand = null;
    }
    catch (System.Data.SqlClient.SqlException _Exception)
    {
        // Error occurred while trying to execute reader
        // send error message to console (change below line to customize error handling)
        Console.WriteLine(_Exception.Message);

        return 0;
    }

    // convert return object to long
    try
    {
        _ReturnID = long.Parse(_ReturnObject.ToString());
    }
    catch (Exception _Exception)
    {
        // Error occurred while trying to convert return id to long
        // send error message to console (change below line to customize error handling)
        Console.WriteLine(_Exception.Message);

        return 0;
    }

    return _ReturnID;
}

Here is a simple example showing how to use above function (ExeSQLReturnLong) to login to SQL server and inserts a row into a table with an identity column (productid) and uses @@IDENTITY to display the identity value used in the new row. This function (ExeSQLReturnLong) retrieves the identity value as an object and converts it into a long and return the value.

// set temporary variable for database connection
System.Data.SqlClient.SqlConnection _SqlConnection = new System.Data.SqlClient.SqlConnection();

// assign database connection string
_SqlConnection.ConnectionString = "Server=SERVERADDRESS;Database=DATABASENAME;Uid=USERID;Pwd=PASSWORD;";

// Connect to database
try
{
    _SqlConnection.Open();
}
catch (Exception _Exception)
{
    // Error occurred while trying to connect to database
    Console.WriteLine(_Exception.Message);
}


// Check for valid open database connection before query database
if (_SqlConnection != null && _SqlConnection.State == ConnectionState.Open)
{
    long _NewProductID = 0;
                    
    // add new record to database table and get the autoincrement product id
    _NewProductID = ExeSQLReturnLong(
                        // Pass open database connection to function
                        ref _SqlConnection, 
                        // Pass SQL statement to insert new record
                        "INSERT INTO sampletable (name, price) VALUES ('sample product name', 22.75)");

    if (_NewProductID > 0)
    {
        // Record successfully insert into database
        Console.WriteLine("Record added to database table - New Record ID : " + _NewProductID.ToString());
    }
    else
    {
        // Record failed to insert into database
        Console.WriteLine("Failed to add new record to database table");
    }

    // close database connection
    _SqlConnection.Close();
}

Here is the same function which modified (ExeSQLReturnLong => ExeSQLReturnObject) to return the identity as an object which gives the opinion to convert the identity value to any data type.

public object ExeSQLReturnObject(
        ref System.Data.SqlClient.SqlConnection _SqlConnection,
        string _SQL)
{
    object _ReturnObject = null;

    try
    {
        // Execute Query, and return last insert ID
        _SQL += ";SELECT @@IDENTITY AS 'Identity'";
        System.Data.SqlClient.SqlCommand _SqlCommand
                        = new System.Data.SqlClient.SqlCommand(_SQL, _SqlConnection);

        _ReturnObject = _SqlCommand.ExecuteScalar();

        _SqlCommand.Dispose();
        _SqlCommand = null;
    }
    catch (System.Data.SqlClient.SqlException _Exception)
    {
        // Error occurred while trying to execute reader
        // send error message to console (change below line to customize error handling)
        Console.WriteLine(_Exception.Message);

        return null;
    }

    return _ReturnObject;
}

C# Keywords Used:

• long
• Parse
• ExecuteNonQuery
• SqlConnection
• ConnectionString
• SqlCommand
• Exception

Code Snippet Information:

• Applies To: .Net, C#, CLI, SQL, ExecuteScalar, Identity Column, SQL Server, SQL Client, Connection String, Database Connection
• Programming Language : C# (C-Sharp)

External Resources:

• @@IDENTITY (Transact-SQL)
• SqlCommand.ExecuteScalar Method
• SqlConnection Class
• SqlConnection.ConnectionString Property
• SqlCommand Class
• Exception Class

Usman :: December 22-2010 :: 02:34 AM

thankyou so much....you saved my day

johan :: October 05-2011 :: 04:42 AM

hello please help how to use ExeSQLReturnObject. thank you so much

Leave a comment