مبتدی: کار با System.Data.SqlClient - VB

[rajabpour]

System.Data.SqlClient
The System.Data.SqlClient namespace provides classes that are required to connect to SQL Server. Let's take a look at the classes provided by System.Data.SqlClient.
SqlConnection Class
The SqlConnection class represents a connection to SQL Server data source.
SqlCommand Class
The SqlCommand class represents a SQL statement or stored procedure for use in a database with SQL Server.
SqlDataReader
The SqlDataReader class creates a data reader to be used with SQL Server.
SqlDataAdapter
The SqlDataAdapter class represents a bridge between the dataset and the SQL Server database. It includes the Select, Insert, Delete and Update commands for loading and updating the data.
Code Samples
The following code samples will put the System.Data.SqlClient namespace and the classes within it to work.
Select Command
Open a new Web Forms page, add a Button to it and paste the following code. The following code will display data from Discounts table in Pubs sample database.



Imports System.Data.SqlClient

Dim myConnection As SqlConnection
Dim myCommand As SqlCommand

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As_
System.EventArgs) Handles MyBase.Load

End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As_
System.EventArgs) Handles Button1.Click
myConnection = New SqlConnection("server=localhost;uid=sa;pwd=;database=pubs")
'establishing connection. you need to provide password for sql server
Try
myConnection.Open()
'opening the connection
myCommand = New SqlCommand("Select * from discounts", myConnection)
Dim dr As SqlDataReader = myCommand.ExecuteReader()
While dr.Read()
'reading from the datareader
Response.Write(dr(0).ToString())
Response.Write(dr(1).ToString())
Response.Write(dr(2).ToString())
Response.Write(dr(3).ToString())
Response.Write(dr(4).ToString())
'displaying data from the table
End While
dr.Close()
myConnection.Close()
Catch
End Try
End Sub


Insert Command
Add a Button control to the Web Forms page and paste the following code. The following code will insert a record into the Jobs table in Pubs sample database.



Private Sub Button2_Click(ByVal sender As System.Object, ByVal e_
As System.EventArgs) Handles Button2.Click
Dim ra As Integer
myConnection = New SqlConnection("server=localhost;uid=sa;pwd=;database=pubs")
'you need to provide password for sql server
myConnection.Open()
myCommand = New SqlCommand("Insert into Jobs values 12,'IT Manager',100,300 ", myConnection)
ra= myCommand.ExecuteNonQuery()
'Since no value is returned we use ExecuteNonQuery
Response.Write("Records Inserted" & ra)
myConnection.Close()
End Sub


Delete Command
Add a Button to the Web Forms page and paste the following code. The following code will delete a record from the Authors table in Pubs sample database.



Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As_
System.EventArgs) Handles Button3.Click
Dim ss As Integer
myConnection = New SqlConnection("server=localhost;uid=sa;pwd=;database=pubs")
myConnection.Open()
myCommand = New SqlCommand("Delete from Authors where city='Oakland' ", myConnection)
ss = myCommand.ExecuteNonQuery()
Response.Write("Records affected" & ss)
myConnection.Close()
End Sub


Update Command
Add a Button to the Web Forms page and paste the following code. The following code will update a record in Authors table.



Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As_
System.EventArgs) Handles Button4.Click
Dim sss As Integer
myConnection = New SqlConnection("server=localhost;uid=sa;pwd=;database=pubs")
myConnection.Open()
myCommand = New SqlCommand("Update Authors Set city='Oakland' where city='San Jose' ",_ myConnection)
sss = myCommand.ExecuteNonQuery()
Response.Write("Records affected" & sss)
myConnection.Close()
End Sub


منبع : http://www.startvbdotnet.com