Plug the User Interface to a Database

On this third part of the Class Design and Object Instantiation project I will be connecting the previously designed form to an Access/SQL Database. In this last version of the form, I have relayed on a new control which is, by itself, one of the most powerful controls in the C# arsenal, the DataGridView. DataGridViews allow easy connection to tables in memory with the purpose of browsing data in an Excel-like format, and due to is popularity and widespread knowledge, just by mentioning the word Excel, what comes to mind are rows and columns as well as sorting, searching, adding, and editing information. The graphic below (Figure 1) shows two new controls added to the form, a DataGridView to the left, and a Delete Record button to the right. I decided to add a ‘delete’ option as a way to demonstrate the code behind an option like this, but, as programmers, we must be careful when providing such an option without the proper restrictions. See Figure 1 below to get an idea of what our final product will be.

Before anything else, I have to introduce the Access Database, the DataGridView, as well as other changes needed to get the form ready to interact with a database, then I will proceed to write the code that actually does the task of talking to the database.

1. Introducing the Access Database.

The two figures below show a graphical representation of the Microsoft Access Database Students Table in Design Mode (Figure 2) and in Browsing Mode (Figure 3) that I will be using as Back-End.

Table 1 below shows the Students table’s fields and their different value types as well as sizes.

You don’t have to have Microsoft Access installed in your computer to connect and interact with an Access database, but you need it to create the database and the objects inside like tables and such. Please create the Access table using the values shown in Table 1 above.

2. Enlarge the form and introduce the DataGridView.

Let us now proceed to work in the form, start by increasing its size to be able to accommodate the DataGridView, so proceed to locate its Width property and change its current value to 919, you will find Width together with Height inside the Size property, then proceed to select all the controls and move them to the right allowing enough space to fit the DataGridView. Now, go to the Toolbox Pane and locate the DataGridView (see Figure 4) and drag and drop it in the form.

Change the DataGridView size to 252 x 303, then proceed to add a Label control and change its properties as indicated in Table 2. After all these changes, the form should look as Figure 5 below.

We now have a DataGridView control in place so let’s change some of its properties by following Table 3 below.

3. Introducing code to connect to the database using DataTables and DataViews.

We are now ready to introduce the changes in code needed to get the form ready to talk to the database so my first simple goal will be to plug the DataGridView to the Access Table to browse the data, and to accomplish that, I need to create a DataTable in memory, then plug the DataTable to the Access Table and for that we need ADO.NET DataSets.

ADO.NET DataSet, as defined by Microsoft .Net website, supports disconnected memory-resident representation of data, and provides a set of relational programming tools to facilitate data interaction regardless of the data source. So three things to observe here:

1. Disconnected memory-resident representation of data.

2. Relational programming tools to interact with data.

3. Regardless of the data source.

Item no 1 means that ADO.NET connects to a physical table, copies to memory a record (or set of), to then, (number 2 item) facilitate interaction with that data in memory via a set of programming tools..... (No. 3) REGARDLESS of the data source. This means that I am able to use the same set of programming tools to connect to, and interact with, different data sources such as SQL, Access, Oracle, xBase, etc.

So code is coming but before that let me say that I will be creating a DataSet containing the Student Table, then I will be using a DataSet’s DataView to funnel data into the DataGridView. You can see a graphical representation in Figure 6 of what I will be accomplishing in code.

4. Plug database table to the DataGridView

So let’s go to Visual Studio Code Pane, and after going all the way to the top of the program under the ‘using’ section, add the following two lines of code:

Then, locate the following line of code, which should be close below the ‘usings’:

and add the following code underneath:

The code above clearly shows the DataProvider section (prefix OleDb) and underneath, the DataSet section (from Figure 6 above). The DataAdapter will be built by coupling the DataProvider result with the DataSet DataTable and DataView. Now, inside Form1_Load() event locate the following line of code:

Underneath write the following line of code:

in the same Load event, remove the following two lines of code:

As a result of the code changes above, you should now have a red curly wave under the line of code Load_Students_DT, and that is because we are missing that function, so go all the way down and find the closing curly brace prior to the start of the Student Class definition and, right above that closing curly brace, insert the following code:

Note: In the code above, under MydbConnString, you must point to your Access Database by using your own path. Also, notice the use of two backslash characters; the reason being is that each special character has to be ‘escaped’ using a backslash and the backslash happens to be an specials character hence the double backslash..

So the red curly wave under the line of code Load_Students_DT should have now be gone, but we got another one, under the Format_DataGridView() function, so right at the end of the Load_Students_DT function add the code below.

At this point, after running the form, you should be able to see the Access Student Table being browsed in the DataGridView via an ADO.NET DataSet through an OleDb DataProvider connection.

Next, what I want to do is to click a DataGridView row and make the row’s information appear in the textboxes. The DataGridView has an event called RowHeaderMouseClick which is called (...invoked...executed) every time the user clicks any of the DataGridView’s row headers so let’s proceed to add some code in that event. Go to Visual Studio Properties pane and click the Lightning Bolt to open the Methods and Events pane, then scroll down until you find the RowHeaderMouseClick event as shown in Figure 7 below.

