راهنمای جامع یکپارچه‌سازی و پایگاه داده Sadr Scales

IF NOT EXISTS
(
    SELECT 1
    FROM dbo.SADR_ItemClass
    WHERE ItemClassCode = @ItemClassCode
)
BEGIN
    INSERT INTO dbo.SADR_ItemClass
        (ItemClassCode, ItemClassName, Descriptions)
    VALUES
        (@ItemClassCode, @ItemClassName, @Descriptions);
END
ELSE
BEGIN
    UPDATE dbo.SADR_ItemClass
    SET ItemClassName = @ItemClassName,
        Descriptions = @Descriptions
    WHERE ItemClassCode = @ItemClassCode;
END;

IF NOT EXISTS
(
    SELECT 1 FROM dbo.SADR_Store WHERE StoreCode = @StoreCode
)
    INSERT INTO dbo.SADR_Store(StoreCode, StoreName, Descriptions)
    VALUES(@StoreCode, @StoreName, @Descriptions);
ELSE
    UPDATE dbo.SADR_Store
    SET StoreName=@StoreName, Descriptions=@Descriptions
    WHERE StoreCode=@StoreCode;

SET XACT_ABORT ON;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;

IF EXISTS
(
    SELECT 1
    FROM dbo.SADR_Scale WITH (UPDLOCK, HOLDLOCK)
    WHERE ScaleIP = @ScaleIP OR ScaleID = @ScaleID
)
    THROW 50001, 'Duplicate ScaleIP or ScaleID', 1;

INSERT INTO dbo.SADR_Scale
(
    ScaleID, ScaleIP, Port, Category,
    DeviceName, StoreName, StoreCode, ItemClassCode,
    AutoSendItems, AutoGetInvoice,
    GetScaleDetail, SendScaleDetail, Used,
    HotKeyCountPerPage, HotKeyPageCount
)
VALUES
(
    @ScaleID, @ScaleIP, @Port, @Category,
    @DeviceName, @StoreName, @StoreCode, @ItemClassCode,
    @AutoSendItems, @AutoGetInvoice,
    1, 0, @Used,
    @HotKeyCountPerPage, @HotKeyPageCount
);

INSERT INTO dbo.SADR_ScaleItemClass(ScaleID, ItemClassCode)
VALUES(@ScaleID, @ItemClassCode);

UPDATE dbo.SADR_Scale
SET LastSendItem = 0, LastSendKey = 0
WHERE ScaleID = @ScaleID;

COMMIT;

SET XACT_ABORT ON;
BEGIN TRANSACTION;

UPDATE dbo.SADR_Scale
SET DeviceName = @DeviceName,
    StoreName = @StoreName,
    StoreCode = @StoreCode,
    ItemClassCode = @ItemClassCode,
    AutoSendItems = @AutoSendItems,
    AutoGetInvoice = @AutoGetInvoice,
    Used = @Used,
    GetScaleDetail = 0,
    SendScaleDetail = 1,
    LastSendItem = 0,
    LastSendKey = 0
WHERE ScaleID = @ScaleID;

DELETE FROM dbo.SADR_ScaleItemClass
WHERE ScaleID = @ScaleID;

INSERT INTO dbo.SADR_ScaleItemClass(ScaleID, ItemClassCode)
VALUES(@ScaleID, @ItemClassCode);

COMMIT;

UPDATE dbo.SADR_Scale
SET Used = 0
WHERE ScaleID = @ScaleID;

UPDATE dbo.SADR_Scale
SET Used = 1
WHERE ScaleID = @ScaleID;

SET XACT_ABORT ON;
BEGIN TRANSACTION;

DELETE FROM dbo.SADR_ScaleItemClass
WHERE ScaleID = @ScaleID;

INSERT INTO dbo.SADR_ScaleItemClass(ScaleID, ItemClassCode)
VALUES(@ScaleID, @Group1),
      (@ScaleID, @Group2);

UPDATE dbo.SADR_Scale
SET LastSendItem = 0
WHERE ScaleID = @ScaleID;

COMMIT;

SET XACT_ABORT ON;
BEGIN TRANSACTION;

