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.
whilestatement, and add rows to it within thewhileloop.