简单搞定数据访问层[续1] - 明辉手游网

明辉手游网中心：是一个免费提供流行视频软件教程、在线学习分享的学习平台！

文章发布全站软件文章热门搜索：免费卡住全屏声音黑屏影音资讯首页腾讯视频爱奇艺暴风影音酷狗音乐酷我音乐影音大全软件下载软件教程电脑系统下载聊天工具办公软件杀毒教程系统工具图形图像电脑学习应用软件网络软件苹果应用注册码网站教程技术开发安卓教程其它教程您当前所在位置：网视手游站 -> 网站教程 -> 简单搞定数据访问层[续1]
简单搞定数据访问层[续1] 发表时间：2024-02-19 来源：明辉站整理相关软件相关文章人气： [摘要]' clsSQLBuilder‘ By YuHonglai‘ www.hahaIT.com‘ hahasoft@msn.com ' Note:提供重载方法以指明表名称，默认情况下，是传入参数 o 的类型＋"tbl_",此时类名称必须是 clsXXX 的形式.&#... ' 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 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 标签：简单搞定数据访问层[续1] 上一篇：XMLHTTP+Javascript+Asp写得聊天室，无刷新完成（一）下一篇：状态条插入可视控件	推荐资讯总人气榜 1PS提示：因为图层已锁定，无法编辑图层的处理方法 2Adobe Illustrator CS5 序列号大全 3picacg苹果版怎么找不到\|picacg ios版是不是下架了介绍 4ACDsee注册码免费分享（含ACDsee18、ACDsee10等版本） 5苹果iphone自动时间不准确的多种校正方法 6PDF浏览器能看3D文件吗？PDF浏览器看3D文件图文详细教程 7Potato(马铃薯聊天)怎么注册不了\|Potato不能注册处理方法介绍 8Potato(土豆聊天)怎么换头像\|Potato app更改头像方法介绍 9ipad版office如何激活? office365激活的图文说明教程 10最新的Adobe Illustrator CS4序列号大全 11Mac怎么通过设置VPN来登录youtube等国外网站 12眼萌app怎么色弱测试眼萌app色弱测试图文说明教程 1支付宝怎么交学费，支付宝教育缴费图文说明教程 2京东微笑面单是什么，京东微笑快递单有什么用 3高铁儿童票网上怎么买，网上买高铁儿童票具体流程 4百度减少收录是什么原因？ 5GG广告申请 6提高谷歌联盟Adsense收入4种方法 7百度降权并不可怕 8新手误区：揭露seo反链工具的真实面目 9被降权问题浅谈百度页面权重 10稳定网站排名的隐秘：百度优先级算法 112014年9月百度绿萝算法再度升级 12浅谈增加网站外部链接的方法最新教程本月人气 1如何利Mcrypt扩展库进行加密与解密 2如何使用C#创建一个3层的数据库应用程序 3选购Web服务器 4在VC中动态使用FoxPro数据库 5个人经验：使用asp尽量减少服务器端的工作量 6ODBC to mySQL 7分发VFP应用时自动创建快捷方式 8转－对于多线程 9.Net中如何设置IIS 10在PHP世界中选择最合适的模板--比较PHPLIB Template与FastTemplate 11菜单项的动态装入 12XML、Web服务与.NET框架(1) 1uc浏览器去广告图文说明教程：uc浏览器广告过滤技巧使用方法_绿茶安卓网 2字符串根据多个字符进行分割的一种方法 3使用c#制作容易的留言板(1) 43星s5电信版支持4g吗？3星galaxy s5电信版支持4g网络吗？ 5nubia x6电信版什么时候上市？中兴nubia x6电信版上市时间 6华为p7移动版什么时候上市？华为p7手机移动版上市时间 7联想黄金斗士s8预约图文说明教程(附联想s8预约网址) 8联想黄金斗士s8加持版与规范版的区别有哪一些？s8加持版/规范版区别 9美颜相机声音如何关？美颜相机关声音方法_绿茶安卓网 10陌陌如何举报？陌陌举报好友图文说明教程_绿茶安卓网 11陌陌群聊如何@别人？陌陌群聊@别人方法_绿茶安卓网 123星s4手势感应如何用？3星Galaxy s4手势感应视频图文说明教程
相关文章如何利Mcrypt扩展库进行加密与解密如何使用C#创建一个3层的数据库应用程序选购Web服务器在VC中动态使用FoxPro数据库个人经验：使用asp尽量减少服务器端的工作量 ODBC to mySQL 分发VFP应用时自动创建快捷方式转－对于多线程 .Net中如何设置IIS 在PHP世界中选择最合适的模板--比较PHPLIB Template与FastTemplate 菜单项的动态装入 XML、Web服务与.NET框架(1)