轻松搞定数据访问层

写在前面：

其实这里的两篇数据访问层的文章都不是我写的，但我在搞数据访问层的时候参考了这两篇文章。其实我是用MS的SqlHelper类来进行底层数据访问，然后自己做一个DAL层。

有空了，我把我的方法写出来，与大家共享！

一笑

2004-10-18

下面实现的方法，可以把你从SQL的Add,Delete,Update,Select的重复劳动解脱出来

1。实体类

2。访问类

现在以下表为例

tblPerson

(perID,perName,perGender,perOld,perNation)

实体类

Person

---------

Name

Gender

Old

Nation

访问基类

DataOper

------------

Shared Delete

Shared Add

Shared Update

Shared Select

访问类

PersonOper:DataOper

--------------------

Shared GetAll

Shared GetPersonByID

Shared GetPersonByName

Shared Get......

将数据库表的信息导入到DB.XML文件中

根据DB.XML文件生成实体类

例如上面的tblPerson表对应的DB.XML中的一个表如下

Name DBName Key Seed Type

--------------------------------------

ID perID 1 1 Integer

Name perName 0 0 String

Gender perGender 0 0 boolean

Old perOld 0 0 Integer

Nation perNation 0 0 String

将从数据库返回的DataSet转换成实体类，如果用一般方式写的话：

ds=cmd.Execute("select * from tblPerson where ID=1")

dim p as new Person

p.ID=Ctype(ds.Tables(0).Rows(0).Item("perID"),Integer)

p.Name=...

p.Gender=...

p.Old=...

p.Nation=...

一张表的转换还好办，那么如果又几十张表效率显得地下了

这里又另外一种通用的方法，利用.NET提供的反射功能！

.NET提供的反射功能可以在知道类方法或属性的名称的情况下，动态访问类的方法。

知道怎么做了吗？

前面我们有一个从数据库对应的DB.XML数据库架构文件

这个文件和实体类的字段一一对应

所以我们只要遍历这个架构文件，利用反射，就能轻松实现上述的赋值了

让我们再看看访问基类

DataOper

--------------

Delete(o as Object)

Add(o as Object)

Update(o as Object)

选择Add(o as Object)为例说明

public sub Add(o as Object)

Dim Type as String=O.GetType.ToString ' 得到传进来的对象的类型

... ' 对象类型的字符串和刚才那个XML架构文件中表的名称一一映射

Dim SchemaTableName as String=GetSchemaTableName(Type) ' 取得需要操作的表的名称及从 Person->tblPerson

' 打开数据库架构文件

Dim dsSchema as new Dataset("DB.XML")

Dim SQL as String="Insert into " & SchemaTableName

Dim r as DataRow

For Each r in dsSchema.Tables(SchemaTableName)

' 在这里操作上面的SQL语句，值的部分利用反射从o中取得，建议写一个专门身成特定对象特定操作的SQL语句的类

' 处理一些特殊情况，如主键，自动增加值字段等

' 如果在数据库架构文件中存储更多的架构信息，如最大值等，还可以完成一些数据验证之类的操作

...

SQLHelper.Excute(ConnectionString,...Text,SQL)

end sub

Delete/Update的通用方法类似

这样，如果你要添加一个Person的话可以这样

Dim p as New Person

p.Name="HahaSoft"

p.Gender=1

p.Old=20

...

PersonOper.Add(p) ' 从DataOper继承的方法,也可以这样写:DataOper.Add(p)

如果要添加一个Book 的话，可以这样

dim b as New Book

b.ISBN=

b.Name=

...

BookOper.Add(b)

怎么样，是不是很通用？

这样写成一个通用类，可以完成所有的实体类的Add/Update/Delete操作

要注意的是，DB.XML数据库架构文件很重要

通用实体类操作完成了，下面是通用的 Select 方法

以前在CSDN上看到过这样的文章:

PersonOper.Keys("Name")="HahaSoft"

PersonOper.keys("Gender")=1

dim p as new Person=PersonOper.Select()

这样就能返回相应的Person的实体类

也可以变相完成一些方法：如

getPersonByID(ID)

getPersonByName()

......

后来苦想一阵，终于实现了.

注：(这里实现的单表的O-R映射，如果对有关联的表的话，只要声明一个如 alAddress as ArrayList 的成员就可以了，当然还涉及一些数据

晚期填充以提高效率的技巧，在这里就不详细介绍了)

数据库表

Tbl_Teacher

