I've got a SQL Table "Roles" in DB. I wish to discover if user have a number of Roles (Admin/User) after which make a move having a value if user have 2 Roles sometimes.

Problem: I cant get all Values from Database, I getting only a first one.

can one get it done with foreach loop? I do not have it here but i am searching for solution.


            cmd = new SqlCommand();
            cmd.Connection = connection;
            cmd.CommandType = System.Data.CommandType.Text;
            cmd.CommandText = @"SELECT Role "
                                  + " FROM UsersRole "
                                  + " WHERE UserName = @UserName ";
            cmd.Parameters.Add(new SqlParameter("@UserName", username));

            reader = cmd.ExecuteReader();

            if (reader.HasRows)
                while (reader.Read())
                    if (reader["Role"] != DBNull.Value)
                        Role = Convert.ToString(reader["Role"]);
                        if(Role == UserRole.Admin.ToString())
                            IsAdmin = true;
                        if (Role == UserRole.User.ToString())
                            IsUser = true;
                        Role = "";

Can you really access Table in DB with C# code or I'm able to get it done different?

Thanks and be mindful, Ragims

You will find plenty of methods to access your DB through code, before anybody here could be much help around the specifics, you will need to choose one of these.

The narrow your search of DB Access techniques is:
1) ORM Software (Entity Framework, NHibernate, etc.) You will find plenty of assets available of these, I suggest doing Google research in it for lessons before you decide to just start asking them questions (the lessons/walkthroughs can enable you to get pretty far)

2) Linq (specificaly Linq-2-SQL) Again, there are many documentation about this available via Google or even the MSDN website.

3) ADO.Internet This is actually the old method of doing things (largely from favor, now, however it works best for quite simple stuff). Again, plenty of assets exist.

Once you have selected a technology and went through fundamentals (your particular question should, indeed, be included in nearly any tutorial you'll find, I'd guess), publish back with any sort of queries about specific problems you are getting. Individuals here on SO should have the ability to knock any question you have out-of-the-park in fairly short order.

I want to educate yourself on ADO, LinqToSql, or even the Entity Framework. All individuals technologies will enable use of a SQL Database.

Edit because you added more information:

Rather than looping though each record individually, you would be best while using database I believe.

Here's some SQL which will group with a username and return individuals customers which have a lot more than 1 role. (note, my sql is not great, might be a better method of doing this). Alter the database posts to fit your table

select username, COUNT(id) from <yourtable> group by username having COUNT(id) > 1