vs2003 VB.NET 版的SqlHelper

自己做项目的时候学着写的

Imports System.Data
Imports System.Data.SqlClient

''' -----------------------------------------------------------------------------
''' Project : ComClass
''' Class : SqlHelp
'''
''' -----------------------------------------------------------------------------
''' <summary>
''' 数据访问抽象基础类
''' </summary>
''' <remarks>
''' </remarks>
''' <history>
''' [skyapplezhao]2009-4-23Created
''' </history>
''' -----------------------------------------------------------------------------
Public NotInheritable Class SqlHelp

Private Shared connectingstrings As String = System.Configuration.ConfigurationSettings.AppSettings("ConnectionString").ToString.Trim

#Region "ExecNonSql执行SQL语句,无返回"
''' -----------------------------------------------------------------------------
''' <summary>
''' ExecNonSql
''' </summary>
''' <param name="SQLString">SQL语句</param>
''' <param name="sqlParms"></param>
''' <returns></returns>
''' <remarks>
''' </remarks>
''' <history>
''' [skyapplezhao]2009-4-23Created
''' </history>
''' -----------------------------------------------------------------------------
Public Overloads Shared Function ExecNonSql(ByVal SQLString As String)
Dim myConn As SqlConnection
Dim cmd As SqlCommand
Try
myConn = New SqlConnection(connectingstrings)
cmd = New SqlCommand(SQLString,myConn)
myConn.Open()
cmd.ExecuteNonQuery()
Catch ex As Exception
Throw ex
Finally
myConn.Close()
cmd.Dispose()
myConn.Dispose()
End Try
End Function

Public Overloads Shared Function ExecNonSql(ByVal SQLString As String,ByVal cmdtype As CommandType,ByVal sqlParms As SqlParameter())
Dim myConn As SqlConnection
Dim cmd As SqlCommand
Try
myConn = New SqlConnection(connectingstrings)
cmd = New SqlCommand
FillSqlCommand(cmd,myConn,Nothing,cmdtype,SQLString,sqlParms)
cmd.ExecuteNonQuery()
cmd.Parameters.Clear()
Catch ex As Exception
Throw ex
Finally
myConn.Close()
cmd.Dispose()
myConn.Dispose()
End Try
End Function
#End Region

#Region "ExecuteSqlTran执行SQL语句，实现数据库事务"
''' -----------------------------------------------------------------------------
''' <summary>
''' ExecuteSqlTran
''' </summary>
''' <param name="SQLString">SQL语句</param>
''' <param name="sqlnum">SQL语句的行数(0或者正确的行数)</param>
''' <returns>true/false</returns>
''' <remarks>
''' </remarks>
''' <history>
''' [skyapplezhao]2009-4-23Created
''' </history>
''' -----------------------------------------------------------------------------
Public Shared Function ExecuteSqlTran(ByVal SQLString As String,ByVal sqlnum As String)
Dim myConn As SqlConnection
Dim cmd As SqlCommand
Dim myTrans As SqlTransaction
Dim errorStr As String
Dim reInt As String
Dim rebol As Boolean
Try
myConn = New SqlConnection(connectingstrings)
cmd = New SqlCommand(SQLString,myConn)
myConn.Open()
myTrans = myConn.BeginTransaction
cmd.Transaction = myTrans
reInt = cmd.ExecuteNonQuery()
If sqlnum = 0 Then
myTrans.Commit()
rebol = True
Else
If reInt = sqlnum Then
myTrans.Commit()
rebol = True
Else
reInt = 0
myTrans.Rollback()
rebol = False
End If
End If
Catch ex As Exception
errorStr = ex.ToString
reInt = 0
myTrans.Rollback()
rebol = False
Finally
myConn.Close()
cmd.Dispose()
myConn.Dispose()
End Try
Return rebol
End Function

#End Region

