SQL Databases Create, Update, and Query

One thing Visual Basic has always been good at is interacting with databases. VB.NET continues this great tradition. In fact this tutorial and source sample shows how you can create a SQL database, create a Table, create a Stored Procedure to populate the Table, , Create a View, and Execute the Stored Procedure to insert sample data. Lastly we will see how we can query the data using the View we created and store this into a DataSet and then display it in a DataGridView.

Before we start I recommend you download the source code sample and follow along:

Database Sample Source Code

The Connection String

The first step to any database handling code in VB.NET is to figure out your connection string so you can get to the database. In a real application this value is usually read from a configuration file or gathered from the user in some way. For a simple sample like ours we will just declare the string as a constant at the top of our file (See line 13 of Form1.vb).

Protected Const SqlConnectionString As String = _
"Server=(local);" & _
"DataBase=;" & _
"Integrated Security=SSPI"

Notice that the connection string has information about what server to hit, what default database to use, and security information. In our case we are telling it to hit our local SQL server, we don’t want to use a default DataBase, and we will use Integrated Security. This means that you need to be running as a user that has access to your SQL server (if all else fails try running Visual Studio as Administrator).

Note: If you have SQL Server Express installed you might need to modify the connection string to look like this:

Protected Const SqlConnectionString As String = _
"Server=(local)\sqlexpress;" & _
"DataBase=;" & _
"Integrated Security=SSPI"

The Sample UI

If you look at the sample source code provided with this article you will see a simple form that has six buttons on it. Each button demonstrates a different Database task we can do using VB.NET.

image

The tasks we will be covering in this sample and tutorial are:

Create the Database we will put everything in
Create the Table that will store our actual data
Create the SPROC (Stored Procedure) that we will use to populate our table with some data
Create the View that we will query to look at our data
Populate our Table with data using our Store Procedure
Lastly query our data, put it in a DataSet, and then display it in the GridView control

Create The Database

Creating a database in VB.NET is relatively straight forward. The key is to know the SQL code to create the database. With this we can create a SqlConnection to our Sql Server (using our connection string above), Create a Sql Command, and execute the SQL code to create the Database.

You can see an example of this in our VB.NET Source sample’s CreateDatabase Subroutine (around line 55):

Dim sqlStatement As String = _
"IF EXISTS (" & _
"SELECT * " & _
"FROM master..sysdatabases " & _
"WHERE Name = 'HowToDemo')" & vbCrLf & _
"DROP DATABASE HowToDemo" & vbCrLf & _
"CREATE DATABASE HowToDemo"

Dim connection As New SqlConnection(connectionString)

' A SqlCommand object is used to execute the SQL commands.
Dim cmd As New SqlCommand(sqlStatement, connection)

connection.Open()
cmd.ExecuteNonQuery()
connection.Close()

The SQL portion of this code checks to see if the “HowToDemo” database exists already. If it does we drop the old database. We then create the HowToDemo database. Notice that for the VB.NET code we create a SqlConnection object and point it to our server using the connectionString we created earlier. We then create a SqlCommand object and tell it to use the SQL code we created above and the connection we already created. Finally once everything is set up we open the connection, execute our SQL code, and then close the connection. Notice we call the ExecuteNonQuery method on the SqlCommand object. This method is the fastest one to call as all it does is “blindly” executes the SQL code and doesn’t return anything back to us.

Create The Table

Now that we have a database created we need to create a table that will have our rows of data in it. For those of you that aren’t familiar with databases you can kind of think of them like Excel Spreadsheets on steroids. Each Table would be one sheet in Excel. It would have column headers (which are called Fields in SQL Server) and it would have rows of data. If you look at the code in the click event handler for the Create Table button you will see how we create a new table called Contact (Around line 144).

Dim strSQL As String = _
"USE HowToDemo" & vbCrLf & _
"IF EXISTS (" & _
"SELECT * " & _
"FROM HowToDemo.dbo.sysobjects " & _
"WHERE Name = 'Contact' " & _
"AND TYPE = 'u')" & vbCrLf & _
"BEGIN" & vbCrLf & _
"DROP TABLE HowToDemo.dbo.Contact" & vbCrLf & _
"END" & vbCrLf & _
"CREATE TABLE Contact (" & _
"ContactID Int NOT NULL," & _
"FirstName NVarChar(40) NOT NULL," & _
"LastName NVarChar(40) NOT NULL" & ")"

