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 lt String gt  of the returned records   No Linq etc  as I am using VS2005

User · Answer

If you would like to query all columns   List lt Users gt  list users   new List lt Users gt     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

User · Answer

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

User · Answer

Loop through the Items and Add to the Collection  You can use the Add method  List lt string gt items new List lt string gt     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

User · Answer

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 lt List lt String gt  gt  ResultSet   new List lt List lt String gt  gt              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 lt string gt                     for  int i   0  i  lt   reader FieldCount-1  i      The mathematical formula for reading the next fields must be  lt                                                               rec Add reader GetString i                                      ResultSet Add rec

User · Answer

I think this is what you re looking for   List lt String gt  columnData   new List lt String gt      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

[c#] How to generate List<String> from SQL query?

Examples related to c#

Examples related to sql

Examples related to list

Examples related to visual-studio-2005

Examples related to dbcommand