轻松搞定数据访问层[续1]

原创 2004年07月28日 18:39:00

' clsSQLBuilder<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

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<'<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" />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

 

传智播客.韩顺平.轻松搞定网页设计(html.css.javascript)

传智播客.韩顺平.轻松搞定网页设计(html.css.javascript)http://www.kesjc.com/bbs/forum-66-1.html 第00讲.开山篇 第01讲.html介...
  • cgnine
  • cgnine
  • 2014年07月01日 18:08
  • 1324

轻松搞定RabbitMQ(一)——RabbitMQ基础知识+HelloWorld

本文是简单介绍一下RabbitMQ,参考官网上的教程。同时加入了一些自己的理解。官网教程详见:"Hello World!"。 引言        你是否遇到过多个系统间需要通过定时任务来同步某些数据?...
  • xiaoxian8023
  • xiaoxian8023
  • 2015年09月23日 16:26
  • 7559

数据访问层的设计(一)——功能与接口定义

数据访问层的设计我研究了很长时间,关于接口的定义,好几次都推翻重来。 园子看到过很多easyui+MVC+EF的文章,在早期,我的设计也类似。 但是后来为了增强它,想加点功能通用的功能进去,就耗费...
  • dyllove98
  • dyllove98
  • 2013年07月19日 19:19
  • 7918

韩顺平_轻松搞定网页设计(html+css+javascript)_第20讲_js基本数据类型_js运算符1_学习笔记_源代码图解_PPT文档整理

文西马龙:http://blog.csdn.net/wenximalong/ js基本语法——基本数据类型 javascript基本数据类型三大类型 ①基本数据类型 分为: (1)数...
  • wenximalong
  • wenximalong
  • 2012年11月21日 12:31
  • 2212

1.创建一个数据访问层

  • 2008年10月24日 22:38
  • 1.98MB
  • 下载

持续集成之路——数据访问层的单元测试(续)

在上一篇中,完成了对测试用数据源的配置。下面继续构建可运行的测试。        三、使用DBUnit管理数据        测试的维护一直是我比较头疼的问题,期望可以有一个比较易于维护和可复用的方法...
  • mydeman
  • mydeman
  • 2013年07月15日 14:21
  • 3211

CYQ.Data 轻量数据访问层(八) 自定义数据表实现绑定常用的数据控件(中)

继上一节之后,我们开始寻找绑定之法 先是一回想,我们平常是拿什么绑定到数据控件的:List,DataTable,DataSet,DataView之类的, 而写法也就这么个样: xxxx.Data...
  • cyq1162
  • cyq1162
  • 2016年11月23日 12:02
  • 93

CYQ.Data 轻量数据访问层(九) 自定义数据表实现绑定常用的数据控件(下)

继上一篇已过两月有余,上一篇时正巧遇出差。回来时找不到当初的demo程序,于是此系列就暂时放下了。 上一篇:CYQ.Data 轻量数据访问层(六) 自定义数据表实现绑定常用的数据控件(中) 不过,...
  • cyq1162
  • cyq1162
  • 2016年11月23日 12:03
  • 145

用数据透视表来做分组计数很轻松,顺带可以搞定直方图!

  • 2014年11月16日 23:07
  • 1.07MB
  • 下载

安卓Android BLE低功耗蓝牙接受数据详解 只需100行代码轻松搞定

  • 2016年11月23日 18:41
  • 26.54MB
  • 下载
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:轻松搞定数据访问层[续1]
举报原因:
原因补充:

(最多只允许输入30个字)