数据字段名称

类型

说明

teaID

Int

自动编号

teaCode

Char(20)

教师员工号

teaName

Nchar(10)

teaGender

Bit

teaNation

Nchar(6)

teaAge

TinyInt

Tbl_Student

数据字段名称

类型

说明

stuID

Int

自动编号

stuCode

Char(20)

学生证号

stuTeacherCode

Char(20)

班主任的员工号

stuName

Nchar(10)

姓名

stuGender

Bit

性别

stuNation

Nchar(6)

民族

stuAge

TinyInt

年龄

stuClassID

Int

班级的ＩＤ

用以描述这两张表的XML文件，该XML文件存放的是这两种表的结构信息

假设该文件的名称位? DB.XML

Tbl_Teacher

Name

DBName

Type

Seed

Key

teaID

Integer

Code

teaCode

String

Name

teaName

String

Gender

teaGender

Boolean

Nation

teaNation

String

Age

teaAge

Integer

Tbl_Student

Name

DBName

Type

Seed

Key

stuID

Integer

Code

stuCode

String

TeacherCode

stuTeacherCode

String

Name

stuName

String

Gender

stuGender

Boolean

Nation

stuNation

String

Age

stuAge

Integer

ClassID

stuClassID

Integer

必须的类

clsSQLHelper:微软的数据访问辅助类

clsSQLBuilder:根据配置文件生成相应SQL语句的类见轻松搞定数据访问层[续1]

clsDataAccessOper:所以操作类的父类，提供通用的数据操作方法见轻松搞定数据访问层[续2]

clsDataAccess:暂时没有什么用

数据类和访问类

实体类clsTeacher 的属性(Property),与数据库字段一一对应

Property ID as Integer

Property Code as String

Property Name as String

Property Gender as Boolean

Property Nation as string

Property Age as integer

实体类clsStudent

Property ID as Integer

Property Code as String

Property TeacherCode as String

Property Name as string

Property Gender as Boolean

Property Nation as string

Property Age as Integer

Property ClassID as Integer

注意:上面两个类的具体代码就不用写了吧

访问类clsTeacherOper 从 clsDataAccessOper 继承

Public class clsTeacherOper

Public Function getAll() as ArrayList

Return clsDataAccessOper.Select((New clsTeacher).GetType)

End Function

Public Function getTeacherByCode(Code as string) as clsTeacher

sDataAccessOper.SelectKeys("Code") = Code

Return clsDataAccessOper.Select((New clsTeacher).GetType).Item(0)

End Function

'以下方法类似，实现的是一些如 getXXX by YYY 的查询

'据操作如添加/删除/修改全部从 clsDataAccessOper 中继承

'这里的查询方法都可以用 clsDataAccessOper.SelectKeys(“”)=… 实现

'只是为了提供更友好的接口,如果你时间紧迫可以把

' clsDataAccessOper.SelectKeys/Select 方法提交给逻辑层或表示层的程序员

End Class

访问类 clsStudentOper从 clsDataAccessOper 继承

(同 clsTeacherOper 类)

上篇<<轻松搞定数据访问层>>文章有人说我代码太少，看不懂。其实我只想说明一下道理。

现在，我贴出 clsSQLBuilder clsDataAccessOper 这两个核心类的全部代码。这也只是想更明白的说清楚道理。这两个类还是第一版本，代码质量不是很高，一些异常没有抛出.只是实现了一些功能。

顺便提到的是，实体类可以通过数据库脚本生成，这个工具当然是要自己写的了。工具很简单，两三百行就可以搞定。还有那个数据库结构信息的XML文件自己写也很麻烦，所以也可以写一个自动生成它的工具。

这都不是这个文章讨论的重点，这里就不介绍怎样才能生成那些模样几乎相同的代码了。

这种数据访问方法的优点在于：

如果你的数据库有变动，比如说tbl_Teahcer 表加了一个字段职称(teaTitle)，那么你只需要在 clsTeacher 表里加一个属性 Property Title，然后在 DB.XML 文件中的 tblTeacher 里加一条记录 Title|teaTitle|String|0|0就可以了。

编码速度快，除了这两个核心类，数据实体类可以自动生成，访问类也只是简单的写一些查询方法.

不用写SQL语句。

使用方法举例：

添加一个教师

Dim newTeacher as New clsTeacher

With newTeahcer

‘ 此处如果有ID的赋值，将被忽略，因为是自动增加值。见 clsSQLBuilder

