Doing Data Extraction with Visual Basic.NET

CodeGuru content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

Introduction

Extracting data from databases is not complicated at all. Any developer needs to know how to do this, as it’s essentially what makes your app tick. With this article I will demonstrate how to extract any information from a database and put it in your VB.NET application.

SQL

Structured Query Language is the programming language you use to build databases, extract information from databases, and store information into databases. In an earlier article I spoke about the term database, so if you haven’t read it yet, have a read here. If this is your first time hearing the term SQL, have a look here.

To extract any information form any database we need to write an SQL query. A query can be explained as a request for information, which it then presents ultimately. Now, as I have mentioned, all database information gets stored inside tables. These tables contain all the information that has been stored. We must write a query to get this information and present it to the user.

Queries

The simplest form of a query would look like:

SELECT * FROM TableName

This will give you all the data stored in that particular table. Remember now, that some tables can have millions of records inside them, so in this case, this simple query can give you all the millions of records. Usually this is not really needed. Usually there is some sort of condition involved when extracting information. A query with a condition will look like:

SELECT * FROM TableName WHERE Field = Value

A query similar to the above now has the capability to return only certain information. Let me use a bank as an example. With any bankm they deal with millions of customers. Many of these customers may have the same last names, or even the exact same names. Any customer of the bank may have more than one type of account. Now, taking all of this into consideration, a query to return all the information to a specific person may be troublesome. If conditions did not exist, this would have caused you stand in the banking queue for a very long time while the poor teller is sifting through all the information just to find the correct person with the correct information.

We can take conditions further, and write a query like the following:

SELECT * FROM TableName WHERE Field1 = Value1 AND Field2 > Value2 OR Field3 < Value3

It gets trickier now, as here we deal with more than one field’s value and more than one condition. The above query can be translated into layman’s terms to mean:

Select all the data from the table named TableName where Column1 is equal to Value1 AND Column2 is greater than Value2 OR Column3 is less than Value3. More information on Query Conditions can be found here.

It can get more complicated…

SELECT * FROM Table1, Table2, Table3 WHERE Table1.Field1 = Value1 AND Table2.Field2 > Value2 OR Table3.Field3 < Value3

This SQL code extracts certain data from three different tables, based on various conditions set.

LIKE QUERY

This query makes use of wildcards in order to narrow down search criteria. This is what we will work with today.

Yes, and even more complicated, a lot more! Here is an example:

USE AdventureWorks2008R2;
GO
SELECT Ord.SalesOrderID, Ord.OrderDate,
    (SELECT MAX(OrdDet.UnitPrice)
     FROM AdventureWorks.Sales.SalesOrderDetail AS OrdDet
     WHERE Ord.SalesOrderID = OrdDet.SalesOrderID) AS MaxUnitPrice
FROM AdventureWorks2008R2.Sales.SalesOrderHeader AS Ord

This is what is called a sub query. More information about sub queries can be found here

SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;

This query creates a join between tables. More information on Joins, can be found here.

Let me not get too far ahead of myself here…

Sample Project

Now that you understand queries and how they work, let us do a practical application that interrogates the data stored in a database table. I will demonstrate the code for both Microsoft Access 2010 as well as SQL Server. You can do both, or only one, depending on your need. Open Visual Studio 2012 and start a new VB.NET Windows Forms Project.

Add two forms to your project and design them according to Figure 1 and Figure 2 below.

Figure 1 – Form 1

Figure 2 – Form 2

With this article I will make use of the same databases I used in the Data Controls article I did earlier. If you haven’t read it yet, I suggest that you do so now.

Add the following items to the Search Field Combobox:

• Name
• Surname
• StudentNumber

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.
4. Click on the Drop Down Arrow next to Choose Data Source.
5. Click on Add Project Data Source. The Wizard will open.
6. Make sure Database is selected, then click Next.
7. Make sure Dataset is selected, then click on Next.
8. Click on New Connection.
9. Click on Browse next to Database name, and select your Students.accdb database.

    
   Figure 3 – 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.

    
    Figure 4 – 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 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 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.

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.

   
   Figure 4 – Choose Project DataSource

4. Click on the Drop Down Arrow next to Choose Data Source.

   
   Figure 5 – Add Project Data Source

