数据库-ADONET-排序、搜索和筛选

 

排序、搜索和筛选

如何使用一个值或一组值来查找DataTable中的特定行？

如何使用筛选程序是只有满足条件的行可见？

如何控制要访问或者显示的行的排序顺序？

可以使用：

DataRowCollection类的Find方法，DataTable类是Select方法，DataView对象和DataRowView对象。

1           使用DataTable对象的搜索和筛选功能

DataTable对象公开了两个方法，它们可以用来根据搜索标准查找数据。

Find方法，根据主键值来查找数据。

Select方法，类似于筛选程序，根据更灵活的搜索标准返回多行数据。

1.1         根据主键值查找行

查询数据库以获取信息时，通常都会希望，根据其主键列的值，来获取特定行的数据。

可以使用以下查询：

Select CustomerID,CompanyName,ContactName,Phone from Customers where CustomerID=”ALFKI”

还可以根据行的主键值，在DataTable中查找DataRow。

尽管Find方法是为DataTable对象设计的，但是，它实际上是由DataRowCollection类公开的。

Find方法接受一个包含要查询的行的主键值作为参数，因为主键值是惟一的，所以Find方法只能返回一行DataRow。

Dim strConn, strSql as string

strConn=”Provider=SQLOLEDB;Data Source=(local)"NetSDK; Initial Catalog=Northwind;
Trusted_Connection=Yes;”

strSql=”select CustomerID,CompanyName,ContactName,Phone from Customers”

Dim da as New Oledb.OledbDataAdapter(strSql,strConn)

Dim tbl as new DataTable

Da.fill(tbl)

Tbl.PrimaryKey=new DataColumn(){tbl.Columns(“CustomerID”)}

Dim row as DataRow=tbl.Rows.Find(“ALFKI”)

If row Is Nothing then

     Console.WriteLine(“Row Not Found.”)

Else

     Console.WriteLine(Row(“CompanyName”))

End IF

注意：

从技术角度来说，DataTable可以包含拥有相同主键值的多个行。

如果，将DataSet的EnforceConstraints属性设置为假False，那么即使违反了主键约束，DataTable也不会抛出异常。

Find方法将返回找到的所有目标主键值的行。

Find方法，针对DataTable的主键包含多个DataColumn对象的场景，进行了重载。（组合主键）

例如：对于【Order Details】表，主键是OrderID/ProductID，查找演示如下：

Dim strConn,strSql as string

strConn=”...”

strSql=”select OrderID,ProductID,Quantity,UnitPrice from [Order Details]”

dim da as New OleDbDataAdapter(strSql,strConn)

dim tbl as new DataTable()

da.Fill(tbl)

tbl.PrimaryKey=new DataColumn(){tbl.Column(“OrderID”),tbl.Columns(“ProductID”)}

Dim objCriteria as new Object(){10643,28}

Dim row as DataRow=tbl.Rows.Find(objCriteria)

If row Is Nothing Then

     Console.WriteLine(“Row Not Found”)

Else

     Console.WriteLine(“row(“Quantity”) & “ – “ & row(“UnitPrice”))

End If

1.2         执行更动态的搜索

根据主键值查找行，是很高效的。

但是，并非所有搜索都是如此直观。

如果希望查找“位于美国而不在西雅图市的客户”，该怎么办？可以使用Where子句向查询添加一个条件，如下：

Select CustomerID,CompanyName,ContactName,Phone,City,Country from Customers
where Country=”USA” AND City <> “Seattle”

在ADONET中，还可以用DataTable对象的Select方法来实现类似的条件查询。

Dim strConn as string=”Provider=SQLOLEDB;Data Source=(local)"NETSDK;Initial Catalog=Northwind;
Trusted_Connection=Yes;”

Dim strSql as String=”select CustomerID,CompanyName,ContactName,Phone,City,Country
from Customers”

Dim da as New OleDbDataAdapter(strSql,strConn)

Dim tbl as new DataTable()

Da.Fill(tbl)

Dim arows as DataRow()

Dim row as DataRow

Arows=tbl.select(“Country=’USA’ AND City <> ‘Seattle”)

For Each row in arows

     Console.WriteLine(row(“CompanyName”) & “ – “ & row(“City”) & “ – “ & row(“Country”))

Next row

1.3         执行通配符搜索

ADONET允许使用通配符进行搜索。

在SQL语句中查询CustomerID列是以字母A开始的客户，

Select CustomerID,CompanyName from Customers where CustomerID LIKE ‘A%’

