how to pass the params to stored procedure

Basit Nisar

in my c# code the request payload is:

{"lstContractInvoicePerdays":[{"ContractInvoicePerDayID":"1061","LastModifiedDate":"2023-04-24T04:37:44"},{"ContractInvoicePerDayID":"1135","LastModifiedDate":"2023-04-24T04:37:45"}]}

Let me help to pass the params in stored procedure as existing logic is not helping

try
{
    int? BaseStateProvinceID = null;
    List lstContractInvoicePerdays = new List();
    lstContractInvoicePerdays = staffCIPDApprovedViewModel.lstContractInvoicePerdays.ToList();
    if (lstContractInvoicePerdays.Count == 1)
    {
    }
    CIPDPushReqMod timeCardPushRequest = new CIPDPushReqMod();
    List  ContractInvoicePerDayIDs = staffCIPDApprovedViewModel.lstContractInvoicePerdays.Select(x=> x.ContractInvoicePerDayIDs.Split(',').Select(int.Parse).ToList();
    using (SqlConnection connection = new SqlConnection(db.Database.Connection.ConnectionString))
    {
        try
        {
            using (SqlCommand command = new SqlCommand("spGetCIPDForPushToLawson", connection))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add("@ContractInvoicePerDayIDs", SqlDbType.NVarChar).Value = ContractInvoicePerDayIDs as object ?? DBNull.Value;
                if (connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
                connection.Open();
                using (var reader = command.ExecuteReader())
                {
                    timeCardPushRequest.TimeCardLst = ((IObjectContextAdapter)db).ObjectContext.Translate(reader).ToList();
                    reader.NextResult();
                    timeCardPushRequest.AdditionalComponents = ((IObjectContextAdapter)db).ObjectContext.Translate(reader).ToList();
                }
            }
        }
        catch (Exception)
        {
        }

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Tuhin Paul3y ago

Accepted answer

As per your C# code and stored procedure, it seems that you are trying to pass a list of ContractInvoicePerDayIDs as a parameter to the stored procedure. To achieve this, you need to convert the list of ContractInvoicePerDayIDs into a comma-separated string and then pass it as a parameter to the stored procedure.

Here is the code :

try
{
    List lstContractInvoicePerdays = staffCIPDApprovedViewModel.lstContractInvoicePerdays.ToList();

    string contractInvoicePerDayIDs = string.Join(",", lstContractInvoicePerdays.Select(x => x.ContractInvoicePerDayID));

    using (SqlConnection connection = new SqlConnection(db.Database.Connection.ConnectionString))
    {
        using (SqlCommand command = new SqlCommand("spGetCIPDForPushToLawson", connection))
        {
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@ContractInvoicePerDayIDs", SqlDbType.NVarChar).Value = contractInvoicePerDayIDs;
            if (connection.State != ConnectionState.Closed)
            {
                connection.Close();
            }
            connection.Open();
            using (var reader = command.ExecuteReader())
            {
                timeCardPushRequest.TimeCardLst = ((IObjectContextAdapter)db).ObjectContext.Translate(reader).ToList();
                reader.NextResult();
                timeCardPushRequest.AdditionalComponents = ((IObjectContextAdapter)db).ObjectContext.Translate(reader).ToList();
            }
        }
    }
}
catch (Exception)
{
}

I removed the BaseStateProvinceID variable since it is not being used, and simplified the ContractInvoicePerDayIDs parameter by removing the unnecessary Split and Select(int.Parse) calls.

Basit Nisar3y ago

The procedure is :

ALTER PROCEDURE [dbo].[spGetCIPHForPushToLawson]
@ContractInvoicePerHourIDs NVARCHAR(MAX)
AS
BEGIN
SET NOCOUNT ON;
    SELECT 
    ContractInvoicePerHourID,
    ContactID,
    EmployeeNumber,
    BranchID,
    BranchNumber,
    ProvinceCode,
    WeekStartDate,
    WeekEndDate,
    CompletionDate,
    ContactAssignmentPSId,
    JobClass,
    COUNT(*) AS DaysWorked,
    ROUND(CAST(SUM(HoursWorked) AS DECIMAL) / CAST(60 AS DECIMAL), 2) AS TotalHourWorks,
    CAST(SUM
    (
        ISNULL(BillableMiles, 0) * 
        (ISNULL((CASE WHEN ClientMileageRateAmount IS NOT NULL THEN ClientMileageRateAmount
        ELSE MileageRateAmount
        END), 0) * AdjusterPercentage / 100)
    ) AS DECIMAL(18, 2)) AS BillableMiles,
    CASE WHEN DiemTypeID = 1 OR DiemTypeID = 4 THEN CAST(SUM(ISNULL(PerDiemValue, 0)) AS INT) ELSE NULL END PerDiemDays
    INTO #TempCIPH
    FROM
    (
        SELECT
        CIPH.ContractInvoicePerHourID,
        CIPH.ContactID,
        EmployeeNumber,
        CIPH.BranchID,
        BCH.BranchNumber,
        BCH.ProvinceCode,
        WeekStartDate,
        (WeekStartDate + 6) AS WeekEndDate,
        CA.CompletionDate,
        ContactAssignmentPSId,
        (CASE 
        WHEN CAPS.CompensationTypeID = 2 AND CAPS.RateTypeID = 1 THEN 'CCH'
        WHEN CAPS.CompensationTypeID = 2 AND CAPS.RateTypeID = 2 THEN 'CDO'
        END) AS JobClass,
        HoursWorked,
        BillableMiles,
        (CASE WHEN CBM.IsClientMilageRate = 1 AND CBM.ClientMilageRate > 0 THEN ClientMilageRate ELSE NULL END) AS ClientMileageRateAmount,
        ISNULL(CBM.AdjusterPercentage, 0) AS AdjusterPercentage,
        (
            SELECT TOP(1) MileageRateAmount FROM [dbo].[MileageRate] WHERE IsActive = 1 AND MileageRateAmount > 0 AND CAST(EffectiveDate AS DATE) <= CAST(CIPHD.TimeCardDetailDate AS DATE)
            ORDER BY EffectiveDate DESC
        ) AS MileageRateAmount,
        CAPS.DiemType AS DiemTypeID,
        PerDiem,
        PerDiemValue
        FROM 
        (
            SELECT * FROM [dbo].[ContractInvoicePerHour] WHERE StatusID <> 5 AND ContractInvoicePerHourID IN (SELECT Item FROM [dbo].[fnSplitString](@ContractInvoicePerHourIDs, ','))
        ) CIPH
        INNER JOIN [dbo].[ContractInvoicePerHourDetail] CIPHD
        ON CIPH.ContractInvoicePerHourID = CIPHD.ContractInvoicePerHourID
        LEFT JOIN [dbo].[ContactAssignmentPayStructure] CAPS
        ON CIPH.ContactAssignmentPSId = CAPS.ContactAssignmentPayStructureID
        LEFT JOIN [dbo].[ContactAssignment] CA
        ON CIPH.ContactAssignmentId = CA.ContactAssignmentID
        LEFT JOIN [dbo].[ClientRequirement] CR
        ON CA.ClientRequirementID = CR.ClientRequirementID
        LEFT JOIN [dbo].[ContractBillingMapping] CBM
        ON CR.ClientContractID = CBM.ClientContractID AND ISNULL(CIPH.ContractBillingTypeID, 1) = CBM.ContractBillingTypeID AND ISNULL(CBM.IsActive, 0) = 1 AND ISNULL(CBM.IsDeleted, 0) = 0
        LEFT JOIN [dbo].[Branch] BCH
        ON CIPH.BranchID = BCH.BranchID
        WHERE CIPHD.BeginTime IS NOT NULL AND CIPHD.EndTime IS NOT NULL AND DATEDIFF(MINUTE, CIPHD.BeginTime, CIPHD.EndTime) > 0
    ) AS TempCIPH
    GROUP BY ContractInvoicePerHourID,
    ContactID,
    EmployeeNumber,
    BranchID,
    BranchNumber,
    ProvinceCode,
    WeekStartDate,
    WeekEndDate,
    CompletionDate,
    ContactAssignmentPSId,
    JobClass,
    DiemTypeID;
    SELECT * FROM #TempCIPH;
    SELECT
    ContractInvoicePerHourID,
    IsRopeAndHarness,
    SUM(ISNULL(AdjusterInvoiceAmount, 0)) BillableAmount
    FROM
    (
        SELECT 
        CIPH.ContractInvoicePerHourID,
        ISNULL(CPR.IsRopeandHarness, CAST(0 AS BIT)) AS IsRopeAndHarness,
        CIPHAD.AdjusterInvoiceAmount
        FROM #TempCIPH CIPH
        INNER JOIN [dbo].[ContractInvoicePerHourDetail] CIPHD
        ON CIPH.ContractInvoicePerHourID = CIPHD.ContractInvoicePerHourID 
        INNER JOIN [dbo].[ContractInvoicePerHourAdditionalDetail] CIPHAD
        ON CIPHD.ContractInvoice = CIPHAD.ContractInvoicePerHourDetailID
        LEFT JOIN [dbo].[ContractPositionRate] CPR
        ON CIPHAD.ContractPositionRateID = CPR.ContractPositionRateID
    ) AS Temp_AC