#Region "ExecuteSql// ExecuteSql执行SQL语句，返回影响的记录数"
''' -----------------------------------------------------------------------------
''' <summary>
''' ExecuteSql// ExecuteSql
''' </summary>
''' <param name="SQLString">sql语句</param>
''' <returns>返回影响的记录数</returns>
''' <remarks>
''' </remarks>
''' <history>
''' [skyapplezhao]2009-4-23Created
''' </history>
''' -----------------------------------------------------------------------------
Public Overloads Shared Function ExecuteSql(ByVal SQLString As String) As Int16
Dim i As Int16 = 0
Dim myConn As SqlConnection
Dim cmd As SqlCommand
Try
myConn = New SqlConnection(connectingstrings)
cmd = New SqlCommand(SQLString,myConn)
myConn.Open()
i = cmd.ExecuteNonQuery()
Catch ex As Exception
Throw ex
Finally
myConn.Close()
cmd.Dispose()
myConn.Dispose()
End Try
Return i
End Function
Public Overloads Shared Function ExecuteSql(ByVal SQLString As String,ByVal sqlParms As SqlParameter()) As Int16
Dim r As Int16 = 0
Dim myConn As SqlConnection
Dim cmd As SqlCommand
Try
myConn = New SqlConnection(connectingstrings)
cmd = New SqlCommand
'''cmd.CommandType = cmdtype
FillSqlCommand(cmd,sqlParms)
r = cmd.ExecuteNonQuery()
cmd.Parameters.Clear()
Catch ex As Exception
Throw ex
Finally
myConn.Close()
cmd.Dispose()
myConn.Dispose()
End Try
Return r

End Function
#End Region

#Region "ExecuteScalarSql // ExecuteScalarS 执行SQL语句，返回查询结果"
''' -----------------------------------------------------------------------------
''' <summary>
''' ExecuteScalarSql // ExecuteScalarS
''' </summary>
''' <param name="SQLString">SQL语句</param>
''' <returns>返回第一行第一列</returns>
''' <remarks>
''' </remarks>
''' <history>
''' [skyapplezhao]2009-4-23Created
''' </history>
''' -----------------------------------------------------------------------------
Public Shared Function ExecuteScalarSql(ByVal SQLString As String) As Object
Dim myConn As SqlConnection
Dim cmd As SqlCommand
Dim obj As Object = Nothing
Try
myConn = New SqlConnection(connectingstrings)
cmd = New SqlCommand(SQLString,myConn)
myConn.Open()
obj = cmd.ExecuteScalar()
Catch ex As Exception
Throw ex
Finally
myConn.Close()
cmd.Dispose()
myConn.Dispose()
End Try
End Function
Public Shared Function ExecuteScalarS(ByVal SQLString As String,ByVal sqlParms As SqlParameter()) As Object
Dim myConn As SqlConnection
Dim cmd As SqlCommand
Dim obj As Object = Nothing
Try
myConn = New SqlConnection(connectingstrings)
cmd = New SqlCommand
FillSqlCommand(cmd,sqlParms)
obj = cmd.ExecuteScalar()
cmd.Parameters.Clear()
Return obj
Catch ex As Exception
Throw ex
Finally
myConn.Close()
cmd.Dispose()
myConn.Dispose()
End Try
End Function
#End Region

#Region "ExecuteReader//ExecuteReadSql执行查询语句，返回SqlDataReader(使用该方法切记要手工关闭SqlDataReader和连接)"
''' -----------------------------------------------------------------------------
''' <summary>
''' ExecuteReader//ExecuteReadSql
''' </summary>
''' <param name="SQLString">查询语句</param>
''' <returns>返回SqlDataReader</returns>
''' <remarks>
''' 使用该方法切记要手工关闭SqlDataReader和连接
''' </remarks>
''' <history>
''' [skyapplezhao]2009-4-23Created
''' </history>
''' -----------------------------------------------------------------------------
Public Shared Function ExecuteReader(ByVal SQLString As String) As SqlDataReader
Dim myReader As SqlDataReader = Nothing
Dim myConn As SqlConnection
Dim cmd As SqlCommand
Try
myConn = New SqlConnection(connectingstrings)
cmd = New SqlCommand(SQLString,myConn)
myConn.Open()
myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
Return myReader
Catch ex As Exception
Throw ex
End Try
End Function
Public Shared Function ExecuteReadSql(ByVal SQLString As String,ByVal sqlParms As SqlParameter()) As SqlDataReader
Dim myReader As SqlDataReader = Nothing
Dim myConn As SqlConnection
Dim cmd As SqlCommand
Try
myConn = New SqlConnection(connectingstrings)
cmd = New SqlCommand
FillSqlCommand(cmd,sqlParms)
myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
cmd.Parameters.Clear()
Return myReader
Catch ex As Exception
Throw ex
End Try
End Function
#End Region

