The Final Guide

The Microsoft SQL Server Database provides an Identity value which is generated automatically when a record is added in such tables which contains IDENTITY. We can fetch the auto incremented value from MS-SQL Server database to our applications using built in function @@IDENTITY( ) (used in MS-Access) or SCOPE_IDENTITY( ) which is the recommended method for obtaining identity value on SQL Server. Scope_Identity( ) retrieves the last identity value created in the current scope which is a single module that can be a stored procedure, batch, function or trigger. We can implement the coding to retrieve the Scope_Identity in many ways.
1. Using batch command with two sql queries seperated with a semicolor, with ExecuteScalar( ) against the batch command to return single value.

[C#]
string strSQL = "INSERT INTO PRODUCTS (PRODUCT_NAME) VALUES(@PRODUCT_NAME); SELECT SCOPE_IDENTITY()";
int intID;
string strSqlCon = @"Server=.SQLEXPRESS;User Id=sa;Password=sa;Database=MyDb";
using(SqlConnection dbCon = new SqlConnection(strSqlCon))
{
      dbCon.Open();
      using(SqlCommand Cmd = new SqlCommand())
      {
           Cmd.Connection = dbCon;
           Cmd.CommandType = CommandType.Text;
           Cmd.Parameters.AddWithValue("@PRODUCT_NAME", strProdName);
           intID = (int)Cmd.ExecuteScalar( );
      }
      dbCon.Close( );
}

2. We can use output parameter from a stored procedure and ExecuteNonQuery( ).
Stored Procedure declaration

CREATE PROCEDURE dbo.SP_PRODUCT_ADD
(@PRODUCT_NAME VARCHAR(250), @PRODUCT_ID INT OUTPUT)
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO PRODUCTS (PRODUCT_NAME) VALUES (@PRODUCT_NAME);
    SET @PRODUCT_ID = SCOPE_IDENTITY( );
END

Our Application Coding…

[C#]
int intID;
string strSqlCon = @"Server=.SQLEXPRESS;User Id=sa;Password=sa;Database=MyDb";
using(SqlConnection dbCon = new SqlConnection(strSqlCon))
{
      dbCon.Open( );
      using(SqlCommand Cmd = new SqlCommand( ))
      {
           Cmd.Connection = dbCon;
           Cmd.CommandType = CommandType.StoredProcedure;
           Cmd.CommandText = "SP_PRODUCT_ADD";
           Cmd.Parameters.AddWithValue("@PRODUCT_NAME", strProdName);
           SqlParameter parm1 = new SqlParameter( );
           param1.ParameterName = "@PRODUCT_ID";
           param1.SqlDbType = SqlDbType.BigInt;
           param1.Direction = ParameterDirection.Output;
           Cmd.Parameters.Add(param1);
           Cmd.ExecuteNonQuery( );
           intID = Convert.ToInt32(param1.Value);
      }
      dbCon.Close( );
}

You may also like to read Change sa password for SQL Server Express Fetch the incremented AutoNumber value from MS-Access Configure SQL Server for ASP.Net session state ASP.Net Page Refresh Problem ASP.Net connect to database via AJAX using JQUERY ASP.Net connect to database using HttpHandler ASHX file phpMyAdmin 3.4.3.1 (Freeware) Download and Install ASP.Net Ajax for Visual Studio

This entry was posted on Wednesday, June 10th, 2009 at 4:23 pm and is filed under Database, SQL Server. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.