SCOPE IDENTITY IN SQL SERVER USING C#

 Let’s focus on retrieving the last generated ID from a SQL Server table after an INSERT operation using C# in an ASP.NET Core MVC context. I’ll provide a step-by-step explanation, including what this is, why it’s useful, and a practical example with proper coding standards, comments, and naming conventions. We’ll assume this fits into your Employee Management System (e.g., inserting a new Task and getting its TaskId). The example will use ADO.NET with SqlCommand and the SCOPE_IDENTITY() function, which is the most reliable way to get the last inserted ID in SQL Server.

---

What and Why

What

• Retrieving the last generated ID means capturing the auto-incremented primary key (e.g., IDENTITY column) of a newly inserted row in a SQL Server table.
• In SQL Server, SCOPE_IDENTITY() returns the last identity value generated within the current scope (e.g., your INSERT statement).

Why

• Tracking: After inserting a record (e.g., a new task), you often need its ID for further operations (e.g., assigning it to an employee or logging).
• Reliability: SCOPE_IDENTITY() ensures you get the ID from your specific insert, avoiding conflicts in multi-user scenarios (unlike @@IDENTITY, which might return IDs from triggers).
• Use Case: In your EmployeeManagement app, after adding a Task, you might need its TaskId to return to the UI or link to another entity.

---

Step-by-Step Example

Step 1: Setup the SQL Server Table

• Assumption: A Tasks table exists with an auto-incrementing TaskId.
• SQL Script (run in SQL Server Management Studio or similar):
  sql

  CREATE TABLE Tasks (
      TaskId INT PRIMARY KEY IDENTITY(1,1),
      TaskName NVARCHAR(100) NOT NULL,
      EmployeeId INT NOT NULL,
      ProjectId INT NOT NULL,
      StartTime DATETIME NOT NULL,
      EndTime DATETIME NOT NULL,
      Status NVARCHAR(50) NOT NULL
  );

Step 2: Define the Model

• Where: EmployeeManagement/Models/Task.cs
• Code:
  csharp

  // Represents a task in the Employee Management System
  public class Task
  {
      public int TaskId { get; set; }
      public string TaskName { get; set; }
      public int EmployeeId { get; set; }
      public int ProjectId { get; set; }
      public DateTime StartTime { get; set; }
      public DateTime EndTime { get; set; }
      public string Status { get; set; }
  }

Step 3: Implement the Repository

• What: Update ITaskRepository and TaskRepository to insert a task and return its ID.
• Where: EmployeeManagement/Repositories/
• ITaskRepository.cs:
  csharp

  public interface ITaskRepository
  {
      int AddTask(Task task); // Returns the generated TaskId
  }

• TaskRepository.cs:
  csharp

  using System.Data.SqlClient;
  
  public class TaskRepository : ITaskRepository
  {
      private readonly string _connectionString;
  
      // Constructor to inject connection string
      public TaskRepository(string connectionString)
      {
          _connectionString = connectionString ?? throw new ArgumentNullException(nameof(connectionString));
      }
  
      // Adds a task and returns its generated TaskId
      public int AddTask(Task task)
      {
          using (var connection = new SqlConnection(_connectionString))
          {
              connection.Open();
              var sql = @"
                  INSERT INTO Tasks (TaskName, EmployeeId, ProjectId, StartTime, EndTime, Status)
                  VALUES (@TaskName, @EmployeeId, @ProjectId, @StartTime, @EndTime, @Status);
                  SELECT CAST(SCOPE_IDENTITY() AS INT);";
  
              using (var command = new SqlCommand(sql, connection))
              {
                  command.Parameters.AddWithValue("@TaskName", task.TaskName);
                  command.Parameters.AddWithValue("@EmployeeId", task.EmployeeId);
                  command.Parameters.AddWithValue("@ProjectId", task.ProjectId);
                  command.Parameters.AddWithValue("@StartTime", task.StartTime);
                  command.Parameters.AddWithValue("@EndTime", task.EndTime);
                  command.Parameters.AddWithValue("@Status", task.Status);
  
                  // ExecuteScalar returns the first column of the first row (TaskId)
                  var result = command.ExecuteScalar();
                  return Convert.ToInt32(result);
              }
          }
      }
  }

• Explanation:
  • SCOPE_IDENTITY(): Captures the last identity value in the current scope.
  • ExecuteScalar: Executes the query and returns a single value (the new TaskId).
  • Using Statements: Ensures proper disposal of SQL resources.

Step 4: Update the Service

