本人经过多次使用,无需修改可以直接使用的数据库类
Imports System
Imports System.Collections
Imports System.Collections.Specialized
Imports System.Data
Imports System.Data.sqlClient
Imports System.Configuration
Imports System.Data.Common
Imports System.Collections.Generic
Namespace 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 Class
End Namespace
原文链接:https://www.f2er.com/vb/260778.html