Visual Studio C# | Communicating with MS Access

One of the important thing when developing an application is to have a data storage where you can store all you important data whether it is from the user or for the user.

This post is all about connecting with MS Access as Database with Visual Studio C# and populate the retrieved data to the ListView component.

//initialize database connection
private OleDbConnection dbConn = new OleDbConnection();

//Connection string
string strConnString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Elfe Rain.Cisneros\Documents\Visual Studio 2013\Projects\Rental-System-Tool\Rental-System-Tool\bin\Debug\Rental.accdb;
Persist Security Info=False;";

//establish connection string
dbConn.ConnectionString = strConnString;

//Open database
dbConn.Open();

// establish query string
string strQuery = "Select * from Customer";

//Represents an SQL statement or stored procedure to execute against a data source.
OleDbCommand dbCommand = new OleDbCommand();

//Provides a way of reading a forward-only stream of data rows from a data source. This class cannot be inherited.
OleDbDataReader reader;

//Represents an item in a ListView control.
ListViewItem colItem;

// declaring a string of array with size 4
string[] colDataItem = new string[4];

// as a start, the listview is cleared.
listCustomer.Items.Clear();

            try //catch possible exception occurred.
            {
		//send connection and query
                dbCommand.Connection = dbConn;
                dbCommand.CommandText = strQuery;
                reader = dbCommand.ExecuteReader();
		
		// read all rows returned by the query
                while (reader.Read())
                {
                    colDataItem[0] = reader["CustomerID"].ToString();
                    colDataItem[1] = reader["LastName"].ToString();
                    colDataItem[2] = reader["FirstName"].ToString();
                    colDataItem[3] = reader["MiddleName"].ToString();

                    colItem = new ListViewItem(colDataItem);
                    listCustomer.Items.Add(colItem);
                    listCustomer.GridLines = true;


                } reader.Close();

            }
            catch (Exception ex)
            {
                MessageBox.Show("Error " + ex);
            }

//close database connection
dbConn.Close();

The Result (listview populated):

It's up to your application flow / logic where to place the codes above. It depends on your needs and purposes.

Sources:

 Microsoft Library : https://msdn.microsoft.com/en-us/library 

 Connectoin String : http://www.connectionstrings.com/access/