.Name=”Haha”

.Code=” 2001”

.Gender=True

…

End With

clsTeacherOper.Add(newTeacher)

添加一个学生

Dim newStudent as new clsStudent

With newStudent

.Name=”Tom”

.Gender=True

.TeacherCode=” 2001”

…

End with

clsStudentOper.Add(newStudent)

更新删除类似(这里就不举例了)。

现在再就添加一个教师的程序流程大概介绍一下。

当执行 clsTeacherOper.Add(newTeancher) 后，clsDataAccessOper.Add 会把 newTeacher继续传递给 clsSQLBuilder.Add() 方法,在这个方法里,clsSQLBuilder首先取得该对象的类型

在这里是 “clsTeacher” 这个类型字符串和 DB.XML 文件中 tblTeacher 有一一对应的关系，这里是去掉前缀 cls,加上前缀 tbl.你也可以用其它更灵活的方法,如:把映射关系记录到文件中.

然后遍历DB.xml文件中的tblTeacher 表的所有行,其中用到反射方法,以在知道对象属性名称的情况下取得该属性的值(这其中我也波折了一下,开始用 InvokeMember调用,好麻烦.最后发现竟然有一个 CallByName的方法,用起来特简单.其实它也是封装了InvokeMember,).最终生成这样的SQL语句返回.

Insert Into tblTeacher(Name,Code,Gender…) values (‘Haha’,’ 2001’ ,1…)

(有几点要注意,在Add/Delete/Update不同的操作方法里面,对数据表字段有不同的要求.比如在添加时,不能对自动编号ID赋值,所以上面的SQL语句就没有ID字段.这里我只用了Seek和Key的字段属性,其实还可以添加其它属性,如数字型的最大值,字符型验证的正则表达式.以在clsSQLBuilder中完成数据验证操作)

最后clsDataAccessOper.Add 方法用这个SQL语句连接数据库,进行操作.

clsDataAccessOper和clsSQLBuilder我就不详细解说了.代码都贴出来了,自己看吧

' clsSQLBuilder

‘ By YuHonglai

‘ www.hahaIT.com

‘ hahasoft@msn.com

' Note:提供重载方法以指明表名称，默认情况下，是传入参数 o 的类型＋"tbl_",此时类名称必须是 clsXXX 的形式.

' 如：

' dim Rooms as new clsRooms

' SQLBuilder.Add(Rooms)

' 此时程序将把 clsRooms 转换成 tbl_Rooms,以操作数据库表 tbl_Rooms

' 如果类名称和数据库表名称不具有上述对应关系，请使用 Add(o,"TableName")形式的方法，以显示指定要操作的数据库表的名称

Public Class SQLBuilder

' 当要生成的SQL语句的 where 条件语句很复杂时，用该常量作为 Select 方法中 FindCondition(HashTable)

' Key,例如：要生成 where Birth<' 2000-4-4 ' and Birth>' 1980-1-1 ' 的复杂条件时，用以下方法:

' Dim h as new HashTable

' h.Add(ComplexSQL,"_Birth<' 2000-4-4 ' and _Birth>' 1980-1-1 '")

' 注意，Birth是实体类的属性名称，前面必须有一个下划线 "_"

' 处理时，程序将用实际数据库字段名称代替相应的 _Birth

Public Const ComplexSQL As String = "@ComplexSQL"

' 根具实体类生成相应的 Insert ...SQL 语句

' 如果与数据库表名称对应的属性时关键字段而且是自动增加值时(在DB.XML文件中seed的值为 1 )

' 那么该属相将忽略,不会出现在返回的 Insert... SQL语句中

Public Overloads Shared Function Add(ByVal o As Object) As String

Dim typeString As String = o.GetType.ToString

Dim i As Int16

i = typeString.IndexOf("cls") + 3

typeString = "tbl_" & typeString.Substring(i, typeString.Length - i)

Return Add(o, typeString)

End Function

Public Overloads Shared Function Add(ByVal o As Object, ByVal TableName As String) As String

Try

Dim outSQL As String

Dim tmpString As String

outSQL = "insert into [" & TableName & "]("

tmpString = ""

Dim dsDB As New DataSet

dsDB.ReadXml(clsPersistant.DBConfigPath)

Dim row As Data.DataRow

For Each row In dsDB.Tables(TableName).Rows

If row.Item("seed") & "" = "0" Then

outSQL = outSQL & row.Item("dbname") & ","

