Initialize lists from databases without binding in C#

[initialize lists]

Note that this is a very manual way to initialize lists. Often you can display data more easily by using data binding.

In this example, I added the Access database file Books.mdb to the project and set its “Copy to Output Directory” property to “Copy if Newer.” The program uses OLE DB data objects so I added the following using statement to the code.

using System.Data.OleDb;

The following code shows how the program initializes its ListBox when it starts.

// The database connection.
private OleDbConnection Conn;

// Display a list of titles.
private void Form1_Load(object sender, EventArgs e)
{
    // Compose the database file name.
    // This assumes it's in the executable's directory.
    string db_name = Application.StartupPath + "\\Books.mdb";

    // Connect to the database
    Conn = new OleDbConnection(
        "Provider=Microsoft.ACE.OLEDB.12.0;" +
        "Data Source=" + db_name + ";" +
        "Mode=Share Deny None");

    // Get the titles.
    OleDbCommand cmd = new OleDbCommand(
        "SELECT Title FROM Books ORDER BY Title",
        Conn);
    Conn.Open();
    OleDbDataReader reader = cmd.ExecuteReader();
    while (reader.Read())
    {
        lstTitles.Items.Add(reader["Title"]);
    }
    reader.Close();
    Conn.Close();
}

The code starts by declaring an OleDbConnection object named Conn. (This example could declare the object within the form’s Load event handler. I’m making it class-level to make it easier to use in later examples.)

The form’s Load event handler builds a path to the database file and uses it to create the OleDbConnection object.

Next the code creates an OleDbCommand object that selects the Books table’s Title field, ordering the result by Title. The program then performs a common database programming sequence: open the connection, execute the command, process the results, close the connection.

The program assigns the result of the command object’s ExecuteReader method to an OleDbDataReader object. It can use that object to iterate through the results.

To process the results, the program enters a loop that executes as long as the reader’s Read method returns true. The while loop begins by calling the Read method to move to the first record in the result set. After the loop has processed all of the returned records, the Read method returns false and the loop exits.

When it fetches a record, the program adds the value of the Title field to the ListBox.

This may all seem a bit cumbersome, but it gives you total control over what’s happening. Once you get the hang of it (and possibly build this code into a helper method) it’s fairly easy to use.


Download Example   Follow me on Twitter   RSS feed   Donate




This entry was posted in controls, database, lists, user interface and tagged , , , , , , , , , , , , . Bookmark the permalink.

One Response to Initialize lists from databases without binding in C#

  1. Pingback: Display database records without data binding in C# - C# HelperC# Helper

Leave a Reply

Your email address will not be published. Required fields are marked *