可以在字符串的开始或者结尾使用“%或*”作为通配符。

strFilter=”State LIKE ‘New %’” – 将返回在以New开头地区的客户

strFilter=”State LIKE ‘% Dakota ‘” – 将返回北Dakota和南Dakota的客户

ADONET不能使用单字母通配符（如：“？”、“_”等）

1.4         使用分隔符

在搜索条件中，不能简单的使用“单引号”括住一个字符串，就拉倒了。

如果是动态构建搜索条件，其中可能会包含“单引号”，那么搜索条件就必须重复它（就是替换成’’），这可以用String类的Replace方法处理。

strCriteria=”LastName=’” & strLastName.Replace(“ ’ ”, ” ’’ ”) & “’”

以上是对于“字符串”的处理，那么怎么分隔日期？

使用pound号（就是#号）括住日期。

strCriteria=”OrderData>= #01/02/2002# AND OrderDate < #02/02/2002#”

在某些情况下，需要在搜索条件中分隔列名，原因可能是：

l 列名中有空格或其他非字母字符

l 列名是保留单词，如LIKE或SUM等等

在ADONET中，可以使用方括号来作为“列分隔符”。

strCriteria=”[Space In Name]=3”

如果在列名中有列分隔符，又怎么办？

可以在条件字符串的结束分隔符“）”之前用转移字符“"”。比如：列名为Bad]Column[Name，可以这样写：strCriteria=”[Bad"]Column[Name]=5”

需要特别注意的是，对于C#来说，由于“"”是转义字符，所以实际的字符串应该这样写：

“[Bad""]Column[Name]=5”

1.5         使用附加Select方法

Select方法有一些重载方法，其中的参数包括：

l 只提供搜索字符串

l 包括排序顺序以及控制要搜索的行的状态参数（例如：仅搜索添加的行或仅搜索被修改的行）

1.5.1   包括“排序顺序”

可以通过使用Select重载方法之一，来空值返回的DataRow对象的顺序。

在Sql查询中，可以使用ORDER BY子句，来控制有查询返回的数据的排序顺序。

例如：

Select CustomerID,CompanyName,Phone,City from Customers ORDER BY City

如果是City按照降序排序，则改为ORDER BY City DESC

DataTable的Select方法，使用示例如下：

Dim strConn,strSql as String

strConn=”Provider=SQLOLEDB;Data Source=...;Initial Catalog=Northwind;
Trusted_Connection=Yes;”

strSql=”select CustomerID,CompanyName,Phone,City,Country from Customers”

Dim da as New Oledb.OleDBDataAdapter(strSql,strConn)

Dim tbl as New DataTable()

Da.Fill(tbl)

Dim strCriteria As String=”Country=’USA’ AND City <> ‘Seattle’”

Dim strSortOrder as string=”City DESC”

Dim arows as DataRow()=tbl.select(strCriteria,strSortOrder)

Dim row as DataRow

For each row in arows

    Console.WriteLine(row(“CompanyName”) & “ – “ & row(“City”) & “ – “ & row(“Country”))

Next Row

1.5.2   指定要搜索的行的状态RowState

使用Select方法的重载版本之一，指定DataViewRowState枚举值作为参数，可以只对特殊状态的行进行搜索。

举例：希望只检查DataTable中被修改的和被删除的行，使用DataViewRowState枚举类型中的ModifiedOriginal和Deleted，将筛选和排序参数使用空串，具体如下：

Dim dvrs as DataViewRowState

Dvrs=DataViewRowState.ModifiedOriginal Or DataViewRowState.Deleted

Dim arows() as DataRow=tbl.Select(“”,””,dvrs)

Dim row as DataRow

For Each row in arows

    Console.WriteLine(row(“CompanyName”,DataRowVersion.Original))

Next Row

这里要注意的是，被删除的行只能检查行的“原始值Original”。

2          DataView对象

DataTable的Select方法，它有两方面的局限性。

第一：因为它接受动态的搜索条件，所以效率不高；

第二：Windows和WEB窗体都不支持“绑定”到Select方法的返回值（DataRow数组）

ADONET针对此问题，提供的解决方案是“DataView”对象。

ADONET体系中DataTable对象基本等价于数据库中的表，所以可以假设DataView对象类似于视图。

2.1         DataView对象从DataTable中返回数据

DataView对象不维护自己的数据副本。

当通过DataView访问数据时，DataView将返回存储在相应DataTable中的数据。

