How to populate list or array with values from access database

Question

I am trying to populate a group of labels in a C# windows form with some values that are in a certain attribute (PlayerName) in a database that I have in access.

Currently the only code I have is:

OleDbConnection connection = new OleDbConnection(CONNECTION STRING HERE);

OleDbCommand command = new OleDbCommand();
command.Connection = connection;
command.CommandText = "SELECT PlayerName FROM [TotalPlayerName] WHERE Team = 1 AND SportID = " + Form1.IDNumber;

I need a list or array that holds these values so I can use them to populate the labels, but I am unaware of how to do this.

Take a look at this: https://msdn.microsoft.com/en-us/library/979byfca%28v=vs.110%29.aspx — Yacoub Massad, Jan 27 '16 at 14:28

Yacoub Massad · Accepted Answer · 2016-01-27T14:38:55.840

2

You need to call ExecuteReader to obtain a data reader and then loop through the rows of the result set like this:

List<string> result = new List<string>();

using (var reader = command.ExecuteReader())
{
    while (reader.Read())
    {
        result.Add(reader.GetString(0));
    }
}

Before you do this, don't forget to open the connection like this:

connection.Open();

edited Jan 27 '16 at 14:38

answered Jan 27 '16 at 14:32

Yacoub Massad

27,509
2
36
62

score 1 · Answer 2 · edited May 23 '17 at 12:15

There are a couple of things here..... for sake of best practice well its more standard practice... as I like to say!

Use USING as this cleans up after connection.. see here for great examples in a "using" block is a SqlConnection closed on return or exception?

using (OdbcDataReader DbReader = DbCommand.ExecuteReader())
    {
       int fCount = DbReader.FieldCount;
       while (DbReader.Read())
         {
              Label1 = DbReader.GetString(0);
              Label2 = DbReader.GetString(1);
              Label3 = DbReader.GetString(2);
              Label4 = DbReader.GetString(3);

              for (int i = 0; i < fCount; i++)
                {
                   String col = DbReader.GetString(i);
                    Console.Write(col + ":");
                }
                Console.WriteLine();
        }
}

NB your SQL only return 1 field /String at the moment

Soner Gönül · Answer 3 · 2016-01-27T14:35:55.493

0

One option might be using OleDbDataAdapter to fill a DataTable those values that returns your query;

var dt = new DataTable();
using(var da = new OleDbDataAdapter(command))
{
    da.Fill(dt);
}

And since your query return one column, you can use AsEnumerable to that datatable to get them as a string like;

List<string> list = dt.AsEnumerable()
                      .Select(r => r.Field<string>("PlayerName"))
                      .ToList();

You can read: Queries in LINQ to DataSet

By the way, you should always use parameterized queries. This kind of string concatenations are open for SQL Injection attacks.

Also use using statement to dispose your connection and command automatically as I did for OleDbDataAdapter in my example.

edited Jan 27 '16 at 14:35

answered Jan 27 '16 at 14:32

Soner Gönül

97,193
102
206
364

If it is going to a List then I would skip the overhead and steps of a DataTable – paparazzo Jan 27 '16 at 14:35
@Frisbee Well, kind of agreed. Using `ExecuteReader` is somewhat better in such a case. – Soner Gönül Jan 27 '16 at 14:39

score 0 · Answer 4 · answered Jan 27 '16 at 14:33

0

while reading the data fill the list like

List<string> players = new List<string>();

OleDbDataReader rdr = command.ExecuteReader();
While(rdr.Read())
{
  players.Add(rdr["PlayerName"].ToString());
}

answered Jan 27 '16 at 14:33

Rahul

76,197
13
71
125

score 0 · Answer 5 · answered Jan 27 '16 at 14:35

You need to create a OleDbReader object to read the response from the query. You will also need to create a List to store the data Something like this:

List<string> playerNameList = new List<string>(); 
using (OleDbReader r = command.ExecuteReader())
{
   while(reader.Read())
   {
     playerNameList.Add(reader.GetString(0));
   }
}

How to populate list or array with values from access database

5 Answers5