How to generate List<String> from SQL query?

Question

If I have a DbCommand defined to execute something like:

SELECT Column1 FROM Table1

What is the best way to generate a List<String> of the returned records?

No Linq etc. as I am using VS2005.

Eachelle

Answer 1

I think this is what you're looking for.

List<String> columnData = new List<String>();

using(SqlConnection connection = new SqlConnection("conn_string"))
{
    connection.Open();
    string query = "SELECT Column1 FROM Table1";
    using(SqlCommand command = new SqlCommand(query, connection))
    {
        using (SqlDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                columnData.Add(reader.GetString(0));
            }         
        }
    }
}

Not tested, but this should work fine.

Breland

Just a note. No need to explicitly close the connection since you are using the using block – Planogamete

same comment applies to the SqlDataReader – Tellford

I have modified my answer to reflect that. Thanks! – Breland

Answer 2

Loop through the Items and Add to the Collection. You can use the Add method

List<string>items=new List<string>();
using (var con= new SqlConnection("yourConnectionStringHere")
{
    string qry="SELECT Column1 FROM Table1";
    var cmd= new SqlCommand(qry, con);
    cmd.CommandType = CommandType.Text;
    con.Open();
    using (SqlDataReader objReader = cmd.ExecuteReader())
    {
        if (objReader.HasRows)
        {              
            while (objReader.Read())
            {
              //I would also check for DB.Null here before reading the value.
               string item= objReader.GetString(objReader.GetOrdinal("Column1"));
               items.Add(item);                  
            }
        }
    }
}

Planogamete

So no way to do this without having to loop through the records? – Eachelle

No. If you use DataReader you need to loop thru the items. IF you use a DataTable, You can get it there and using some LINQ you can get it as a list of string – Planogamete

How can you get List from DataTable without using Linq? – Eachelle

Sorry if i wrote wrong. You need LINQ, If you do not want to loop through. Internally LINQ extension methods does the same i guess – Planogamete

Ok, VB6 ADO had a function to get an array from a recordset. Seems strange that .NET would be less functional. – Eachelle

why dont you use LINQ ? Which version of .NET are you using ? – Planogamete

Answer 3

Or a nested List (okay, the OP was for a single column and this is for multiple columns..):

        //Base list is a list of fields, ie a data record
        //Enclosing list is then a list of those records, ie the Result set
        List<List<String>> ResultSet = new List<List<String>>();

        using (SqlConnection connection =
            new SqlConnection(connectionString))
        {
            // Create the Command and Parameter objects.
            SqlCommand command = new SqlCommand(qString, connection);

            // Create and execute the DataReader..
            connection.Open();
            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                var rec = new List<string>();
                for (int i = 0; i <= reader.FieldCount-1; i++) //The mathematical formula for reading the next fields must be <=
                {                      
                    rec.Add(reader.GetString(i));
                }
                ResultSet.Add(rec);

            }
        }

Copter

Answer 4

If you would like to query all columns

List<Users> list_users = new List<Users>();
MySqlConnection cn = new MySqlConnection("connection");
MySqlCommand cm = new MySqlCommand("select * from users",cn);
try
{
    cn.Open();
    MySqlDataReader dr = cm.ExecuteReader();
    while (dr.Read())
    {
        list_users.Add(new Users(dr));
    }
}
catch { /* error */ }
finally { cn.Close(); }

The User's constructor would do all the "dr.GetString(i)"

Keister

This wouldn't be great for when it comes time to scale up to asynchronous database operations (i.e. if you have nullable columns, you couldn't async/await those null checks). Plus relies on the Users constructor being kept up to date with any changes to the query. And what's worse is it will compile without any warnings if for instance a field is renamed or changed, with neither class having clearly readable way of seeing if it's been adjusted. – Rarity

Answer 5

Where the data returned is a string; you could cast to a different data type:

(from DataRow row in dataTable.Rows select row["columnName"].ToString()).ToList();

Salvidor

I said no Linq in the question. – Eachelle

Answer 6

This version has the same purpose of @Dave Martin but it's cleaner, getting all column, and easy to manipulate the data if you wan't to put it on Email, View, etc.

List<string> ResultSet = new List<string>();
using (SqlConnection connection = DBUtils.GetDBConnection())
{
    connection.Open();
    string query = "SELECT * FROM DATABASE";
    using (SqlCommand command = new SqlCommand(query, connection))
    {
        using (SqlDataReader reader = command.ExecuteReader())
        {
             while (reader.Read())
             {
                  var rec = new List<string>();
                  for (int i = 0; i <= reader.FieldCount - 1; i++)
                  {
                       rec.Add(reader.GetString(i));
                  }
                  string combined = string.Join("|", rec);
                  ResultSet.Add(combined);
             }
        }
    }
}

Sargasso

Answer 7

It's possible with 1 code line now

List<string>? columnData = await UkrGuru.SqlJson.DbHelper.ExecAsync<List<string>>(@"DECLARE @Data varchar(max) = '[]'; 
    SELECT @Data = JSON_MODIFY(@Data , 'append $', Column1) FROM Table1; SELECT @Data;");

Sharolynsharon