IF NOT EXISTS (SELECT 1 FROM dbo.SADR_Item WHERE PluNo=@PluNo)
BEGIN
    INSERT INTO dbo.SADR_Item
    (
        ItemClassCode, PluNo, PluUnit, UnitPrice,
        PrintFormat, PluCost, BarFormat, BarFlags,
        ItemCode, IndexBarcode, Tare,
        ShelfDate, ShelfDatePrint, SaleDatePrint, SaleTimePrint,
        OnlyTare, TaxRate, PluName,
        Text1, Text2, Text3, Text4, Text5, Text6, Text7,
        DeleteFlag
    )
    VALUES
    (
        @ItemClassCode, @PluNo, @PluUnit, @UnitPrice,
        @PrintFormat, @PluCost, @BarFormat, @BarFlags,
        @ItemCode, @IndexBarcode, @Tare,
        @ShelfDate, @ShelfDatePrint, @SaleDatePrint, @SaleTimePrint,
        @OnlyTare, @TaxRate, @PluName,
        @Text1, @Text2, @Text3, @Text4, @Text5, @Text6, @Text7,
        @DeleteFlag
    );
END
ELSE
BEGIN
    UPDATE dbo.SADR_Item
    SET ItemClassCode=@ItemClassCode,
        PluUnit=@PluUnit,
        UnitPrice=@UnitPrice,
        PrintFormat=@PrintFormat,
        PluCost=@PluCost,
        BarFormat=@BarFormat,
        BarFlags=@BarFlags,
        ItemCode=@ItemCode,
        IndexBarcode=@IndexBarcode,
        Tare=@Tare,
        ShelfDate=@ShelfDate,
        ShelfDatePrint=@ShelfDatePrint,
        SaleDatePrint=@SaleDatePrint,
        SaleTimePrint=@SaleTimePrint,
        OnlyTare=@OnlyTare,
        TaxRate=@TaxRate,
        PluName=@PluName,
        Text1=@Text1, Text2=@Text2, Text3=@Text3, Text4=@Text4,
        Text5=@Text5, Text6=@Text6, Text7=@Text7,
        DeleteFlag=@DeleteFlag
    WHERE PluNo=@PluNo;
END;

COMMIT;

IF EXISTS
(
    SELECT 1
    FROM dbo.SADR_KeyAssignment
    WHERE ItemClassCode=@Group
      AND PageNo=@PageNo
      AND KeyNo=@KeyNo
)
    UPDATE dbo.SADR_KeyAssignment
    SET PluNo=@PluNo
    WHERE ItemClassCode=@Group
      AND PageNo=@PageNo
      AND KeyNo=@KeyNo;
ELSE
    INSERT INTO dbo.SADR_KeyAssignment
        (ItemClassCode, PageNo, KeyNo, PluNo)
    VALUES
        (@Group, @PageNo, @KeyNo, @PluNo);

SET XACT_ABORT ON;
BEGIN TRANSACTION;

DELETE FROM dbo.SADR_ScaleItemMap
WHERE ScaleID=@ScaleID;

INSERT INTO dbo.SADR_ScaleItemMap
    (ScaleID, PluNo, ItemCode, PageNo, KeyNo)
VALUES
    (@ScaleID, @PluNo1, @ItemCode1, @PageNo1, @KeyNo1),
    (@ScaleID, @PluNo2, @ItemCode2, NULL, NULL);

UPDATE dbo.SADR_Scale
SET LastSendItem=0,
    LastSendKey=0
WHERE ScaleID=@ScaleID;

COMMIT;

SELECT TOP (@BatchSize)
    ID, DeviceNo, Identify, [DateTime], FID, SID,
    Salesman, SubID, TotalPrice, PLU, Class, Dept,
    Amount, Unit, LogType, Tax,
    Text1, Text2, Text3, Text4,
    UnitPrice, CoFID, PLUName
FROM dbo.SADR_Logs
WHERE ID > @LastProcessedId
ORDER BY ID ASC;

