.net执行多重查询

最新推荐文章于 2024-09-27 00:02:43 发布

ITFLY8

最新推荐文章于 2024-09-27 00:02:43 发布

阅读量899

点赞数

分类专栏： ASP.NET 文章标签： .net integer sqlserver security cmd string

本文链接：https://blog.csdn.net/ITFLY8/article/details/532198

版权

ASP.NET 专栏收录该内容

212 篇文章 0 订阅

订阅专栏

出处：淘特网

一般我们在写程序的时候，经常会遇到多次查询的情况，看下面的例子：

' 建立与Sql数据库Northwind的连接
Dim connString As String = _
"Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial " _
& "Catalog=Northwind;Data Source=."
Dim cn As New OleDbConnection(connString)
cn.Open()
' 读取数据总个数
Dim cmd As New OleDbCommand("SELECT COUNT(*) FROM Customers", cn)
Dim totalRows As Long = CInt(cmd.ExecuteScalar())

' 读取所有记录
cmd = New OleDbCommand("SELECT * FROM Customers", cn)
Dim dr As OleDbDataReader = cmd.ExecuteReader()

Dim currRow As Integer
Do While dr.Read
     '更新标签
     currRow += 1
     Label1.Text = String.Format("{0}% complete", currRow * 100 / totalRows)
     Label1.Refresh()
     '
     ' ...

Loop
dr.Close()
cn.Close()
看到了吧，，如果我们要执行两个或以上的查询，我们就需要不断的复制cmd = New OleDbCommand("SELECT * FROM Customers", cn)之类的代码，幸运的是Sqlserver提供了对多重查询的支持，所以我们的代码可以修改如下：
' 建立与数据库Northwind的连接
Dim connString As String = _
"Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial " _
& "Catalog=Northwind;Data Source=."
Dim cn As New OleDbConnection(connString)
cn.Open()

' 准备两条查询命令
Dim cmd As New OleDbCommand("SELECT COUNT(*) FROM Customers;SELECT * FROM " _
& "Customers", cn)
Dim dr As OleDbDataReader = cmd.ExecuteReader()
' 第一条查询记录的结果仅为一列
dr.Read()
Dim totalRows As Long = CInt(dr(0))

' 第二条查询记录返回所有的记录
dr.NextResult()
Dim currRow As Integer
Do While dr.Read
    ' 更新标签
    currRow += 1
    Label1.Text = String.Format("{0}% complete", currRow * 100 / totalRows)
    Label1.Refresh()
    '
    ' ...
Loop
dr.Close()
cn.Close()