vB.net DbHelper类（完整）

概述本人经过多次使用，无需修改可以直接使用的数据库类 Imports SystemImports System.CollectionsImports System.Collections.SpecializedImports System.DataImports System.Data.SqlClientImports System.ConfigurationImports S

本人经过多次使用，无需修改可以直接使用的数据库类

imports Systemimports System.Collectionsimports System.Collections.Specializedimports System.Dataimports System.Data.sqlClIEntimports System.Configurationimports System.Data.Commonimports System.Collections.Genericnamespace Tianli.DBUtility    '修改成實際項目的命名空間名稱     ''' <summary>     ''' 資料訪問基礎類(基於Oracle)     ''' 可以用戶可以修改滿足自己專案的需要。     ''' </summary>     Public Mustinherit Class DbHelpersql        ' Protected Shared connectionString As String = ConfigurationSettings.AppSettings("conectiongstring")        Protected Shared connectionString As String = PubConstant.ConnectionString        Public Sub New()        End Sub#Region "公用方法"        Public Shared Function Exists(ByVal strsql As String,ByVal ParamArray cmdParms As sqlParameter()) As Boolean            Dim obj As Object = Getsingle(strsql,cmdParms)            Dim cmdresult As Integer            If ([Object].Equals(obj,nothing)) OrElse ([Object].Equals(obj,System.dbnull.Value)) Then                cmdresult = 0            Else                cmdresult = Integer.Parse(obj.ToString())            End If            If cmdresult = 0 Then                Return False            Else                Return True            End If        End Function        Public Shared Function Exists(ByVal strsql As String) As Boolean            Dim obj As Object = Getsingle(strsql)            Dim cmdresult As Integer            If ([Object].Equals(obj,System.dbnull.Value)) Then                cmdresult = 0            Else                cmdresult = Integer.Parse(obj.ToString())            End If            If cmdresult = 0 Then                Return False            Else                Return True            End If        End Function#End Region#Region "執行簡單sql語句"        ''' <summary>        ''' 执行SQL语句，返回影响的记录数        ''' </summary>        ''' <param name="sqlstring">SQL语句</param>        ''' <returns>影响记录数据</returns>        ''' <remarks></remarks>        Public Shared Function Executesql(ByVal sqlstring As String) As Integer            Dim connection As New sqlConnection(connectionString)            If connection.State = ConnectionState.Closed Then                connection.open()            End If            Dim trans As sqlTransaction = connection.BeginTransaction            Dim cmd As New sqlCommand            cmd.Connection = connection            cmd.Transaction = trans            cmd.CommandText = sqlstring            Try                Dim row As Integer = cmd.ExecuteNonquery                trans.Commit()                Return row            Catch ex As Exception                trans.Rollback()                Throw New Exception(ex.Message)            End Try        End Function        Public Shared Sub Executesqltran(ByVal sqlstringList As ArrayList)            Dim conn As New sqlConnection(connectionString)            conn.open()            If conn.State = ConnectionState.Closed Then                conn.open()            End If            Dim trans As sqlTransaction = conn.BeginTransaction            Dim cmd As New sqlCommand            cmd.Connection = conn            cmd.Transaction = trans            Try                For n As Integer = 0 To sqlstringList.Count - 1                    Dim strsql As String = sqlstringList(n).ToString()                    If strsql.Trim().Length > 1 Then                        cmd.CommandText = strsql                        cmd.ExecuteNonquery()                    End If                Next                trans.Commit()            Catch ex As Exception                trans.Rollback()                Throw New Exception(ex.Message)            End Try        End Sub        ''' <summary>         ''' 執行一條計算查詢結果語句，返回查詢結果（object）。         ''' </summary>         ''' <param name="sqlString">計算查詢結果語句</param>         ''' <returns>查詢結果（object）</returns>         Public Shared Function Getsingle(ByVal sqlstring) As Object            Dim connection As New sqlConnection(connectionString)            If connection.State = ConnectionState.Closed Then                connection.open()            End If            Dim cmd As New sqlCommand(sqlstring,connection)            Try                Dim obj As Object = cmd.ExecuteScalar()                If ([Object].Equals(obj,nothing)) Or ([Object].Equals(obj,System.dbnull.Value)) Then                    Return nothing                Else                    Return obj                End If            Catch ex As Exception                connection.Close()                Throw New Exception(ex.Message)            End Try        End Function        ''' <summary>         ''' 執行查詢語句，返回sqlExecuteReader         ''' </summary>         ''' <param name="strsql">查詢語句</param> 一般用於聚集函數返回值        ''' <returns>sqlExecuteReader</returns>         Public Shared Function sqlExecuteReader(ByVal strsql As String) As sqlDataReader            Dim connection As New sqlConnection(connectionString)            If connection.State = ConnectionState.Closed Then                connection.open()            End If            Dim cmd As New sqlCommand(strsql,connection)            Try                Dim myReader As sqlDataReader = cmd.ExecuteReader()                Return myReader            Catch ex As Exception                Throw New Exception(ex.Message)            End Try        End Function        ''' <summary>         ''' 執行帶一個存儲過程參數的的sql語句。         ''' </summary>         ''' <param name="sqlString">sql語句</param>         ''' <param name="content">參數內容,比如一個欄位是格式複雜的文章，有特殊符號，可以通過這個方式添加</param>         ''' <returns>影響的記錄數</returns>         Public Shared Function Executesql(ByVal sqlString As String,ByVal content As String) As Integer            Dim connection As New sqlConnection(connectionString)            Dim cmd As New sqlCommand(sqlString,connection)            Dim myParameter As New System.Data.sqlClIEnt.sqlParameter("@content",sqlDbType.NText)            myParameter.Value = content            cmd.Parameters.Add(myParameter)            Try                connection.open()                Dim rows As Integer = cmd.ExecuteNonquery()                Return rows            Catch E As System.Data.sqlClIEnt.sqlException                Throw New Exception(E.Message)            Finally                cmd.dispose()                connection.Close()            End Try        End Function        ''' <summary>        ''' 返回最大值        ''' </summary>        ''' <param name="FIEldname">字段名称</param>        ''' <param name="tablename">表名称</param>        ''' <returns></returns>        ''' <remarks></remarks>        Public Shared Function GetMaxID(ByVal FIEldname As String,ByVal tablename As String) As Integer            Dim strsql As String = "select max(" + FIEldname + ")+1 from " + tablename            Dim obj As Object = Getsingle(strsql)            If obj Is nothing Then                Return 1            Else                Return Integer.Parse(obj.ToString())            End If        End Function        ' <summary>         ' 向資料庫裏插入圖像格式的欄位(和上面情況類似的另一種實例)         ' </summary>         ' <param name="strsql">sql語句</param>         ' <param name="fs">圖像位元組,資料庫的欄位類型為image的情況</param>         ' <returns>影響的記錄數</returns>         '//保存sql server2000的Image        'string pathname;         'pathname = this.openfileDialog1.filename;         '//將圖像讀入到字節數組         'System.IO.fileStream fs = new System.IO.fileStream(pathname,System.IO.fileMode.Open,System.IO.fileAccess.Read);         'byte[] buffByte = new byte[fs.Length];         'fs.Read(buffByte,(int)fs.Length);         'fs.Close();         'fs = null;         'public byte[] Convertimage(Image image)         '{         '    fileStream fs=new fileStream("imagetemp",fileMode.Create,fileAccess.Write,fileShare.None);         '    BinaryFormatter bf = new BinaryFormatter();         '    bf.Serialize(fs,(object)image);         '    fs.Close();         '    fs=new fileStream("imagetemp",fileMode.Open,fileAccess.Read,fileShare.None);         '    byte[] bytes = new byte[fs.Length];         '    fs.Read(bytes,(int)fs.Length);         '    fs.Close();         '    return bytes;         '}         'public Image ReadImage(byte[] bytes)         '{         '　　fileStream fs=new fileStream("imagetemp1",fileShare.None);         '　　foreach(byte a in bytes)         '　　{         '　　　　fs.WriteByte(a);         '　　}         '    fs.Close();         '    fs=new fileStream("imagetemp1",fileShare.None);         '    BinaryFormatter bf = new BinaryFormatter();         '    object obj=bf.Deserialize(fs);         '　　fs.Close();         '　　return (Image)obj;         '}         Public Shared Function ExecutesqlInsertimg(ByVal strsql As String,ByVal fs As Byte()) As Integer            Dim connection As New sqlConnection(connectionString)            If connection.State = ConnectionState.Closed Then                connection.open()            End If            Dim cmd As New sqlCommand(strsql,connection)            Dim myParameter As New sqlParameter("@fs",sqlDbType.Image)            myParameter.Value = fs            cmd.Parameters.Add(myParameter)            Try                Dim rows As Integer = cmd.ExecuteNonquery                Return rows            Catch ex As Exception                Throw New Exception(ex.Message)            Finally                cmd.dispose()                connection.Close()            End Try        End Function        ''' <summary>         ''' 執行查詢語句，返回DataSet         ''' </summary>         ''' <param name="sqlString">查詢語句</param>         ''' <returns>DataSet</returns>        Public Shared Function query(ByVal sqlString As String) As DataSet            Dim connection As New sqlConnection(connectionString)            Dim ds As New DataSet            Try                connection.open()                Dim myda As New sqlDataAdapter(sqlString,connection)                myda.Fill(ds)            Catch ex As Exception                Throw New Exception(ex.Message)            End Try            Return ds        End Function#End Region#Region "消息模塊"        'Public Shared Function Show(ByVal page As System.Web.UI.Page,ByVal msg As String)        '    page.RegisterStartupScript("message","<script language='JavaScript' defer>alert('" + msg.ToString() + "');</script>")        'End Function#End Region#Region "執行帶參數的sql語句"        Private Shared Sub PrepareCommand(ByRef cmd As sqlCommand,ByRef conn As sqlConnection,ByRef trans As sqlTransaction,ByRef cmdText As String,ByRef cmdParms As sqlParameter())            If conn.State = ConnectionState.Closed Then                conn.open()            End If            cmd.Connection = conn            cmd.CommandText = cmdText            If Not trans Is nothing Then                cmd.Transaction = trans            End If            cmd.CommandType = CommandType.Text            If Not cmdParms Is nothing Then                For Each parm As sqlParameter In cmdParms                    cmd.Parameters.Add(parm)                Next            End If        End Sub        Public Shared Function Executesql(ByVal sqlString As String,ByVal ParamArray cmdParms As sqlParameter()) As Integer            Dim connection As New sqlConnection(connectionString)            If connection.State = ConnectionState.Closed Then                connection.open()            End If            Dim trans As sqlTransaction = connection.BeginTransaction            Dim cmd As New sqlCommand            Try                PrepareCommand(cmd,connection,trans,sqlString,cmdParms)                Dim rows As Integer = cmd.ExecuteNonquery()                cmd.Parameters.Clear()                trans.Commit()                Return rows            Catch ex As Exception                trans.Rollback()                Throw New Exception(ex.Message)            Finally                connection.Close()                cmd.dispose()            End Try        End Function        '*        ' Dim parameters As OracleParameter() = {New OracleParameter("DPPM_",OracleType.Number),New OracleParameter("Issue_ID_",New OracleParameter("code1_ID_",New OracleParameter("defectcode_ID_",OracleType.Number)}        ' parameters(0).Value = dppm        ' parameters(1).Value = issue_ID        ' parameters(2).Value = code1_ID        ' parameters(3).Value = defect_code_ID        ' Return ITSC.DbHelpersql.ExecuteSP("Updatecsm_issue_code",parameters)        '*'        Private Shared Sub PrepareCommandSP(ByVal cmd As sqlCommand,ByVal conn As sqlConnection,ByVal trans As sqlTransaction,ByVal cmdText As String,ByVal cmdParms As sqlParameter())            If conn.State <> ConnectionState.Open Then                conn.open()            End If            cmd.Connection = conn            If Not trans Is nothing Then                cmd.Transaction = trans            End If            cmd.CommandText = cmdText            cmd.CommandType = CommandType.StoredProcedure            If Not cmdParms Is nothing Then                For Each parm As sqlParameter In cmdParms                    cmd.Parameters.Add(parm)                Next            End If        End Sub        Public Shared Function ExecuteSP(ByVal SP As String,ByVal ParamArray cmdParms As sqlParameter()) As Integer            Dim connection As New sqlConnection(connectionString)            If connection.State = ConnectionState.Closed Then                connection.open()            End If            Dim trans As sqlTransaction = connection.BeginTransaction()            Dim cmd As New sqlCommand            Try                PrepareCommandSP(cmd,SP,cmdParms)                Dim rows As Integer = cmd.ExecuteNonquery()                cmd.Parameters.Clear()                trans.Commit()                Return rows            Catch ex As System.Data.sqlClIEnt.sqlException                trans.Rollback()                Throw New Exception(ex.Message)            End Try        End Function        ''' <summary>         ''' 執行一條計算查詢結果語句，返回查詢結果（object）。         ''' </summary>         ''' <param name="sqlString">計算查詢結果語句</param>         ''' <returns>查詢結果（object）</returns>         Public Shared Function GetSingle(ByVal sqlString As String,ByVal ParamArray cmdParms As sqlParameter()) As Object            Dim connection As New sqlConnection(connectionString)            Dim cmd As New sqlCommand            Try                PrepareCommand(cmd,nothing,cmdParms)                Dim obj As Object = cmd.ExecuteScalar()                cmd.Parameters.Clear()                If ([Object].Equals(obj,System.dbnull.Value)) Then                    Return nothing                Else                    Return obj                End If            Catch e As System.Data.sqlClIEnt.sqlException                Throw New Exception(e.Message)            End Try        End Function        ''' <summary>        ''' 执行查询语句，返回DataReader        ''' </summary>        ''' <param name="sqlString">查询语句</param>        ''' <param name="cmdParms">查询参数</param>        ''' <returns>DataReader</returns>        ''' <remarks>DataReader</remarks>        Public Shared Function ExecuteReader(ByVal sqlString As String,ByVal ParamArray cmdParms As sqlParameter()) As sqlDataReader            Dim connection As New sqlConnection(connectionString)            Dim cmd As New sqlCommand            Try                PrepareCommand(cmd,cmdParms)                Dim myReader As sqlDataReader = cmd.ExecuteReader()                cmd.Parameters.Clear()                Return myReader            Catch e As System.Data.sqlClIEnt.sqlException                Throw New Exception(e.Message)            End Try        End Function        ''' <summary>         ''' 執行查詢語句，返回DataSet         ''' </summary>         ''' <param name="sqlString">查詢語句</param>         ''' <returns>DataSet</returns>         Public Shared Function query(ByVal sqlString As String,ByVal ParamArray cmdParms As sqlParameter()) As DataSet            Dim connection As New sqlConnection(connectionString)            Dim cmd As New sqlCommand            PrepareCommand(cmd,cmdParms)            Dim Myda As New sqlDataAdapter(cmd)            Dim ds As New DataSet            Try                Myda.Fill(ds,"ds")                cmd.Parameters.Clear()            Catch ex As System.Data.sqlClIEnt.sqlException                Throw New Exception(ex.Message)            End Try            Return ds        End Function        ''' <summary>         ''' 執行多條sql語句，實現資料庫事務。         ''' </summary>         ''' <param name="sqlStringList">sql語句的哈希表（key為sql語句，value是該語句的OracleParameter[]）</param>         Public Shared Sub ExecutesqlTran(ByVal sqlStringList As Hashtable)            Dim conn As New sqlConnection(connectionString)            conn.open()            Dim trans As sqlTransaction = conn.BeginTransaction()            Dim cmd As New sqlCommand            Try                '迴圈                 For Each myDE As DictionaryEntry In sqlStringList                    Dim cmdText As String = myDE.Key.ToString()                    Dim cmdParms As sqlParameter() = DirectCast(myDE.Value,sqlParameter())                    PrepareCommand(cmd,conn,cmdText,cmdParms)                    Dim val As Integer = cmd.ExecuteNonquery()                    cmd.Parameters.Clear()                    trans.Commit()                Next            Catch                trans.Rollback()                Throw            End Try        End Sub#End Region#Region "存儲過程 *** 作"        ''' <summary>         ''' 執行存儲過程         ''' </summary>         ''' <param name="storedProcname">存儲過程名</param>         ''' <param name="parameters">存儲過程參數</param>         ''' <returns>OracleDataReader</returns>         Public Shared Function RunProcedure(ByVal storedProcname As String,ByVal parameters As IDataParameter()) As sqlDataReader            Dim connection As New sqlConnection(connectionString)            Dim returnReader As sqlDataReader            connection.open()            Dim command As sqlCommand = BuildqueryCommand(connection,storedProcname,parameters)            command.CommandType = CommandType.StoredProcedure            returnReader = command.ExecuteReader()            Return returnReader        End Function        ''' <summary>         ''' 執行存儲過程         ''' </summary>         ''' <param name="storedProcname">存儲過程名</param>         ''' <param name="parameters">存儲過程參數</param>         ''' <param name="tablename">DataSet結果中的表名</param>         ''' <returns>DataSet</returns>         Public Shared Function RunProcedure(ByVal storedProcname As String,ByVal parameters As IDataParameter(),ByVal tablename As String) As DataSet            Dim connection As New sqlConnection(connectionString)            Dim dataSet As New DataSet            connection.open()            Dim sqlDA As New sqlDataAdapter            sqlDA.SelectCommand = BuildqueryCommand(connection,parameters)            sqlDA.Fill(dataSet,tablename)            connection.Close()            Return dataSet        End Function        ''' <summary>         ''' 構建 OracleCommand 物件(用來返回一個結果集，而不是一個整數值)         ''' </summary>         ''' <param name="connection">資料庫連接</param>         ''' <param name="storedProcname">存儲過程名</param>         ''' <param name="parameters">存儲過程參數</param>         ''' <returns>OracleCommand</returns>         Private Shared Function BuildqueryCommand(ByVal connection As sqlConnection,ByVal storedProcname As String,ByVal parameters As IDataParameter()) As sqlCommand            Dim command As New sqlCommand(storedProcname,connection)            command.CommandType = CommandType.StoredProcedure            For Each parameter As sqlParameter In parameters                command.Parameters.Add(parameter)            Next            Return command        End Function        ''' <summary>         ''' 執行存儲過程，返回影響的行數         ''' </summary>         ''' <param name="storedProcname">存儲過程名</param>         ''' <param name="parameters">存儲過程參數</param>         ''' <param name="rowsAffected">影響的行數</param>         ''' <returns></returns>         Public Shared Function RunProcedure(ByVal storedProcname As String,ByRef rowsAffected As Integer) As Integer            Dim connection As New sqlConnection(connectionString)            Dim result As Integer            connection.open()            Dim command As sqlCommand = BuildIntCommand(connection,parameters)            rowsAffected = command.ExecuteNonquery()            result = CInt(command.Parameters("ReturnValue").Value)            'Connection.Close();             Return result        End Function        ''' <summary>         ''' 創建 OracleCommand 物件實例(用來返回一個整數值)         ''' </summary>         ''' <param name="storedProcname">存儲過程名</param>         ''' <param name="parameters">存儲過程參數</param>         ''' <returns>OracleCommand 物件實例</returns>         Private Shared Function BuildIntCommand(ByVal connection As sqlConnection,ByVal parameters As IDataParameter()) As sqlCommand            Dim command As sqlCommand = BuildqueryCommand(connection,parameters)            command.Parameters.Add(New sqlParameter("ReturnValue",sqlDbType.Int,4,ParameterDirection.ReturnValue,False,_            0,String.Empty,DaTarowVersion.[Default],nothing))            Return command        End Function#End Region    End ClassEnd namespace

总结

以上是内存溢出为你收集整理的vB.net DbHelper类（完整）全部内容，希望文章能够帮你解决vB.net DbHelper类（完整）所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。