Open SQL Server database by using SQL Server .NET Data Provider with Visual Basic .NET

2025-07-16

This article provides information about how to open Microsoft SQL Server databases by using SQL Server .NET Data Provider together with Visual Basic .NET. You can use ADO.NET to open a SQL Server database by using the SQL Server .NET Data Provider. ADO.NET gathers all the classes that are required for data handling.

The System.Data.SqlClient namespace describes a collection of classes that are used to programmatically access a SQL Server data source. You can access ADO classes through the System.Data.OleDb namespace to provide support for OLE DB databases.

In this article, connections are set up both programmatically and by using the Visual Studio .NET Server Explorer. The code samples in this article use the SqlConnection, SqlCommand, and SqlDataReader ADO.NET objects.

Applies to: Visual Basic .NET

Original KB number: 308656

Prerequisites

Microsoft SQL Server
Visual Basic .NET

Note

SQL Server and Visual Basic .NET must be installed and running on the same computer. Additionally, the user must be able to use Windows Integrated Security to connect to SQL Server.

This article assumes that you're familiar with the following topics:

ADO.NET concepts
SQL Server concepts and Transact-SQL (T-SQL) syntax
The Northwind sample database

Create Visual Basic .NET Windows application

In Visual Studio .NET, create a Visual Basic Windows Application project, and name it SQLDataAccess.
Open Form1.
In the first line of Form1.vb, add a reference to the ADO.NET namespace, as follows:
```
Imports System.Data.SqlClient
```
To make sure that the SQL Server service is running on your computer, select Start, point to Programs, point to Microsoft SQL Server, and then select SQL Server Service Manager.
Set the Server property to the name of your computer, and then set the Services property to MSSQLServer.
If the service isn't running, select Start.
Close the SQL Server Service Manager dialog box.

Create ADO.NET objects

Modify the Form1 class as follows:

Public Class Form1
    Inherits System.Windows.Forms.Form
    'Create ADO.NET objects.
    Private myConn As SqlConnection
    Private myCmd As SqlCommand
    Private myReader As SqlDataReader
    Private results As String

The SqlConnection object establishes a database connection, the SqlCommand object runs a query against the database, and the SqlDataReader object retrieves the results of the query.

Use the SqlConnection object to open SQL Server connection

To set up the connection string of the SqlConnection object, add the following code to the Form1_Load event procedure:

 'Create a Connection object.
 myConn = New SqlConnection("Initial Catalog=Northwind;" & _
 "Data Source=localhost;Integrated Security=SSPI;")

To set up the Command object that contains the SQL query, add the following code to the Form1_Load event procedure:

'Create a Command object.
myCmd = myConn.CreateCommand
myCmd.CommandText = "SELECT FirstName, LastName FROM Employees"

'Open the connection.
myConn.Open()

SqlConnection uses your Windows logon details to connect to the Northwind database on your computer.

Use the SqlDataReader object to retrieve data from SQL Server

Add the following code to the Form1_Load event procedure:
```
myReader = myCmd.ExecuteReader()
```

When the myCmd.ExecuteReader method runs, SqlCommand retrieves two fields from the Employees table and creates a SqlDataReader object.

To display the query results, add the following code to the Form1_Load event procedure:

'Concatenate the query result into a string.
Do While myReader.Read()
    results = results & myReader.GetString(0) & vbTab & _
    myReader.GetString(1) & vbLf
Loop
'Display results.
MsgBox(results)

The myReader.Read method returns a boolean value that indicates whether there are more records to be read. The results of the SQL query are displayed in a message box.

To close the SqlDataReader and SqlConnection objects, add the following code to the Form1_Load event procedure:
```
'Close the reader and the database connection.
 myReader.Close()
 myConn.Close()
```
Save and run the project.

View database in Server Explorer

On the View menu, select Server Explorer.
Right-click Data Connections, and then select Add connection.
In the Data Link Properties dialog box, select localhost in the Select or enter a server name box.
To log on to the server, select Windows NT Integrated Security.
Select Select the database on the server, and then select the Northwind database in the list.
To validate the connection, select Test Connection, and then select OK.
In the Server Explorer, select to expand the Data Connections tree so that the Employees table node expands. The properties of individual fields appear in the Properties window.

Use Server Explorer to open SQL Server connection

View Form1 in Design view.
From the Employees table in Server Explorer, drag and drop the FirstName and LastName database fields onto Form1. This action creates SqlConnection and SqlDataAdapter objects on the form.
On the View menu, select Toolbox.
On the Data tab, drag and drop a DataSet object (DataSet1) onto the form.
In the Add Dataset dialog box, select Untyped dataset, and then select OK.
In the Form1_Load event procedure, insert a line of code before the DataReader and Connection objects are closed to end the procedure, as follows:
```
SqlDataAdapter1.Fill(DataSet1, "Employees")
myReader.Close()
myConn.Close()
```
On the Window Forms tab of the toolbox, drag and drop a DataGrid control onto Form1.
In the Form1_Load event procedure, add the following code before the myReader.close() line to bind the DataGrid to the DataSet object that you created earlier:
```
DataGrid1.SetDataBinding(DataSet1, "Employees")
```
Save and run the project.

Share via