我们知道,ADO.NET的本质是为程序提供一个易于操作的类集,便于访问数据库。那就就按照整个访问过程,熟悉一下其中几个常用对像的使用方法,主要是代码展示。
首先使用sqlconnection对象指定数据源,并进行连接,见下面实例; Public Sub linking()
'需要设置的连接串
Dim cnStr As String = "Password=qq; Persist Security Info=true;User ID =sa ;" & "Initial Catalog=Norhwind;Data Source=CHQAO"
Dim cn As SqlConnection = New SqlConnection()
'设置对象cn的connectionstring属性以指定数据源
cn.ConnectionString = cnStr
'以上两行可以直接通过 connection 的构造函数来指定连接串
'Dim cn As SqlConnection = New SqlConnection(cnStr)
'在设置好属性后,就可以调用open方法来打开数据源了
cn.Open()
'在对数据源进行操作后,记得调用close方法,关闭与数据源的连接
cn.Close()
End Sub
接着利用sqlcommand对象设置其属性,指定要操作的SQL语句;
Public Sub operating1()
Dim cnStr As String = "Password=qq; Persist Security Info=true;User ID =sa ;" & "Initial Catalog=Norhwind;Data Source=CHQAO"
Dim cn As SqlConnection = New SqlConnection(cnStr)
cn.Open()
Dim sql As String = " SELECT * FROM Categories"
Dim cmd As SqlCommand = New SqlCommand()
'设置对像的connection属性,指定要对那个连接好的数据源进行操作,因为此时可能打开了过个连接
cmd.Connection = cn
cmd.CommandText = sql
'以上三行亦可以直接通过 command 构造函数直接指定 连接对象和命令文本
'Dim cmd As SqlCommand = New SqlCommand(cn, sql) 可以达到相同的结果
End Sub
随之,调用sqlcommand对像的方法,执行SQL语句,完成对数据源的操作,主要有ExecuteNonQuery()方法和ExecuteReader()方法;
Public Sub executing()
Dim cnStr As String = "Password=qq; Persist Security Info=true;User ID =sa ;" & "Initial Catalog=Norhwind;Data Source=CHQAO"
Dim cn As SqlConnection = New SqlConnection(cnStr)
cn.Open()
Dim sql As String = "UPDATE Customers SET Companyname= 'Jone' WHEREE Companyname='Bill'"
Dim cmd As SqlCommand = New SqlCommand(sql, cn)
'调用方法执行命令
cmd.ExecuteNonQuery()
'ExecuteNonQuery()方法一般用于执行 UPDATE DELETE INSERT 等非查询语句,在操作查询语句时一般执行ExecuteReader()方法,返回DataReader对象
End Sub
见DataReader对象和ExecuteReader()方法的具体搭配使用
Public Sub reading()
'DataReader 没有构造函数,所以不能直接实例化,可通过command对象的ExecuteRead()方法返回一个DataReader实例
Dim cnStr As String = "Password=qq; Persist Security Info=true;User ID =sa ;" & "Initial Catalog=Norhwind;Data Source=CHQAO"
Dim cn As SqlConnection = New SqlConnection(cnStr)
Dim sql As String = "SELECT Count(*) FROM Customers"
Dim cmd As SqlCommand = New SqlCommand(sql, cn)
'这里通过command对象的ExecuteRead()方法返回一个DataReader实例
Dim dr As SqlDataReader = cmd.ExecuteReader()
While (dr.Read())
Dim id As String = dr("CategoryID").ToString()
Dim name As String = dr("CategoryName").ToString()
Console.WriteLine("编号{0},姓名{}", id, name)
End While
dr.Close()
cn.Close()
End Sub
在对数据源访问(操作)方式上有有两种;以上的示例表示的一种:直接的访问;另一种就是接下来要展示的:使用数据集(DateSet),间接对数据源操作。数据集要想从数据源取得数据,必须借助DataAdapter对象,他是连接数据集和数据源的桥梁,这里需要注意它的四个比较重要的属性:
- selectcommand
- insertcommand
- deletecommand
- updatecommand
Public Sub DataFill()
Dim cnStr As String = "Password=qq; Persist Security Info=true;User ID =sa ;" & "Initial Catalog=Norhwind;Data Source=CHQAO"
Dim cn As SqlConnection = New SqlConnection(cnStr)
cn.Open()
Dim sql As String = "UPDATE Customers SET Companyname= 'Jone' WHEREE Companyname='Bill'"
Dim cmd As SqlCommand = New SqlCommand(sql, cn)
Dim myDataAdapter As SqlDataAdapter = New SqlDataAdapter()
Dim ds As DataSet = New DataSet()
myDataAdapter.SelectCommand = cmd
myDataAdapter.Fill(ds)
For Each dr As DataRow In ds.Tables(0).Rows
Console.WriteLine(dr("CatagoryName").ToString)
Next
'可将数据库表Catagory表的结构和数据添加到数据集 ds 中去,并创建一个名为Table的表,可进行一下操作:
Dim dt As DataTable = ds.Tables(0)
'还可以使用Fill()的另一个重载来指定在DataSet中要填充的表的名称
myDataAdapter.Fill(ds, "myTable ")
cn.Close()
End Sub
在获得数据源的数据后,对数据进行操作,并不等于对数据源的数据操作成功了,还得借助于对象DataAdapter的Update()方法:
Public Sub Updating()
Dim cnStr As String = "Password=qq; Persist Security Info=true;User ID =sa ;" & "Initial Catalog=Norhwind;Data Source=CHQAO"
Dim cn As SqlConnection = New SqlConnection(cnStr)
cn.Open()
Dim sql As String = "UPDATE Customers SET Companyname= 'Jone' WHEREE Companyname='Bill'"
Dim cmd As SqlCommand = New SqlCommand(sql, cn)
Dim myDataAdapter As SqlDataAdapter = New SqlDataAdapter()
Dim ds As DataSet = New DataSet()
myDataAdapter.SelectCommand = cmd
myDataAdapter.Fill(ds)
Dim myDataRow As DataRow = ds.Tables(0).NewRow
myDataRow("gategoryid") = "99"
myDataRow("gategoryname") = "drink"
ds.Tables(0).Rows.Add(myDataRow)
ds.Tables(0).Rows(1)(0) = "88"
ds.Tables(0).Rows(1)(1) = "meat"
ds.Tables(0).Rows(2).Delete()
'用更改后的数据集更新数据库
myDataAdapter.Update(ds)
cn.Close()
End Sub
本文旨在阐述几个常用对象的属性和方法的使用流程,有什么欠妥的地方,请留言,相互交流,共同进步!