Now that you know how to code a connection between Visual Studio 2005 Express and SQL Server 2005 Express and build and use ADO.NET connection strings to connect to local and remote databases, you can use a Visual Basic (VB) connection to retrieve data from SQL Server Express. (For links to my columns about coded connections and connection strings, see the Related Articles list at the end of this column.) The VB subroutine below illustrates how to open a connection to SQL Server Express, use the connection to retrieve selected information from a database, and display the information in a list box.
Imports System.Data.SqlClient
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
Dim cn As New SqlConnection()
cn.ConnectionString = "SERVER=.\SQLEXPRESS; " _
& "Integrated Security=True;DATABASE=AdventureWorks"
Dim cmd As New SqlCommand _
("SELECT ProductNumber, Name FROM Production.Product ", cn)
cmd.CommandType = CommandType.Text
Try
cn.Open()
Dim rdr As SqlDataReader
rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
' Read and add column values to a list box
ListBox1.Items.Clear()
Do While rdr.Read()
ListBox1.Items.Add(rdr("ProductNumber") & vbTab & _
rdr.Item("Name"))
Loop
rdr.Close()
Catch ex As Exception
MessageBox.Show("Connection Error: " & ex.ToString())
Finally
cn.Close()
End Try
End Sub
End Class
Near the beginning of the subroutine, you can see the code that creates a SqlConnection object named cn and a SqlCommand object named cmd. The SqlCommand object's constructor, which actually creates the instance of the object, sets the Command property in the Dim statement to a SQL SELECT statement that retrieves the value of the ProductNumber and Name columns from the AdventureWorks database's Production.Product table.
The code in the Try block opens the SqlConnection object and declares a new SqlDataReader object named rdr. The SqlCommand object's ExecuteReader method instantiates the SqlDataReader object and uses the CommandBehavior.CloseConnection flag to automatically close the connection when the SqlDataReader is closed.
The While loop in the Try block reads the forward-only data stream returned by the SqlDataReader object. Within the While loop, the values from the two columns returned by the result set, ProductNumber and Name, are added to a list box named ListBox1. To use this functionality, you need to know that the columns must be accessed in the order in which they appear in the result set. After all the results have been retrieved, the While loop is terminated.
In the event of an error, the code in the Catch block is executed and the MessageBox.Show method displays an exception message in a message box.
As I've mentioned in the past, a big advantage of coded connections is that they are portable between projects. As you can see here, using them is quite straightforward, but you do need a level of familiarity with the ADO.NET objects. Another nice thing about code-based connections is that they also work in Visual Studio 2008.
The new Visual Studio 2008 Express line of products is now available, and SQL Server connectivity has been enhanced in those products. So, in an upcoming Express Essentials column, I'll illustrate how to connect Visual Studio 2008 Express to SQL Server Express.
Related Articles:
"Connecting Visual Studio 2005 to SQL Server Express,"
"Connecting Visual Studio 2005 to SQL Server Express, Part 2,"
"The Code Connection Between Visual Studio 2005 and SQL Server Express,"
