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, May 07 '18 at 09:58
try to write `while (reader.Read())` instead of `if (reader.Read())` — Serkan Arslan, May 07 '18 at 09:58
[This](https://stackoverflow.com/questions/12024226/how-to-generate-liststring-from-sql-query) shows you how. — kurakura88, May 07 '18 at 09:59
Possible duplicate of [How to generate List from SQL query?](https://stackoverflow.com/questions/12024226/how-to-generate-liststring-from-sql-query) — CodeNotFound, May 07 '18 at 10:01

Marc Gravell · Answer 1 · 2018-05-07T09:59:52.260

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 07 '18 at 09:59

answered May 07 '18 at 09:58

Marc Gravell

1,026,079
266
2,566
2,900

@CodeNotFound indeed – Marc Gravell May 07 '18 at 10:00
Thanks much for the quick help. – Jeya Suriya Muthumari May 07 '18 at 10:07
3

@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 – Marc Gravell May 07 '18 at 10:12
Any reference link for Dapper ? would be more useful to me – Jeya Suriya Muthumari May 07 '18 at 10:15
1

@JG'sSpark https://www.nuget.org/packages/Dapper/ or https://github.com/StackExchange/Dapper/blob/master/Readme.md – Marc Gravell May 07 '18 at 10:17
Sure, Thanks for the link :) – Jeya Suriya Muthumari May 07 '18 at 10:19

score 5 · Answer 2 · answered May 07 '18 at 09:58

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 07 '18 at 09:58

apomene

14,282
9
46
72

Oilid · Answer 3 · 2018-05-07T10:11:35.800

4

Try this :

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

edited May 07 '18 at 10:11

answered May 07 '18 at 10:07

Oilid

141
1
8

Tim Schmelter · Answer 4 · 2018-05-07T10:16:49.053

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

SQL query result to array/List

4 Answers4