Uncategorized

Get Identity Column Value just after Inserting a row in MS SQL Server Table

Many a time , We need to get the auto Incremented Identity Column value just after Inserting/Saving a row to a Table in MS SQL Server.

In my Case of this Example, When I Insert a New Project to my tblProjects Table , I want the ID column value( which is basically auto incremented Identity Column in SQL Server) of my inserted Row .

For this , TSQL provides us a way to do that like below –

SELECT SCOPE_IDENTITY()

So in C# , our code can be like below :::

Note : We can use StoredProcedue Which is better practice .Then inside the SP ,we will use the code mentioned.

public static int CreateProject(Project projectObj)
        {
            string insertProjectQuery = "INSERT INTO tblProjects VALUES('" + projectObj.ProjectName + "','" + projectObj.TblCustomerId + "','" + projectObj.BClosed + "') SELECT SCOPE_IDENTITY() As ProjectId";
            DataTable dt = QueryGateway.GetDataThroughDataTable(insertProjectQuery);
            return  Convert.ToInt32(dt.Rows[0]["ProjectId"]);
        }

Cheers

Standard