Dim dbConnection As New SqlConnection(connectionString)

' A SqlCommand object is used to execute the SQL commands.
Dim cmd As New SqlCommand(strSQL, dbConnection)

dbConnection.Open()
cmd.ExecuteNonQuery()
dbConnection.Close()

The VB.NET portion of this code should look very familiar to you. In fact it looks the exact same as our code above for creating the database. The difference is in the SQL code. Notice that this time we check to see if the Table named Contact exists. If it does we drop the table and then re-create it. Our table will have 3 fields: ContactId, FirstName, and LastName.

Create The Stored Procedure (SPROC)

A stored procedure is like a subroutine (or procedure) that is stored in the database – hence the name. Stored procedures can be used for all sorts of things when doing database development. In our sample we are using it to populate our Contact table with some initial data. Here is what the code looks like to do this (See click handler for our Create Sproc Button – around line 206).

Dim dbConnection As New SqlConnection(connectionString)

Dim strSQL As String = _
"USE HowToDemo" & vbCrLf & _
"IF EXISTS (" & _
"SELECT * " & _
"FROM HowToDemo.dbo.sysobjects " & _
"WHERE Name = 'AddContacts' " & _
"AND TYPE = 'p')" & vbCrLf & _
"BEGIN" & vbCrLf & _
"DROP PROCEDURE AddContacts" & vbCrLf & _
"END"

' A SqlCommand object is used to execute the SQL commands.
Dim cmd As New SqlCommand(strSQL, dbConnection)

dbConnection.Open()
cmd.ExecuteNonQuery()

cmd.CommandText = _
"CREATE PROCEDURE AddContacts AS" & vbCrLf & _
"INSERT INTO Contact" & vbCrLf & _
"(ContactID, FirstName, LastName)" & _
"SELECT EmployeeID, FirstName, LastName " & _
"FROM Northwind.dbo.Employees"

cmd.ExecuteNonQuery()
dbConnection.Close()

By now this code should be making sense to you. Notice we are actually executing two commands this time. Also notice that we only open the connection once. We use that same connection for both command executions and then we close the connection. The first chunk of SQL we execute is similar to what we did before. We check to see if our stored procedure already exists. If it does we remove it.

The second command we execute is the SQL to create a new procedure. Notice all our procedure does is insert the values from the sample northwind database that Microsoft lets us use for free.

Important Note: If you don’t have the sample Northwind database installed when you go to populate the database (on Step 5) will see an error like this one:

image

If this happens you will either need to download and install the Northwind database or you can populate the table with just three contacts by replacing the create procedure SQL code above with this code:

cmd.CommandText = _
"CREATE PROCEDURE AddContacts AS" & vbCrLf & _
"INSERT INTO Contact" & vbCrLf & _
"(ContactID, FirstName, LastName)" & _
"SELECT 1, 'Joe', 'Smith' UNION " & _
"SELECT 2, 'Billy', 'Bob' UNION " & _
"SELECT 3, 'Jane', 'Doe'"

Create The View

A view is simply another way to look at data in a table. In our sample all our view does is returns everything from the table. In a real world application your views will often be much more complex. Displaying data from multiple tables with filters and other things applied. The code to create our view looks like this (In the Create View button click event – around line 274):

Dim dbConnection As New SqlConnection(connectionString)

Dim strSQL As String = _
"USE HowToDemo" & vbCrLf & _
"IF EXISTS (" & _
"SELECT * " & _
"FROM HowToDemo.dbo.sysobjects " & _
"WHERE Name = 'GetContacts' " & _
"AND TYPE = 'v')" & vbCrLf & _
"BEGIN" & vbCrLf & _
"DROP VIEW GetContacts" & vbCrLf & _
"END"

' A SqlCommand object is used to execute the SQL commands.
Dim cmd As New SqlCommand(strSQL, dbConnection)

dbConnection.Open()
cmd.ExecuteNonQuery()

cmd.CommandText = _
"CREATE VIEW GetContacts AS " & _
"SELECT * " & _
"FROM Contact"