5. Click on Add Project Data Source. The Wizard will open.

   
   Figure 6 – Choose a Data Source Type

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

   
   Figure 7 – Database Model

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

   
   Figure 8 – Add Connection

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

   
   Figure 9 – Change Data Source

10. Select Microsoft SQL Server from the list, 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.
    
    Figure 10 – 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.

    
    Figure 11 – Tables

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

    
    Figure 12 – 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 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 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.

   
   Figure 13 – TextBox DataBindings

If you were to run your project now, all the data should be displayed.

Code

Now that we have connected both databases, or the one of your choice, we can move on to the code to extract information out of a database table.

Extracting Data from a Microsoft Access 2010 Database Table

Add the necessary Import statement to import the data reading capabilities for an Access 2010 database:

Imports System.Data.OleDb 'Import Data Library To Handle MS Access Databases

Add the following modular variables, as we will be using these throughout our entire form:

	Private strSearch As String 'Main Search String
	Private strSearchField As String 'Filed To Search
	Private strSearchValue As String 'Value To Search inside Field

These aptly named objects will store our search query, the field to search that was selected from the Search Field Combobox, as well as the value the user has entered to search.

If necessary, add the following inside your Form_Load event:

	Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

		'Fill DataTable & Display Data When Form Loads
		Me.StudentInfoTableAdapter.Fill(Me.StudentsDataSet.StudentInfo)

	End Sub

If you have connected the controls to the Access table as illustrated earlier, this should have been in automatically.

Add the next code segment to the Search Field Combobox’s SelectedIndexChanged event. Just a note, your objects may be named differently than mine, so keep that in consideration.

	Private Sub cboFields_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cboFields.SelectedIndexChanged

		'Start Building Final Query String
		strSearch = "SELECT * FROM StudentInfo WHERE "

		'Determine Which Field Was Selected
		Select Case cboFields.SelectedIndex

			Case 0

				'Field StudentName Was Selected
				strSearchField = "StudentName "

			Case 1

				'Field StudentSurname Was Selected
				strSearchField = "StudentSurname "

			Case 2

				'Field StudentNumber Was Selected
			    strSearchField = "StudentNumber "

		End Select

	End Sub

This starts to build the final query string that we will use to get the desired data from the database table. StrSearch initially looks like an ordinary SQL query (as I have explained earlier), but I have included the WHERE keyword in there in order to add a condition to the query. The field in question will be selected from the ComboBox and be placed inside the strSearchField variable, which will later be concatenated to strSearch. Add the following code to the Search String Textbox’s LostFocus event:

	Private Sub txtSearchString_LostFocus(sender As Object, e As EventArgs) Handles txtSearchString.LostFocus

		'Specify Search Value
		strSearchValue = "LIKE '%" & txtSearchString.Text & "%'"

	End Sub

This completes our ultimate query. We should now just join the initial query string with the selected field as well as the Value that we are looking for, which is now stored inside the strSearchValue variable.

You may have noticed the word LIKE and a couple of % signs above. What are they?

The Like operator enables you to narrow down your search via the use of wildcards. In this case the % signs are replacements for all text at that certain point. You also get a ? wildcard. The question mark substitutes one character.

Let me explain it better:

Let’s say you know that the name of the person that you’re looking for starts with an H, but you do not know what the full name is; here, wildcards can help to identify all the values starting with ‘H’, so if I were to enter:

H%

Inside the LIKE condition, exactly that would happen? Let me take it further: let us presume that you know there is an ‘h’ in the name somewhere, you will enter a condition such as:

%h%

That is how simple it is to replace multiple characters with a wildcard. Now, single character wildcards work the same way. If I enter:

H?n?es

I can end up with values such as the following:

• Hannes
• Hinges
• Hhnhes