tmpString = tmpString & getS(row.Item("type")) & "@" & row.Item("dbname") & getS(row.Item("type")) & ","

End If

outSQL = outSQL.Substring(0, outSQL.Length - 1)

tmpString = tmpString.Substring(0, tmpString.Length - 1)

outSQL = outSQL & ") values (" & tmpString & ")"

For Each row In dsDB.Tables(TableName).Rows

If row.Item("seed") & "" <> "1" Then

' tmpString = o.GetType.InvokeMember(row.Item("name"), Reflection.BindingFlags.GetField Or Reflection.BindingFlags.GetProperty, Nothing, o, New Object() {}) & ""

tmpString = CallByName(o, CType(row.Item("name"), String).Trim, CallType.Get) & ""

If tmpString = "True" Then

tmpString = "1"

ElseIf tmpString = "False" Then

tmpString = "0"

End If

outSQL = outSQL.Replace("@" & row.Item("dbname"), tmpString)

End If

Return outSQL.Trim

Catch ex As Exception

Throw ex

End Try

End Function

' 如 Add 方法，关键字段不会更新

' 而且关键字段会作为 update....where .... 的 where 的条件出现

Public Overloads Shared Function Update(ByVal o As Object) As String

Dim typeString As String = o.GetType.ToString

Dim i As Int16

i = typeString.IndexOf("cls") + 3

typeString = "tbl_" & typeString.Substring(i, typeString.Length - i)

Return Update(o, typeString)

End Function

Public Overloads Shared Function Update(ByVal o As Object, ByVal TableName As String) As String

Try

Dim outString As String = ""

Dim tmpString As String

outString = "update [" & TableName & "] set "

tmpString = ""

Dim whereString As String = ""

Dim dsDB As New DataSet

dsDB.ReadXml(clsPersistant.DBConfigPath)

Dim row As Data.DataRow

For Each row In dsDB.Tables(TableName).Rows

If row.Item("key") & "" = "1" Then

whereString = whereString & row.Item("dbname") & "=" & getS(row.Item("type")) & "@" & row.Item("dbname") & getS(row.Item("type")) & " and "

Else

tmpString = tmpString & row.Item("dbname") & "=" & getS(row.Item("type")) & "@" & row.Item("dbname") & getS(row.Item("type")) & ","

End If

If whereString.Trim = "" Then

Throw New Exception("必须指定一个以上的主键!")

End If

tmpString = tmpString.Substring(0, tmpString.Length - 1)

whereString = whereString.Substring(0, whereString.Length - 4)

outString = outString & tmpString & " where " & whereString

For Each row In dsDB.Tables(TableName).Rows

' tmpString = o.GetType.InvokeMember(row.Item("name"), Reflection.BindingFlags.GetField Or Reflection.BindingFlags.GetProperty, Nothing, o, New Object() {}) & ""

tmpString = CallByName(o, CType(row.Item("name"), String).Trim, CallType.Get) & ""

If tmpString = "True" Then

tmpString = "1"

ElseIf tmpString = "False" Then

tmpString = "0"

End If

outString = outString.Replace("@" & row.Item("dbname"), tmpString)

Return outString.Trim

Catch ex As Exception

Throw ex

End Try

End Function

' 更具对象的关键属性(与数据库表的关键字段对应)删除指定的记录

' 对象的其他属性将被忽略

Public Overloads Shared Function Delete(ByVal o As Object) As String

Dim typeString As String = o.GetType.ToString

Dim i As Int16

i = typeString.IndexOf("cls") + 3

typeString = "tbl_" & typeString.Substring(i, typeString.Length - i)

Return Delete(o, typeString)

End Function

Public Overloads Shared Function Delete(ByVal o As Object, ByVal TableName As String) As String

Try

Dim outString As String = ""

Dim tmpString As String

outString = "delete from [" & TableName & "] where "

Dim whereString As String = ""

Dim dsDB As New DataSet

dsDB.ReadXml(clsPersistant.DBConfigPath)

Dim row As Data.DataRow

For Each row In dsDB.Tables(TableName).Rows

If row.Item("key") & "" = "1" Then

whereString = whereString & row.Item("dbname") & "=" & getS(row.Item("type")) & "@" & row.Item("dbname") & getS(row.Item("type")) & " and "

End If

If whereString.Trim = "" Then

Throw New Exception("必须指定一个以上的主键!")

End If

whereString = whereString.Substring(0, whereString.Length - 4)