SELECT
    t.TotalID, t.ScaleID, t.SaleDateTime,
    t.ReceiptNo, t.TotalBarcode, t.ItemBarcode,
    t.NTrans, t.PriceWTax, t.ClerkNo, t.LableStatus,
    d.DetailID, d.TransNo, d.PluNo,
    d.Weight, d.QTY, d.Uprice, d.ActPrice,
    d.ItemBarcode AS DetailItemBarcode,
    d.ItemStatus
FROM dbo.SADR_Total t
LEFT JOIN dbo.SADR_Detail d
    ON d.TotalID = t.TotalID
WHERE t.TotalBarcode = @ScannedBarcode
ORDER BY d.DetailID;

-- Step 1: read the next unprocessed invoice.
DECLARE @TotalID int;

SELECT TOP (1)
    @TotalID = TotalID
FROM dbo.SADR_Total
WHERE ISNULL(LableStatus, 0) = 0
ORDER BY TotalID;

IF @TotalID IS NOT NULL
BEGIN
    SELECT *
    FROM dbo.SADR_Total
    WHERE TotalID = @TotalID;

    SELECT *
    FROM dbo.SADR_Detail
    WHERE TotalID = @TotalID
    ORDER BY DetailID;
END;

-- Step 2: run only after the destination commit succeeds.
SET XACT_ABORT ON;
BEGIN TRANSACTION;

UPDATE dbo.SADR_Total
SET LableStatus = 1
WHERE TotalID = @TotalID
  AND ISNULL(LableStatus, 0) = 0;

UPDATE dbo.SADR_Detail
SET ItemStatus = 1
WHERE TotalID = @TotalID
  AND ISNULL(ItemStatus, 0) = 0;

COMMIT;

DECLARE @TotalBarcode varchar(50) =
    '25' + RIGHT('000' + CAST(@ScaleID AS varchar(10)), 3)
         + RIGHT('000000000' + CAST(@FID AS varchar(20)), 9);

SELECT *
FROM dbo.SADR_Total
WHERE ScaleID=@ScaleID AND TotalBarcode=@TotalBarcode;

SELECT d.*
FROM dbo.SADR_Detail d
INNER JOIN dbo.SADR_Total t ON t.TotalID=d.TotalID
WHERE t.ScaleID=@ScaleID AND t.TotalBarcode=@TotalBarcode
ORDER BY d.DetailID;

SELECT *
FROM dbo.SADR_Logs
WHERE DeviceNo=@ScaleID AND FID=@FID
ORDER BY SubID;

SET XACT_ABORT ON;
BEGIN TRANSACTION;

-- 1) Group
IF NOT EXISTS (SELECT 1 FROM dbo.SADR_ItemClass WHERE ItemClassCode='10')
    INSERT INTO dbo.SADR_ItemClass(ItemClassCode, ItemClassName, Descriptions)
    VALUES('10', N'Fruit', N'Fruit group');

-- 2) Store
IF NOT EXISTS (SELECT 1 FROM dbo.SADR_Store WHERE StoreCode='1')
    INSERT INTO dbo.SADR_Store(StoreCode, StoreName, Descriptions)
    VALUES('1', N'Main Store', N'');

-- 3) Scale
IF NOT EXISTS (SELECT 1 FROM dbo.SADR_Scale WHERE ScaleID=1 OR ScaleIP='192.168.1.50')
BEGIN
    INSERT INTO dbo.SADR_Scale
    (
        ScaleID, ScaleIP, Port, Category,
        StoreCode, StoreName, ItemClassCode, DeviceName,
        HotKeyCountPerPage, HotKeyPageCount,
        AutoSendItems, AutoGetInvoice, Used
    )
    VALUES
    (
        1, '192.168.1.50', 33581, 'LSG',
        '1', N'Main Store', '10', N'Checkout Scale 1',
        63, 3,
        1, 0, 1
    );

    INSERT INTO dbo.SADR_ScaleItemClass(ScaleID, ItemClassCode)
    VALUES(1, '10');
END;

