VB.NET: How to connect to SQL Server

This topic is a part of VB.NET SQL Database Basics Tutorial series. On this article, you will learn how to connect to any version of SQL server using VB.NET. For programmers, database programming is a must if your working on data oriented companies. Before you can insert, update or query records from database, you must first know how to connect to it and this is my objective for this article. At the end of this article, you should be able to know how easy to connect to SQL server.


Note: I assumed that you already installed your SQL Server and Visual Studio .NET.

Here is the step-by-step procedure to connect to SQL server:
1. Create your VB.NET project.
2. Include the following namespaces.

Imports System.Data
Imports System.Data.SqlClient

The System.Data namespace provides access to classes that represent the ADO.NET architecture while the System.Data.SqlClient namespace is the.NET Framework Data Provider for SQL Server.
3. Declare and instantiate your SQLConnection object as shown below

Dim con As New SqlConnection

SQLConnection class represents an open connection to a SQL Server database.
4. Pass the SQL connection string to ConnectionString property of your SqlConnection object.

con.ConnectionString = "Data Source=atisource;Initial Catalog=BillingSys;Persist Security Info=True;User ID=sa;Password=12345678"

The connectionstring value usually contains the following :
Data Source - physical server hostname
Initial Catalog - your database name
User ID - SQL username use to connect to the server
Password - SQL username's password

On this sample, I am using an SQL Server 2005. For the connectionstring for other SQL version, you can get it from here http://www.connectionstrings.com/.

5. Last step is to invoke the Open method of the connection object

con.Open()

The complete sample sourcecode:

Imports System.Data.SqlClient
Imports System.Data

Private Sub ConnectToSQL()
Dim con As New SqlConnection
Dim cmd As New SqlCommand

con.ConnectionString = "Data Source=atisource;Initial Catalog=BillingSys;Persist Security Info=True;User ID=sa;Password=12345678"
con.Open()
End Sub

To capture if the connection was successful or not, just tweak the above code:

Imports System.Data.SqlClient
Imports System.Data

Private Sub ConnectToSQL()
Dim con As New SqlConnection
Dim cmd As New SqlCommand
Try
con.ConnectionString = "Data Source=atisource;Initial Catalog=BillingSys;Persist Security Info=True;User ID=sa;Password=12345678"
con.Open()
Catch ex As Exception
MessageBox.Show("Error while connecting to SQL Server." & ex.Message) Finally
con.Close() 'Whether there is error or not. Close the connection.
End Try
End Sub

Related Topic: VB.NET Tutorial: SQL Database Basics