How to select multiple records from a SQL database in ASP.NET

How do I select data from an SQL database in ASP.NET and retrieve multiple records? For example: I want to select all userids.

String sql = 
  "SELECT [UserId] FROM [UserProfiles] WHERE NOT [UserId] = 'CurrentUserId'";

string strCon = System.Web
                      .Configuration
                      .WebConfigurationManager
                      .ConnectionStrings["SocialSiteConnectionString"]
                      .ConnectionString;

SqlConnection conn = new SqlConnection(strCon);
SqlCommand comm = new SqlCommand(sql, conn);
conn.Open();

/*
 This is where I need to know how to retrieve the information from the
 above command(comm). I am looking for something similiar to php's
 mysql_result. I want to access the records kind of like an array or some
 other form of retrieving all the data.
 Also when the new SqlCommand is called...does that actual run the
 SELECT STATEMENT or is there another step. 
*/

conn.Close();

Solution

I think that this is what you are looking for.

String sql = "SELECT [UserId] FROM [UserProfiles] WHERE NOT [UserId] = 'CurrentUserId'";

string strCon = System.Web
                      .Configuration
                      .WebConfigurationManager
                      .ConnectionStrings["SocialSiteConnectionString"].ConnectionString;

SqlConnection conn = new SqlConnection(strCon);
SqlCommand comm = new SqlCommand(sql, conn);
conn.Open();
SqlDataReader nwReader = comm.ExecuteReader();
while (nwReader.Read())
{
    int UserID = (int)nwReader["UserID"];
    // Do something with UserID here...
}
nwReader.Close();
conn.Close();

I do have to say, though, that the overall approach can use a lot of tuning. First, you could at least start by simplifying access to your ConnectionString. For example, you could add the following to your Global.asax.cs file:

using System;
using System.Configuration;

public partial class Global : HttpApplication
{
    public static string ConnectionString; 

    void Application_Start(object sender, EventArgs e)
    { 
          ConnectionString = ConfigurationManager.ConnectionStrings["SocialSiteConnectionString"].ConnectionString;
    }
    ...
}

Now, throughout your code, just access it using:

SqlConnection conn = new SqlConnection(Global.ConnectionString);

Better yet, create a class in which the "plumbing" is hidden. To run the same query in my code, I'd just enter:

using (BSDIQuery qry = new BSDIQuery())
{
    SqlDataReader nwReader = qry.Command("SELECT...").ReturnReader();
    // If I needed to add a parameter I'd add it above as well: .ParamVal("CurrentUser")
    while (nwReader.Read())
    {
        int UserID = (int)nwReader["UserID"];
        // Do something with UserID here...
    }
    nwReader.Close();
}

This is just an example using my DAL. However, notice that there is no connection string, no command or connection objects being created or managed, just a "BSDIQuery" (which does lots of different things in addition to that shown). Your approach would differ depending on the tasks that you do most often.