-- 4) Item
IF NOT EXISTS (SELECT 1 FROM dbo.SADR_Item WHERE PluNo=1001)
    INSERT INTO dbo.SADR_Item
        (ItemClassCode, PluNo, PluName, PluUnit, UnitPrice, DeleteFlag)
    VALUES
        ('10', 1001, N'Apple', 3, 150000, 0);
ELSE
    UPDATE dbo.SADR_Item
    SET ItemClassCode='10', PluName=N'Apple',
        PluUnit=3, UnitPrice=150000, DeleteFlag=0
    WHERE PluNo=1001;

-- 5) Group HotKey: page 0 key 1
IF EXISTS
(
    SELECT 1 FROM dbo.SADR_KeyAssignment
    WHERE ItemClassCode='10' AND PageNo=0 AND KeyNo=1
)
    UPDATE dbo.SADR_KeyAssignment
    SET PluNo=1001
    WHERE ItemClassCode='10' AND PageNo=0 AND KeyNo=1;
ELSE
    INSERT INTO dbo.SADR_KeyAssignment(ItemClassCode,PageNo,KeyNo,PluNo)
    VALUES('10',0,1,1001);

UPDATE dbo.SADR_Scale
SET LastSendItem=0, LastSendKey=0
WHERE ScaleID=1;

COMMIT;

-- Step 1: read the scanned invoice.
DECLARE @TotalID int;

SELECT TOP (1)
    @TotalID = TotalID
FROM dbo.SADR_Total
WHERE TotalBarcode = @ScannedBarcode;

IF @TotalID IS NULL
    THROW 50001, 'Invoice barcode not found', 1;

SELECT *
FROM dbo.SADR_Total
WHERE TotalID = @TotalID;

SELECT *
FROM dbo.SADR_Detail
WHERE TotalID = @TotalID
ORDER BY DetailID;

SET XACT_ABORT ON;
BEGIN TRANSACTION;

UPDATE dbo.SADR_Total
SET LableStatus = 1
WHERE TotalID = @TotalID
  AND ISNULL(LableStatus, 0) = 0;

UPDATE dbo.SADR_Detail
SET ItemStatus = 1
WHERE TotalID = @TotalID
  AND ISNULL(ItemStatus, 0) = 0;

COMMIT;

IF OBJECT_ID(N'dbo.SADR_ItemClass', N'U') IS NULL THROW 50001, 'SADR_ItemClass missing', 1;
IF OBJECT_ID(N'dbo.SADR_Store', N'U') IS NULL THROW 50002, 'SADR_Store missing', 1;
IF OBJECT_ID(N'dbo.SADR_Scale', N'U') IS NULL THROW 50003, 'SADR_Scale missing', 1;
IF OBJECT_ID(N'dbo.SADR_ScaleItemClass', N'U') IS NULL THROW 50004, 'SADR_ScaleItemClass missing', 1;
IF OBJECT_ID(N'dbo.SADR_Item', N'U') IS NULL THROW 50005, 'SADR_Item missing', 1;
IF OBJECT_ID(N'dbo.SADR_KeyAssignment', N'U') IS NULL THROW 50006, 'SADR_KeyAssignment missing', 1;
IF OBJECT_ID(N'dbo.SADR_ScaleItemMap', N'U') IS NULL THROW 50007, 'SADR_ScaleItemMap missing', 1;
IF OBJECT_ID(N'dbo.SADR_Total', N'U') IS NULL THROW 50008, 'SADR_Total missing', 1;
IF OBJECT_ID(N'dbo.SADR_Detail', N'U') IS NULL THROW 50009, 'SADR_Detail missing', 1;
IF OBJECT_ID(N'dbo.SADR_Logs', N'U') IS NULL THROW 50010, 'SADR_Logs missing', 1;

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

public static class SadrSchema
{
    public static SqlConnection Open(string connectionString)
    {
        SqlConnection connection = new SqlConnection(connectionString);
        connection.Open();
        return connection;
    }