• What: Modify EmployeeService to use the repository and return the ID.
• Where: EmployeeManagement/Services/
• IEmployeeService.cs:
  csharp

  public interface IEmployeeService
  {
      int AssignTaskToEmployee(int employeeId, int projectId, string taskName, DateTime startTime, DateTime endTime, string status);
  }

• EmployeeService.cs:
  csharp

  public class EmployeeService : IEmployeeService
  {
      private readonly ITaskRepository _taskRepository;
  
      public EmployeeService(ITaskRepository taskRepository)
      {
          _taskRepository = taskRepository ?? throw new ArgumentNullException(nameof(taskRepository));
      }
  
      // Assigns a task and returns its generated TaskId
      public int AssignTaskToEmployee(int employeeId, int projectId, string taskName, DateTime startTime, DateTime endTime, string status)
      {
          if (string.IsNullOrWhiteSpace(taskName))
              throw new ArgumentException("Task name cannot be empty.", nameof(taskName));
          if (endTime <= startTime)
              throw new ArgumentException("End time must be after start time.", nameof(endTime));
          if (string.IsNullOrWhiteSpace(status))
              throw new ArgumentException("Status cannot be empty.", nameof(status));
  
          var task = new Task
          {
              EmployeeId = employeeId,
              ProjectId = projectId,
              TaskName = taskName,
              StartTime = startTime,
              EndTime = endTime,
              Status = status
          };
  
          return _taskRepository.AddTask(task);
      }
  }

Step 5: Configure Dependency Injection

• Where: EmployeeManagement/Program.cs
• Code:
  csharp

  var builder = WebApplication.CreateBuilder(args);
  builder.Services.AddControllersWithViews();
  
  // Register dependencies with connection string from appsettings.json
  builder.Services.AddScoped<ITaskRepository>(sp => 
      new TaskRepository(builder.Configuration.GetConnectionString("DefaultConnection")));
  builder.Services.AddScoped<IEmployeeService, EmployeeService>();
  
  var app = builder.Build();
  app.UseStaticFiles();
  app.UseRouting();
  app.MapControllerRoute("default", "{controller=Home}/{action=Index}/{id?}");
  app.Run();

• appsettings.json:
  json

  {
    "ConnectionStrings": {
      "DefaultConnection": "Server=YourServer;Database=EmployeeDB;Trusted_Connection=True;"
    }
  }

Step 6: Example Usage in Controller

• Where: EmployeeManagement/Controllers/TaskController.cs
• Code:
  csharp

  using Microsoft.AspNetCore.Mvc;
  using EmployeeManagement.Services;
  
  public class TaskController : Controller
  {
      private readonly IEmployeeService _employeeService;
  
      public TaskController(IEmployeeService employeeService)
      {
          _employeeService = employeeService;
      }
  
      [HttpPost]
      public IActionResult AddTask(int employeeId, int projectId, string taskName, DateTime startTime, DateTime endTime, string status)
      {
          try
          {
              int taskId = _employeeService.AssignTaskToEmployee(employeeId, projectId, taskName, startTime, endTime, status);
              TempData["SuccessMessage"] = $"Task added successfully with ID: {taskId}";
              return RedirectToAction("Index");
          }
          catch (Exception ex)
          {
              TempData["ErrorMessage"] = ex.Message;
              return View();
          }
      }
  
      public IActionResult Index()
      {
          return View();
      }
  }

Step 7: Test in Visual Studio 2022

1. Setup:
   • Ensure SQL Server is running and the Tasks table exists.
   • Update appsettings.json with your connection string.
2. Run:
   • Build solution (Ctrl+Shift+B).
   • Set EmployeeManagement as startup project → F5.
   • Post a task via a form or API call (e.g., via a simple view).

---

Key Points

• SQL: SELECT CAST(SCOPE_IDENTITY() AS INT) appended to INSERT retrieves the ID.
• C#: command.ExecuteScalar() captures the result as a single value.
• Error Handling: Validation ensures robust input; SQL exceptions are caught in the controller.

Sample Output

• Insert a task: TaskName="Design Homepage", EmployeeId=1, ProjectId=1, StartTime=2025-03-07 09:00, EndTime=2025-03-07 17:00, Status="Pending".
• Result: taskId = 1 (assuming first insert), displayed in TempData["SuccessMessage"].

---

Why This Approach?

• Reliability: SCOPE_IDENTITY() is scope-specific, avoiding trigger-related issues.
• Simplicity: ADO.NET is lightweight compared to EF Core for this task.
• Flexibility: Works in your ADO.NET-based EmployeeManagement system.