2

I wrote a Web API method to get transaction history from the database using a stored procedure. The input includes a table type parameter which gets input from IEnumerable<string> transactionCategories. 

public IEnumerable<ITransactionItem> Get(long loanId, DateTime? fromDate, DateTime? toDate, IEnumerable<string> transactionCategories)
{
    IList<ITransactionItem> items;

    try
    {
        var transCategories = transactionCategories.Select(t => new TransCategoryTableType(t));

        using (var connector = GetConnector(ConnectionNames.UDSConnection))
        {
            items = (IList<ITransactionItem>)connector.StoredProcedure("spname")
                    .Parameter("@LoanId", loanId)
                    .Parameter("@FromDate", fromDate)
                    .Parameter("@ToDate", toDate)
                    .TableValuedParameter("@TransactionCategories", transCategories)
                    .As<ILedgerTransactionItem>()
                    .WithMapper(_mappingStrategy)
                    .GetRows();
        }
    }
    catch (Exception e)
    {
        throw;
    }

    return items;
}

I was data like this ['Tax','Interest'], but the stored procedure doesn't take this input and always returns null. I have defined the TransCategoryTableType as below

public sealed class TransCategoryTableType
{
    public TransCategoryTableType(string description)
    {
        Description = description;
    }

    public string Description { get; set; }
}

Need help to figure this out. The stored proc initial part looks like below:

ALTER PROCEDURE [Core].[uspLedgerTransactionHistory]
(
     @LoanId   BIGINT,
     @FromDate DATETIME = NULL,
     @ToDate   DATETIME = NULL,
     @TransactionCategories [Reference].[TransCategories] READONLY
)
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        CREATE TABLE #Temp(TransCodeId INT)
        INSERT INTO #Temp
        Select tc.transactioncodeid FROM Reference.tTransactionCodes tc
        JOIN Reference.tTransCodeCategories tcc
        ON tc.TransactionCodeCategoryId=tcc.TransCodeCategoryId
        WHERE tcc.Description IN (SELECT t.Description FROM     @TransactionCategories t)
bastos.sergio
  • 6,684
  • 4
  • 26
  • 36
Rash
  • 300
  • 1
  • 3
  • 19
  • What does your **stored procedure** (at least the header with the parameter definitions) look like?? – marc_s Oct 25 '16 at 05:15
  • Take a look here: http://stackoverflow.com/a/10779567/2074825. Maybe your problem is that you should not use a IEnumerable. – Edwin Stoteler Oct 25 '16 at 05:58
  • You need to pass in `IEnumerable`. – Solomon Rutzky Oct 25 '16 at 14:11
  • Thanks for the replies. The actual issue was that I was passing data in the table type valued from swagger like this: ['Tax','Interest']. But I should pass data like this: Tax Interest. The above code works fine. – Rash Oct 26 '16 at 22:02

0 Answers0