    public static void Validate(SqlConnection connection)
    {
        const string sql = @"
IF OBJECT_ID(N'dbo.SADR_ItemClass', N'U') IS NULL
    THROW 50001, 'SADR_ItemClass missing', 1;
IF OBJECT_ID(N'dbo.SADR_Store', N'U') IS NULL
    THROW 50002, 'SADR_Store missing', 1;
IF OBJECT_ID(N'dbo.SADR_Scale', N'U') IS NULL
    THROW 50003, 'SADR_Scale missing', 1;
IF OBJECT_ID(N'dbo.SADR_ScaleItemClass', N'U') IS NULL
    THROW 50004, 'SADR_ScaleItemClass missing', 1;
IF OBJECT_ID(N'dbo.SADR_Item', N'U') IS NULL
    THROW 50005, 'SADR_Item missing', 1;
IF OBJECT_ID(N'dbo.SADR_KeyAssignment', N'U') IS NULL
    THROW 50006, 'SADR_KeyAssignment missing', 1;
IF OBJECT_ID(N'dbo.SADR_ScaleItemMap', N'U') IS NULL
    THROW 50007, 'SADR_ScaleItemMap missing', 1;
IF OBJECT_ID(N'dbo.SADR_Total', N'U') IS NULL
    THROW 50008, 'SADR_Total missing', 1;
IF OBJECT_ID(N'dbo.SADR_Detail', N'U') IS NULL
    THROW 50009, 'SADR_Detail missing', 1;
IF OBJECT_ID(N'dbo.SADR_Logs', N'U') IS NULL
    THROW 50010, 'SADR_Logs missing', 1;";

        using (SqlCommand command = new SqlCommand(sql, connection))
        {
            command.CommandTimeout = 30;
            command.ExecuteNonQuery();
        }
    }
}

public sealed class SadrItem
{
    public string ItemClassCode { get; set; }
    public int PluNo { get; set; }
    public int PluUnit { get; set; }
    public int UnitPrice { get; set; }
    public int PrintFormat { get; set; }
    public int PluCost { get; set; }
    public int BarFormat { get; set; }
    public int BarFlags { get; set; }
    public string ItemCode { get; set; }
    public string IndexBarcode { get; set; }
    public double Tare { get; set; }
    public int ShelfDate { get; set; }
    public int ShelfDatePrint { get; set; }
    public int SaleDatePrint { get; set; }
    public int SaleTimePrint { get; set; }
    public int OnlyTare { get; set; }
    public double TaxRate { get; set; }
    public string PluName { get; set; }
    public string Text1 { get; set; }
    public string Text2 { get; set; }
    public string Text3 { get; set; }
    public string Text4 { get; set; }
    public string Text5 { get; set; }
    public string Text6 { get; set; }
    public string Text7 { get; set; }
    public int DeleteFlag { get; set; }
}

public static void UpsertItemClass(
    SqlConnection connection,
    string code,
    string name,
    string description)
{
    const string sql = @"
IF NOT EXISTS
(
    SELECT 1
    FROM dbo.SADR_ItemClass
    WHERE ItemClassCode = @Code
)
BEGIN
    INSERT INTO dbo.SADR_ItemClass
        (ItemClassCode, ItemClassName, Descriptions)
    VALUES
        (@Code, @Name, @Description);
END
ELSE
BEGIN
    UPDATE dbo.SADR_ItemClass
    SET ItemClassName = @Name,
        Descriptions = @Description
    WHERE ItemClassCode = @Code;
END;";

    using (SqlCommand command = new SqlCommand(sql, connection))
    {
        command.Parameters.Add("@Code", SqlDbType.VarChar, 50).Value = code;
        command.Parameters.Add("@Name", SqlDbType.NVarChar, 100).Value =
            (object)name ?? DBNull.Value;
        command.Parameters.Add("@Description", SqlDbType.NVarChar, 150).Value =
            (object)description ?? DBNull.Value;
        command.ExecuteNonQuery();
    }
}

