Database Access In this lesson, we create a simple form that queries and shows data of individual record from the Access database created in the last lesson. Open Visual Studio and create a new Windows Application. Name the project AccessConnection. Add three labels and three textboxes. Change the Text property of the labels to First Name, Last Name, and Age. Change the Name property of the TextBoxes to txtFirstName, txtLastName, and txtAge respectively. Add four buttons and change their text to First, Prev, Next, and Last. Change their Name property to btnFirst, btnPrev, btnNext, and btnLast. Your form should look like this:

Database Access
Database Access

Double click the actual form to generate an event handler for the form’s Load event. The load event will contain the code that queries the values to the database and show the field values of the first record to their respective text boxes.

using System;using System.Windows.Forms;using System.Data;using System.Data.OleDb;namespace AccessConnection{    public partial class Form1 : Form    {        private OleDbConnection connection;        private OleDbCommand command;        private OleDbDataAdapter adapter;        private DataSet dataset;        private string firstname;        private string lastname;        private int age;        private int position;        public Form1()        {            InitializeComponent();        }        private void Form1_Load(object sender, EventArgs e)        {            connection = new OleDbConnection();            command = new OleDbCommand();            adapter = new OleDbDataAdapter();            dataset = new DataSet();            connection.ConnectionString =                @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:Members.accdb;" +                "Persist Security Info=False";            command.Connection = connection;            command.CommandText = "SELECT * FROM Members";            adapter.SelectCommand = command;            try            {                adapter.Fill(dataset, "Members");            }            catch (OleDbException)            {                MessageBox.Show("Error occured while connecting to database.");                Application.Exit();            }            ShowValuesOfRow(0);        }        private void ShowValuesOfRow(int pos)        {            DataRow row = dataset.Tables["Members"].Rows[pos];            position = pos;            firstname = row["FirstName"].ToString();            lastname = row["LastName"].ToString();            age = (int)row["Age"];            txtFirstName.Text = firstname;            txtLastName.Text = lastname;            txtAge.Text = age.ToString();        }    }}

Example 1

Notice that we use the System.Data.OleDb namespace. This namespace contains the classes that we will be needing to connect to our Access Database. The namespace System.Data contains the DataSet class which will hold the data retrieved from the database. We declared objects for the OleDbConnection (used for connecting to database), OleDbCommand (handles the SQLcommand), OleDbDataAdapter (used to communicate to the database and retrieve its values), the DataSet, and some fields to hold the values from the database. The position field will be used to monitor which record to show in the textbox.

Inside the Load event handler of the Form, we initialized the objects necessary to connect to the data source. We then set the ConnectionString property of the OleDbConnection class to the connection string of Access 2007. The connection string parameter Data Source specifies the path of the Access 2007 file in your system.

We connect the OleDbConnection instance to the OleDbCommand through its Connection property. We also specified the SQL command through its CommandText property. The SQL command specifies that we should get all the rows and fields from the Members table. We set the SelectCommand property of the OleDbDataAdapter instance. We used the OleDbDataAdapter.Fill() method that executes the command in the SelectCommand property and put the data into the dataset with the specified table name. Enclosing the Fill()method in a try..catch handles any exception that might occur.

We called the ShowValuesOfRow() method to show the values of the specified row or index. We passed 0 as an argument to show the values of the first row. Inside the method, we retrieve the specified row by accessing the Rows property of the Tables property of the DataSet object. We passed the name of the table(in this case “Members”) as the key to the Tables property and the value of posparameter as the index of the Rows property to get a specific row from the Members table. The result is stored to a DataRow object which represents a single row in a DataTable. The position is then adjusted. The values of each column of the retrieved row are placed to their respective fields and the fields are placed to their respective text boxes. When you run the program, the form should display the first record.

Database Access
Database Access

Let’s add functionality to the four buttons. Let’s start with btnFirst. Double click the button the add an event handler to its Click event.

private void btnFirst_Click(object sender, EventArgs e) {    ShowValuesOfRow(0);}

Example 2

The code simply calls the ShowValuesOfRow() method and passes 0 as an argument to display the first row.

Double click btnLast and add the following code.

private void btnLast_Click(object sender, EventArgs e){    int lastIndex = dataset.Tables["Members"].Rows.Count - 1;    ShowValuesOfRow(lastIndex);}

Example 3

The first line determines the index of the last record from the Members table. We called the ShowValuesOfRow() method and passed the last index to the method to display the last row’s data.

Double click the btnPrev button and add the following code.

private void btnPrev_Click(object sender, EventArgs e){    if (position > 0)    {        position--;        ShowValuesOfRow(position);    }}

Example 4

We first check if the position is greater than 0. This will prevent the program from querying a row located at an invalid index such as -1. Inside the if structure, we decrement the position and called the ShowValuesOfRow() passing the new position as the argument.

Double click the btnNext button and add the following code.

private void btnNext_Click(object sender, EventArgs e){    int lastIndex = dataset.Tables["Members"].Rows.Count - 1;    if (position < lastIndex)    {        position++;        ShowValuesOfRow(position);    }}

Example 5

We retrieve the last index of the rows and check if the position is less than the last index. This is also to prevent the program on retrieving an invalid index that will cause an IndexOutOfRange exception to be thrown. The position is incremented by 1 and the values of the new row are displayed.

Now run the program. Clicking the First button displays the first record. Clicking the Last button displays the last record. Clicking the prev and next buttons will display the previous and next records.

We can use the codes in this lesson to connect to an SQL Data Source. All we need to do is change the type of Provider. You also need to change the connection string to an SQL Server connection string.