How to get last inserted identity value of an identity column in SQL Server using C#.Net.
This article shows how to get last inserted identity value of a table in SQL Server 2005. Here Scope_Identity() method of SQL Server used to fetch the identity of last inserted record.
In this article I will show how to get Identity after insert a row in
SQL Server 2005. I will use Select Scope_Identity() method of
SQL Server to fetch the identity of inserted record.
Scope_Identity() method returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure,
function, or batch.
1. Create Table
CREATE TABLE [dbo].[TabAssetMaster](
[AssetId] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[AssetName] [nvarchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Description] [nvarchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_TabAssetMaster] PRIMARY KEY CLUSTERED
(
[AssetId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
2. Create Store procedure. This procedure return Identity of last inserted row as in table identity is auto Incremented.
CREATE PROCEDURE [InsertAssetMaster]
@AssetName nvarchar(100),
@Description nvarchar(500)
AS
BEGIN
INSERT INTO TabAssetMaster(AssetName,Description,)
VALUES (@AssetName,@Description)
Select Scope_Identity()
END
3. Get Identity after insert row in table. This method return
Identity as Int64
private Int64 ExeSPAsset( )
{
public SqlConnection objCon = new SqlConnection();
SqlCommand CD = new SqlCommand();
try
{
objCon.ConnectionString = "Your Connection String";
objCon.Open();
CD = new SqlCommand( "InsertAssetMaster", objCon);
CD.CommandType = CommandType.StoredProcedure;
CD.Parameters.Add(new SqlParameter("@AssetName",
SqlDbType.NVarChar,100));
CD.Parameters["@AssetName"].Value = "Asset Name";
CD.Parameters.Add(new SqlParameter("@Description", SqlDbType.NVarChar,
500));
CD.Parameters["@Description"].Value = "Descryption";
return Convert.ToInt64(CD.ExecuteScalar());
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
As you told me “Mistakes in stored procedure. Code which returns the Identity is not complete."
Please look at table structure , It's a working code in my project.