Return a list of user names

Question

User1793652459 posted

Hello,

I have a stored procedure, when I execute it with the right parameter it returns 3 user names like this in one row in SQL: 

User1 -- User2 ---User3

John---- Mike-----Jim

I am trying to display this complete list of users in a field on the webpage as shown above in the row. However when I pass the necessary parameter from the website to SQL, I only get the first name back, in this case John. Here is the Dapper ORM code that calls the stored procedure: 

  public List<String> GetUsers(string locNo)

        {

            List<String> Users = new List<String>();

            using (IDbConnection conn = GetConnection())

            {

                try

                {

                    var parameters = new DynamicParameters();

                    parameters.Add("@LocNo", locNo);

                    conn.Open();

                    Users = conn.Query<String>("dbo.spGetUsers",

                        param: parameters,

                        commandType: CommandType.StoredProcedure).ToList();

                }

                catch (Exception e)

                {

                    throw e;

                }

                return Users; //Users only contain the first user and 2 other users are missing, I need a list of all 3 users here. 

            }

        }

Thanks

Answer 1

User991499041 posted

Hi John,

I don't see any problems with the code right now, you could set a function breakpoint in 

Users = conn.Query<String>("dbo.spGetUsers",

Breakpoint  allows breaking execution of code at runtime when debugging applications.

When your running code hits a breakpoint and halts, you can inspect your variables and call stacks to determine what is going on.

Using Breakpoints

https://msdn.microsoft.com/en-us/library/5557y8b4.aspx

Regards,

zxj

Answer 2

User-707554951 posted

Hi johnzee,

Working sample as below:

Table data as below:

SP:

CREATE PROCEDURE dbo.spGetUsers
 @LocNo int   
As  
select User1,User2,User3 from[Table]  where LocNo=@LocNo

Code as below:

 <asp:GridView ID="GridView1" runat="server"></asp:GridView>
        <hr />
        <asp:GridView ID="GridView2" runat="server"></asp:GridView>

Codebehind:

 protected void Page_Load(object sender, EventArgs e)
        {
            GridView1.DataSource = GetUsers("1");
            GridView1.DataBind();
        }
        public class TestUser
        {
            public String  User1 { get; set; }
            public String User2 { get; set; }
            public String User3 { get; set; }
        }
        public List<TestUser> GetUsers(string locNo)
        {
            List<TestUser> Users = new List<TestUser>();

            using (IDbConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
            {
                try
                {

                    var parameters = new DynamicParameters();
                    parameters.Add("@LocNo", int.Parse(locNo));
                    conn.Open();
                    Users = conn.Query<TestUser>("dbo.spGetUsers",
                      new { LocNo= locNo },
                      commandType: CommandType.StoredProcedure).ToList();
                    List<string> users = new List<string>();
                   foreach(TestUser testuser in Users)
                    {
                        users.Add(testuser.User1);
                        users.Add(testuser.User2);
                        users.Add(testuser.User3);
                    }
                    GridView2.DataSource = users;
                    GridView2.DataBind();

                }
                catch (Exception e)
                {
                    throw e;

                }

                return Users; //Users only contain the first user and 2 other users are missing, I need a list of all 3 users here. 
            }
        }

Output:

Best regards

Cathy

Answer 3

User1793652459 posted

As always thanks for your wonderful answer.