איך לשלוח רשימה של אובייקטים מתוכנית c# לטבלה של SQL

בקריאה לפרוצדורה יש לנו אפשרות לשלוח רשימה של משתנים, אבל מה קורה אם יש לי רשימה של אובייקטים לשלוח?

נניח למשל שיש לי אתה מכירות, תהיה לי טבלת הזמנות, לשמירה של ההזמנה עצמה, ותהיה לי טבלה של מוצרים-לרשימה שבה ישמרו המוצרים שקשורים לרשימה. איך אפשר לשלוח את הרשימה של המוצרים כשכל מוצר הוא אובייקט עם מידע, למשל קוד המוצר, כמות מוזמנת וכו'

אפשר לשלוח את המוצרים אחד אחרי השני, זה יהיה הקוד של ה-API:

using System.Data.SqlClient;

public class OrderItem
{
    public int OrderId { get; set; }
    public int ProductId { get; set; }
    public decimal Price { get; set; }
    // Other properties
}

public void SaveOrderItems(List<OrderItem> orderItems)
{
    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        conn.Open();

        using (SqlTransaction transaction = conn.BeginTransaction())
        {
            try
            {
                foreach (var item in orderItems)
                {
                    using (SqlCommand cmd = new SqlCommand("INSERT INTO orderItems (OrderId, ProductId, Price) VALUES (@OrderId, @ProductId, @Price)", conn, transaction))
                    {
                        cmd.Parameters.AddWithValue("@OrderId", item.OrderId);
                        cmd.Parameters.AddWithValue("@ProductId", item.ProductId);
                        cmd.Parameters.AddWithValue("@Price", item.Price);
                        // Add other parameters as necessary

                        cmd.ExecuteNonQuery();
                    }
                }

                transaction.Commit();
            }
            catch
            {
                transaction.Rollback();
                throw;
            }
        }
    }
}

הבעיה היא שזה לא מתאם אם רוצים לחסוך במשאבים.

הדרך היותר יעילה לשלוח את הרשימה היא על ידי יצירה מקומית של טבלה ב-API ושליחה של כל הטבלה כמשתנה ל-SQL.

שלב 1: יצירת סוג הטבלה ב-SQL, הטבלה הזאת תקבל את הנתונים שיגיעו.

CREATE TYPE dbo.OrderItemType AS TABLE
(
    OrderId INT,
    ProductId INT,
    Price DECIMAL(18, 2)
    -- Define other columns as needed
);

במקרה הזה יש לי טבלת ItemsOrder שאליה אני רוצה להכניס את הנתונים, הטבלה שאני יוצרת עכשיו היא מעין טבלה מקשרת שמקבלת אליה את הנתונים מה-API ומכניסה אותם לטבלת ItemsOrder.

את ההגדרות של הטבלה אפשר למצוא אחר כך תחת תיקיית Programming של מסד הנתונים בתוך תיקיית Type ושם ב- User-Defined Table Types.

בטבלה הזאת אני מגדירה רק את הפרמטרים שמגיעים מה-API, אין צורך להגדיר את כל השדות של הטבלה שאליה אני רוצה להכניס את הנתונים.

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

שלב 2: יצירת הפרוצדורה שמקבלת את הטבלה.

CREATE PROCEDURE dbo.InsertOrderItems
    @OrderItems dbo.OrderItemType READONLY
AS
BEGIN
    INSERT INTO orderItems (OrderId, ProductId, Price)
    SELECT OrderId, ProductId, Price FROM @OrderItems;
END;

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

שלב 3: יצירת טבלה ב-C# ששולחת את הנתונים.

public void SaveOrderItems(List<OrderItem> orderItems)
{
    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        conn.Open();

        using (SqlCommand cmd = new SqlCommand("dbo.InsertOrderItems", conn))
        {
            cmd.CommandType = CommandType.StoredProcedure;

            DataTable table = new DataTable();
            table.Columns.Add("OrderId", typeof(int));
            table.Columns.Add("ProductId", typeof(int));
            table.Columns.Add("Price", typeof(decimal));
            // Add other columns as necessary

            foreach (var item in orderItems)
            {
                table.Rows.Add(item.OrderId, item.ProductId, item.Price);
                // Add other values as necessary
            }

            SqlParameter tvpParam = cmd.Parameters.AddWithValue("@OrderItems", table);
            tvpParam.SqlDbType = SqlDbType.Structured;
            tvpParam.TypeName = "dbo.OrderItemType";

            cmd.ExecuteNonQuery();
        }
    }
}