"Connecting Visual Studio 2005 to SQL Server Express" (www.sqlmag.com/Article/ArticleID/97216 ) and "Connecting Visual Studio 2005 to SQL Server Express, Part 2" (www.sqlmag.com/Article/ArticleID/97258 ) covered how to create a connection between Visual Basic (VB) 2005 Express Edition and SQL Server Express. In those articles I used the VB Express wizards and interface elements to create the connection to SQL Server Express. Today I dive deeper and show you how to create a code-only connection between VB and your SQL Server Express database. Using code to create the connection has an important advantages: The code is easily portable between projects.
When you manually create a connection to SQL Server Express from your VB project, you first need to make sure that your project has a reference to the System.Data.SqlClient namespace. This namespace provides database support and isn't included in your project by default. To add the namespace to your project, use the Project, Add Reference menu option in VB Express. On the .NET tab, scroll through the list until you find System.Data.SqlClient. Select it from the list and click OK. Next, in the Declarations section of your project add an Imports statement for the namespace:
Imports System.Data.SqlClient
The Imports statement lets you use the classes within the System.Data.SqlClient namespace without explicitly spelling out each class name.
After adding the Imports statement, you can create your connection in ADO.NET code as in the following example:
Dim cn As New SQLConnection
' Set the connection string.
cn.ConnectionString = "SERVER=.\SQLEXPRESS;Integrated Security=TRUE"
Try
' Open the connection.
cn.Open()
Catch ex As Exception
' Display error messages.
MessageBox.Show("Connection error: :" & ex.ToString())
End Try
' Close the connection.
cn.Close()
The above code first creates an instance of the SqlConnection object and names the instance cn. Then the code sets the ConnectionString property to a value that will enable the application to connect to the local SQL Server Express instance. (I'll go into more detail about the connection string in a future Express Essentials column.) Next, the cn Connection object's Open method is used to actually establish the connection to SQL Server Express. Just in case an error occurs, I put the Open method within a Try loop. In the event of an error, the code in the Try loop's Catch section displays a message box showing the error text. Finally, the code closes the cn Connection object.
You can take this code and easily cut and paste it into future projects to quickly create a new database connection between VB Express and SQL Server Express.