类似的，数据库中的视图也是一样，查询一个视图时，数据库会从视图所引用的一个或多个表中返回数据。

2.2         DataView对象不是SQL查询

数据库的视图实际上就是一个查询。

在数据库中创建视图时，要提供数据库将要执行的，返回视图的数据查询：

CREATE VIEW ViewCustomersAndOrders AS

            SELECT c.CustomerID,c.CompanyName,c.ContactName,c.Phone,o.OrderID,o.EmployeeID,
            o.OrderDate

            FROM Customers c,Orders o WHERE c.CustomerID=o.CustomerID

而，ADONET的DataView对象可以，筛选、排序、搜索DataTable的内容，但——它们并非SQL查询。

l 不能用DataView在两个DataTable间联结Join数据。

l 不能用DataView查看DataTable中的部分列。

DataView确实支持基于动态标准的行筛选，但是，它们只能访问一个DataTable，而且，DataTable中所有列都可通过DataView得到。

用DataRelation来模拟连接

可以用DataRelation配合一个基于表达式的列模拟联结。

比如说：有一个客户DataTable和一个订单DataTable，可以在两个表之间创建关系，然后在订单表中添加基于表达式的列（用来显示客户表的列）：

Ds.Relations.Add(“CustomersOrders”,ds.Tables(“Customers”).Columns(“CustomerID”),
ds.Tables(“Orders”).Columns(“CustomerID”))

Ds.Tables(“Orders”).Columns.Add(“CompanyName”,GetType(String),
“Parent(CustomersOrders).CompanyName”)

3          在代码中使用DataView对象

DataView对象提供了与DataTable对象的Select方法类似的功能。

3.1         创建DataView对象

要使用DataView对象查看DataTable中数据，必须【将它与DataTable关联】。

可以——使用DataView的Table属性；或者在DataView构造函数中——指定所要使用的DataTable

Dim tbl as New DataTable(“TableName”)

Dim vue as DataView

Vue=new DataView()

Vue.Table=tbl

Vue=new DataView(tbl)

注意：如果使用DataView属性Table来设置关联的DataTable时，一定要保证DataTable的TableName不能是默认值（就是空字符串）。

这个限制在使用DataView的构造函数是就没有。

DataView的另一个构造函数，签名与DataTable.Select方法极为接近。

在其中，需要设置（DataView的Table、RowFilter、Sort、RowStateFilter）

Dim tbl as New DataTable(“Customers”)

Dim dvrs as DataViewRowState

Dvrs=DataViewRowState.ModifiedOriginal or DataViewRowState.Deleted

Dim vue as newDataView()

Vue.Table=tbl

Vue.RowFilter=”Country=’USA’”

Vue.Sort=”City DESC”

Vue.RowStateFile=dvrs

Vue=new DataView(tbl,”Country=’USA’”,”City DESC”, dvrs)

3.2         使用RowStateFilter属性

RowStateFilter属性接受DataViewRowState枚举类型值。

RowStateFilter属性，能起到双重筛选程序的作用。比如：值ModifiedOriginal，表示修改过的行，会显示原始值。

l DataViewRowState枚举值表

值	说明
Added	包括新添加的行
CurrentRows	包括未删除的行（这是默认值）
Deleted	包括被删除的行。
ModifiedCurrent	包括被修改的行，且显示“当前值”
modifiedOriginal	包括被修改的行，且显示“原始值”
None	不包括任何行
OriginalRows	所有的行（被删除的、被修改的、未被删除的），都显示“原始值”
Unchanged	包括未被修改的行

3.3         使用DataRowView对象

在使用DataTable.Select方法时，存在以下问题：如果指定了ModifiedOriginal，那么返回的是被修改过的行，对于返回的DataRow对象，需要调用过程才能得到行的原始值。

因为DataView对象返回数据使用的是专门的“DataRowView对象”。

DataRowView提供了与DataRow类似的功能，它公开了默认的Item属性，使用该属性，通过提供列名或者列索引来访问列的内容。DataRowView的Item属性，可以用来检查和修改行的内容；但是，“通过DataRowView只能使用一个版本的行数据”，也就是说，在DataView对象的DataRowVersion属性中指定哪个版本的数据，DataRowView就只能显示这个版本数据。

Dim tbl As New DataTable(“Customers”)

...

Dim vue As DataView

Vue=New DataView(tbl)

Dim row as DataRowView=vue(0)