outString = outString & tmpString & whereString

For Each row In dsDB.Tables(TableName).Rows

If row.Item("key") & "" = "1" Then

' tmpString = o.GetType.InvokeMember(row.Item("name"), Reflection.BindingFlags.GetField Or Reflection.BindingFlags.GetProperty, Nothing, o, New Object() {}) & ""

tmpString = CallByName(o, CType(row.Item("name"), String).Trim, CallType.Get) & ""

outString = outString.Replace("@" & row.Item("dbname"), tmpString)

End If

Return outString.Trim

Catch ex As Exception

Throw ex

End Try

End Function

' 更具对象的关键属性(与数据库表的关键字段对应)判断该对象是否存在于数据库中

' 对象的其他属性将被忽略

Public Overloads Shared Function Exists(ByVal o As Object) As String

Dim typeString As String = o.GetType.ToString

Dim i As Int16

i = typeString.IndexOf("cls") + 3

typeString = "tbl_" & typeString.Substring(i, typeString.Length - i)

Return Exists(o, typeString)

End Function

Public Overloads Shared Function Exists(ByVal o As Object, ByVal TableName As String) As String

Try

Dim outString As String

outString = "select count(*) from [" & TableName & "] where "

Dim tmpString As String

Dim whereString As String = ""

Dim dsDB As New DataSet

dsDB.ReadXml(clsPersistant.DBConfigPath)

Dim row As Data.DataRow

For Each row In dsDB.Tables(TableName).Rows

If row.Item("key") & "" = "1" Then

whereString = whereString & row.Item("dbname") & "=" & getS(row.Item("type")) & "@" & row.Item("dbname") & getS(row.Item("type")) & " and "

End If

If whereString.Trim = "" Then

Throw New Exception("必须指定一个以上的主键!")

End If

whereString = whereString.Substring(0, whereString.Length - 4)

outString = outString & tmpString & whereString

For Each row In dsDB.Tables(TableName).Rows

If row.Item("key") & "" = "1" Then

' tmpString = o.GetType.InvokeMember(row.Item("name"), Reflection.BindingFlags.GetField Or Reflection.BindingFlags.GetProperty, Nothing, o, New Object() {}) & ""

tmpString = CallByName(o, CType(row.Item("name"), String).Trim, CallType.Get) & ""

outString = outString.Replace("@" & row.Item("dbname"), tmpString)

End If

Return outString.Trim

Catch ex As Exception

Throw ex

End Try

End Function

' 生成 First SQL语句

Public Overloads Shared Function First(ByVal o As Object) As String

Dim typeString As String = o.GetType.ToString

Dim i As Int16

i = typeString.IndexOf("cls") + 3

typeString = "tbl_" & typeString.Substring(i, typeString.Length - i)

Return First(typeString)

End Function

Public Overloads Shared Function First(ByVal TableName As String) As String

Dim MoudleSQL As String

MoudleSQL = "select * from [%TableName%] where [%key%] in(select Min([%key%]) from [%TableName%])"

Dim key As String

Dim dsDB As New DataSet

dsDB.ReadXml(clsPersistant.DBConfigPath)

Dim row As Data.DataRow

For Each row In dsDB.Tables(TableName).Rows

If row.Item("key") = "1" Then

key = CType(row.Item("dbname"), String).Trim

Exit For

End If

MoudleSQL = MoudleSQL.Replace("%TableName%", TableName)

MoudleSQL = MoudleSQL.Replace("%key%", key)

Return MoudleSQL

End Function

Public Overloads Shared Function Last(ByVal o As Object) As String

Dim typeString As String = o.GetType.ToString

Dim i As Int16

i = typeString.IndexOf("cls") + 3

typeString = "tbl_" & typeString.Substring(i, typeString.Length - i)

Return Last(typeString)

End Function

Public Overloads Shared Function Last(ByVal TableName As String) As String

Dim MoudleSQL As String

MoudleSQL = "select * from [%TableName%] where [%key%] in(select Max([%key%]) from [%TableName%])"

Dim key As String

Dim dsDB As New DataSet

dsDB.ReadXml(clsPersistant.DBConfigPath)

Dim row As Data.DataRow

For Each row In dsDB.Tables(TableName).Rows

If row.Item("key") = "1" Then

key = CType(row.Item("dbname"), String).Trim

Exit For

End If

MoudleSQL = MoudleSQL.Replace("%TableName%", TableName)

