Module Db
Dim SqlCon As SqlConnection
''' <summary>
''' 打开Access连接
''' </summary>
''' <remarks></remarks>
Public Function F_SQLOpenCn() As Boolean
Dim Bool As Boolean = False
Try
Dim SqlStrCon As String = "uid=" & Load_Sa & ";pwd=" & Load_ServerPwd & ";database=" & Load_DbName & ";Integrated Security=False;Timeout=1;Server=" & Load_ServerName
SqlCon = New SqlConnection(SqlStrCon)
If SqlCon.State = ConnectionState.Open Then
SqlCon.Close()
Else
SqlCon.Open()
End If
If SqlCon.State = ConnectionState.Closed Then
Bool = False
Else
Bool = True
End If
Catch ex As Exception
' MsgBox("F_SQLOpenCn:" & ex.Message)
Return Bool
End Try
Return Bool
End Function
Public Function F_Exec(ByVal Sql As String, ByVal DataType As DataType, Optional ByVal TranBool As Boolean = False) As Object
Dim Obj As Object = Nothing
Try
If F_SQLOpenCn() = False Then Return Nothing
Dim Tran As SqlClient.SqlTransaction = Nothing '定义 一个事务
Dim Err As Boolean = False
Dim SqlCmd As New SqlClient.SqlCommand(Sql, SqlCon)
If TranBool = True Then '开始事务
Tran = SqlCon.BeginTransaction
sqlCmd.Transaction = Tran
End If
Dim DSet As New DataSet
Dim SqlDpt As New SqlDataAdapter(Sql, SqlCon)
Select Case DataType
Case Db.DataType.DataSet
SqlDpt.Fill(DSet)
Obj = DSet
Case Db.DataType.Datatab
Dim Dt As New DataTable
SqlDpt.Fill(Dt)
Obj = Dt
Case Db.DataType.DataCommand
Obj = SqlCmd.ExecuteNonQuery
Case Else
Obj = SqlCmd.ExecuteScalar
End Select
If TranBool = True Then
If Err = False Then Tran.Commit()
If Err = True Then Tran.Rollback()
End If
SqlCon.Close()
Catch ex As Exception
MsgBox("F_Exec:" & ex.Message)
Return Obj
End Try
Return Obj
End Function
End Module
调用以上的函数
Private Sub BT_Con_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BT_Con.Click
Try
Dim Sql As String = ""
Sql = " Select * from CHM_Sys_Menu where isnull(State,0)=1 "
Dim Dt As DataTable = F_Exec(Sql, DataType.Datatab)
If Dt Is Nothing OrElse Dt.Rows.Count <= 0 Then Exit Sub
'F_Exec参数有三个,如果是执行DataCommand 第三个参数为true
'如
dim Bool as Boolean=false
Bool=F_Exec("Insert into Table(....) values(,,,)", DataType.DataCommand,true)
if bool<=0 then
msgbox("Error")
else
msgbox("OK")
end if
Catch ex As Exception