Enter the following for the Search button:

	Private Sub btnSearch_Click(sender As Object, e As EventArgs) Handles btnSearch.Click

		Try

			'Create A Command To Host Our Query
			Dim oSelCmd As OleDbCommand = New OleDbCommand

			'The Command Only Uses Text
			oSelCmd.CommandType = CommandType.Text

			'Set The Command's Connection
			oSelCmd.Connection = Me.StudentInfoTableAdapter.Connection

			'Specify The Command
			oSelCmd.CommandText = strSearch & strSearchField & strSearchValue

			'Open Connection
			Me.StudentInfoTableAdapter.Connection.Open()

			'Excute Command Via DataReader Object
			Dim oDr As OleDbDataReader = oSelCmd.ExecuteReader(CommandBehavior.Default)

			'Construct A DataTable To Host Returned Values
			Dim dt As New DataTable

			'Declare Counter For Columns
			Dim i As Integer

			'How Many Fields Are There?
			Dim count As Integer = oDr.FieldCount - 1

			'Add Returned Columns To DataTable
			For i = 0 To count

				dt.Columns.Add(oDr.GetName(i), oDr.GetFieldType(i))

			Next

			'Add Returned Rows To DataTable
			Do While oDr.Read()

				'Create A New Row
				Dim r As DataRow = dt.NewRow

				'Add All Records Returned
				For i = 0 To count

					r(i) = oDr.Item(i)

				Next

				'Add Rows
				dt.Rows.Add(r)

			Loop

			'Set DataGridView's DataSource To Show Found Records
			Me.dgvStudents.DataSource = dt

		Catch ex As Exception 'General Exception, Can Narrow It Down Later

			MessageBox.Show(ex.Message.ToString()) 'Show Error

		Finally

			'Close Connection
			Me.StudentInfoTableAdapter.Connection.Close()

		End Try

	End Sub

This is where all the database communication takes place. I did the following inside the Search button’s click event:

• Created a new command to execute on the database.
• Created a connection to the database, which is stored inside the Table Adapter’s connection.
• I specify my command, which is the query I continuously built.
• Opened the connection.
• Executed the command – which is now my query.
• Created a Data Reader object to return the results found, if found at all.
• Created a new Data Table to store the results. With this I specified the columns I wanted, and looped through each found result.
• Set the DataGridView’s Data Source property to the new DataTable.
• Closed the connection.

Whatever gets returned is now placed inside the DataGridView. Almost done with this Form; add these two events:

	Private Sub btnClear_Click(sender As Object, e As EventArgs) Handles btnClear.Click

		Dim ct As Control 'Create a Control Object

		For Each ct In Me.Controls 'Loop Through All Controls

			'Is Current Control Being Looped Over A TextBox
			If TypeOf ct Is TextBox Then

				ct.Text = "" 'Clear The Text

			End If

		Next

	End Sub

		Private Sub btnNext_Click( sender As Object,  e As EventArgs) Handles btnNext.Click

		'Go To Form 2
		Dim F2 As New Form2

		F2.Show

	End Sub

The first event simply clears all the text out, so that we can start another search. The last event simply navigates to the next Form. If you have opted not to do the SQL database, the next form is optional for you.

Extracting Information from an SQL Database Table

Well, I will follow the same procedure as I did above. We first need to Import the necessary Namespace so that we can interrogate an SQL database:

Imports System.Data.SqlClient 'NameSpace To Handle SQL Server Databases

Now, add the modular variables so that we can store the database command, which will fire to retrieve the results in them:

	Private strSearch As String 'Main Search String
	Private strSearchField As String 'Filed To Search
	Private strSearchValue As String 'Value To Search inside Field

These three string variables will continuously build an SQL Query string to retrieve our data from the database table. If necessary, add the following into your Form_Load event:

	Private Sub Form2_Load( sender As Object,  e As EventArgs) Handles MyBase.Load

		'Fill DataTable & Display Data When Form Loads
		Me.StudentInfoTableAdapter.Fill(Me.StudentsDataSetSQL.StudentInfo)

	End Sub

This connects to the database when the form loads and presents the data to the user via the TableAdapter object, which was created when you set up the connections to the database. Add the following events:

	Private Sub txtSearchString_LostFocus(sender As Object, e As EventArgs) Handles txtSearchString.LostFocus

		'Specify Search Value
		strSearchValue = "LIKE '%" & txtSearchString.Text & "%'"

	End Sub


	Private Sub cboFields_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cboFields.SelectedIndexChanged

		'Start Building Final Query String
		strSearch = "SELECT * FROM StudentInfo WHERE "

		'Determine Which Field Was Selected
		Select Case cboFields.SelectedIndex

			Case 0

				'Field StudentName Was Selected
				strSearchField = "StudentName "

			Case 1

				'Field StudentSurname Was Selected
				strSearchField = "StudentSurname "

			Case 2

				'Field StudentNumber Was Selected
			    strSearchField = "StudentNumber "

		End Select

	End Sub

