C# SQL Server stored procedure parameter return list

Question

I'm attempting to execute a SQL Server stored procedure that takes two parameters and returns a list of results. The application is returning the results however I'm unsure how to assign the values to the list in the model. The ORM being utilized is Dapper. Here's my code

string startTime = "2018-04-17 00:00:00.000";
string endTime = "2018-04-17 23:59:59.997";
string db = "database";

List<ShelfInventoryModel> output = new List<ShelfInventoryModel>();

using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(SqlConnection.CnnString(db)))
{
    DataTable dt = new DataTable();

    var p = new DynamicParameters();
    p.Add("@StartDate", startTime);
    p.Add("@EndDate", endTime);

    output = connection.Query<ShelfInventoryModel>("dbo.spGetInventory_Liquor", p, commandType: CommandType.StoredProcedure).ToList();

    Console.WriteLine(output);
    Console.ReadLine();
}

This is the model class

public class ShelfInventoryModel
{
    public decimal Quantity { get; set; }
    public string ItemName { get; set; }
}

I understand that the results need to be filled into potentially a data adapter. However the system gives me an error when I attempt to fill the output to the data adapter

The purpose to return the results to the console. However, at this time the console is only showing:

System.Collections.Generic.List`1[ProcedureTest.ShelfInventoryModel]

What list "in the model"? Also, you don't need to assign output a new List if you're going to replace it with whatever Query<...>(...).ToList() returns. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Aug 2, 2018 at 6:09
The query returns the quantity and item name. Is the list not necessary in this scenario? While stepping through the application its returning an array of objects. I'd like to assign the objects to the list. Possibly using a foreach statement — Gonzo Nick
– Gonzo Nick, Commented Aug 2, 2018 at 6:20
At the end of the code you've shown, output should contain a list of ShelfInventoryModel objects. Can't see anything wrong with that code. So I'm really not sure what you're trying to ask here. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Aug 2, 2018 at 6:21
At the end of your code, output should contain a list of ShelfInventoryModel objects, one for each row returned by the stored procedure, with the value from columns Quantity and ItemName put into those objects. (No need for a data adapter.) So what exactly is the problem? — Richardissimo
– Richardissimo, Commented Aug 2, 2018 at 6:23
That last comment is critical and should have been part of your question. It would have been better to edit your question with that information than put it in comments - I have done that for you. — Richardissimo
– Richardissimo, Commented Aug 2, 2018 at 6:44

Richardissimo · Accepted Answer · 2018-08-02 06:36:52Z

2

Console.WriteLine(output); will simply display the result of ToString on the parameter, which for this type reverts to the default implementation, which just shows the name of the type of the output object. You need to iterate through the items in the list using foreach:

foreach (var item in output)
{
    Console.WriteLine(item);
}

Which would cause you to get a row of lines just giving the type of that object. So either you can write an implementation of ToString for your object, or simply display the bits you want to see...

foreach (var item in output)
{
    Console.WriteLine($"{item.ItemName} {item.Quantity}");
}

edited Aug 2, 2018 at 6:36

answered Aug 2, 2018 at 6:27

Richardissimo

5,8352 gold badges24 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Gonzo Nick Over a year ago

This was my generally understanding as well. However, when I step through the statement the result being returned to the console is ProcedureTest.ShelfInventoryModel

Richardissimo Over a year ago

Answer edited to explain why that's another instance of the same thing happening and how to overcome it.

Gonzo Nick Over a year ago

Amazing! Thank you so much! Here's what I did in order to resolve my issue foreach(ShelfInventoryModel item in output) { Console.WriteLine($"{item.Quantity} {item.ItemName}"); } Console.ReadLine(); }

Md. Suman Kabir · Accepted Answer · 2018-08-02 06:39:23Z

You can use this :

string startTime = "2018-04-17 00:00:00.000";
string endTime = "2018-04-17 23:59:59.997";
string db = "database";

List<ShelfInventoryModel> output = new List<ShelfInventoryModel>();

using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(SqlConnection.CnnString(db)))
{
    DataTable dt = new DataTable();

    var p = new DynamicParameters();
    p.Add("@StartDate", startTime);
    p.Add("@EndDate", endTime);

    output = connection.Query<ShelfInventoryModel>("dbo.spGetInventory_Liquor", p, commandType: CommandType.StoredProcedure).ToList();

    foreach (ShelfInventoryModel item in output)
    {
        Console.WriteLine(item.Quantity.ToString());
        Console.WriteLine(item.ItemName);
    }       
}

Gonzo Nick · Accepted Answer · 2018-08-02 06:42:24Z

0

Here's the working code in order for the application to return a list of results to the console

foreach(ShelfInventoryModel item in output)
            {
                Console.WriteLine($"{item.Quantity} {item.ItemName}");

            }
            Console.ReadLine();
        }

answered Aug 2, 2018 at 6:42

Gonzo Nick

871 silver badge8 bronze badges

Collectives™ on Stack Overflow

C# SQL Server stored procedure parameter return list

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related