3

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();
Improve this question
0

5 Answers 5

Reset to default
6

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

It's pretty bad though in terms of reliability: exceptions will be left hanging when there are exceptions, even if you catch the exception.
Joel - see the additional code I entered. I agree with you but wanted to answer the "basic" question before moving on to the deeper issues raised.
3

Most of the time, I use this (note that I am also using a connection pooling approach):

    public DataTable ExecuteQueryTable(string query)
    {
        return ExecuteQueryTable(query, null);
    }

    public DataTable ExecuteQueryTable(string query, Dictionary<string, object> parameters)
    {
        using (SqlConnection conn = new SqlConnection(this.connectionString))
        {
            conn.Open();

            using (SqlCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = query;

                if (parameters != null)
                {
                    foreach (string parameter in parameters.Keys)
                    {
                        cmd.Parameters.AddWithValue(parameter, parameters[parameter]);
                    }
                }

                DataTable tbl = new DataTable();
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    da.Fill(tbl);
                }

                return tbl;
            }
        }
    }
Improve this answer

2 Comments

This is nice... but I'd like to see it accept an IEnumerable<KeyValuePair> rather than a dictionary, you can stack your using statements to avoid deep nesting, it would be nice to have a way to avoid letting .Net infer the type of your parameters, and you can use DataTable.Load rather than .Fill
I like .Fill, because it feels better to me. And the reason I use the usings like I do is because I run StyleCop on everything. And, I agree on the IEnumerable<KeyValuePair>.
2

Here's an adaption of your existing code:

String sql = "SELECT [UserId] FROM [UserProfiles] WHERE [UserId] != @CurrentUserId";

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

DataTable result = new DataTable();
using (var conn = new SqlConnection(strCon))
using (var cmd = new SqlCommand(sql, conn))
{
    cmd.Parameters.Add("@CurrentUserID", SqlDbType.Int).Value = CurrentUserID;
    conn.Open();

    result.Load(cmd.ExecuteReader());
}
Improve this answer

Comments

1

Creating a SqlCommand doesn't execute it at all.

The command will be executed when you call ExecuteReader or something similar.

If you want something which will fetch all the results into memory, you should be looking at DataSet/DataTable. There's a tutorial for them here - or there are plenty of others on the net, and any decent ADO.NET book will cover them too.

If you don't want to fetch them all into memory at once, then ExecuteReader it the method for you. That will return a SqlDataReader which is like a database cursor - it reads a row at a time, and you ask for individual columns as you want them, calling Read to get to the next row each time.

Improve this answer

1 Comment

I don't really want to fetch the results into memory, how do you access the results once you call ExecuteReader?
0

Whereas in PHP you'd do something like,

while ($row = mysql_fetch_array ($result)) 
{
  //this assumes you're doing something with foo in loop
  $foo = $row["userid"];

  //using $foo somehow
}

in .NET, you do something different. Believe me, originating from a PHP background, the transition from PHP to .NET is not easy. There's a lot of things that will seem bizarre. After a while though, it will make sense! Just stick it out. I personally like it better.

Ok.. assuming you have a DataSet like you say, you can do something like this,

//assuming you have a DataSet called myDataSet
for (int i = 0; i < myDataSet.Tables[0].Rows.Count; i++)
{
  //likewise assuming here you're doing something with foo in loop
  string foo = myDataSet.Tables[0].Rows[i]["userid"].ToString();

  //similarly do something with foo in loop
}

That does the same thing as the PHP snippet.

Improve this answer

2 Comments

Both are bad, though. Why would you set the same variable over and over? If you mean to use that to do some other work inside the loop, add a comment to the code snippets to make that more obvious.
Good catch. It was quite obvious to me as I was typing it ;) Yes, I was assuming you'd be doing something with those variables in the loop.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.