- Connect SQL Server DataBase using ADO.Net in VB.Net
This section explains you that how to open a SQL Server database by using ADO.NET.by
using ADO.NET, retrieve all of the classes that are required for data handling.here,
we must use the System.Data.SqlClient namespace that contains collection of classes
which is used to access a SQL Server data source.likewise, we must use the System.Data.SqlClient
namespace that contains collection of classes which is used to access a OLE DB databases.
here,we can establish a connections through both programmatically and Visual Studio
.NET Server Explorer.for this purpose, the following code snippets use the SqlConnection,
SqlCommand, and SqlDataReader ADO.NET objects.
- Prerequisites
your computer must consists of the following
- Microsoft SQL Server
- Visual Basic .NET
- Create .NET Windows Application using Visual Basic
your computer must consists of the following
- Start Visual Studio .NET --> new Visual Basic Windows Application project provide
name as SQLDataAccess.
- in Form1.vb, includes ADO.NET namespace as follows:
- Imports System.Data.SqlClient
-
- Start --> Programs --> Microsoft SQL Server, and then click SQL Server Service Manager
to ensure that whether the SQL Server service is running on your computer or not.
- 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
in Form1.vb,
-
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
-
here,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 SqlConnection to Open SQL Server
- To set up the connection string of the SqlConnection object, add the following code
to the Form1_Load:
- strConn = New SqlConnection("Initial Catalog=DBName1;" & _
"Data Source=Server1;Integrated Security=SSPI;")
-
- To set up the Command object, which contains the SQL query, add the following code
to the Form1_Load event procedure:
-
strCmd = strConn.CreateCommand
strCmd.CommandText= "SELECT * FROM Employees"
myConn.Open()
- Use SqlDataReader to Retrieve Data from SQL Server
- Add the following code to the Form1_Load:
- strReader = strCmd.ExecuteReader()
-
- To display the query results, add the following code to the Form1_Load:
-
Do While strReader.Read()
strresults = strresults & strReader.GetString(0) & vbTab & _
strReader.GetString(1) & vbLf
Loop
MsgBox(strresults)
-
- finally, close the reader and connection
-
strReader.Close()
strConn.Close()
-
- save application and execute
- access 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
- open SQL Server via Server Explorer
- in Form1 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.
- then close the DataReader and Connection in the Form1_Load as follows:
-
strReader.Close()
strConn.Close()
-
- On the Window Forms tab of the toolbox, drag a DataGrid control, and drop it onto
Form1.
- To bind the Records on the DataGrid ,add the following
code to the Form1_Load before the "myReader.close()" :
-
DataGrid1.SetDataBinding(smplDataSet, "Employees")
-
- finally, save and run the application
a5257a4a-2a3f-4f5f-850b-a31ab0e51841|2|3.5