'-------------------------------------------------------------
'功能:通用的连接数据库模块
'参数:SQL SQL语言
'返回:1.直接对数据库进行操作时无返回值(方法1)
' 2.返回一个 DataTable 对象(方法2)
'方法:1、ExecuteSQL(sql)
' 2、dt=ExecuteSQL(sql) 'dt是已经定义好的一个datatable sql 是查询语句 '
'-------------------------------------------------------------
Public Function ExecuteSQL(ByVal SQLStr As String) As DataTable
ExecuteSQL = New DataTable
Try
'Dim sqlconn As new SqlClient.SqlConnection
'sqlconn = New SqlClient.SqlConnection
'sqlconn.ConnectionString = strConnectString
'下面一句等同于上面3句
Dim sqlconn As New SqlConnection(strConnectString)
Dim sqlCommand As New SqlCommand
With sqlCommand
.Connection = sqlconn
.CommandText = SQLStr
.CommandType = CommandType.Text
End With
Dim sTokens() As String
sTokens = SQLStr.Split '分割字符串,
If Strings.InStr("INSERT,DELETE,UPDATE,BACKUP,RESTORE", sTokens(0).ToUpper) Then
''//此处去除INSERT的作用:正常插入记录是没有返回的,但是我们此处需要返回ID,所以才将插入命令在此删除
'If Strings.InStr("DELETE,UPDATE,BACKUP,RESTORE", sTokens(0).ToUpper) Then
If sqlconn.State <> ConnectionState.Open Then
sqlconn.Open()
End If
sqlCommand.ExecuteNonQuery()
If sqlconn.State <> ConnectionState.Closed Then
sqlconn.Close()
End If
'Select Case sTokens(0).ToUpper
' Case "INSERT"
' MessageBox.Show("追加记录成功!", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information)
' Case "DELETE"
' MessageBox.Show("删除记录成功!", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information)
' Case "UPDATE"
' MessageBox.Show("更新记录成功!", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information)
'End Select
Return Nothing
Else
Dim ds As New DataSet
ds.Tables.Add("temp")
Dim sqladapter As SqlClient.SqlDataAdapter
sqladapter = New SqlClient.SqlDataAdapter
sqladapter.SelectCommand = sqlCommand
If sqlconn.State <> ConnectionState.Open Then
sqlconn.Open()
End If
sqlCommand.ExecuteNonQuery()
If sqlconn.State <> ConnectionState.Closed Then
sqlconn.Close()
End If
sqladapter.Fill(ds, "temp")
Return ds.Tables("temp")
End If
Catch ex As Exception
MessageBox.Show(ex.Message, "错误提示", MessageBoxButtons.OK, MessageBoxIcon.Error)
Return Nothing
End Try
End Function