数据库-使用Command对象进行数据库查询
在ADO.NET中使用Command对象来与数据库进行查询.
一、创建Command对象
有三种方式创建Command对象:
1.7 第一种使用New关键字,调用Command对象的无参构造子,再进行属性设置
1.8 第二种也是使用New关键字,但使用的构造子提供参数来指定查询字符串和Connection对象
1.9 第三种通过Connection对象的CreateCommand方法
二、无返回查询
1 操作查询
不返回结果集的查询通常称为操作查询(action query)
2 操作查询有2种主要类型
2.1 使用数据操纵语言(DML)查询
也称为基于查询的更新,该类型会对数据库进行更新
例1
Update Customers SET CompanyName='NewCompanyName' WHERE CustomerID='ALFKI'
例2
INSERT INTO Customers (CustomerID,CompanyName) VALUES('NewID','NewCustomer')
例3
DELETE FROM Customers WHERE CustomerID='ALFKI'
2.2 数据定义语言(DDL)查询
此种类型常用于更改数据库结构
例1
CREATE TABLE table1 (Field1 int NOT NULL CONSTRAINT PK_Table1 PRIMARY KEY,
Field2 varchar(32))
例2
ALTER VIEW View1 AS SELECT Field1,Field2 FROM table1
例3
DROP PROCEDURE StoredProcedure1
3 VBNET中使用无返回查询代码演示
Dim cn As New OleDbConnection()
cn.ConnectionString=<略>
cn.Open()
Dim cmd As OleDbCommand=cn.CreateCommand()
cmd.CommandText="UPDATE Customers SET CompanyName='NewCompanyName' "&_
" WHERE CustomerID='ALFKI'"
cmd.ExecuteNonQuery()
4 关于"无返回查询"(操作查询)的讨论
4.1 操作查询是一种很有效的查询.只是它们不返回行.
4.2 执行操作查询可能会导致失败.例如:
CREATE TABLE table1 (Field1 int NOT NULL CONSTRAINT PK_Table1 PRIMARY KEY,
Field2 varchar(32))
1.1.7 该查询失败的原因:
l 已经存在同名的表格
l 查询语法错误
l 没有与数据库之间的开放连接
1.1.7 如果执行的查询没有发生错误,那么就意味着成功的创建了新的表格
4.3 由于操作查询是设计用来修改或删除现有行的,更新的操作也可能导致失败
UPDATE Customers SET CompanyName='NewCompanyName' WHERE CustomerID='ALFKI'
1.1.7 无法正确执行可能会因为该行已经被另外一个用户从表中删除.
数据库能够执行该查询,但是由于没有满足WHERE子句条件的行,所以查询不会作出修改行为.
对于数据库来说,这样的结果并不表示失败.
1. 如何辨别查询是否修改了行呢?
在Sql Server的查询分析器中执行,会得到信息:(1 rows affected)
Command对象则将查询所影响的行数作为ExecuteNonQuery方法的返回值.
2. 代码演示
Dim strConn As String=<连接字符串-略>
Dim cn As New OleDbConnection(strConn)
cn.Open()
Dim cmd As OleDbCommand=cn.CreateCommand()
cmd.CommandText="UPDATE Customers SET CompanyName='NewCompanyName' " &_
"WHERE CustomerID='ALFKI'"
Dim recAffected As Integer=cmd.ExecuteNonQuery()
If recAffected=1 Then
Console.WriteLine("Update Succeeded")
Else
Console.WriteLine("Update Failed")
End If
4.4 特别注意
如果执行的是非DML查询,那么ExecuteNonQuery方法返回值是-1.
三、用DataReader对象检查查询结果
1. 执行一个返回结果集的查询
Command对象提供ExecuteReader方法,返回值是DataReader对象,可以通过该对象检查查询结果
2. DataReader对象与.NET其他读取程序的对象(XMLReader/TextReader/StreamReader)使用起来没有区别
3. 代码演示
...(省略-打开Connection对象cn的过程)...
Dim cmd As New OleDbCommand("SELECT CustomerID,CompanyName FROM Customers",cn)
Dim rdr as OleDbDataReader=cmd.ExecuteReader()
While rdr.read()
Console.WriteLine(rdr("CustomerID") & "-" & rdr("CompanyName"))
End While
rdr.Close()
1.1 解释
1.1.1 关于DataReader对象的Read方法
只有在你调用Read方法之后,数据的第一行才可用,所以循环中要在显示之前就调用Read方法
调用一次Read方法,DataReader会自动移动到下一行.
Read方法返回值为布尔类型,表示下一行是否可用.
1.1.2 更快的获取结果集内容
DataReader对象的Item属性(这是个默认参数化属性)
Console.WriteLine(rdr("CustomerID") & "-" & rdr("CompanyName"))
这里就使用了Item属性,这样的代码效率较低.
1. 提高此处代码性能的办法
1.1.2.1.1 使用基于序号的查找
前面的代码段使用列名称查找Item属性,这将引发结果集中每一行都执行一个基于字符串的查找.
如果改为提供列的索引或序号,将避免对字符串的查找,从而提高性能.
此项技巧可应用于所以公开集合的对象.
DataReader对象可以根据列名称取得其索引(GetOrdinal方法).这样你可以使用它来在循环之前取得列名在集合中的索引值,从而加速查找性能.
l 修改后代码演示
…
Dim rdr As OleDbDataReader=cmd.ExecuteReader()
Dim customerIDOrd As Integer=rdr.GetOrdinal(“CustomerID”)
Dim CompanyNameOrd As Integer=rdr.GetOrdinal(“CompanyName”)
While rdr.Read()
Console.WriteLine(rdr(customerIDOrd) & “_” & rdr(companyNameOrd))
End While
rdr.Close()
1.1.2.1.2 使用适当的类型指定Get方法
DataReader对象提供了一系列的方法可以用来返回符合.NET类型的数据
(包括:String/Int32/decimal/Double等)
大多数集合对象都隐含使用了Item属性,其返回值是Object类型.显示其中所存储的值一般需要转换为字符串(ToString方法)
然而我们也可以使用DataReader对象提供的GetXXX方法来指定返回值类型.
应该保持”一直使用与结果集中列返回的数据相对应的类型指定的Get方法”的好习惯,即使我们需要在界面控件中使用字符串,也不例外
l 修改后代码演示
…
Dim rdr As OleDbDataReader=cmd.ExecuteReader()
Dim CustomerIDOrd As Integer=rdr.GetOrdinal(“CustomerID”)
Dim CompanyNameOrd As Integer=rdr.GetOrdinal(“CompantName”)
While rdr.Read()
Console.WriteLine(rdr.GetString(CustomerIDOrd) & “_” &
rdr.GetString(CompanyNameOrd))
End While
1.1.3 获取多个结果-批查询
某些数据库(比如SQL SERVER)支持执行返回多个结果的批查询.
假设有如下查询:
SELECT CustomerID,CompanyName,ContactName,Phone FROM Customers;
SELECT OrderID,CustomerID,EmployeeID,OrderData FROM Orders;
SELECT OrderID,ProcedureID,Quantity,UnitPrice FROM [Order Details]
使用前面的方法,用DataReader对象在循环中Read(),只能在批查询中循环第一个查询的结果.
解决这个问题需要使用DataReader对象提供的NextResult方法,该方法可以使你移动到下一个返回行的查询结果上去.另外,该方法返回一个布尔类型,但是它不可以向Read方法那样在循环之前调用.
l 使用NextResult()的代码演示
…
cn.Open()
Dim ssql as string=” SELECT CustomerID,CompanyName,ContactName,Phone FROM Customers;
SELECT OrderID,CustomerID,EmployeeID,OrderData FROM Orders;
SELECT OrderID,ProcedureID,Quantity,UnitPrice FROM [Order Details]”
Dim cmd as new OleDbCommand(ssql,cn)
Dim rdr as OleDbDataReader=cmd.ExecuteReader()
Do
Do While rdr.Read()
Console.WriteLine(rdr(0) & “_” & rdr(1))
Loop
Console.WriteLine()
Loop While rdr.NextResult()
1.1.4 执行成批的操作查询
使用ADO和SQL SERVER来获取由存储过程生成的结果集经常会存在以下问题.
调用使用SQL Server OLE DB提供者的Sql Server存储过程,并且此存储过程在返回行的查询之前执行了一次查询,那么Recordset会被标记为”关闭”,从而导致返回的记录集不包含返回行的查询结果.
这个问题是有设计造成的.
解决办法有两个:
1. 使用NextRecordset方法
DataReader对象的这个方法能使(当前光标)移动到下一查询的结果上去.
因为:关闭的Recordset与查询操作相对应.更严格的说它是与查询所返回的信息性消息”影响X行”相对应的
2. 在存储过程中添加SET NOCOUNT ON语句
这个语句的作用是取消”影响N行”消息,从而能够是ADO(光标)立即移动到第一个返回行的查询结果上.
l 该操作不仅存在于存储过程.
l 通过执行批查询,也可以创建类似的情况
l 代码演示(ADO2.0与VB经典代码)
Dim cn as ADODB.Connection,rs as ADODB.Recordset
Dim strConn as string, strSql as string
Dim RecAffected as Integer
strConn=”Provider=SQLOLEDB; Data Source={local}"NetSDK;
Initial Catalog=Northwind; Trusted_Connection=Yes;”
Set cn=New ADODB.Connection
Cn.Open strConn
strSql=”insert into Customers …;”&_
“select CustomerID,CompanyName from Customer where …;”&_
“update Customers set CompanyName=… where…;”&_
“select CustomerID,CompanyName from Customer where …”
Set rs=cn.Execute(strSql,recAffected,adCmdText)
Do Until rs Is Nothing
Debug.Print “rs.State=” & rs.State & vbTab &_
“records affected=” & recAffected
Set rs=rs.NextRecordset(recAffected)
Loop
l 代码运行分析
循环中,初始状态下,recordset是关闭的,并且recAffected=1.原因是这个结果对应insert查询,该查询不返回行.而是对数据库记录的修改
一旦调用了NextRecordset方法后,recordset的状态就变成开放的了.并且其中包含了第一个select查询的结果,由于select查询不修改任何记录,所以第一个select查询之后,recAffected=-1
然后第二次调用NextRecordset方法,它将返回一个关闭的数据集,recAffected保存了Update查询影响的行数.
然后在第三次调用NextRecordset方法是返回的是第二个select查询的结果,recAffected有等于-1了.
最后还要调用一次NextRecordset(),此时返回一个设置为Nothing的recordset,表明没有其他的结果需要处理.
3. ADONET处理上例的批查询
ADONET处理批查询的方式与ADO不同.
(为了简化批查询处理的过程)DataReader会自动移动到第一个返回行的查询结果处(第一个select查询返回行)
但是,这个改进仍然有些顾此失彼的地方.
DataReader对象没有提供能够检查每个单独的操作查询所影响行数的方法.
DataReader对象的RecordsAffected属性保存的是运行总计.
l 代码演示
Dim strConn as string=”Provider=SQLOLEDB; Data Source={local}"NetSDK;
Initial Catalog=Northwind; Trusted_Connection=Yes;”
Dim cn as new OleDbConnection(strConn)
Cn.Open()
Dim strSql as string=”insert into Customers …;”&_
“select CustomerID,CompanyName from Customer where …;”&_
“update Customers set CompanyName=… where…;”&_
“select CustomerID,CompanyName from Customer where …”
Dim cmd as new OleDbCommand(strSql,cn)
Dim rdr as OleDbDataReader=cmd.ExecuteReader()
Do
Console.WriteLine(“影响行数=” & rdr.RecordsAffected)
Do While rdr.Read()
Console.WriteLine(vbTab & rdr.GetName(0) & “ - ” & rdr.GetValue(0))
Loop
Loop While rdr.NextResult()
l 代码分析
ADONET的代码和ADO代码很相似.但是,结果略有不同.
当通过Command对象取得DataReader对象之后,DataReader对象会立即返回第一个select查询的结果
第一次调用NextResult方法时,它立即移动到第二个select查询的结果
第二次调用NextResult()时,直接返回false,从而跳出循环.
在性能上,DataReader对象的RecordsAffected属性直接返回所以操作查询影响的记录总和.
实际运行中,当ExecuteReader方法返回DataReader对象时,RecordsAffected属性=1;
当调用NextResult方法后RecordsAffected属性=2
l 切记
DML操作查询(CREATE PROCEDURE 和 DROP TABLE)返回行数总是-1
1.1.5 关闭DataReader对象
在ADONET对象模型中,要记住尽可能快速的关闭DataReader对象,这非常重要.
目前为止,一个带着开发的DataReader的Connection对象仍被认为是锁定的.
如果在关闭DataReader对象之前,试图打开第二个,会导致异常.”需要一个开放并可用的连接”
在RDO中存在相同的限制.
在ADO中(Sql Server)打开两个流水游标则很正常.
此类情形,可以归结为:试图使用一个忙连接.
微软数据访问技术的每一代对于此情况处理都不相同.
l VBSQL生成错误
l DAO/Jet创建新连接
l RDO生成错误
l ADO创建新连接
l ADO.NET生成错误
1. 使用DataReader对象是基于性能方面的原因,你应该在发出查询之后尽快地将结果拉下来
2. 开发的DataReader对象会锁定Connection对象
3. 如果需要在各查询的结果之间前后移动,应该使用DataSet对象;
或者考虑将查询结果存储在某类业务对象之中.
1.1.6 执行返回单值的查询
查询举例:
Select COUNT(*) from Customers
Select CompanyName from Customers where CustomerID=’ALFKI’
如上查询,用DataReader或DataSet都有点大材小用.
Command对象提供了一个方法(ExecuteScalar)专门获取单值查询.其返回值类型为Object
l 代码演示
Dim strConn as string=”Provider=SQLOLEDB; Data Source={local}"NetSDK;
Initial Catalog=Northwind; Trusted_Connection=Yes;”
Dim cn as new OleDbConnection(strConn)
cn.Open()
Dim cmd as OleDbCommand=cn.CreateCommand()
cmd.CommandText=”select COUNT(*) from Customers”
Dim customerCount as Integer=Cint(cmd.ExecuteScalar())
cmd.CommandText=”select CompanyName from Customers where CustomerID=’ALFKI’”
Dim companyName1 as string Convert.toString(cmd.ExecuteScalar())
1.1.7 参数化查询
假设:建立一个应用程序,用户可检查客户端放置的顺序.
如果你有20个客户端,不需要编写20个独立的函数来返回客户端的顺序,只要建立参数化查询.并使它可接收有关客户的信息并为该客户返回其顺序即可.同样,还可以建立接收参数的查询,
SELECT OrderID,CustomerID,EmployeeID,OrderDate FROM Orders WHERE CustomerID=?
l 其中问号?是一个参数标记,(所谓参数标记是指一种在查询中指示参数的标准方式)
l SQL SERVER.NET数据提供者不支持通用的参数标记”?”,它需要的是带有@前缀的命名参数.
SELECT OrderID,CustomerID,EmployeeID,OrderDate FROM Orders WHERE CustomerID=@CustomerID
1.1.7.1 ADO.NET的Parameter对象
创建ADO.NET的Parameter对象即可存储参数信息的一种结构.
一般的,创建Command对象,向其Parameters集合属性中添加一个Parameter,并给它提供值,最后执行Command对象,以便获取特定客户的顺序信息.
l 代码演示
Dim strConn,strSql As string
strConn=”Provider=SQLOLEDB; Data Source=(local)"NetSDK; Initial Catalog=Northwind;”&_
“ Trusted_Connection=Yes;”
Dim cn As New OleDbConnection(strConn)
Cn.Open()
strSql=”Select OrderID,CustomerID,EmployeeID,OrderData from Orders where CustomerID=?”
Dim cmd As New OleDbCommand(strSql,cn)
cmd.Parameters.Add(“@CustomerID”, OleDbType.Wchar,5)
cmd.Parameters(0).Value=”ALFKI”
Dim rdr As OleDbDataReader=cmd.ExecuteReader()
l 总结
使用参数化查询可以大幅度简化编程.
更改参数的值要比通过编程连接查询字符串要容易得多.特别是,在为某参数复制的时候,无需再考虑去删除它的问题.
1.1.8 调用存储过程
假设有一个存储过程,它能返回一行数据.如下(是Sql Server中的)
CREATE PROCEDURE GetCustomer(@CustomerID nchar(5) AS
SELECT CustomerID,CompanyName,ContactName,ContactTitle
FROM Customers WHERE CustomerID=@CustomerID
RETURN
1.1.8.1 从Command对象调用存储过程有两个方法
1.1.8.1.1 方法之一:使用Command对象的CommandType属性
CommandType属性是CommandType枚举类型的值.
取值如下:Text、TableDirect、StoredProcedure。
默认值Text
将CommandType属性设置为StoredPriocedure,表示正在调用一个存储过程.(Command对象将CommandText属性的值和Parameters集合中的信息相结合,生成调用存储过程的语法)
l 示例代码
…
Dim cn As New OleDBConnection(strConn)
Cn.Open()
Dim cmd as OleDbCommand=cn.CreateCommand()
With cmd
.CommandText=”GetCustomer”
.CommandType=CommandType.StoredProcedure
.Parameters.Add(“@CustomerID”,OleDbType.Wchar.5)
.Parameters(0).Value=”ALFKI”
End With
Dim rdr As OleDbDataReader=cmd.ExecuteReader()
If rdr.Read() then
Cosole.WriteLine(rdr(“CompanyName”))
Else
Console.WriteLine(“No Customer Found.”)
endIF
rdr.Close()
cn.Close()
l 总结
这种语法是调用存储过程的标准方法:
{?=CALL MyStoredProc(?,?,?)}
或者不准备使用返回值(就是?)时,形式如下:
{CALL GetCustomer(?)}
比较推荐这种语法,而不是向上面代码中那样依赖与CommandType属性.
可能出现问题的地方是:如果你的查询中一个名称包含了特殊字符(比如空格),那么将CommandType属性设置为TableDirect或StoredProcedure,对象名称中的分隔符无法识别.但是,SqlCommand对象可以准确的确定对象名称的间隔.
1.1.8.1.2 第二种方法(也是推荐的方法)不要依赖CommandType属性,尽量在CommandText属性中为查询使用恰当的语法.
l 示例代码
Dim cmd As New OleDbDataAdapter()
cmd.CommandText=”{CALL GetCustomer(?)}”
cmd.CommandType=CommandType.Text
l 总结
SQL Server中在Query Analyzer工具中使用EXEC语法执行存储过程,也可以在Command对象中使用;但是,并非所有的数据库都支持该语法.
1.1.9 从输出参数中获取数据
并非所有的存储过程都通过结果集返回信息
许多过程是通过输出参数来返回信息的
l 举例
CREATE PROCEDURE GetCustomer(@CustomerID nchar(5),
@CompanyName nvarchar(40) OUTPUT,
@ContactName nvarchar(40) OUTPUT,
@ContactTitle nvarchar(30) OUTPUT) AS
SELECT @CompanyName=CompanyName,@ContactName=ContactName,
@ContactTitle=ContactTitle
FROM Customers WHERE CustomerID=@CustomerID
IF @@ROWCOUNT=1
RETURN 0
ELSE
RETURN -1
1.1.9.1 如何利用Command对象从输出参数中获取数据?
Parameter对象有一个Direction属性,能接收ParameterDirection枚举类型的值
(ParameterDirection枚举值可能是ReturnValue,Input,InputOutput,Output)默认是Input
要从存储过程中获取信息,需要对Parameter参数的Direction属性进行设置
l 示例代码
...
Dim cn As New OleDbConnection(strConn)
cn.Open()
Dim cmd As OleDbCommand=cn.CreateCommand()
With cmd
.CommandText=”{?=CALL GetCustomer(?,?,?,?)}”
.Parameters.Add(“@RetVal”,OleDbType.Integer)
.Parameters.Add(“@CustomerID”,OleDbType.WChar,5)
.Parameters.Add(“@CompanyName”,OleDbType.VarWChar,40)
.Parameters.Add(“@ContactName”,OleDbType,VarWChar,40)
.Parameters.Add(“@ContactTitle”,OleDbType.VarWChar,30)
.Parameters(“@ContactTitle”).Direction=ParameterDirection.Output
.Parameters(“@RetVal”).Direction=ParameterDirection.ReturnValue
.Parameters(“@CustomerID”).Value=”ALFKI”
.Parameters(“@CompanyName”).Direction=ParameterDirection.Output
.Parameters(“@ContactName”).Direction=ParameterDirection.Output
.ExecuteNonQuery()
If Convert.ToInt32(.Parameters(“@RetVal”).Value=0) Then
Console.WriteLine(“找到”)
Else
Console.WriteLine(“没有记录”)
End If
End With
1.1.10 在Transaction中执行查询
Command对象有Transaction属性,要在Transaction中执行Command需要设置该属性.
Command对象调用BeginTransaction方法创建一个Transaction对象.
l 示例代码
...
cn.Open()
Dim txn As OleDbTransaction=cn.BeiginTransaction()
Dim strSql As String=”insert into Customers (...) VALUES (...)”
Dim cmd As New OleDbCommand(strSql,cn,txn)
Dim recAffected As Integer=cmd.ExecuteNonQuery()
If recAffected=1 Then
Console.WriteLine()
txn.Commit()
Else
Console.WriteLine()
txn.Rollback()
End If
