ASP.NET Web API CRUD Operation Using SQL Server Stored Procedures

הראה שימוש ב-Stored Procedures בתוך C# API.

ניצור טבלה על מנת שנוכל לעבוד עליה.

יצירת טבלה חדשה

CREATE TABLE TestEmployee(ID int Identity(1,1) Primary Key, 
    Name varchar(100), Age int, Active int)

הרצת ה-query על ידי F5.

ננסה להביא את המידע מהטבלה, כמובן שהוא יגיע ריק, כי עכשיו יצרנו את הטבלה.

SELECT * FROM TestEmployee

יצירת פרוייקט VS

נעבור ל-Visual Studio וניצור פרוייקט חדש.

אנחנו נבצע 5 פעולות באמצעות ה-API. בחירת טבלה שלמה, בחירת רשומה על פי ID, יצירת רשומה חדשה, עדכון רשומה קיימת, ביטול רשומה קיימת.

כדי לבצע את הפעולות האלה, הוספתי controller עם פעולות בסיסיות.

בניית ה-API

נוסיף class של Employee. המבנה של ה-class יהיה תואם למבנה הטבלה שממנה אנחנו שואבים את הנתונים.

namespace SPWebAPI.Models;

public class Employee
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int Age { get; set; }
    public int Active { get; set; }
}

נעבור ל-controller ונקבל את ה-configuration על מנת להשתמש ב-connection string.

public class HomeController : ControllerBase
{
    private readonly IConfiguration _configuration;
    private readonly string? _connectionString;

    public HomeController(IConfiguration configuration) {
        _configuration = configuration;
    _connectionString = _configuration.GetValue<string>
        ("ConnectionStrings:webapi_conn");
    }
}

כדי לחבר את ה-DB נבנה connection string בקובץ appsettings

לקובץ הזה נוסיף את ה-connection string. כדי למצוא את שם ה-server נחזור למנהל ה-SQL ונלחץ על connect->Database Engine.

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "ConnectionStrings": {
    "webapi_conn": "server=DESKTOP-PDVBMCF\\SQLEXPRESS; 
        database=master; Integrated Security=true; TrustServerCertificate=True"
  },
  "AllowedHosts": "*"
}

כדי להשתמש ב-SQL נתקין Nuget של Sql Client, ואת Microsoft.Extensions.Configuration. נוסיף ל-controller שלושה using.

using System.Data;
using System.Data.SqlClient;
using System.Configuration;

מכיוון שאין לנו נתונים עדיין, נתחיל מפעולת post.

POST

ניצור את הפרוצדורה שמייצרת employee חדש.

CREATE PROC sp_AddEmployee(@Name VARCHAR(100), @Age INT, @Active INT)
AS
BEGIN
	INSERT INTO TestEmployee(Name, Age, Active)
	VALUES (@Name, @Age, @Active)
END;

נעבור ל-controller שמתחבר לפרוצדורה.

namespace SPWebAPI.Controllers;

[Route("api/[controller]")]
[ApiController]
public class HomeController : ControllerBase
{
    private readonly IConfiguration _configuration;
    private readonly string? _connectionString;

    public HomeController(IConfiguration configuration) {
        _configuration = configuration;
    _connectionString = _configuration.GetValue<string>
        ("ConnectionStrings:webapi_conn");
    }