When a user selects a field from the combobox, that particular field name gets stored inside the strSearchField variable. When a person has entered something in the Search String textbox, that gets stored inside the strSearchValue variable. Now, ultimately, in the next code segment you will concatenate the strSearch variable with the strSearchField variable as well as the strSearchValue variable giving you one SQL command containing everything necessary to find the information you are looking for. If you haven’t read the Access section, I would still recommend it as there I not only show how to work with an Access table, but also how this search command has been built.

Let us now add the Search button’s code:

	Private Sub btnSearch_Click(sender As Object, e As EventArgs) Handles btnSearch.Click

		Try

			'Create A Command To Host Our Query
			Dim oSelCmd As SQLCommand = New SQLCommand

			'The Command Only Uses Text
			oSelCmd.CommandType = CommandType.Text

			'Set The Command's Connection
			oSelCmd.Connection = Me.StudentInfoTableAdapter.Connection

			'Specify The Command
			oSelCmd.CommandText = strSearch & strSearchField & strSearchValue

			'Open Connection
			Me.StudentInfoTableAdapter.Connection.Open()

			'Excute Command Via DataReader Object
			Dim oDr As SQLDataReader = oSelCmd.ExecuteReader(CommandBehavior.Default)

			'Construct A DataTable To Host Returned Values
			Dim dt As New DataTable

			'Declare Counter For Columns
			Dim i As Integer

			'How Many Fields Are There?
			Dim count As Integer = oDr.FieldCount - 1

			'Add Returned Columns To DataTable
			For i = 0 To count

				dt.Columns.Add(oDr.GetName(i), oDr.GetFieldType(i))

			Next

			'Add Returned Rows To DataTable
			Do While oDr.Read()

				'Create A New Row
				Dim r As DataRow = dt.NewRow

				'Add All Records Returned
				For i = 0 To count

					r(i) = oDr.Item(i)

				Next

				'Add Rows
				dt.Rows.Add(r)

			Loop

			'Set DataGridView's DataSource To Show Found Records
			Me.dgvStudents.DataSource = dt

		Catch ex As Exception 'General Exception, Can Narrow It Down Later

			MessageBox.Show(ex.Message.ToString()) 'Show Error

		Finally

			'Close Connection
			Me.StudentInfoTableAdapter.Connection.Close()

		End Try

	End Sub

This is where all the database communication takes place. I did the following inside the Search button’s click event:

• Created a new command to execute on the database.
• Created a connection to the database, which is stored inside the Table Adapter’s connection.
• I specify my command, which is the query I continuously built.
• Opened the connection.
• Executed the command – which is now my query.
• Created a Data Reader object to return the results found, if found at all.
• Created a new Data Table to store the results. With this I specified the columns I wanted, and looped through each found result.
• Set the DataGridView’s Data Source property to the new DataTable.
• Closed the connection.

Whatever gets returned is now placed inside the DataGridView. Almost done with this Form, add these two events:

	Private Sub btnClear_Click(sender As Object, e As EventArgs) Handles btnClear.Click

		Dim ct As Control 'Create a Control Object

		For Each ct In Me.Controls 'Loop Through All Controls

			'Is Current Control Being Looped Over A TextBox
			If TypeOf ct Is TextBox Then

				ct.Text = "" 'Clear The Text

			End If

		Next

	End Sub

		Private Sub btnExit_Click(sender As Object, e As EventArgs) Handles btnExit.Click

		'Exit Application
		Application.Exit()

	End Sub

As you can see, the only difference between connecting to an Access database table and an SQL Server database table, is the namespaces involved as well as how the data is stored inside the databases. I will not go into much detail here, as that is a topic for another day. As you can also see it is very easy to communicate with any database.

Further Reading

• SQL Wildcards
• SQL LIKE Operator

Conclusion

This concludes my article for today. I hope you have learned from it, and that you will not be scared in future database applications. Look out for my next article that will explain Parameterized Queries. Until then, cheers!