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 –
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.
{
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