Problem with logic in passing data to a stored procedure EF in C#

Question

I have a stored procedure with 13 parameters in a SQL Server database. And in my C# application, I need to insert data to that stored procedure and I am able to insert 1 value at a time but I need to be able to insert multiple values like 5 or 10 or more. I have 5 arrays that will have many values to insert to that stored procedure but is not inserting if the arrays have more than 1 value, I am thinking my loop is not done correctly.

Please take a look below.

Class that will create a method for the stored procedure

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using BarcodeReceivingApp.Persistence;
using BarcodeReceivingApp.Persistence.Repositories;

namespace BarcodeReceivingApp.Functionality
{
    public class StoredProcedureInsert
    {
        private readonly BarcodeReceivingFootPrintDbContext _barcodeReceivingFootPrintDbContext = new BarcodeReceivingFootPrintDbContext();

        public void CallManualBlindBarcodeParsingEventRequestFootPrintProcedure(decimal actualPackagedAmount, int actualPackagedPackId, string lotLookupCode,
            int warehouseId, int materialId, string vendorLotLookupCode, DateTime vendorLotManufactureDate, 
            DateTime vendorLotExpirationDate, int shipmentId, decimal netWeight, 
            decimal grossWeight, string serialLookupCode, string licensePlateLookupCode)
        {
            _barcodeReceivingFootPrintDbContext.Database
                .ExecuteSqlCommand("EXEC noram_reporting.ManualBlindBarcodeParsingEventRequest " +
                                   "@ActualPackagedAmount, @ActualPackagedPackId, @LotLookupCode, @WarehouseId, @MaterialId, @VendorLotLookupCode," +
                                   "@VendorLotManufactureDate, @VendorLotExpirationDate, @ShipmentId, @netWeight, @grossWeight, @serialLookupCode, @licensePlateLookupCode",
                    new SqlParameter("@ActualPackagedAmount", actualPackagedAmount),
                    new SqlParameter("@ActualPackagedPackId", actualPackagedPackId),
                    new SqlParameter("@LotLookupCode", lotLookupCode),
                    new SqlParameter("@WarehouseId", warehouseId),
                    new SqlParameter("@MaterialId", materialId),
                    new SqlParameter("@VendorLotLookupCode", vendorLotLookupCode),
                    new SqlParameter("@VendorLotManufactureDate", vendorLotManufactureDate),
                    new SqlParameter("@VendorLotExpirationDate", vendorLotExpirationDate),
                    new SqlParameter("@ShipmentId", shipmentId),
                    new SqlParameter("@netWeight", netWeight),
                    new SqlParameter("@grossWeight", grossWeight),
                    new SqlParameter("@serialLookupCode", serialLookupCode),
                    new SqlParameter("@licensePlateLookupCode", licensePlateLookupCode)
                    );
        }
    }
}

Here then I am calling that method to insert to each parameter

private void SendStoredProcedureDataToFootPrint()
{
    var lotList = _connection.ParseLot();
    var netWeightList = _connection.ParseNetWeight();
    var grossWeightList = _connection.ParseGrossWeight();
    var serialNumberList = _connection.ParseSerialNumber();
    var material = _unitOfWork.Shipments.GetLastShipmentMaterialEntry();
    var scanCounts = _connection.CountReceivingBarcodeEntries();
    var packagingId = _unitOfWork.Materials.GetPackagingId();
    var warehouse = _unitOfWork.Warehouses.GetWarehouseIdQuery();
    var shipment = _unitOfWork.Shipments.GetLastShipmentIdEntry();
    var licensePlate = _unitOfWork.LicensePlates.GetLastCreatedLicensePlate();

    try
    {
        for (var i = 0; i < _connection.GetBarcodeList().Count ; i++)
        {

            _storedProcedureInsert.CallManualBlindBarcodeParsingEventRequestFootPrintProcedure(scanCounts, packagingId, lotList[i], warehouseId: warehouse, materialId: 5785,
                vendorLotLookupCode: lotList[i], vendorLotManufactureDate: DateTime.Now,
                vendorLotExpirationDate: DateTime.Now, shipmentId: shipment,
                netWeight: Convert.ToDecimal(netWeightList[i]) / 100,
                grossWeight: Convert.ToDecimal(grossWeightList[i]) / 100,
                serialLookupCode: serialNumberList[i], licensePlateLookupCode: licensePlate);
        }
    }
    catch (Exception exception)
    {
        MetroMessageBox.Show(null, exception.Message, "Error Message", MessageBoxButtons.OK, MessageBoxIcon.Warning);
        throw;
    }
}

So, like I said this works if I insert 1 data for each parameter but If the lotlist, netweightlist, grossweightlist and serialnumberlist arrays have more than 1 data it will not send to the stored procedure.

So the goal is to insert data it doesn't matter have many records I need to insert it could be one or many at one time.

Could not find a good solution on this in other questions like stackoverflow or google.

Answer 1

Please use Table Valued Parameters for handling these kind of scenarios. But you have to convert your data into DataTable. For each array there would be corresponding data table.

I would like to give you an idea.

1. Convert every C# list into DataTable.

   Convert each array ( e.g. serialNumberList into DataTable. ) ( If it's just primitive data, then only one column would correspond, if it's object then complete row. In that case for every row : each cell would corresponds to data corresponding to C# object data member. )

2. Create In SQL Server, a Data Type under "Programmability" which would receive these dataTables from your C# code. This Data Type would have exactly the same data with same SQL individual type that was in C# structure.

3. Whatever you are doing inside the loop ( regarding conversion or doing some Maths, do it in the same list before even passing into SQL. Eventually, pass the data to SQL when it is already processed ).

4. Before passing the data to SQL, you should know that data must be passed as Table Valued Parameter. So it's data type must be selected as SQLDataType.Structured.

5. For every non list data item, dont create any DataTable. Just pass it as it is by using SQL native data type from .NET.

Let me know if you solve your problem in this way. I am sure, it would work out. Moreover, If you further want to know how to pass complicated structure / Simple Native data type Array or Object Array , refer following link.

T-SQL - Insert Data into Parent and Child Tables.