MoudleSQL = MoudleSQL.Replace("%key%", key)

Return MoudleSQL

End Function

Public Overloads Shared Function Previous(ByVal o As Object) As String

Dim typeString As String = o.GetType.ToString

Dim i As Int16

i = typeString.IndexOf("cls") + 3

typeString = "tbl_" & typeString.Substring(i, typeString.Length - i)

Return Previous(o, typeString)

End Function

Public Overloads Shared Function Previous(ByVal o As Object, ByVal TableName As String) As String

Dim MoudleSQL As String

MoudleSQL = "select * from [%TableName%] where [%key%] in(select Max([%key%]) from [%TableName%] where [%key%]<%keyValue%)"

Dim key As String

Dim propertyName As String

Dim propertyValue As String

Dim dsDB As New DataSet

dsDB.ReadXml(clsPersistant.DBConfigPath)

Dim row As Data.DataRow

For Each row In dsDB.Tables(TableName).Rows

If row.Item("key") = "1" Then

key = CType(row.Item("dbname"), String).Trim

propertyName = CType(row.Item("name"), String).Trim

Exit For

End If

' propertyValue = o.GetType.InvokeMember(propertyName, Reflection.BindingFlags.GetField Or Reflection.BindingFlags.GetProperty, Nothing, o, New Object() {}) & ""

propertyValue = CallByName(o, propertyName, CallType.Get) & ""

MoudleSQL = MoudleSQL.Replace("%TableName%", TableName)

MoudleSQL = MoudleSQL.Replace("%key%", key)

MoudleSQL = MoudleSQL.Replace("%keyValue%", propertyValue)

Return MoudleSQL

End Function

Public Overloads Shared Function [Next](ByVal o As Object) As String

Dim typeString As String = o.GetType.ToString

Dim i As Int16

i = typeString.IndexOf("cls") + 3

typeString = "tbl_" & typeString.Substring(i, typeString.Length - i)

Return [Next](o, typeString)

End Function

Public Overloads Shared Function [Next](ByVal o As Object, ByVal TableName As String) As String

Dim MoudleSQL As String

MoudleSQL = "select * from [%TableName%] where [%key%] in(select Min([%key%]) from [%TableName%] where [%key%]>%keyValue%)"

Dim key As String

Dim propertyName As String

Dim propertyValue As String

Dim dsDB As New DataSet

dsDB.ReadXml(clsPersistant.DBConfigPath)

Dim row As Data.DataRow

For Each row In dsDB.Tables(TableName).Rows

If row.Item("key") = "1" Then

key = CType(row.Item("dbname"), String).Trim

propertyName = CType(row.Item("name"), String).Trim

Exit For

End If

' propertyValue = o.GetType.InvokeMember(propertyName, Reflection.BindingFlags.GetField Or Reflection.BindingFlags.GetProperty, Nothing, o, New Object() {}) & ""

propertyValue = CallByName(o, propertyName, CallType.Get) & ""

MoudleSQL = MoudleSQL.Replace("%TableName%", TableName)

MoudleSQL = MoudleSQL.Replace("%key%", key)

MoudleSQL = MoudleSQL.Replace("%keyValue%", propertyValue)

Return MoudleSQL

End Function

' 见 public const ComplexSQL 的说明

Public Shared Function [Select](ByVal FindCondition As Hashtable, ByVal TableName As String) As String

Dim outSQL As String

If FindCondition.Contains(ComplexSQL) Then ' 处理复杂类型的 Where 从句

outSQL = "select * from [" & TableName & "] where " & FindCondition(ComplexSQL)

Dim row As Data.DataRow

Dim dsDB As New DataSet

dsDB.ReadXml(clsPersistant.DBConfigPath)

For Each row In dsDB.Tables(TableName).Rows

outSQL = outSQL.Replace("_" & CType(row.Item("name"), String).Trim, "[" & CType(row.Item("dbname"), String).Trim & "]")

Else

outSQL = "select * from [" & TableName & "] where "

Dim whereString As String = ""

Dim eachKey As Object

For Each eachKey In FindCondition.Keys

whereString = whereString & CType(eachKey, String) & "=" & getS(getTypeByName(TableName, CType(eachKey, String))) & FindCondition(eachKey) & getS(getTypeByName(TableName, CType(eachKey, String))) & " and "

If whereString.Length = 0 Then

whereString = "0=0"

Else

whereString = whereString.Substring(0, whereString.Length - 5)

