Exploring the Data Controls in Visual Basic.NET

Introduction

Databases seem to be mysteriously strange creatures for many young aspiring programmers. Some programmers know about them, but haven’t worked with them physically. A legendary creature called the Loch-Ness Monster, or even Big Foot comes to mind here. Everyone thinks poor old Nessie and Big Foot are terrible and something to be scared of; well, databases are not scary, just misunderstood. Today I will demonstrate how to make a very basic database and which tools Visual Studio 2012 provides for working with a database.

Databases

Databases can be friendly creatures if you know how to use them right; they can also become very bad, especially if you have no idea on how to use them properly. A database’s sole function is to store information, hence the name: data base. Knowing how to properly store inside them takes practice, and lots of planning. People that fail to do this get eaten up by the database monster.

A database consists of the following objects (I will not go into great detail on all of them, as this is only an introduction):

  1. Tables
  2. Queries
  3. Views
  4. Stored Procedures
  5. Functions

Tables

Tables are the main building blocks of any database. This is where all the information will be stored. A table consists of rows (records) and columns (fields). A row, or record contains all the information pertaining to one topic. A column, or field is once piece of information. Here is a small example of records and fields.

Records and Fields
Figure 1 – Records and Fields

In the above figure, you can see that there are seven records and three fields. This database we will create a bit later.

Although you may have many columns in each table and many tables at your disposal in a database, it doesn’t mean that you have to make use of all of them. With databases less is more. This means that the least amount of fields and the least amount of tables used in your database will amount to the best performance. Let me give you another example:

Database Tables used productively
Figure 2 – Database Tables used productively

This is from one of my programs that I had to implement at work. Its whole purpose is to track student progress according to which course the student is doing. Each course has different subjects. The reason I show this here is to show you that a simple program can make use of multiple inter-connected tables. An inexperienced programmer would add all the fields into one table, which is possible, but is also wrong. This system has been running successfully for twelve years already. There is more than enough space to store the student’s personal information, as well as more than enough space for custom courses and custom subjects.

All of these tables are connected by a term called relationships. If you look closely at the above tables in Figure 2, you will see that inside each table there is a reference to another table’s field. This forms a relationship between tables. A relationship becomes possible when there is a Primary key inside one table referencing a Foreign key. This is not really the main topic of today’s article, so let me not get ahead of myself (as always) again. More information on Database tables can be found here.

Queries

Now that we know where and how to store data, we need to know how to access that data. This is done through a database query. You usually have to write a query to extract the needed information from the appropriate tables. I will delve deeper into this in my next article (Extracting data). More information on database queries can be found here.

Views

A View is a dynamic result set. This means that we can create a view based on a query and it will not affect any data that is stored. More information on Views can be found here.

Stored Procedures

A Stored Procedure is a sequence of SQL code that can perform tedious tasks quickly. By making use of Stored Procedures, you will save a lot of time and won’t need to run each query (that can reside in a Stored Procedure) manually. More information on Stored Procedures can be found here.

Functions

There are many functions inside the SQL Language. These functions can help with string manipulation, date manipulation as well as computing Averages, just to name a few. A complete list of SQL Functions can be found here.

This concludes the Introductory part of this article. If you are still in the dark concerning databases have a look at these:

Data Controls in Visual Basic.NET

Now that you know how data is stored inside a database, you need to know what controls VB provides for accessing data. The Visual Basic 2012 Toolbox provide the following (as shown in Figure 3) Data Controls:

Toolbox Data Controls
Figure 3 – Toolbox Data Controls

I will only cover the following:

  1. BindingSource
  2. BindingNavigator
  3. Dataset
  4. DataGridView
  5. TableAdapter

As you can see, I will unfortunately not be talking about Charts, as that has very little to do with Databases and I do not want to confuse you. If you also look closely at the above numbered list, you will notice that I made reference to the TableAdapter. This is not part of this set of controls as well, but it does get added automatically once you have set up your connections to the database. I will go into detail with all of them, as well as some of the normal controls’ properties that assist in working with Databases.

I have always found it easiest to learn by doing things practically, so let’s start.

Creating the Database

You may use either Microsoft Access or SQL Server to create your database. I will in any case highlight the steps for both quickly.

Creating an SQL Server Database