public static void UpsertItem(SqlConnection connection, SadrItem item)
{
    const string sql = @"
IF NOT EXISTS (SELECT 1 FROM dbo.SADR_Item WHERE PluNo=@PluNo)
BEGIN
    INSERT INTO dbo.SADR_Item
    (
        ItemClassCode, PluNo, PluUnit, UnitPrice,
        PrintFormat, PluCost, BarFormat, BarFlags,
        ItemCode, IndexBarcode, Tare,
        ShelfDate, ShelfDatePrint, SaleDatePrint, SaleTimePrint,
        OnlyTare, TaxRate, PluName,
        Text1, Text2, Text3, Text4, Text5, Text6, Text7,
        DeleteFlag
    )
    VALUES
    (
        @ItemClassCode, @PluNo, @PluUnit, @UnitPrice,
        @PrintFormat, @PluCost, @BarFormat, @BarFlags,
        @ItemCode, @IndexBarcode, @Tare,
        @ShelfDate, @ShelfDatePrint, @SaleDatePrint, @SaleTimePrint,
        @OnlyTare, @TaxRate, @PluName,
        @Text1, @Text2, @Text3, @Text4, @Text5, @Text6, @Text7,
        @DeleteFlag
    );
END
ELSE
BEGIN
    UPDATE dbo.SADR_Item
    SET ItemClassCode=@ItemClassCode,
        PluUnit=@PluUnit,
        UnitPrice=@UnitPrice,
        PrintFormat=@PrintFormat,
        PluCost=@PluCost,
        BarFormat=@BarFormat,
        BarFlags=@BarFlags,
        ItemCode=@ItemCode,
        IndexBarcode=@IndexBarcode,
        Tare=@Tare,
        ShelfDate=@ShelfDate,
        ShelfDatePrint=@ShelfDatePrint,
        SaleDatePrint=@SaleDatePrint,
        SaleTimePrint=@SaleTimePrint,
        OnlyTare=@OnlyTare,
        TaxRate=@TaxRate,
        PluName=@PluName,
        Text1=@Text1, Text2=@Text2, Text3=@Text3, Text4=@Text4,
        Text5=@Text5, Text6=@Text6, Text7=@Text7,
        DeleteFlag=@DeleteFlag
    WHERE PluNo=@PluNo;
END;";

    using (SqlTransaction tx = connection.BeginTransaction())
    using (SqlCommand command = new SqlCommand(sql, connection, tx))
    {
        command.Parameters.Add("@ItemClassCode", SqlDbType.VarChar, 50).Value = item.ItemClassCode;
        command.Parameters.Add("@PluNo", SqlDbType.Int).Value = item.PluNo;
        command.Parameters.Add("@PluUnit", SqlDbType.Int).Value = item.PluUnit;
        command.Parameters.Add("@UnitPrice", SqlDbType.Int).Value = item.UnitPrice;
        command.Parameters.Add("@PrintFormat", SqlDbType.Int).Value = item.PrintFormat;
        command.Parameters.Add("@PluCost", SqlDbType.Int).Value = item.PluCost;
        command.Parameters.Add("@BarFormat", SqlDbType.Int).Value = item.BarFormat;
        command.Parameters.Add("@BarFlags", SqlDbType.Int).Value = item.BarFlags;
        command.Parameters.Add("@ItemCode", SqlDbType.VarChar, 10).Value =
            (object)item.ItemCode ?? DBNull.Value;
        command.Parameters.Add("@IndexBarcode", SqlDbType.VarChar, 50).Value =
            (object)item.IndexBarcode ?? DBNull.Value;
        command.Parameters.Add("@Tare", SqlDbType.Float).Value = item.Tare;
        command.Parameters.Add("@ShelfDate", SqlDbType.Int).Value = item.ShelfDate;
        command.Parameters.Add("@ShelfDatePrint", SqlDbType.Int).Value = item.ShelfDatePrint;
        command.Parameters.Add("@SaleDatePrint", SqlDbType.Int).Value = item.SaleDatePrint;
        command.Parameters.Add("@SaleTimePrint", SqlDbType.Int).Value = item.SaleTimePrint;
        command.Parameters.Add("@OnlyTare", SqlDbType.Int).Value = item.OnlyTare;
        command.Parameters.Add("@TaxRate", SqlDbType.Float).Value = item.TaxRate;
        command.Parameters.Add("@PluName", SqlDbType.NVarChar, 100).Value =
            (object)item.PluName ?? DBNull.Value;
        command.Parameters.Add("@DeleteFlag", SqlDbType.Int).Value = item.DeleteFlag;

        string[] values = { item.Text1, item.Text2, item.Text3, item.Text4,
                            item.Text5, item.Text6, item.Text7 };
        for (int i = 0; i < values.Length; i++)
        {
            command.Parameters.Add("@Text" + (i + 1), SqlDbType.NVarChar, 100).Value =
                (object)values[i] ?? DBNull.Value;
        }

        command.ExecuteNonQuery();
        tx.Commit();
    }
}