#Region "ExecuteDataSet // ExecuteDataSet执行查询语句，返回DataSet"
''' -----------------------------------------------------------------------------
''' <summary>
''' ExecuteDataSet // ExecuteDataSet
''' </summary>
''' <param name="SQLString">查询语句</param>
''' <returns>返回DataSet</returns>
''' <remarks>
''' </remarks>
''' <history>
''' [skyapplezhao]2009-4-23Created
''' </history>
''' -----------------------------------------------------------------------------
Public Overloads Shared Function ExecuteDataSet(ByVal SQLString As String) As DataSet
Dim myConn As SqlConnection
Dim myAdapter As SqlDataAdapter
Dim ds As DataSet = Nothing
Try
myConn = New SqlConnection(connectingstrings)
myConn.Open()
myAdapter = New SqlDataAdapter(SQLString,myConn)
ds = New DataSet
myAdapter.Fill(ds,"ds")
Catch ex As Exception
Throw ex
Finally
myConn.Close()
myAdapter.Dispose()
myConn.Dispose()
End Try
Return ds
End Function
Public Overloads Shared Function ExecuteDataSet(ByVal SQLString As String,ByVal sqlParms As SqlParameter()) As DataSet
Dim myConn As SqlConnection
Dim myAdapter As SqlDataAdapter
Dim ds As DataSet = Nothing
Dim cmd As SqlCommand
Try
myConn = New SqlConnection(connectingstrings)
cmd = New SqlCommand
FillSqlCommand(cmd,sqlParms)
myAdapter = New SqlDataAdapter(cmd)
ds = New DataSet
myAdapter.Fill(ds)
cmd.Parameters.Clear()
Catch ex As Exception
Throw ex
Finally
myConn.Close()
cmd.Dispose()
myAdapter.Dispose()
myConn.Dispose()
End Try
Return ds
End Function

#End Region

#Region "ExecuteDataTableSql执行查询语句,返回DataTable"
''' -----------------------------------------------------------------------------
''' <summary>
''' ExecuteDataTableSql
''' </summary>
''' <param name="SQLString">查询语句</param>
''' <returns>返回DataTable</returns>
''' <remarks>
''' </remarks>
''' <history>
''' [skyapplezhao]2009-4-23Created
''' </history>
''' -----------------------------------------------------------------------------
Public Overloads Shared Function ExecuteDataTableSql(ByVal SQLString As String) As DataTable
Dim myConn As SqlConnection
Dim myAdapter As SqlDataAdapter
Dim myTable As DataTable = Nothing
Try
myConn = New SqlConnection(connectingstrings)
myConn.Open()
myAdapter = New SqlDataAdapter(SQLString,myConn)
myAdapter.Fill(myTable)
Catch ex As Exception
Throw ex
Finally
myConn.Close()
myAdapter.Dispose()
myConn.Dispose()
End Try
Return myTable
End Function
Public Overloads Shared Function ExecuteDataTableSql(ByVal SQLString As String,ByVal sqlParms As SqlParameter()) As DataTable
Dim myConn As SqlConnection
Dim myAdapter As SqlDataAdapter
Dim myTable As DataTable = Nothing
Dim cmd As SqlCommand
Try
myConn = New SqlConnection(connectingstrings)
cmd = New SqlCommand
FillSqlCommand(cmd,sqlParms)
myAdapter = New SqlDataAdapter(cmd)
myAdapter.Fill(myTable)
Catch ex As Exception
Throw ex
Finally
myConn.Close()
myAdapter.Dispose()
cmd.Dispose()
myConn.Dispose()
End Try
Return myTable
End Function
#End Region

#Region "填充SqlCommand,Private域"
''' -----------------------------------------------------------------------------
''' <summary>
''' FillSqlCommand
''' </summary>
''' <param name="cmd">SqlCommand</param>
''' <param name="conn">SqlConnection</param>
''' <param name="trans">SqlTransaction</param>
''' <param name="cmdType">CommandType</param>
''' <param name="cmdText">语句</param>
''' <param name="sqlParms">SqlParameter</param>
''' <remarks>
''' </remarks>
''' <history>
''' [skyapplezhao]2009-4-23Created
''' </history>
''' -----------------------------------------------------------------------------
Private Shared Sub FillSqlCommand(ByVal cmd As SqlCommand,ByVal conn As SqlConnection,ByVal trans As SqlTransaction,ByVal cmdType As CommandType,ByVal cmdText As String,ByVal sqlParms As SqlParameter())
If conn.State <> ConnectionState.Open Then
conn.Open()
End If
cmd.Connection = conn
cmd.CommandType = cmdType
cmd.CommandText = cmdText
If Not (trans Is Nothing) Then
cmd.Transaction = trans
End If
If Not (sqlParms Is Nothing) Then
For Each sqlParm As SqlParameter In sqlParms
If (sqlParm.Direction = ParameterDirection.Input Or sqlParm.Direction = ParameterDirection.InputOutput) And sqlParm.Value Is Nothing Then
sqlParm.Value = DBNull.Value
End If
cmd.Parameters.Add(sqlParm)
Next
End If
End Sub
#End Region

End Class