Article ID: 308656
Article Last Modified on 7/15/2004
APPLIES TO
- Microsoft .NET Framework 1.1 Service Pack 1
- Microsoft ADO.NET 1.1
- Microsoft Visual Basic .NET 2002 Standard Edition
- Microsoft Visual Basic .NET 2003 Standard Edition
This article was previously published under Q308656
For a Microsoft Visual Basic 6.0 version of this article, see 172403.
This article refers to the following Microsoft .NET Framework Class Library namespace:
- System.Data.SqlClient
IN THIS TASK
SUMMARY
This article describes how you can use ADO.NET to open a SQL Server database by using the SQL Server .NET data provider. ADO.NET gathers all of 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 using the Visual Studio .NET Server Explorer. The code samples in this article use the SqlConnection, SqlCommand, and SqlDataReader ADO.NET objects.
back to the top
Requirements
The following list outlines the required hardware, software, network infrastructure, and service packs that you need:
- Microsoft SQL Server
- Visual Basic .NET
NOTE: SQL Server and Visual Basic .NET must be installed and running on the same computer. In addition, the user must be able to use Windows Integrated Security to connect to SQL Server.
This article assumes that you are familiar with the following topics:
- ADO.NET concepts
- SQL Server concepts and Transact-SQL (T-SQL) syntax
- Northwind sample database
Create Visual Basic .NET Windows Application
- Start Visual Studio .NET, and create a new Visual Basic Windows Application project named SQLDataAccess.
Open Form1. In the first line of Form1.vb, add a reference to the ADO.NET namespace as follows:
Imports System.Data.SqlClient
- From the Windows Start menu, point to Programs, point to Microsoft SQL Server, and then click SQL Server Service Manager to ensure that the SQL Server service is running on your computer.
- Set the Server property to the name of your computer, and set the Services property to MSSQLServer.
- If the service is not running, click 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.
back to the top
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, which 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.
back to the top
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 is executed, 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, which 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, click Server Explorer.
- Right-click Data Connections, and then click Add connection.
- In the Data Link Properties dialog box, click localhost in the Select or enter a server name box.
- Click Windows NT Integrated Security to log on to the server.
- Click Select the database on the server, and then select Northwind database from the list.
- Click Test Connection to validate the connection, and then click OK.
- In the Server Explorer, click 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.
- Drag the FirstName and Last Name database fields from Employees table in Server Explorer, and drop these fields onto Form1. A SqlConnection and SqlDataAdapter object are created on the form.
- From the View menu, click Toolbox.
- On the Data tab, drag a DataSet object (DataSet1), and drop it onto the form.
- In the Add Dataset dialog box, click Untyped dataset, and then click OK.
Insert a line of code before the DataReader and Connection objects are closed in the Form1_Load event procedure. The end of the procedure should appear as follows:
SqlDataAdapter1.Fill(DataSet1, "Employees") myReader.Close() myConn.Close()
- On the Window Forms tab of the toolbox, drag a DataGrid control, and drop it onto Form1.
To bind the DataGrid to the DataSet that you created earlier, add the following code to the Form1_Load event procedure before the "myReader.close()" line of code:
DataGrid1.SetDataBinding(DataSet1, "Employees")
- Save and run the project.
REFERENCES
For more information about using ADO.NET, refer to the "Data" section of the "Visual Basic" topic in the Visual Studio .NET Help documentation.
back to the top
Keywords: kbhowtomaster kbsqlclient kbsystemdata KB308656