VB.NET: How to connect to SQL Server

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;PersistSecurity 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