public static DataTable ReadSalesAfterId(
    SqlConnection connection,
    long lastProcessedId,
    int batchSize)
{
    const string sql = @"
SELECT TOP (@BatchSize)
    ID, DeviceNo, Identify, [DateTime], FID, SID,
    Salesman, SubID, TotalPrice, PLU, Class, Dept,
    Amount, Unit, LogType, Tax,
    Text1, Text2, Text3, Text4,
    UnitPrice, CoFID, PLUName
FROM dbo.SADR_Logs
WHERE ID > @LastProcessedId
ORDER BY ID ASC;";

    using (SqlCommand command = new SqlCommand(sql, connection))
    {
        command.Parameters.Add("@BatchSize", SqlDbType.Int).Value = batchSize;
        command.Parameters.Add("@LastProcessedId", SqlDbType.BigInt).Value = lastProcessedId;

        using (SqlDataAdapter adapter = new SqlDataAdapter(command))
        {
            DataTable table = new DataTable();
            adapter.Fill(table);
            return table;
        }
    }
}

public static DataSet ReadInvoiceByBarcode(
    SqlConnection connection,
    string totalBarcode)
{
    const string headerSql = @"
SELECT *
FROM dbo.SADR_Total
WHERE TotalBarcode=@TotalBarcode;";

    const string detailSql = @"
SELECT d.*
FROM dbo.SADR_Detail d
INNER JOIN dbo.SADR_Total t ON t.TotalID=d.TotalID
WHERE t.TotalBarcode=@TotalBarcode
ORDER BY d.DetailID;";

    DataSet result = new DataSet();

    using (SqlCommand header = new SqlCommand(headerSql, connection))
    {
        header.Parameters.Add("@TotalBarcode", SqlDbType.VarChar, 50).Value = totalBarcode;
        using (SqlDataAdapter adapter = new SqlDataAdapter(header))
            adapter.Fill(result, "Header");
    }

    using (SqlCommand detail = new SqlCommand(detailSql, connection))
    {
        detail.Parameters.Add("@TotalBarcode", SqlDbType.VarChar, 50).Value = totalBarcode;
        using (SqlDataAdapter adapter = new SqlDataAdapter(detail))
            adapter.Fill(result, "Detail");
    }

    return result;
}

public static void AcknowledgeInvoice(
    SqlConnection connection,
    int totalId)
{
    const string sql = @"
UPDATE dbo.SADR_Total
SET LableStatus=1
WHERE TotalID=@TotalID
  AND ISNULL(LableStatus,0)=0;

UPDATE dbo.SADR_Detail
SET ItemStatus=1
WHERE TotalID=@TotalID
  AND ISNULL(ItemStatus,0)=0;";

    using (SqlTransaction tx = connection.BeginTransaction())
    using (SqlCommand command = new SqlCommand(sql, connection, tx))
    {
        command.Parameters.Add("@TotalID", SqlDbType.Int).Value = totalId;
        command.ExecuteNonQuery();
        tx.Commit();
    }
}

static void Main()
{
    string connectionString = LoadConnectionStringFromSecureSettings();
    using (SqlConnection connection = SadrSchema.Open(connectionString))
    {
        SadrSchema.Validate(connection);
        UpsertItemClass(connection, "10", "Fruit", "Fruit group");
        DataTable sales = ReadSalesAfterId(connection, 0, 100);
        foreach (DataRow row in sales.Rows)
        {
            // 1) Map row to the destination model.
            // 2) Save it idempotently in the destination database.
            // 3) Persist the last successfully processed SADR_Logs.ID.
        }
    }
}