轻松搞定数据访问层4

转载 2004年09月07日 08:28:00
 

' clsDataAccessOper 该类是所有数据访问类的父类<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

' 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

        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

 

轻松搞定Java Web面试

-
  • 1970年01月01日 08:00

韩顺平轻松搞定网页设计(html.css.js)(完整版)

  • 2017年09月05日 17:34
  • 2.11MB
  • 下载

传智播客_韩顺平轻松搞定网页设计(html+css+js)

  • 2016年02月25日 23:46
  • 48B
  • 下载

韩顺平轻松搞定网页设计全集(54讲-全)

  • 2016年09月21日 14:56
  • 35KB
  • 下载

轻松搞定C++语言.pdf

  • 2018年04月06日 11:10
  • 12.95MB
  • 下载

传智播客.韩顺平.轻松搞定网页设计(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:16
  • 1553

传智播客_韩顺平轻松搞定网页设计_div css.ppt

  • 2015年06月06日 15:53
  • 6.38MB
  • 下载

(花见花开)产品需求文档

初学写PRD,没什么经验,都是摸索 相关PRDHTML文件下载浏览http://pan.baidu.com/s/1qYckLCo...
  • qq_34162802
  • qq_34162802
  • 2016-06-21 13:54:57
  • 334

轻松搞定数据访问层3

  clsSQLBuilder‘ By YuHonglai‘ www.hahaIT.com‘ hahasoft@msn.com  Note:提供重载方法以指明表名称,默认情况下,是传入参数 o 的类型...
  • windlian
  • windlian
  • 2004-09-07 08:27:00
  • 484

轻松搞定数据访问层2

 轻松搞定数据访问层     yuhonglai [原作]  数据库表Tbl_Teacher数据字段名称类型说明teaIDInt自动编号teaCodeChar(20)教师员工号teaNameNchar...
  • windlian
  • windlian
  • 2004-09-07 08:25:00
  • 575
收藏助手
不良信息举报
您举报文章:轻松搞定数据访问层4
举报原因:
原因补充:

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