c# sql database - how to reada all specific rows from table?

Question

Hello all,

I have a SQL Table "Roles" in DB. I want to find out if user have one or more Roles (Admin/User) and then do something with a value if user have 2 Roles at time.

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

can I do it with foreach loop? I dont have it here but i'm looking for solution.

like:

            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;
                        }
                    }
                    else
                    {
                        Role = "";
                    }
                }
            }
            reader.Close();

IS it possible to access Table in DB with C# code or I can do it different?

Thanks and take care, Ragims

Answer 1

I think you need to read up on ADO, LinqToSql, or the Entity Framework. All of those technologies will enable access to a SQL Database.

Edit since you added more info:

Instead of looping though each record one at a time, you'd be better off using the database I think.

Here is some SQL that will group by a username and return those users that have more than 1 role. (note, my sql isn't great, could be a better way to do this). Change the database columns to match your table

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

Answer 2

There are lots of ways to access your DB through code, before anyone here can be much help on the specifics, you'll need to select one of them.

The short list of DB Access methods is:
1) ORM Software (Entity Framework, NHibernate, etc.) There are lots of resources out there for these, I recommend doing Google research on them for tutorials before you just start asking questions (the tutorials/walkthroughs can get you pretty far)

2) Linq (specificaly Linq-2-SQL) Again, there's lots of documentation on this available via Google or the MSDN website.

3) ADO.NET This is the old way of doing things (largely out of favor, now, but it works for very simple stuff). Again, lots of resources exist.

Once you've picked a technology and gone through the basics (your specific question should, indeed, be covered by just about any tutorial you can find, I'd guess), post back with any specific questions regarding specific problems you're having. The folks here on SO should be able to knock any question you've got out-of-the-park in fairly short order.

Answer 3

If you're using "straight" ADO.NET (no ORM, no Linq-to-SQL or something), then your code would have to look something like this:

// you pass in a UserID (numeric), you get back a list of roles that user has
public List<string> GetRolesForUser(int userID)
{    
   // SQL select statement - adjust as needed 
   string selectStmt = "SELECT Role FROM dbo.UserRole WHERE UserID = @YourUserID";

   // create the resulting list of roles
   List<string> _allRoles = new List<string>();

   // define SqlConnection and SqlCommand to grab the data
   using(SqlConnection _con = new SqlConnection('your connection string here'))
   using(SqlCommand _cmd = new SqlCommand(selectStmt, _con))
   {
       // define the parameter for your SQL statement and fill the value
       _cmd.Parameters.Add("@YourUserID", SqlDbType.Int);
       _cmd.Parameters["@YourUserID"].Value = userID;

       _con.Open();

       // create SqlDataReader to grab the rows
       using(SqlDataReader rdr = _cmd.ExecuteReader())
       {
           // loop over all rows returned by SqlDataReader
           while(rdr.Read())
           {
              // grab the column no. 0 (corresponds to "Role" from your
              // SQL select statement) as a string, and store it into list of roles
              _allRoles.Add(rdr.GetString(0));
           }

           rdr.Close();
       }

       _con.Close();
   }    

   return _allRoles;
}