Follow these steps:

  1. Open SQL Server Management Studio.
  2. Right click on the folder named Databases, then select New. A screen similar to Figure 4 will be shown.

    New SQL Database
    Figure 4 – New SQL Database
     

  3. Give it a name of Students and click OK – You will now see Students in the list of Databases.
  4. Expand Students in the Database list, and right click on Tables, then select New Table,  as shown in Figure 5.

    New Database Table
    Figure 5 – New Database Table

  5. Enter the following to create this table’s fields:

    Table Columns
    Figure 6 – Table Columns

  6. Click on the Save button and name your Table StudentInfo.
  7. In SQL Management Studio, click on New Query and type the following:
    INSERT INTO [Students].[dbo].[StudentInfo]
               ([StudentName]
               ,[StudentSurname]
               ,[StudentNumber])
         VALUES
               ('Hannes', 'du Preez', 1)
    
    INSERT INTO [Students].[dbo].[StudentInfo]
               ([StudentName]
               ,[StudentSurname]
               ,[StudentNumber])
         VALUES
               ('YourName', 'YourSurname', 2)
    GO
  8.   Click on Execute – There will now be two records inside your database.

If you are unfamiliar with the INSERT INTO SQL command, have a look here. It simply inserts records into the specified table. You can add more. I will go into greater detail about this SQL command in a future article. I am including the SQL script with this article, in case you are struggling.

Creating a Microsoft Access 2010 Database

Follow these steps to create an MS Access 2010 database:

  1. Open Microsoft Access.
  2. In the Available Templates, select Blank Database, as shown in Figure 7:

    New MS Access Database
    Figure 7 – New MS Access Database

  3. On the right side of the screen enter the File name, in this case: Students.accdb, then click Create, as shown in Figure 8:

    Create the Database
    Figure 8 – Create the Database

  4. Inside the new Screen, edit the Columns and data to reflect Figure 9:

    Access Table Columns
    Figure 9 – Access Table Columns

  5. Save the table as StudentInfo.

VB.NET Project

Now that we have created the database(s), you can proceed with creating a new VB.NET Windows Forms Project in VB.NET 2012. Name it anything you like. Design Form 1 to resemble the next Figure.

Note: In this example I will make use of both SQL Server and MS Access databases. If you have only one of the mentioned products, do not worry, as I will be demonstrating both.

Use the Project menu to add a new Windows Form and design it precisely the same as Form 1. I used Form 1 for the SQL Server database, and Form 2 for the MS Access Database. As mentioned, it is OK if you only follow your appropriate selection here.

Our design for both forms
Figure 10 – Our design for both forms

The controls used in the above figure are listed below:

  • BindingNavigator
  • DataGridView
  • Three Textboxes with no text – we will set the Properties later
  • Three Labels

Connecting to an SQL Database using the Data Controls

Now that you have all the controls on the form, you need to connect them with your database. This section covers only an SQL Database. The Access database will be covered shortly.

To connect to your SQL Database, follow these easy steps:

  1. Click on the DataGridView on your Form.
  2. A small right pointing triangle will appear.
  3. Click on it. This will give you a screen similar to Figure 11.

    Choose Project DataSource
    Figure 11 – Choose Project DataSource

  4. Click on the Drop Down Arrow next to Choose Data Source. This will produce a screen that resembles Figure 12.

    Add Project Data Source
    Figure 12 – Add Project Data Source

  5. Click on Add Project Data Source. The Wizard will open as shown in Figure 13.

     Choose a Data Source Type
    Figure 13 – Choose a Data Source Type

  6. Make sure Database is selected, then click Next. The next screen appears.

    Database Model
    Figure 14 – Database Model

  7. Make sure Dataset is selected, then click on Next.
  8. Click on New Connection. The following screen will appear.

    Add Connection
    Figure 15 – Add Connection

  9. Next to the Data Source, click on Change. This will produce the following screen.

    Change Data Source
    Figure 16 – Change Data Source

  10. Select Microsoft SQL Server from the list, as reflected in Figure 16, then click on OK.
  11. On the displayed screen, select your Server name from the dropdown list. In my case, my Server name is HANNES. Once you have selected your Server name, select your database from the list. In our case, it is Students. Click OK.
    Add SQL Database Connection
    Figure 17 – Add SQL Database Connection
  12. Make sure that you select StudentsSQLConnectionString from the displayed screen, then click on Next.
  13. Choose your database objects. In this case we only have a Table, so select the box next to Tables, as shown in Figure 18.

    Tables
    Figure 18 – Tables

  14. Give the DataSet an appropriate name such as StudentsDataSet or testingdataSet, then Click Finish. This will close the Wizard, and add these controls to your design window

    Added Data controls
    Figure 19 – Added Data controls

