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

Question

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

Is your question about how to retrieve data from the db, or how to write the algorithm? — D'Arcy Rittich
– D'Arcy Rittich, Commented Aug 20, 2010 at 13:51
Are you using any data access technologies like Entity Framework, LINQ to SQL, nhibernate? Or do you want to just use the .net SqlClient? — CodingGorilla
– CodingGorilla, Commented Aug 20, 2010 at 13:51
RedFilter: how to retriev data. CodingGorilla: I reading data from DB direct in c# code with support of SQL classes. — r.r
– r.r, Commented Aug 20, 2010 at 13:54
Note that you generally don't want to loop through every record in a table and check each one for conditions. Instead, you want to retrieve from the table only the records which match your conditions and then use those in your logic. — David
– David, Commented Aug 20, 2010 at 13:56
OK, i thought before that it will work better with a loop, but if i have a table with lot of entries it will make probably not lot of sense. — r.r
– r.r, Commented Aug 20, 2010 at 13:59

AllenG · Accepted Answer · 2010-08-20 13:59:19Z

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.

sarnold · Accepted Answer · 2012-03-20 03:09:22Z

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));
           }
       }
   }    

   return _allRoles;
}

mdm20 · Accepted Answer · 2010-08-20 14:23:34Z

0

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

edited Aug 20, 2010 at 14:23

answered Aug 20, 2010 at 13:53

mdm20

4,5632 gold badges24 silver badges24 bronze badges

5 Comments

r.r Over a year ago

that meen i need write a sql statement in c# code for checking how much roles has user in table?

David Over a year ago

@Ragims: It depends on how you're accessing the database. Your question is a little unclear. Do you already have your data access in place and are just trying to structure the logic, or are you asking how to access the data? If the former, tell us which method you're using to access data.

r.r Over a year ago

I did got data from database all right. But I have in Property Role just a first selected Value of User (value=admin). A problem is that am user has second Value (value=user), and this will be not readed. that way i have no idea how to get a second value. maybe with foreach loop...

David Over a year ago

@Ragims: Maybe update the question with details about how the data itself is structured? That will certainly help us help you.

r.r Over a year ago

is it ADO? 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));

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related