Executed Stored Procedure using Dapper

dapper sql-server

Question

I am trying to execute a stored procedure using Dapper but I get two types of errors:

  1. Error parsing column 9 (fTaxInvoiceNumber=INV0000000028PPN - String)
  2. Object reference not set to an instance of an object

Here is the code to execute the stored procedure:

SqlConnection db = new SqlConnection(ConnectionFactory.ConnectionString("RebateSystem"));
db.Open();

try
{
    var result = db.Query<PracticeRebateOrders>("GetPracticeRebateOrderByInvoiceNumber", new
    {
       TaxInvoiceNumber = InvoiceNumber
    }, commandType: CommandType.StoredProcedure).FirstOrDefault();
    db.Close();
    db.Dispose();

    return result;
}
catch (Exception ex)
{
   throw;
}

This is my stored procedure:

ALTER PROCEDURE [dbo].[GetPracticeRebateOrderByInvoiceNumber] 
    @TaxInvoiceNumber NVARCHAR(20)
AS
BEGIN
   SELECT TOP(1) 
      [fPracticeRebateOrdersID]
      ,[fPracticeID]
      ,[fPracticeUserID]
      ,[fCreatedDate]
      ,[fInvoiceInCredits]
      ,[fPurchaseDate]
      ,[fPracticeRebateOrderStatusID]
      ,[fRebatePaymentRunID]
      ,[fRebateBatchID]
      ,[fTaxInvoiceNumber]
   FROM 
      [PracticeRebateSystem].[dbo].[PracticeRebateOrders]
   WHERE 
      fTaxInvoiceNumber = @TaxInvoiceNumber
END

Expert Answer

Not really an answer, but based on the code available in the question and in the comments I have this... which works fine and successfully...

public void SO24605346_ProcsAndStrings()
{
    connection.Execute(@"create proc #GetPracticeRebateOrderByInvoiceNumber @TaxInvoiceNumber nvarchar(20) as
        select @TaxInvoiceNumber as [fTaxInvoiceNumber]");
    string InvoiceNumber = "INV0000000028PPN";
    var result = connection.Query<PracticeRebateOrders>("#GetPracticeRebateOrderByInvoiceNumber", new
    {
        TaxInvoiceNumber = InvoiceNumber
    }, commandType: CommandType.StoredProcedure).FirstOrDefault();

    result.TaxInvoiceNumber.IsEqualTo("INV0000000028PPN");
}
class PracticeRebateOrders
{
    public string fTaxInvoiceNumber;
    [System.Xml.Serialization.XmlElementAttribute(Form = System.Xml.Schema.XmlSchemaForm.Unqualified)]
    public string TaxInvoiceNumber { get { return fTaxInvoiceNumber; } set { fTaxInvoiceNumber = value; } }
}

I'm happy to help, but I cannot reproduce an issue. Looks fine here.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why