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: msdn.microsoft.com/en-us/library/979byfca%28v=vs.110%29.aspx — Yacoub Massad
– Yacoub Massad, Commented Jan 27, 2016 at 14:28

Yacoub Massad · Accepted Answer · 2016-01-27 14:38:55Z

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, 2016 at 14:38

answered Jan 27, 2016 at 14:32

Yacoub Massad

27.9k2 gold badges42 silver badges68 bronze badges

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

Comments

Community · Accepted Answer · 2017-05-23 12:15:24Z

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

Rahul · Accepted Answer · 2016-01-27 14:33:42Z

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, 2016 at 14:33

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Comments

WylieG · Accepted Answer · 2016-01-27 14:35:42Z

0

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

answered Jan 27, 2016 at 14:35

WylieG

763 bronze badges

Comments

Soner Gönül · Accepted Answer · 2016-01-27 14:35:55Z

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, 2016 at 14:35

answered Jan 27, 2016 at 14:32

Soner Gönül

99.1k103 gold badges224 silver badges375 bronze badges

2 Comments

paparazzo Over a year ago

If it is going to a List then I would skip the overhead and steps of a DataTable

Soner Gönül Over a year ago

@Frisbee Well, kind of agreed. Using ExecuteReader is somewhat better in such a case.

Collectives™ on Stack Overflow

How to populate list or array with values from access database

5 Answers 5

Comments

Comments

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related