0

I am trying to get multiple rows back from my sqlDataReader. I have a table that has Username associated with different Roles. Roles are ADMIN, Management, User.

Here is my C# code:

FormsAuthentication.Initialize();
string roles = string.Empty;
var conn = @"Server=Myserver;Database=Mydatabase;Trusted_Connection=True;";

using (var con = new SqlConnection(conn))
{
    using (SqlCommand cmd = new SqlCommand("[Web].Get_User_Roles"))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@Username", SqlDbType.NVarChar);
        cmd.Parameters["@Username"].Value = userName;
        cmd.Connection = con;
        con.Open();
        SqlDataReader reader = cmd.ExecuteReader();

        while (reader.Read())

            roles = reader["Roles"].ToString();
        con.Close();

    }
}

Here is my stored procedure

ALTER Procedure [Web].[Get_User_Roles]
@Username NVARCHAR(25)
AS
BEGIN

    Set Nocount ON;

        SELECT A.[Employee_ID], Roles 
        FROM  [Web].[Users] A INNER JOIN  [Web].[User_Roles] B
        ON A.[Employee_ID] = B.[Employee_ID] INNER JOIN [Web].[Roles] C
        ON b.[Role_ID] = C.[Role_ID] WHERE [Username] = @Username

end

Result When the Datareader reads, it is a list of Usernames and Roles. But Right now it just pulls the very 1st role. But, if a username has 2 roles associated with that, I want it to pull both.

Example It checks username, table has Admin and User associated with that Username. It should give BOTH roles to that user. I know I need a list<string> rles = new list<string> somewhere in there after my while statement, but not sure where.

5
  • If you are getting just one row back from a query then it appears to be an issue with your query, not C#. Show the stored procedure you are using. Commented Mar 3, 2020 at 17:07
  • "I know I need a list<string> somewhere in there after my while statement" - put the list creation before the while statement, and add rows to it within the while loop. Commented Mar 3, 2020 at 17:07
  • @Barns The query does the select statement perfectly. No problem with that at all. It calls the data it's suppost to. The problem is that the reader itself only chooses the 1st data row. I need it to choose all datarows, and when no rows exist come back Null. Commented Mar 3, 2020 at 17:19
  • @stuartd Gonna do that now. Commented Mar 3, 2020 at 17:19
  • Then your question is unclearly stated: "Right now it just pulls the very 1st role." The word "pulls" is generally associated with the result of a query. Commented Mar 3, 2020 at 17:23

4 Answers 4

3

The OPs code above will not work because C# is not an "indent" language. A While statement will work, but without brackets it will only execute the next line of code. Putting the While block in curly braces would define the code that will be executed within that block.

List<string> roles;
var conn = @"Server=Myserver;Database=Mydatabase;Trusted_Connection=True;";

using (var con = new SqlConnection(conn))
using (SqlCommand cmd = new SqlCommand("[Web].Get_User_Roles"))
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@Username", SqlDbType.NVarChar);
    cmd.Parameters["@Username"].Value = userName;
    cmd.Connection = con;
    con.Open();

    using(SqlDataReader reader = cmd.ExecuteReader())
    {
        roles = new List<string>();
        while (reader.Read())
        {
            var role = reader["Roles"].ToString();
            roles.Add(role);
        }

        //con.Close(); —- this is redundant because the using block will take care of Close and Dispose!
    } //Close and Dispose of the SqlDataReader
} //Close and Dispose of SqlCommand and SqlConnection

NOTE:
I wish not to deviate too far from the OPs original code. I tend to do things a bit differently. At the very least I would not use a simple List<string> to hold my results since only having the “role” might not have much use. But, I do not know the requirements.

Simple suggestion:
— The code could be made Asynchronous by changing ExecuteReader() into ExecuteReaderAsync() and Read() into ReadAsync():: in general not a bad idea for calls to DBs.
— I generally add a CommandTimeout, but this might have been omitted in order to make the code easier to understand for the sake of the question statement.

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

3 Comments

This still only gets the 1 role. It doesn't get a user who has 2 roles associated with himself. I'll post the stored procedure code above in a edit.**posted the code above**
@Airizzo The above code would iterate over all available "Rows" in the SqlDataReader. It does indeed sound like it could be the SP.
Try the following SQL and see what you get: SELECT A.[Employee_ID], B.[Roles] FROM [Web].[Users] A INNER JOIN [Web].[User_Roles] B ON A.[Employee_ID] = B.[Employee_ID] WHERE [Username] = @Username AND A.[Role_ID] = B.[Role_ID]
1

