操作数据库(vb.net)

 Imports Microsoft.VisualBasic Imports System.Data Imports System.Data.OleDb Namespace Cenxi '''<summary> '''说明：支持绝大部分对数据库的操作（读取，添加，修改） '''<para>支持ado.net事务机制</para> '''<para>非常高效的分页（必须有主键ID）</para> '''<para>支持access和mssql数据库mssql需将OleDb换成sql</para> '''<para>最新：2.40更新时间：2009-12-04，添加了</para> '''<para>制作：晨曦 QQ：260222188</para> '''<para>2.3添加的注释的说明</para> '''</summary> '''<varsion>2.4.0.0</varsion> Public Class sjKu '传递参数 Private _conn As OleDbConnection '数据源 Private _biao As String = "" '操作表 Private _tJian As String = "" '条件 Private com As New OleDbCommand() '''<summary> '''Connection对象 '''</summary> WriteOnly Property Conn() As OleDbConnection Set(ByVal value As OleDbConnection) _conn = value End Set End Property '''<summary> '''操作表 '''</summary> WriteOnly Property Biao() As String Set(ByVal value As String) _biao = value End Set End Property '''<summary> '''条件，格式:"id=@id" '''</summary> WriteOnly Property tJian() As String Set(ByVal value As String) _tJian = value End Set End Property '''<summary> '''sjKuClass '''</summary> ''' <param name="Connection">Connection对象</param> Protected Sub New(ByVal Connection As OleDbConnection) _conn = Connection End Sub '''<summary> '''添加值 '''</summary> ''' <param name="ziduan">字段名称</param> ''' <param name="zhi">赋于的值</param> ''' <param name="leixing">字段类型</param> ''' <param name="changdu">长度</param> Sub Add(ByVal ziduan As String, ByVal zhi As String, Optional ByVal leixing As OleDbType = OleDbType.VarChar, Optional ByVal changdu As Integer = 0) If changdu = 0 Then com.Parameters.Add("@" & ziduan, leixing).Value = zhi Else com.Parameters.Add("@" & ziduan, leixing, changdu).Value = zhi End If End Sub '''<summary> '''读取数据库 '''</summary> Class Du Inherits sjKu '传递参数 Private _ziduan As String = "*" '返回字段 Private _paixu As String = "" '排序 Private _ye As Integer = 1 '当前页 Private _yDaxiao As Integer = 0 '页大小 Private _feiye As Boolean = False '是否分页 Private _canshu As String = "?" '?号后的参数 Private _id As String = "id" '主键 Private _pin As String = "" '多表拼接 Private _zongshu As Integer = 0 '总记录数 '内部变量 Private sqlstr As String = "" 'sql语句 Private zongye As Integer = 0 '''<summary> '''需要的字段 '''</summary> WriteOnly Property Ziduan() As String Set(ByVal value As String) _ziduan = value End Set End Property '''<summary> '''排序 '''</summary> WriteOnly Property Paixu() As String Set(ByVal value As String) _paixu = value End Set End Property '''<summary> '''当前页码 '''</summary> WriteOnly Property Ye() As String Set(ByVal value As String) _ye = value End Set End Property '''<summary> '''页码大小 '''</summary> WriteOnly Property yDaxiao() As String Set(ByVal value As String) _yDaxiao = value End Set End Property '''<summary> '''是否分页 '''</summary> WriteOnly Property Feiye() As Boolean Set(ByVal value As Boolean) _feiye = value End Set End Property '''<summary> '''分页参数　格式：index.aspx?p={?page} '''</summary> WriteOnly Property Canshu() As String Set(ByVal value As String) _canshu = value End Set End Property '''<summary> '''主键id '''</summary> WriteOnly Property Id() As String Set(ByVal value As String) _id = value End Set End Property '''<summary> '''多表拼接 '''</summary> WriteOnly Property Pin() As String Set(ByVal value As String) _pin = value End Set End Property '''<summary> '''设置总记录数 '''</summary> Property Zongshu() As Integer Get Return _zongshu End Get Set(ByVal value As Integer) _zongshu = value End Set End Property '''<summary> '''Sql语句字符串 '''</summary> Function Sqlstring() As String Dim str As String = "" If _yDaxiao <> 0 Then str = "Top " & _yDaxiao.ToString & " " End If If _tJian <> "" Then _tJian = " Where " & _tJian End If Dim pxstring As String = "" If _paixu <> "" Then pxstring = " Order By " & _paixu End If If _feiye Then _zongshu = hsZong() zongye = hsZongye() If _ye > zongye And _ye > 1 Then _ye = zongye End If If _ye = 1 Then sqlstr = "Select top " & _yDaxiao & " " & _ziduan & " From " & _biao & _pin & _tJian & " " & pxstring Else fySqlstring() End If Return sqlstr Else Return "Select " & str & _ziduan & " From " & _biao & _pin & _tJian & " " & pxstring End If End Function Sub fySqlstring() '判断是否是单排序 Dim dx As String = "" Dim dan As String = "" If Regex.IsMatch(_paixu, ",") = False Then '单排序 If Regex.IsMatch(_paixu, " desc", RegexOptions.IgnoreCase) Then dx = Regex.Replace(_paixu, " desc", "") dx = Trim(dx) dan = dx Else dx = _paixu & " Desc" dan = Trim(_paixu) End If Else Dim sz() As String = _paixu.Split(",") Dim i As Int16 For i = 0 To sz.Length - 1 If Regex.IsMatch(sz(i), " desc", RegexOptions.IgnoreCase) Then Dim t As String = "" t = Regex.Replace(sz(i), " desc", "") If i = 0 Then dx = t dan = Trim(t) Else dx += "," & t dan += "," & Trim(t) End If Else If i = 0 Then dx = sz(i) & " Desc" dan = Trim(sz(i)) Else dx += "," & sz(i) & " Desc" dan += "," & Trim(sz(i)) End If End If Next End If If dan = _id Then sqlstr = "Select top " & _yDaxiao & " " & _ziduan & " From " & _biao & _pin & _tJian & " And " & _id & " Not In (Select top " & _yDaxiao * _ye - _yDaxiao & " " & _id & " From " & _biao & _tJian & " Order By " & _paixu & ") Order By " & _paixu Else sqlstr = "Select top " & _yDaxiao & " " & _ziduan & " From " & _biao & _pin & " Where " & _id & " In " sqlstr += "(select top " & _yDaxiao & " " & _id & " From " sqlstr += "(select top " & (_yDaxiao * _ye).ToString & " " & dan & " From " & _biao & _tJian & " Order By " & _paixu & ") As t " sqlstr += "Order By " & dx & ") " sqlstr += "Order By " & _paixu End If End Sub '''<summary> '''获得总记录数 '''</summary> Function hsZong() As Integer If _zongshu > 0 Then Return _zongshu Else Dim Sqlstr As String = "Select Count(*) From " & _biao & _tJian com.Connection = _conn com.CommandText = Sqlstr Return com.ExecuteScalar End If End Function '''<summary> '''获得总页数 '''</summary> Function hsZongye() As Integer If _zongshu Mod _yDaxiao = 0 Then Return _zongshu / _yDaxiao Else Return _zongshu / _yDaxiao + 1 End If End Function '''<summary> '''ExecuteReader '''</summary> Function DataReader(Optional ByVal shiwu As OleDbTransaction = Nothing) As OleDbDataReader If shiwu IsNot Nothing Then com.Transaction = shiwu End If com.CommandText = Sqlstring() com.Connection = _conn Return com.ExecuteReader Qing() End Function '''<summary> '''分页函数 '''</summary> Function hsfyHtml() As String Dim t1 As String Dim t2 As String If _ye = 1 Or _ye < 1 Then t1 = "首页 上一页" Else t1 = "<a href ='" & Replace(_canshu, "{?page}", 1) & "'>首页</a> <a href ='" & Replace(_canshu, "{?page}", _ye - 1) & "'>上一页</a>" End If If _ye = zongye Or _ye > zongye Then t2 = "下一页 尾页" Else t2 = "<a href ='" & Replace(_canshu, "{?page}", _ye + 1) & "'>下一页</a> <a href ='" & Replace(_canshu, "{?page}", zongye) & "'>尾页</a>" End If Return "当前第 " & _ye & "/" & zongye & "页 共 " & _zongshu & " 条记录 " & t1 & " " & t2 End Function '''<summary> '''回收资源 '''</summary> Sub Qing() sqlstr = "" _tJian = "" com.Dispose() End Sub End Class '''<summary> '''更新数据库 '''</summary> Class gXin Inherits sjKu Private str1 As String = "" Private str2 As String = "" Private _sqlstring As String = "" '''<summary> '''添加值 '''</summary> ''' <param name="ziduan">字段名称</param> ''' <param name="zhi">赋于的值</param> ''' <param name="leixing">字段类型</param> ''' <param name="changdu">长度</param> Sub Jia(ByVal ziduan As String, ByVal zhi As String, Optional ByVal leixing As OleDbType = OleDbType.VarChar, Optional ByVal changdu As Integer = 0) If str1 = "" Then str1 += ziduan Else str1 += "," & ziduan End If If str2 = "" Then str2 += "@" & ziduan Else str2 += ",@" & ziduan End If If changdu = 0 Then com.Parameters.Add("@" & ziduan, leixing).Value = zhi Else com.Parameters.Add("@" & ziduan, leixing, changdu).Value = zhi End If End Sub '''<summary> '''修改值 '''</summary> ''' <param name="ziduan">字段名称</param> ''' <param name="zhi">赋于的值</param> ''' <param name="leixing">字段类型</param> ''' <param name="changdu">长度</param> Sub Gai(ByVal ziduan As String, ByVal zhi As String, Optional ByVal leixing As OleDbType = OleDbType.VarChar, Optional ByVal changdu As Integer = 0) If str1 = "" Then str1 += ziduan & "=@a" & ziduan Else str1 += "," & ziduan & "=@a" & ziduan End If If changdu = 0 Then com.Parameters.Add("@a" & ziduan, leixing).Value = zhi Else com.Parameters.Add("@a" & ziduan, leixing, changdu).Value = zhi End If End Sub '''<summary> '''运行添加数据sql '''</summary> ''' <param name="shiwu">事务名称</param> Function RunxingJia(Optional ByVal shiwu As OleDbTransaction = Nothing) As Integer If shiwu IsNot Nothing Then com.Transaction = shiwu End If _sqlstring = "Insert into " & _biao & " (" & str1 & ") values(" & str2 & ")" com.Connection = _conn com.CommandText = _sqlstring Dim i As Integer = com.ExecuteNonQuery qing() Return i End Function '''<summary> '''运行修改数据sql '''</summary> ''' <param name="shiwu">事务名称</param> Function RunxingGai(Optional ByVal shiwu As OleDbTransaction = Nothing) As String If shiwu IsNot Nothing Then com.Transaction = shiwu End If If _tJian <> "" Then str1 += " Where " & _tJian End If _sqlstring = "Update " & _biao & " Set " & str1 com.Connection = _conn com.CommandText = _sqlstring Dim i As Integer = com.ExecuteNonQuery qing() Return i End Function '''<summary> '''运行删除数据sql '''</summary> ''' <param name="shiwu">事务名称</param> Function RunxingsChu(Optional ByVal shiwu As OleDbTransaction = Nothing) As Integer If shiwu IsNot Nothing Then com.Transaction = shiwu End If If _tJian <> "" Then str1 += " Where " & _tJian End If _sqlstring = "Delete From " & _biao & str1 com.Connection = _conn com.CommandText = _sqlstring Dim i As Integer = com.ExecuteNonQuery qing() Return i End Function '''<summary> '''直接运行sql语句 '''</summary> ''' <param name="sqlstr">sql语句</param> ''' <param name="shiwu">事务名称</param> Function RunxingSql(ByVal sqlstr As String, Optional ByVal shiwu As OleDbTransaction = Nothing) As Integer If shiwu IsNot Nothing Then com.Transaction = shiwu End If com.Connection = _conn com.CommandText = sqlstr Dim i As Integer = com.ExecuteNonQuery qing() Return i End Function '''<summary> '''回收资源，每次操作都已加入此方法 '''</summary> Sub qing() _sqlstring = "" str1 = "" str2 = "" _tJian = "" com.Dispose() End Sub End Class End Class End Namespace