Connecting to SQL Server

Use the SQL Server .NET data provider in the

System.Data.SqlClient namespace to

connect to a Microsoft SQL Server 7.0 or 2000 database (for example, the Northwind

sample database).Your connection strings can take advantage of integrated security or

connect using a given SQL login and password.

First, you need to reference the SqlClient namespace. Add the following statement

to the top of your data access client source file to reference the System.Data.SqlClient

namespace:

Imports System.Data.SqlClient

Next, you need to create the connection, use it, and clean up inside a method. For

example, you can place the following in Page_Load:

Dim connectString As string =

➥"Integrated Security=yes;Initial Catalog=Northwind;Data Source=(local)"

Dim sqlConn As SqlConnection = New SqlConnection( connectString )

Try

sqlConn.Open()

'do something and then be sure to close the connection...

Finally

If Not sqlConn Is Nothing Then

sqlConn.Close()

End If

End Try

Here's an alternative version of an integrated security connection string.This will look

familiar if you build your connection strings using Universal Data Link (.UDL) files.

This VB.NET example explicitly references the Security Support Provider Interface

(SSPI) for the integrated security string and sets the connection string via the

ConnectionString property:

Dim sqlConn As SqlConnection = New SqlConnection()

sqlConn.ConnectionString = "Integrated Security=SSPI;Initial

➥Catalog=Northwind;Data Source=(local)"

Try

sqlConn.Open()

'do something and then be sure to close the connection...

Finally

If Not sqlConn Is Nothing Then

sqlConn.Close()

End If

End Try

As mentioned, you might also define a connection string that uses a specific SQL Server

logon ID. Using the first example, only the connection string changes:

connectString =

"User ID=user;Password=pass;Initial Catalog=Northwind;Data Source=(local)"

Now, let's say you want to create a connection to a remote database server. In this case,

you need to specify the SQL Server logon ID and password again. Using the

ConnectionString property, the connection string will look similar to this:

sqlConn.ConnectionString = "User ID=user;Password=pass;

Network Library=DBMSSOCN;Data Source=192.168.1.1,1433"

Of course, you have to change the IP address to match your configuration.

Comments

The most important point to remember is this: if you open a connection you must

remember to close it.You cannot rely on ADO.NET's garbage collector to do it for you

in a timely manner. If you neglect to close your connections, you will likely run out of

available connections in short order! The best way to ensure that connections are properly

closed is to wrap their usage in Try/Catch/Finally blocks. Also, in addition to the

techniques you've just seen, SqlConnection can also be created implicitly by certain

ADO.NET objects like the dataset.

Technorati : Connecting to SQL Server