SQL query result to array/List

Question

Following is the C# code for querying the DB to get the list of users id:

int id;
con = new SqlConnection(Properties.Settings.Default.ConnectionStr);
con.Open();
id = 180;
SqlCommand command = new SqlCommand("Select userid from UserProfile where grpid=@id", con);
command.Parameters.AddWithValue("@id", id);

using (SqlDataReader reader = command.ExecuteReader())
{
    if (reader.Read())
    {
        Console.WriteLine(String.Format("{0}", reader["userid"]));
    }
}

con.Close();

Output: 5629

Actually, the list of Users having grpid = 180 are 5629, 5684, 5694.

How can I read the results in a list or an array ?

not exactly sure what you're asking, What do you mean by "read the results in list" ? — Vulpex
– Vulpex, Commented May 7, 2018 at 9:58
try to write while (reader.Read()) instead of if (reader.Read()) — Serkan Arslan
– Serkan Arslan, Commented May 7, 2018 at 9:58
Possible duplicate of How to generate List<String> from SQL query? — CodeNotFound
– CodeNotFound, Commented May 7, 2018 at 10:01

Marc Gravell · Accepted Answer · 2018-05-07 09:59:52Z

8

simply:

List<int> results = new List<int>();
using (SqlDataReader reader = command.ExecuteReader())
{
    while (reader.Read())
    {
        results.Add((int)reader["userid"]));
    }
}
// use results

However, you might find a tool like "Dapper" a useful time saver here

var results = con.Query<int>("Select userid from UserProfile where grpid=@id",
    new { id }).AsList();

edited May 7, 2018 at 9:59

answered May 7, 2018 at 9:58

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

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

6 Comments

Marc Gravell Over a year ago

@CodeNotFound indeed

Jeya Suriya Muthumari Over a year ago

Thanks much for the quick help.

Marc Gravell Over a year ago

@JG'sSpark emphasis: I strongly recommend tools like Dapper instead of raw ADO.NET; it saves a lot of time and unnecessary bugs; I'm very biased, though

Jeya Suriya Muthumari Over a year ago

Any reference link for Dapper ? would be more useful to me

Marc Gravell Over a year ago

@JG'sSpark nuget.org/packages/Dapper or github.com/StackExchange/Dapper/blob/master/Readme.md

|

apomene · Accepted Answer · 2018-05-07 09:58:34Z

5

Simply define a List, and use like:

List<string> Users = new List<string>();
using (SqlDataReader reader = command.ExecuteReader())
{
    while (reader.Read())
    {
        Users.Add(reader[0].ToString());
    }
}

answered May 7, 2018 at 9:58

apomene

14.4k10 gold badges51 silver badges76 bronze badges

Comments

Oilid · Accepted Answer · 2018-05-07 10:11:35Z

4

Try this :

var userIds = new List<int>();
using (var reader = command.ExecuteReader())
{
    while (reader.Read())
    {
        userIds.Add(reader.GetInt32(0));
    }
}

edited May 7, 2018 at 10:11

answered May 7, 2018 at 10:07

Oilid

1511 silver badge9 bronze badges

Comments

Tim Schmelter · Accepted Answer · 2018-05-07 10:16:49Z

4

You could use this extension method:

public static class DbExtensions
{
    public static List<T> ToList<T>(this IDataReader reader, int columnOrdinal = 0)
    {
        var list = new List<T>();
        while(reader.Read())
            list.Add((T)reader[columnOrdinal]);
        return list;
    }
}

Now you can use it in this way:

List<int> userIdList;
using (var con = new SqlConnection(Properties.Settings.Default.ConnectionStr))
{
    using(var command = new SqlCommand("Select userid from UserProfile where grpid=@id", con))
    {
       command.Parameters.AddWithValue("@id", id);
       con.Open();
       using (SqlDataReader rd = command.ExecuteReader())
          userIdList = rd.ToList<int>();
    }
}

edited May 7, 2018 at 10:16

answered May 7, 2018 at 10:11

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

Collectives™ on Stack Overflow

SQL query result to array/List

4 Answers 4

6 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related