How do I return stored procedure output to a list

Question

1.00/5 (1 vote)

See more:

I have a program that displays a list of values matching particular conditions. When i run the SP to retrieve the values in C# console app i get System.Collections.Generic.List'1[StringsTest.Models.Broker] being printed instead of the result set. However running exec getCOAgents in SSMS returns my desired resultset.
My SP is as below :

SQL

 CREATE PROCEDURE [dbo].[getCOAgents]    
    AS
    BEGIN   
SET NOCOUNT ON;
SELECT         a.agent_shortname
    FROM            BrokerTest AS a INNER JOIN
                         (SELECT        agent_shortname, COUNT(*) AS Expr1
                           FROM            BrokerTest
                           GROUP BY agent_shortname
                           HAVING         (COUNT(*) > 1)) AS b ON a.agent_shortname = b.agent_shortname
    WHERE        (a.agent_shortname LIKE '_________C%') AND (a.term = 
    'NB')
    ORDER BY a.agent_shortname
            END
            GO

What I have tried:

This SP returns a list of values with the character C at index 10 . I am invoking the GetData() in console app's Main method as below:

C#

	namespace StringsTest
{
   class Program
    {
        static void Main(string[] args)
        {
 Console.WriteLine(GetData());
} } }

And the function as :

C#

public static List<Broker> GetData()
        {          
            List<Broker> details = new List<Broker>();
            using (SqlConnection conn = new SqlConnection(Helpers.DatabaseConnect))
            {
                conn.Open();
                DataTable dt = new DataTable();
                SqlCommand cmd = new SqlCommand("getCOAgents", conn);
                cmd.CommandType = CommandType.StoredProcedure;               
                cmd.ExecuteNonQuery();
           
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
                foreach (DataRow dr in dt.Rows)
                {
                    Broker broker = new Broker();
                    broker.Code = Convert.ToString(dr["agent_shortname"]);
                    details.Add(broker);
                }
                conn.Close();
            }
           return  details;
        }

Expected output in console (example result set)

ALID-HEA-CO-001
ALID-HEA-CO-001
ALID-HEA-CO-001
EMIL-MTR-CO-012
EMIL-MTR-CO-012

Posted 27-Sep-19 3:14am

Tshumore

Updated 27-Sep-19 3:48am

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2019-09-27T03:21:00

Solution 1

Simple:

C#

static void Main()
{
    List<Broker> data = GetData();
    foreach (Broker broker in data)
    {
        Console.WriteLine(broker.Code);
    }
}

Posted 27-Sep-19 3:21am

Richard Deeming

MadMyche · Accepted Answer · 2019-09-27T03:48:00

GetData returns a List of type Broker, and your Console.WriteLine is reflecting just that; and is working as intended. What You really want to do then is to iterate through that List. And there are a couple of ways of doing this

The first would be in your Main module

C#

static void Main(string[] args) {
 	List<Broker> BrokerList = GetData();
	foreach(Broker b in BrokerList) {
		Console.WriteLine(b);
	}
}

Another would be to pass this off into another Void

C#

private void DisplayList(List<Broker> bl) {
	foreach(Broker b in BrokerList) {
		Console.WriteLine(b);
	}
}
// alter Main to use this display method
static void Main(string[] args) {
 	List<Broker> BrokerList = GetData();
	DisplayList(BrokerList);
}

There are other ways you could do this as well... If you wanted to super-encapsulate this you create create a wrapper for the BrokerList and give it it's own ToString() override, which basically would fall back on the same iteration routine

C#

public class BrokerListWrapper {
	List<Broker> BrokerList { get; set; }
	public BrokerListWrapper(){}

	public override string ToString() {
		StringBuilder sb = new StringBuilder();
		foreach(Broker b in BrokerList) {
			sb.AppendLine(b);
		}
		return sb.ToString();
	}
}

So in short... there are a myriad of ways to do what you want, you just need to know what you want and how to make it work via whatever calling method