轻松搞定数据访问层

类别:.NET开发 点击:0 评论:0 推荐:
写在前面:
    其实这里的两篇数据访问层的文章都不是我写的,但我在搞数据访问层的时候参考了这两篇文章。其实我是用MS的SqlHelper类来进行底层数据访问,然后自己做一个DAL层。
   有空了,我把我的方法写出来,与大家共享!
                                                                            一笑 
                                                                           2004-10-18

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

1。实体类
2。访问类

现在以下表为例
tblPerson
(perID,perName,perGender,perOld,perNation)

实体类
Person
---------
ID
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语句的类
        ' 处理一些特殊情况,如主键,自动增加值字段等
        ' 如果在数据库架构文件中存储更多的架构信息,如最大值等,还可以完成一些数据验证之类的操作
 ...
    Next
    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

班级的ID

 

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

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

Tbl_Teacher

Name

DBName

Type

Seed

Key

ID

teaID

Integer

1

1

Code

teaCode

String

0

0

Name

teaName

String

0

0

Gender

teaGender

Boolean

0

0

Nation

teaNation

String

0

0

Age

teaAge

Integer

0

0

 

Tbl_Student

Name

DBName

Type

Seed

Key

ID

stuID

Integer

1

1

Code

stuCode

String

0

0

TeacherCode

stuTeacherCode

String

0

0

Name

stuName

String

0

0

Gender

stuGender

Boolean

0

0

Nation

stuNation

String

0

0

Age

stuAge

Integer

0

0

ClassID

stuClassID

Integer

0

0

 

必须的类

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字段.这里我只用了SeekKey的字段属性,其实还可以添加其它属性,如数字型的最大值,字符型验证的正则表达式.以在clsSQLBuilder中完成数据验证操作)

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

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

 

' clsSQLBuilder

By YuHonglai

www.hahaIT.com

[email protected]

 

' 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

            Next

            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

            Next

 

            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

            Next

            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)

            Next

 

            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

            Next

            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

            Next

 

            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

            Next

            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

            Next

 

            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

        Next

 

        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

        Next

 

        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

        Next

        ' 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

        Next

        ' 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 & "]")

            Next

        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 "

            Next

            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

        Next

        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

[email protected]

 

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

        Next

 

        ' 检查 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)

            Next

            alOut.Add(newObject)

            newObject = Nothing

        Next

        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

        Next

        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

            End If

        Else

            Return Nothing

        End If

    End Function

 

End Class

本文地址:http://com.8s8s.com/it/it43470.htm