cmd.ExecuteNonQuery()
dbConnection.Close()

This code is about the same as all the earlier code so I’m not going to explain it in more detail.
Populate the Table with Data

Now that we have everything in place its an easy task for us to populate our table with some sample data. All we need to do is execute the stored procedure we created above. You can find the code for this in the Populate button’s click event (around line 333):

Dim strSQL As String = "EXECUTE HowToDemo.dbo.AddContacts"

Dim cmd As New SqlCommand(strSQL, dbConnection)

dbConnection.Open()
cmd.ExecuteNonQuery()
dbConnection.Close()

If when you go to run this code you get an exception see the note above. This is where the stored procedure you created is actually executed so if your Northwind database isn’t set up you will either have to set it up or use the work around I described above.
Display Data using a DataSet and a DataGrid

Now for the final step. Lets query our database and see the data we created. To start with lets take a look at the code to query the database and populate a DataSet (around line 389):

Dim strSQL As String = _
"USE HowToDemo" & vbCrLf & _
"SELECT * " & _
"FROM GetContacts"

Dim dbConnection As New SqlConnection(connectionString)

' A SqlCommand object is used to execute the SQL commands.
Dim cmd As New SqlCommand(strSQL, dbConnection)

' The SqlDataAdapter is responsible for using a SqlCommand object to
' fill a DataSet.
Dim da As New SqlDataAdapter(cmd)
Dim dsContacts As New DataSet()
da.Fill(dsContacts, "Contact")

Notice the beginning of this code looks very similar to what we’ve already seen. We make a connection to our database, create a Command, and set it up to use our SQL code and our connection. Notice the difference here though. Instead of executing our SqlCommand directly we instead pass it into our SqlDataAdapter. We then create our DataSet and use call the Fill function on our DataAdapter. The Fill method takes two parameters. The first is our DataSet we want to populate. The second parameter is the name of the Table – in our case Contact.

Now we have a DataSet that is filled with the contents of our table. Next we will get our DataGrid set up so that it will display our data:

With Me.DataGridView1
.Visible = True
.AutoGenerateColumns = False
.AlternatingRowsDefaultCellStyle.BackColor = Color.Lavender
.BackColor = Color.WhiteSmoke
.ForeColor = Color.MidnightBlue
.CellBorderStyle = DataGridViewCellBorderStyle.None
.ColumnHeadersDefaultCellStyle.Font = New Font("Tahoma", 8.0!, FontStyle.Bold)
.ColumnHeadersDefaultCellStyle.BackColor = Color.MidnightBlue
.ColumnHeadersDefaultCellStyle.ForeColor = Color.WhiteSmoke
.DefaultCellStyle.ForeColor = Color.MidnightBlue
.DefaultCellStyle.BackColor = Color.WhiteSmoke
End With

Notice that we make our Grid visable we also set it to not Auto Generate its columns. The rest of the code is to just make the DataGrid look a little nicer as it displays our data.

Lastly we tell the Data Grid View to display our data:

Me.DataGridView1.DataSource = dsContacts.Tables(0)
Dim newColumn As Integer = Me.DataGridView1.Columns.Add("ContactID", "Contact ID")
Me.DataGridView1.Columns(newColumn).DataPropertyName = "ContactID"

newColumn = Me.DataGridView1.Columns.Add("FirstName", "First Name")
Me.DataGridView1.Columns(newColumn).DataPropertyName = "FirstName"

newColumn = Me.DataGridView1.Columns.Add("LastName", "Last Name")
Me.DataGridView1.Columns(newColumn).DataPropertyName = "LastName"

The first line of the code above is how you get data to show up in the Grid. You set its DataSource property. In our case we are setting it the first (and only) table in our DataSet we created before. Next we create Add a column to the DataGrid. Notice we keep track of the newColumn number so that we can reference it again and set its DataPropertyName. This value needs to match the FieldName of our table. We do this for all three columns and we are good to go.
Summary

Business applications and many other programs need to make use of Databases. This tutorial gave us a good introduction to how we can interact with SQL databases to store our data and to display it in the GridView control. If you haven’t already done so I recommend you donwload the source code sample below: …