机房重构第一次用到SQLHelper类,下面看一下其代码。
Public Class SqlHelper
'数据库连接
Private Shared strConnection As String = System.Configuration.ConfigurationManager.AppSettings("connString") '利用配置文件 连接数据库
Shared conn As SqlConnection = New SqlConnection(strConnection)
Shared cmd As New SqlCommand
''' <summary>
''' 查询 操作(有参)1
''' </summary>
''' <param name="cmdText">需要执行的命令 sql语句或存储过程</param>
''' <param name="cmdType">所执行命令的类型</param>
''' <param name="paras">传入的参数</param>
''' <returns></returns>
''' <remarks></remarks>
Public Function ExecSelect(ByVal cmdText As String, ByVal cmdType As CommandType, ByVal paras As SqlParameter()) As DataTable
Dim sqlAdapter As SqlDataAdapter
Dim dt As New DataTable
Dim ds As New DataSet
'给cmd赋值
cmd.CommandText = cmdText
cmd.CommandType = cmdType
cmd.Connection = conn
cmd.Parameters.AddRange(paras) '参数添加
sqlAdapter = New SqlDataAdapter(cmd) '实例化Ada
Try
sqlAdapter.Fill(ds) '用 adapter填充 dataset
dt = ds.Tables(0) 'datatable为dataSet的第一个表
Catch ex As Exception
MsgBox("数据库操作")
Finally
cmd.Parameters.Clear() '清除参数
Call CloseCmd(cmd) '销毁cmd命令
End Try
Return dt
End Function
''' <summary>
''' 查询 操作(无参)
''' </summary>
''' <param name="cmdText"></param>
''' <param name="cmdType"></param>
''' <returns></returns>
''' <remarks></remarks>
Public Function ExecSelectNo(ByVal cmdText As String, ByVal cmdType As CommandType) As DataTable
Dim sqlAdapter As SqlDataAdapter
Dim dt As New DataTable
Dim ds As New DataSet
cmd.CommandText = cmdText
cmd.CommandType = cmdType
cmd.Connection = conn
sqlAdapter = New SqlDataAdapter(cmd) '实例化Adapter
Try
sqlAdapter.Fill(ds) '用adapter填充dataset
dt = ds.Tables(0) 'datatable为dataSet的第一个表
Catch ex As Exception
Return Nothing
Finally
Call CloseConn(conn)
Call CloseCmd(cmd) '销毁cmd命令
End Try
Return dt
End Function
''' <summary>
''' 增删改 操作(有参)
''' </summary>
''' <param name="cmdText"></param>
''' <param name="cmdType"></param>
''' <param name="paras"></param>
''' <returns></returns>
''' <remarks></remarks>
Public Function ExecAddDelUpdate(ByVal cmdText As String, ByVal cmdType As CommandType, ByVal paras As SqlParameter()) As Integer
'给cmd赋值
cmd.CommandText = cmdText
cmd.CommandType = cmdType
cmd.Connection = conn
cmd.Parameters.AddRange(paras) '参数添加
Try
conn.Open() '打开链接
Return cmd.ExecuteNonQuery() '执行增删改操作
'cmd.Parameters.Clear() '清除参数
Catch ex As Exception '如有异常,返回值为0 '此处有错误
Return 0
Finally
cmd.Parameters.Clear() '清除参数
Call CloseConn(conn) '关闭连接
Call CloseCmd(cmd) '销毁cmd命令
End Try
End Function
Public Function ExecAddDelUpdateNo(ByVal cmdText As String, ByVal cmdType As CommandType) As Integer
'给cmd赋值
cmd.CommandText = cmdText
cmd.CommandType = cmdType
cmd.Connection = conn
Try
conn.Open() '打开链接
Return cmd.ExecuteNonQuery() '执行增删改操作
Catch ex As Exception '如有异常,返回值为0
Return 0
Finally
Call CloseConn(conn) '关闭连接
Call CloseCmd(cmd) '销毁cmd命令
End Try
End Function
Public Sub CloseCmd(ByVal cmd As SqlCommand)
If Not IsNothing(cmd) Then '如果cmd命令存在
cmd.Dispose() '销毁
cmd = Nothing
End If
End Sub
Public Sub CloseConn(ByVal conn As SqlConnection)
If (conn.State <> ConnectionState.Closed) Then '如果没有关闭
conn.Close() '关闭连接
conn = Nothing
End If
End Sub
End Class
第一次接触,有些地方不是很明白,以后慢慢学习。