1、查询数据集
'获取查询数据集
Public Overloads Function QueryCommand(ByRef cmd As SqlCommand, Optional ByRef errMsg As String = "") As DataTable
errMsg = ""
Try
If cmd.Connection Is Nothing Then
GetSqlConnection()
cmd.Connection = Me.cn
Me.openSqlConnection(Me.cn)
End If
Dim ds As New DataSet
Dim ap As SqlDataAdapter = New SqlDataAdapter(cmd)
ap.Fill(ds, "table01")
cmd.Dispose()
ap.Dispose()
ds.Dispose()
Return ds.Tables(0)
Catch ex As Exception
errMsg = "执行QueryCommand失败" & Chr(13) & ex.Message
WriteLog("执行QueryCommand失败" & Chr(13) & "SQL:" & cmd.CommandText & Chr(13) & ex.Message & Chr(13) & ex.StackTrace)
Return Nothing
End Try
End Function
2、执行SQL查询
Public Function QuerySQL(sqlstr As String, ByRef errmsg As String) As DataTable
errmsg = ""
Dim cmd As New SqlCommand
Dim errcode As String = TenpayUtil.getTimestamp
Try
Me.GetSqlConnection()
Me.openSqlConnection(Me.cn)
cmd.Connection = Me.cn
cmd.CommandText = sqlstr
errmsg = ""
Return QueryCommand(cmd)
Catch ex As Exception
errmsg = "执行查询出错,错误码:" & errcode
WriteLog(errmsg & ",详情:" & Chr(13) & ex.Message & Chr(13) & ex.StackTrace)
Return Nothing
Finally
Me.closeSqlConnection(Me.cn)
If cmd IsNot Nothing Then
cmd.Dispose()
End If
End Try
End Function
3、执行存储过程,无返回数据集
Public Function ExecSQL(sqlstr As String, ByRef errmsg As String, Optional ByRef sqltran As SqlTransaction = Nothing) As Integer
errmsg = ""
Dim cmd As New SqlCommand
Dim errcode As String = TenpayUtil.getTimestamp
Try
If sqltran IsNot Nothing Then
If sqltran.Connection IsNot Nothing Then
cmd.Connection = sqltran.Connection
cmd.Transaction = sqltran
Else
Me.GetSqlConnection()
Me.openSqlConnection(Me.cn)
cmd.Connection = Me.cn
End If
Else
Me.GetSqlConnection()
Me.openSqlConnection(Me.cn)
cmd.Connection = Me.cn
End If
cmd.CommandText = sqlstr
errmsg = ""
Return cmd.ExecuteNonQuery
Catch ex As Exception
errmsg = "执行SQL出错,错误码:" & errcode
WriteLog(errmsg & "SQL:" & sqlstr & Chr(13) & ",详情:" & Chr(13) & ex.Message & Chr(13) & ex.StackTrace)
Return 0
Finally
If sqltran Is Nothing Then
Me.closeSqlConnection(Me.cn)
End If
If cmd IsNot Nothing Then
cmd.Dispose()
End If
End Try
End Function
4、执行存储过程,有数据集返回、有参数返回
Sub execProc(procName As String, inputs As Dictionary(Of String, Object), ByRef outputs As Dictionary(Of String, SqlParameter), ByRef errMsg As String, ByRef resTable As DataTable, Optional ByRef sqlTran As SqlTransaction = Nothing)
'事务优先
Dim errcode As String = TenpayUtil.getTimestamp
Dim cmd As SqlCommand
Dim sqlpara As SqlParameter
If inputs Is Nothing Then
errMsg = "inputs不能为Nothing"
Exit Sub
End If
If outputs Is Nothing Then
errMsg = "outputs不能为Nothing"
Exit Sub
End If
Try
cmd = New SqlCommand(procName)
If sqlTran IsNot Nothing Then
If sqlTran.Connection IsNot Nothing Then
cmd.Connection = sqlTran.Connection
cmd.Transaction = sqlTran
Else
Me.cn = GetSqlConnection()
Me.openSqlConnection(Me.cn)
cmd.Connection = Me.cn
End If
Else
Me.cn = GetSqlConnection()
Me.openSqlConnection(Me.cn)
cmd.Connection = Me.cn
End If
cmd.CommandType = CommandType.StoredProcedure
'输入参数
For Each key As String In inputs.Keys
sqlpara = getSQLParaByObj(key, inputs(key))
cmd.Parameters.Add(sqlpara)
Next
'输出参数
If outputs IsNot Nothing Then
For Each key As String In outputs.Keys
If outputs(key) IsNot Nothing Then
outputs(key).Direction = ParameterDirection.Output
cmd.Parameters.Add(outputs(key))
End If
Next
End If
cmd.ExecuteNonQuery()
Dim ds As New DataSet
Dim ap As SqlDataAdapter = New SqlDataAdapter(cmd)
ap.Fill(ds, "table01")
cmd.Dispose()
ap.Dispose()
ds.Dispose()
resTable = ds.Tables(0)
Catch ex As SqlException
If ex.Number <> 266 Then
errMsg = "执行" & procName & "出错,错误码:" & errcode
WriteLog(errMsg & ",详情:" & Chr(13) & ex.Message & Chr(13) & ex.StackTrace)
End If
Finally
If sqlTran Is Nothing Then
Me.closeSqlConnection(Me.cn)
End If
End Try
End Sub