C++/CLI Code Snippet - Get Identity Column Value from SQL Server

C++/CLI Code Snippet - Get Identity Column Value from SQL Server

This .Net C++/CLI 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.

System::Int64 ExeSQLReturnLong(System::Data::SqlClient::SqlConnection ^%_SqlConnection, System::String ^_SQL)
{
    System::Int64 _ReturnID = 0;
    System::Object ^_ReturnObject = nullptr;

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

        _ReturnObject = _SqlCommand->ExecuteScalar();

        delete _SqlCommand;
        _SqlCommand = nullptr;
    }
    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 = System::Int64::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 Int64 (long) and return the value.

// set temporary variable for database connection
System::Data::SqlClient::SqlConnection ^_SqlConnection = gcnew 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 != nullptr && _SqlConnection->State == ConnectionState::Open)
{
    System::Int64 _NewProductID = 0;

    // add new record to database table and get the autoincrement product id
                        // Pass open database connection to function
                        // Pass SQL statement to insert new record
    _NewProductID = ExeSQLReturnLong(_SqlConnection, "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.

System::Object ^ExeSQLReturnObject(System::Data::SqlClient::SqlConnection ^%_SqlConnection, System::String ^_SQL)
{
    System::Object ^_ReturnObject = nullptr;

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

        _ReturnObject = _SqlCommand->ExecuteScalar();

        delete _SqlCommand;
        _SqlCommand = nullptr;
    }
    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 nullptr;
    }

    return _ReturnObject;
}

C++/CLI Keywords Used:

• Int64
• 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++/CLI

External Resources:

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

Leave a comment