Visual Basic and SQL Server Introduction

SQL Server is a database that can be used in conjunction with Visual Basic to create desktop, web and console based application. It can be used to store data, as well as configuration information. Visual Basic can be used to query the data stored within an SQL Server database, as well as insert, update and delete the data.

Connecting to an SQL Server Database

In order to access data within an SQL Server database with Visual Basic, a connection to the database must first be established. Below is an example of how this can be achieved. In order for this to work, there must be an ‘Imports‘ statement for the ‘System.Data.SqlClient’ namespace.

' Database variables.
Dim server As String = "localhost\MSSQLSERVERDEMO"
Dim database As String = "Demo"
Dim username As String = "DemoUN"
Dim password As String = "DemoPW"

' Database connection variable.
Dim connect = New SqlConnection(
    "Server=" & server & ";" &
    "Database=" & database & ";" &
    "User Id=" & username & ";" &
    "Password=" & password)

Try

    ' Connect to database.
    connect.Open()

    ' Message confirming successful database connection.
    Console.WriteLine("Database connection successful.")

Catch ex As Exception

    ' Message confirming unsuccessful database connection.
    Console.WriteLine("Database connection unsuccessful.")

    ' Stop program execution.
    End

End Try

In this example, a ‘Try-Catch’ block is used to catch any exceptions that may arise in connecting to the database. A ‘Try-Catch’ block can be used to handle any exceptions in a user friendly manner. The ‘End’ statement, after the confirmation of an unsuccessful database connection, stops execution of the program completely, so no further statements are executed.

It should be noted that it isn’t necessary to have the server and database information in separate variables before using it in the database connection. It much depends on personal preference and whether the variables are going to be re-used elsewhere in the program. The database connection variable can be re-written as follows.

' Database connection variable.
Dim connect = New SqlConnection(
    "Server=localhost\MSSQLSERVERDEMO; Database=Demo;" &
    "User Id=DemoUN; Password=DemoPW")