After finding and double-clicking the RowHeaderMouseClick, go to the Code Pane and locate the just created event under the name dgvStudents_RowHeaderMouseClick (the prefix dgvStudents is my DataGridView’s name). Go ahead and add the following code inside the event:

After adding the code above, I am getting two red wavy lines, one under the txtStudentID and the second one under the ReadStudentRecord function. To fix the first one, proceed to add one Textbox and change its properties according to the table below. This new textbox will be holding the StudentID field. If it is not already selected, click in the new textbox and change the following properties:

Since the txtSudentID will be hidden its position is irrelevant but to keep everything organized let’s place it next to the txtSSN textbox. Now to fix the second red wavy line, locate your ReadStudentRecord function and completely replace it with the one below:

Now locate the btnExit_Click event and comment-out the following two lines of code:

Next locate the btnSaveRecord_Click event and do the same as above.

Finally, locate your LoadUserControls function and make sure it looks exactly like the code below:

Observe, in the animated figure below, how the clicking of the DataGridView row header evolves into calling the RowHeaderMouseClick event, which in turn, calls two other functions. Proceed to click the green Play button and once the form is up and running, click on each of the rows and observe how the textboxes are populated with information coming from the DataTable.

Once the form finish loading, I want the first DataGridView row selected and the textboxes populated. Then using the arrow keys, scroll up and down through the DataGridView while having information shown in the controls. To accomplish that I need to activate an event in the DataGridView called SelectionChanged. So click the DataGridView, then go to the Properties Pane, click the Lightning Bolt, and scroll down until you reach the SelectionChanged event and double-click on it. Then go to the Code Pane, locate the method just added (should be called dgvStudents_SelectionChanged) and add the code below:

Now go all the way up to the Form’s Load event and add the following line of code:

So now, observe how, after running the form, the first row gets selected and its content appears in the textboxes; also observe how, by scrolling up and down using the arrow keys, each row gets displayed in the textboxes.

After adding the code above, I am able to navigate through the Table records displaying their content in the textboxes, but now I want to modify any of the fields content and save the changes to the Database table so I will now add code to the 'Save Record' button click event. Go the Code Pane and locate the btnSaveRecord_Click event and replace whatever code is in there with the following code:

After adding the code above, two red wavy lines appear under the functions AddNewRecordOleDB and SaveEditedRecordOleDB. As you can see in the code above, I am using the memory variable gNewRecordMode to determine if the saving event refers to a new or an edited record. You may remember how the gNewRecordMode variable is set to True when the user clicks the ‘New Record’ button taking the Form into a ‘New Record Mode’ otherwise the Form is in ‘Edit Record Mode’. To take care of this issue, go to the closing curly bracket for the btnSaveRecord_Click event, and add the following code:

Note: In the code above, under MydbConnString, you must point to your Access Database by using your own path. Also, notice the use of two backslash characters; the reason being is that each special character has to be ‘escaped’ using a backslash and the backslash happens to be an specials character hence the double backslash..

Proceed to run the Form, change the data in some of the textboxes and click the Save Record button. Observe how, when changing the Last Name, the DataGridView re-sorts the records accordingly. Also observe how the ‘saving code’ is doing ‘round trips’ to the Database. It saves the changes to the Table, then reloads the Table in memory, to finally refresh the DataGridView with the just committed changes. This is an important aspect to consider, or keep in mind, when writing code interacting with Multi-user databases so just be conscious of this type of issues or challenges. In other words, you don’t want to unwittingly overwrite data changes done by other users.

Let’s now add code to the New Record button, so go the Form Design Pane and double-click that button, then go back to the Code Pane, and locate the newly created event called btnNewRecord_Click and add the following code inside that event:

Add the function below, which is the ClearUserControls function:

Now locate the btnExit_Click event and replace all code in there with the code below:

This last piece of code takes care of the logic involving the Exit Button and the New Record Mode as we have previously talked about.

Observe how when clicking the New Record button, all the textbox controls are emptied as part of getting the form ready to capture information for a brand new record. This time, after filling out some of the textboxes, proceed to click the Save Record button, and observe how the AddNewRecordOleDB function will be called, inserting a new record in the Database table accordingly. At this time, because we are lacking data validation pretty much at all levels, you will have to populate all the fields to avoid crashing the form after clicking the Save Record button.

As you can see throughout the different routines, I have built the code to talk to Access or an SQL Server based on a single variable called abcBackEnd. When abcBackEnd is equal to 1, the routines talk to Access otherwise they talk to an SQL Server. Two more variables are needed when interacting with an SQL server, the Server and Database names, abcSQLServer and abcSQLDB. These three variables must be declared and initialized at the properties level right at the beginning of the program

I hope you find this project useful. Thanks for visiting and please leave your comments below.