You will now see that your DataGridView shows the three columns of our table. The DataGridView is now connected to your database. We still need to connect our Textboxes as well as the BindingNavigator to the database.

For more information on the DataSet control, have a look here. For BindingSource, have a look here. Lastly, for the TableAdapter control, have a look here.

Connecting the BindingNavigator to the BindingSource

Follow these steps:

  1. Select the BindingNavigator and open the Properties Window.
  2. Click on The BindingSource Property in the Properties window.
  3. Select StudentInfoSQLBindingSource from the list.
  4. Your BindingNavigator is now connected.
  5. For more information on the BindingNavigator, have a look here.

Connecting the TextBoxes to the SQL Database

Follow these steps:

  1. Select a TextBox and open the Properties Window.
  2. Expand the DataBindings property (at the top of the list).
  3. Select Text.
  4. Select the appropriate Field you want to connect to  from the displayed combobox, as shown in Figure 20.

     TextBox DataBindings
    Figure 20 – TextBox DataBindings

If you were to run your project now, all the data should be displayed, and you will be able to navigate between them using the BindingNavigator.

Connecting to an MS Access 2010 Database using the Data Controls

To connect to your SQL Database, follow these easy steps:

  1. Click on the DataGridView on your Form.
  2. A small right pointing triangle will appear.
  3. Click on it. This will give you a screen similar to Figure 11.
  4. Click on the Drop Down Arrow next to Choose Data Source. This will produce a screen that resembles Figure 12.
  5. Click on Add Project Data Source. The Wizard will open as shown in Figure 13.
  6. Make sure Database is selected, then click Next. Figure 14 appears.
  7. Make sure Dataset is selected, then click on Next.
  8. Click on New Connection. Figure 15 will appear.
  9. Click on Browse next to Database name, and select your Students.accdb database, as shown in Figure 21.

     Access Connection
    Figure 21 – Access Connection

  10. Click OK
  11. Select Tables, as shown in Figure 18. Give the ConnectionString a name such as StudentAccessDataSet.
  12. Click Finish.
  13. The following controls will appear inside your design window.

    Added Data access controls
    Figure 22 – Added Data access controls

You will now see that your DataGridView shows the three columns of our table. The DataGridView is now connected to your database. We still need to connect our Textboxes as well as the BindingNavigator to the database.

For more information on the DataSet control, have a look here. For BindingSource, have a look here. Lastly, for the TableAdapter control, have a look here.

Connecting the BindingNavigator to the BindingSource

Follow these steps:

  1. Select the BindingNavigator and open the Properties Window.
  2. Click on The BindingSource Property in the Properties window.
  3. Select StudentInfoSQLBindingSource from the list.
  4. Your BindingNavigator is now connected.
  5. For more information on the BindingNavigator, have a look here.

Connecting the TextBoxes to the Access Database

Follow these steps:

  1. Select a TextBox and open the Properties Window.
  2. Expand the DataBindings property (at the top of the list).
  3. Select Text.
  4. Select the appropriate Field you want to connect to  from the displayed combobox, as shown in Figure 19.

Conclusion

This concludes today’s article. Now that you know what databases are, and how to connect to them, watch this space for my next article, which will demonstrate how to extract information out of our tables. Until then, cheers!

Hannes DuPreez
Hannes DuPreez
Ockert J. du Preez is a passionate coder and always willing to learn. He has written hundreds of developer articles over the years detailing his programming quests and adventures. He has written the following books: Visual Studio 2019 In-Depth (BpB Publications) JavaScript for Gurus (BpB Publications) He was the Technical Editor for Professional C++, 5th Edition (Wiley) He was a Microsoft Most Valuable Professional for .NET (2008–2017).

More by Author

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Must Read