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 –


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"]);



Convert image file to Byte/Binary and Save into SQL Server

For most of the Products , We need to save many types of Image files to SQL Server Table as binary Data .

For that case , We can keep our Column of the Table as varbinary(MAX) and Save the Image into this Column . Here is the SQL to do such things .

UPDATE DocumentTable
DocumentContent = (SELECT * FROM OPENROWSET(BULK N'C:\logo.jpg', SINGLE_BLOB) as imagefile)
WHERE Id = 3