    [HttpPost]
    [ProducesResponseType(201, Type = typeof(Employee))]
    public async Task<ActionResult<Employee>> CreateEmployee(Employee employee) {
        try {
            SqlConnection con = new SqlConnection(_connectionString);

            if (employee != null) {
                SqlCommand cmd = new SqlCommand("sp_AddEmployee", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@Name", employee.Name);
                cmd.Parameters.AddWithValue("@Age", employee.Age);
                cmd.Parameters.AddWithValue("@Active", employee.Active);

                con.Open();
                int i = cmd.ExecuteNonQuery();
                con.Close();

                if (i > 0) {
                    return Ok("Data was created");
                }
            }
        }
        catch {
            return NotFound("Data was not created");
        }        
    }
}

אנחנו מייצרים SqlConnection עם ה-connection string שיצרנו.

מייצרים SqlCommand שמקבל את שם הפרוצדורה ואת החיבור ומגדירים את סוג הפקודה כפרוצדורה.

מחברים את הפרמטרים לפקודה.

פותחים חיבור, שולחים את הפקודה וסוגרים חיבור.

אם החיבור לא עובד אפשר לנסות להכנס להגדרות של החיבור ולסמן את Trust Server.

GET ALL

כשיש נתונים בתוך ה-DB אפשר לבצע פעולות select. ניצור את הפרוצדורה המתאימה.

CREATE PROC sp_GetAllEmployees
AS
BEGIN
	SELECT * FROM TestEmployee
END;

נחזור ל-controller כדי לבנות את הפונקציה שמובילה להבאת הנתונים.

[HttpGet]
public List<Employee> GetAllEmployees() {
    List<Employee> lstEmployees = new List<Employee>();

    try {
        SqlConnection con = new SqlConnection(_connectionString);
        SqlDataAdapter da = new SqlDataAdapter("sp_GetAllEmployees", con);
        DataTable dt = new DataTable();

        da.Fill(dt);

        if (dt.Rows.Count > 0) {
            for(int i = 0; i < dt.Rows.Count; i++) {
                Employee emp = new Employee();

                emp.Id = Convert.ToInt32(dt.Rows[i]["Id"]);
                emp.Name = dt.Rows[i]["Name"].ToString();
                emp.Age = Convert.ToInt32(dt.Rows[i]["Age"]);
                emp.Active = Convert.ToInt32(dt.Rows[i]["Active"]);

                lstEmployees.Add(emp);
            }              
        }

        if (lstEmployees.Count > 0) {
            return lstEmployees;
        }
        return null;
    }
    catch {
        return null;
    }
}

בצורה דומה נביא מידע לגבי עובד אחד.

GET BY ID

נכתוב את הפרוצדורה המתאימה.

CREATE PROC sp_GetEmployeeById(@Id INT)
AS
BEGIN
	SELECT * FROM TestEmployee WHERE Id = @Id
END;

נפעיל את הפרוצדורה כדי שתישמר ונבנה את הפונקציה המתאימה ב-API.

[HttpGet("{id}")]
public Employee GetEmployeeById(int id) {
    Employee emp = new Employee();

    try {
        SqlConnection con = new SqlConnection(_connectionString);
        SqlDataAdapter da = new SqlDataAdapter("sp_GetEmployeeById", con);
        da.SelectCommand.CommandType = CommandType.StoredProcedure;
        da.SelectCommand.Parameters.AddWithValue("@Id", id);

        DataTable dt = new DataTable();

        da.Fill(dt);

        if (dt.Rows.Count > 0) {

            emp.Id = Convert.ToInt32(dt.Rows[0]["Id"]);
            emp.Name = dt.Rows[0]["Name"].ToString();
            emp.Age = Convert.ToInt32(dt.Rows[0]["Age"]);
            emp.Active = Convert.ToInt32(dt.Rows[0]["Active"]);
        }

        if (emp != null) {
            return emp;
        }
        return null;
    }
    catch {
        return null;
    }
}

UPDATE

עדכון רשומה על פי Id.

CREATE PROC sp_UpdateEmployee(@Id INT, @Name VARCHAR(100), @Age INT, @Active INT)
AS
BEGIN
	UPDATE TestEmployee SET Name = @Name, Age = @Age, Active = @Active
	WHERE Id = @Id
END;

והפונקציה המתאימה.

[HttpPut("{id}")]
public async Task<ActionResult<Employee>> UpdateEmployeeById(int id, Employee employee) {
    try {
        SqlConnection con = new SqlConnection(_connectionString);

        if (employee != null) {
            SqlCommand cmd = new SqlCommand("sp_UpdateEmployee", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@Id", id);
            cmd.Parameters.AddWithValue("@Name", employee.Name);
            cmd.Parameters.AddWithValue("@Age", employee.Age);
            cmd.Parameters.AddWithValue("@Active", employee.Active);

            con.Open();
            int i = cmd.ExecuteNonQuery();
            con.Close();

            if (i > 0) {
                return Ok("Data was updated");
            }
        }
        return Created("api/products/{product.id}", employee);
    }
    catch {
        return NotFound("Data was not updated");
    }
}

DELETE

הפרוצדורה.

CREATE PROC sp_DeleteEmployee(@Id INT)
AS
BEGIN
	DELETE FROM TestEmployee WHERE Id = @Id
END;

והפונקציה.

[HttpDelete("{id}")]
public string DeleteEmployeeById(int id, Employee employee) {
    try {
        SqlConnection con = new SqlConnection(_connectionString);

        SqlCommand cmd = new SqlCommand("sp_DeleteEmployee", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@Id", id);

        con.Open();
        int i = cmd.ExecuteNonQuery();
        con.Close();

        if (i > 0) {
            return ("Data was deleted");
        }

        return ("Data was not deleted");
    }
    catch {
        return ("Data was not deleted");
    }
}

הקוד פה לא מושלם, ויש מה לעבוד על הודעות השגיאה, אבל בגדול יש פה את כל הפעולות של עבודה מול ה-DB עם שימוש בפרוצדורות.

ניווט במאמר

מאמרים אחרונים

Weekly Tutorial