End If

outSQL = outSQL & whereString

End If

Return outSQL

End Function

' 返回指定的字段(数据库表字段的名称)的数据类型名称(VB数据类型)

Private Shared Function getTypeByName(ByVal TableName As String, ByVal n As String) As String

Dim outStr As String

Dim dsDB As New Data.DataSet

dsDB.ReadXml(clsPersistant.DBConfigPath)

Dim eachRow As DataRow

For Each eachRow In dsDB.Tables(TableName).Rows

If CType(eachRow.Item("dbname"), String).Trim.ToLower = n.Trim.ToLower Then

outStr = CType(eachRow.Item("type"), String).Trim.ToLower

Exit For

End If

Return outStr

End Function

' 根具数据类型名称，返回空或' SQL语句中数字和字符型是否被‘括起来

Private Shared Function getS(ByVal t As String) As String

Dim outString As String

t = t.ToLower.Trim

If t = "single" Or t = "int16" Or t = "int32" Or t = "int64" Or t = "double" Or t = "byte" Then

outString = ""

Return outString

ElseIf t = "date" Or t = "string" Then

outString = "'"

Return outString

End If

End Function

End Class

' clsDataAccessOper 该类是所有数据访问类的父类

' by YuJun

‘ www.hahaIT.com

‘ hahasoft@msn.com

Public Class clsDataAccessOper

' 当Update,Delete,Add方法操作失败返回 False 时，记录出错的信息

Public Shared ModifyErrorString As String

Private Shared Keys As New Hashtable

' 数据库连接字符串

Public Shared Property ConnectionString() As String

Get

Return SqlHelper.cnnString.Trim

End Get

Set(ByVal Value As String)

SqlHelper.cnnString = Value.Trim

End Set

End Property

' Update 不更新主键，包括联合主键

Public Shared Function Update(ByVal o As Object) As Boolean

ModifyErrorString = ""

Try

If CType(SqlHelper.ExecuteNonQuery(SqlHelper.cnnString, CommandType.Text, SQLBuilder.Exists(o)), Int64) = 0 Then

Throw New Exception("该记录不存在!")

End If

Catch ex As Exception

Throw ex

End Try

Try

SqlHelper.ExecuteNonQuery(SqlHelper.cnnString, CommandType.Text, SQLBuilder.Update(o))

Catch ex As Exception

ModifyErrorString = ex.Message

Return False

End Try

Return True

End Function

' Delete 将忽略

Public Shared Function Delete(ByVal o As Object) As Boolean

ModifyErrorString = ""

Try

SqlHelper.ExecuteNonQuery(SqlHelper.cnnString, CommandType.Text, SQLBuilder.Delete(o))

Catch ex As Exception

ModifyErrorString = ex.Message

Return False

End Try

Return True

End Function

' Add 方法将忽略自动增加值的主键

Public Shared Function Add(ByVal o As Object) As Boolean

ModifyErrorString = ""

Try

SqlHelper.ExecuteNonQuery(SqlHelper.cnnString, CommandType.Text, SQLBuilder.Add(o))

Catch ex As Exception

ModifyErrorString = ex.Message

Return False

End Try

Return True

End Function

' 通用数据库查询方法

' 重载方法用于明确指定要操作的数据库表名称

' 否则会以 ReturnType 的类型描述得到要操作的数据库表的名称 eg: ReturnType="clsRooms" ,得道 TableName="tbl_Rooms"

' 该查询方法将查询条件添加到 Keys(HashTable) 中，然后调用 Select 方法返回对象的集合

' 当Keys包含特殊键时，将要处理的是复杂类型的查询,见 SQLBuilder 的 ComplexSQL 说明

' 该方法可以拓展数据访问类的固定查询方法

Public Overloads Shared Function [Select](ByVal ReturnType As Type) As ArrayList

Dim tableName As String

tableName = ReturnType.Name

Dim i As Int16

i = tableName.IndexOf("cls") + 3

tableName = "tbl_" & tableName.Substring(i, tableName.Length - i)

Return [Select](ReturnType, tableName)

End Function

Public Overloads Shared Function [Select](ByVal ReturnType As Type, ByVal TableName As String) As ArrayList

Dim alOut As New ArrayList

Dim dsDB As New Data.DataSet

dsDB.ReadXml(clsPersistant.DBConfigPath)

Dim xxxH As New Hashtable

Dim eachRow As Data.DataRow

