数据库-使用Command对象进行数据库查询

最新推荐文章于 2022-10-28 14:44:39 发布

weixin_30809173

最新推荐文章于 2022-10-28 14:44:39 发布

阅读量570

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/lizunicon/archive/2008/09/16/1291828.html

版权

在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 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()

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

转载于:https://www.cnblogs.com/lizunicon/archive/2008/09/16/1291828.html

weixin_30809173

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
数据库-使用Command对象进行数据库查询

在ADO.NET中使用Command对象来与数据库进行查询.一、创建Command对象有三种方式创建Command对象:1.7 第一种使用New关键字,调用Command对象的无参构造子,再进行属性设置1.8 第二种也是使用New关键字,但使用的构造子提供参数来指定查询字符串和Connection对象1.9 第三种通过Co...
复制链接

扫一扫