通用的连接数据库模块（vb2010）

 

    '-------------------------------------------------------------
    '功能：通用的连接数据库模块

    '参数：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