Console.WriteLine(vue(“CompanyName”)

如果发现DataRowView对象的功能不足以满足要求，可以使用DataRowView的Row属性访问相应的DataRow对象。

3.4         通过DataView检查所有可用数据各行

用DataView访问数据与直接访问DataTable数据略有不同。

DataTable通过Row属性公开了自己的数据行（你可以用For Each遍历内容）；

DataView则没有此种可枚举的集合来公开数据。

DataView对象公开了一个Count属性，该属性返回DataView可见行的数量。通过它可以构造一个For循环检查各行。

DataView还公开了一个返回IEnumerator对象的方法（GetEnumerator）。IEnumerator对象属于System.Collections命名空间，提供了与DataReader的MoveNext方法类似的导航功能。

Dim tbl as New DataTable(“Customers”)

...

Dim vue as DataView

Vue=New DataView(tbl,””,””,DataViewRowState.ModifiedOriginal)

Dim rowView as DataRowView

 

Dim intCounter as Integer

For intCounter=0 to vue.count-1

     RowView=vue(intCounter)

     Console.WriteLine(rowView(“CompanyName”))

Next intcounter

 

Dim objEnum as IEnumerator=vue.GetEnumerator()

Do While objEnum.MoveNext()

     Row=Ctype(objEnum.Current,DataRowView)

     Console.WriteLine(row(“Companyname”))

Loop

3.5         在DataView中搜索数据

DataView对象使用：RowFilter和RowStateFilter——支持“筛选”。

它还使用：Find和FindRows方法——支持“搜索”。

3.5.1   Find方法

使用Find方法，首先要设置DataView的Sort属性。Find方法根据Sort属性中指定的列来查找，可以 提供一个或多个值。

不过，DataView的Find方法的返回值：不是DataRow或者DataRowView对象；它返回一个整数值，对应所需行在DataView中的索引。如果没找到，返回值为-1。

Dim strConn As String =”Providor=SQLOLEDB;Data Source=...;Initial Catalog=Northwind;
Trusted_Connection=Yes;”

Dim strSql As String=”Select ComstomerID,CompanyName,ContactName,Phone,City,Country
from Customers”

Dim da As New OleDBDataAdapter(strSql,strConn)

Dim tbl As new DataTable()

Da.Fill(tbl,”Customers”)

 

Dim vue as New DataView(tb)

Vue.Sort=“ContactName”

Dim i As Integer=vue.Find(“Fran Wilson”)

If i=-1 Then

    Console.WriteLine(“Row Not Found”)

Else

    Console.WriteLine(vue(i)(“CompanyName”))

End IF

3.5.2   FindRows方法

在DataRowCollection对象中，其Find方法会根据DataTable对象的“PrimaryKey属性”，进行搜索，因为（主键也与惟一键约束相关联），所以最多只能有一行满足
DataRowCollection.Find方法所指定的条件。

然而，DataView对象的Find方法是根据（其Sort属性中指定的列）来进行搜索，DataView中的数据在此排序列可能有多行数据是相同值，此时不能用DataView.Find定位所有的【相同值数据行】，因为Find方法返回值是一个整数。

☆DataView对象的另一个方法【FindRows】可以完成这种功能，调用方法跟Find一样，但它返回满足条件的DataRowView对象数组。

...

Dim vue As New DataView(tbl)

Vue.Sort=”Country”

Dim arows as DataRowView()=vue.FindRows(“Spain”)

If arows.Length=0 Then

    Console.WriteLine(“没找到”)

Else

    Dim row As DataRowView

    For Each row in arows

           Console.WriteLine(row(“City”))

    Next Row

End If

3.6         修改DataRowView对象

用DataRowView对象修改一行数据，类似于修改DataRow的内容。

DataRowView对象也公开了BeginEdit、EndEdit、CancelEdit和Delete方法。

创建新数据行时，DataRowView与DataRow有点不同：

DataView有一个AddNew方法，它返回一个新DataRowView，在返回的DataRowView调用EndEdit方法之后，新行才真正被添加到底层的DataTable

Dim tbl as New DataTable(“Customers”)

...

Dim vue as New DataView(tbl)

Dim row as DataRowView=vue.AddNew()

Row(“CustomerID”)=”ABCDE”

Row(“CompanyName”)=”New Company”

Row(“ContactName”)=”New Contact”

Row(“Phone”)=”(617) 555-1212”)

Row.EndEdit()

‘修改一行

Row.BeginEdit()

Row(“CompanyName”)=”Modified”

Row.EndEdit()

‘删除一行

Row.Delete()