Try this:

        FormsAuthentication.Initialize();
        List<string> roles = new List<string>()
        var conn = @"Server=Myserver;Database=Mydatabase;Trusted_Connection=True;";

        using (var con = new SqlConnection(conn))
        {
            using (SqlCommand cmd = new SqlCommand("[Web].Get_User_Roles"))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@Username", SqlDbType.NVarChar);
                cmd.Parameters["@Username"].Value = userName;
                cmd.Connection = con;
                con.Open();
                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    var role = reader["Roles"].ToString();
                    roles.Add(role);
                }
                con.Close();

            }
        }

5 Comments

This code will not work. And you did not clean up the sloppy code.
@svladimirrc Can you please add an explanation to your code, describing what you changed and why? That's helpful for the reader to understand your solution better. We tend to discourage raw code dump answers.
Good point. This code above will not work because C# is not an "indent" language. A While statement will work, but without brackets it will only on the next line of code. Thus, the roles.Add(role)' would only add the last value that was assigned to it. Putting the While` block in curly braces would define the code that will be executed within that block. I was brief because I just got a call and didn't have the opportunity to explain further... thinking the poster would figure it out themself.
With added curly braces {} around the while this will work. Here is the microsoft documentation if you are interested. learn.microsoft.com/en-us/dotnet/framework/data/adonet/…
sorry was my fault, i missed the curly braces, thanks
0

You're using raw ADO.NET to get data from your database. That's okay, it's certainly helpful to learn raw ADO.NET. But the syntax is overly complicated, and a micro-ORM such as Dapper can make it significantly easier.

const string connectionString = @"Server=Myserver;Database=Mydatabase;Trusted_Connection=True;";

using (var con = new SqlConnection(connectionString))
{
    List<string> roles = con.Query<string>("[Web].Get_User_Roles", new { Username = userName}, commandType: CommandType.StoredProcedure);
    return roles;
}

3 Comments

I've never seen Syntax to combine all the calls and what not. This is Dapper? It looks like you could use this as normal also.
@Airizzo Yes, I used Dapper syntax in my code. It's a library you can simply reference by NuGet, add using Dapper; to the top of your .cs file, and use the syntax. It adds extension methods on top of normal ADO.NET objects like SqlConnection that make the syntax much easier. Dapper was created by the makers of Stack Overflow.
this looks so much easier then the crazyness I just did to solve this. I want to learn more about this!
0

A lot of suggestions in this thread, but what I was looking for was this. It's very sloppy code, but it gets me my answer and if need be I can explain in detail. Here is the code. I needed to pull both roles that I was enabled to have in according to my SQL Stored Procedure. Here was my Result and C# Code to make it work the way I needed too.

string role = string.Empty;

            var conn = @"Server=lol;Database=Did;Trusted_Connection=True;";

            using (var con = new SqlConnection(conn))

            using (SqlCommand cmd = new SqlCommand("[Web].Get_User_Roles"))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@Username", SqlDbType.NVarChar);
                cmd.Parameters["@Username"].Value = userName;
                cmd.Connection = con;
                con.Open();

                using (SqlDataReader reader = cmd.ExecuteReader())
                {

                    reader.Read();
                    role = reader["Roles"].ToString();
                    while (reader.Read())

                    {

                        role = role + " , " + reader["Roles"].ToString();


                    }

                }
            }

Is it dirty code? Yes, I can definitely clean it up a bit. But, I get the result I was looking for. I didn't need to list, just to concatenate the results which gave me my answer.

5 Comments

Not sure why this will "pull" more rows that before.
@Barns to be honest, I have no idea why it does it either. I think because each read pulls 1 row, and if more then 1 exist it will pull the next role and I made it so it will concatenate. I don't really understand it as I said. Maybe pull was the incorrect word to use and get would be correct?
Read returns a boolean value indicating whether a value is available to be read. While (like above in my answer) will evaluate the Read() at each iteration--when it is available. So given the same "userName" and same data my code would yield a safer response than your code (given the same object to which you wish to assign the result values). Why? because your code does not evaluate the result of reader.Read(). Should for some reason (and it is not unthinkable) that reader.Read() evaluates to 'False' your code will crash when you try to access reader["Roles"].ToString();
The only difference here is that instead of storing the results in a List variable, you're concatenating them in a single string. The same number of rows would be returned either way regardless, it's just what you do with the rows that matters. You could easily get them all in a list and the use string.Concat to turn them into a single string if you needed them in that format.
@mason is correct. It is easy to turn a List<string> into a comma separated (or what ever char you would like) string. Eg.: var userRoles = string.Join(',', rolesList); Where "rolesList" would be a List<string> object. One nice thing about using string.Join() is it returns an empty string if "rolesList" has no elements. Another nice thing is it does not leave a dangling separation char at the end.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.