For Each eachRow In dsDB.Tables(TableName).Rows

If Keys.Contains(CType(eachRow.Item("name"), String).ToLower.Trim) Then

xxxH.Add(CType(eachRow.Item("dbname"), String).ToLower.Trim, Keys(CType(eachRow.Item("name"), String).Trim.ToLower))

End If

' 检查 Keys 的合法性

Dim dsSelect As New Data.DataSet

If Keys.Count <> xxxH.Count Then

Keys.Clear()

Dim InvalidField As New Exception("没有您设置的字段:")

Throw InvalidField

Else

Keys.Clear()

Try

dsSelect = SqlHelper.ExecuteDataset(SqlHelper.cnnString, CommandType.Text, SQLBuilder.Select(xxxH, TableName))

Catch ex As Exception

Throw ex

End Try

End If

Dim eachSelect As Data.DataRow

Dim fieldName As String

Dim DBfieldName As String

For Each eachSelect In dsSelect.Tables(0).Rows

Dim newObject As Object = System.Activator.CreateInstance(ReturnType)

For Each eachRow In dsDB.Tables(TableName).Rows

fieldName = CType(eachRow.Item("name"), String).Trim

DBfieldName = CType(eachRow.Item("dbname"), String).Trim

CallByName(newObject, fieldName, CallType.Set, CType(eachSelect.Item(DBfieldName), String).Trim)

alOut.Add(newObject)

newObject = Nothing

Return alOut

End Function

Public Shared WriteOnly Property SelectKeys(ByVal KeyName As String)

Set(ByVal Value As Object)

Keys.Add(KeyName.Trim.ToLower, Value)

End Set

End Property

' 下面4个方法用来移动记录

' 移动记录安主键的大小顺序移动，只能对有且仅有一个主键的表操作

' 对于组合主键，返回 Nothing

' 当记录移动到头或末尾时返回 Noting,当表为空时，First,Last 均返回Nothing

Public Shared Function First(ByVal o As Object) As Object

Return Move("first", o)

End Function

Public Shared Function Last(ByVal o As Object) As Object

Return Move("last", o)

End Function

Public Shared Function Previous(ByVal o As Object) As Object

Return Move("previous", o)

End Function

Public Shared Function [Next](ByVal o As Object) As Object

Return Move("next", o)

End Function

' 返回一个表的主键的数量，keyName,keyDBName 记录的是最后一个主键

Private Shared Function getKey(ByRef keyName As String, ByRef keyDBName As String, ByVal TableName As String) As Int16

Dim keyNum As Int16 = 0

Dim dsDB As New DataSet

dsDB.ReadXml(clsPersistant.DBConfigPath)

Dim row As Data.DataRow

For Each row In dsDB.Tables(TableName).Rows

If row.Item("key") = "1" Then

keyNum = keyNum + 1

keyName = CType(row.Item("name"), String).Trim

keyDBName = CType(row.Item("dbname"), String).Trim

Exit For

End If

Return keyNum

End Function

' 为 First,Previous,Next,Last 提供通用函数

Private Shared Function Move(ByVal Type As String, ByVal o As Object) As Object

Dim moveSQL As String

Select Case Type.Trim.ToLower

Case "first"

moveSQL = SQLBuilder.First(o)

Case "last"

moveSQL = SQLBuilder.Last(o)

Case "previous"

moveSQL = SQLBuilder.Previous(o)

Case "next"

moveSQL = SQLBuilder.Next(o)

End Select

Dim typeString As String = o.GetType.ToString

Dim i As Int16

i = typeString.IndexOf("cls") + 3

typeString = "tbl_" & typeString.Substring(i, typeString.Length - i)

Dim TableName As String = typeString

Dim keyName As String

Dim keyDBName As String

Dim tmpString As String

If getKey(keyName, keyDBName, TableName) = 1 Then

Keys.Clear()

Dim ds As New Data.DataSet

ds = SqlHelper.ExecuteDataset(SqlHelper.cnnString, CommandType.Text, moveSQL)

If ds.Tables(0).Rows.Count = 0 Then

Return Nothing

Else

tmpString = CType(ds.Tables(0).Rows(0).Item(keyDBName), String).Trim

Keys.Add(keyName.Trim.ToLower, tmpString)

Dim al As New ArrayList

al = [Select](o.GetType)

If al.Count = 1 Then

Return al.Item(0)

Else

Return Nothing

End If

Else

Return Nothing

End If

End Function

End Class