clssql.vb

Here we are at the crossroads once again Youre telling me youre so confused You cant make up your

'/*数据库操作类*/
Imports System.Data.SqlClient
Imports System.IO

Public Class ClsSQL
    Private strService As String    '定义服务器名称
    Private strDataBase As String   '定义数据库名称
    Private strUser As String   '定义登陆用户名
    Private strPassword As String   '定义登陆用户密码
    Private strConnect As String
    Private strType As String   '定义连接类型
    Private SQLConnect As New SqlConnection(strConnect)  '定义连接对象
    Private SQLCmd As New SQLCommand       '定义数据库操作命令
    Private SQLDA As New SqlDataAdapter    '定义数据库操作适配器
    Private SQLDS As New DataSet    '定义数据集
    Public SQLDR As SQLDataReader
    Sub New(ByVal strS As String, ByVal strD As String, ByVal strU As String, ByVal strP As String, ByVal StrT As Char)
        strService = strS
        strDataBase = strD
        strUser = strU
        strPassword = strP
        strType = StrT

    End Sub
    Sub New()

    End Sub
    Public Property ServiveName() As String
        Get
            Return strService
        End Get
        Set(ByVal Value As String)
            strService = Value
        End Set
    End Property
    Property DataBaseName() As String
        Get
            Return strDataBase
        End Get
        Set(ByVal Value As String)
            strDataBase = Value
        End Set
    End Property
    Property UserName() As String
        Get
            Return strUser
        End Get
        Set(ByVal Value As String)
            strUser = Value
        End Set
    End Property
    Property Password() As String
        Get
            Return strPassword
        End Get
        Set(ByVal Value As String)
            strPassword = Value
        End Set
    End Property
    Property Type() As String
        Get
            Return strType
        End Get
        Set(ByVal Value As String)
            strType = Value
        End Set
    End Property
    Public Function TestConnect() As Boolean  '判断数据库连接是否畅通
        Try
            Select Case strType
                Case "0"  '远程连接
                    strConnect = "Data Source=" & strService & ",1433;Network Library=DBMSSOCN;Initial Catalog=" & strDataBase & ";User Id=" & strUser & ";password=" & strPassword & ";"

                Case "1" '本地连接
                    strConnect = "workstation id=" & strService & ";packet size=4096;integrated security=SSPI;data source=" & strService & ";persist security info=False;initial catalog=" & strDataBase

                Case "2" '信用连接
                    strConnect = "Server=" & strService & ";Database=" & strDataBase & ";User ID=" & strUser & ";Password=" & strPassword & ";Trusted_Connection=False"
                Case "3" '本地连接
                    strConnect = "Data Source=" & strService & ";User ID=" & strUser & ";Pwd=" & strPassword & ";Initial Catalog=" & strDataBase & ""
                Case Else
                    strConnect = "workstation id=" & strService & ";packet size=4096;integrated security=SSPI;data source=" & strService & ";persist security info=False;initial catalog=" & strDataBase

            End Select

            SQLConnect.ConnectionString = strConnect
            SQLCmd.Connection = SQLConnect
            SQLConnect.Open()

            If SQLConnect.State = ConnectionState.Open Then
                Return True
            Else
                Return False
            End If
        Catch ex As Exception
            'MessageBox.Show(ex.Message)
        Finally
            SQLConnect.Close()
        End Try
    End Function
    Public Function ReadFileToStream(ByVal strpath As String) As Byte() ' //读入指定文件，通过tmpByte转换为字节数组

        Dim st As System.IO.FileStream = New System.IO.FileStream(strpath, System.IO.FileMode.Open, System.IO.FileAccess.Read)
        Dim mbr As System.IO.BinaryReader = New System.IO.BinaryReader(st)
        Dim buffer() As Byte
        ReDim buffer(st.Length)
        mbr.Read(buffer, 0, CInt(st.Length))
        st.Close()
        Return buffer
    End Function
    Public Function SQLDataReader(ByVal strSelect As String) As Integer
        Try
            If TestConnect() = True Then
                SQLCmd.Connection = SQLConnect
                SQLCmd.CommandText = strSelect
                SQLConnect.Open()
                SQLDR = SQLCmd.ExecuteReader(CommandBehavior.CloseConnection)
                Return 1
            Else
                Return 0
            End If
        Catch ex As Exception
            Return -1
        End Try
    End Function
    Public Function SQLCommand(ByVal strcmd As String) As Integer  '数据库的插入、删除、修改操作
        Try
            If TestConnect() = True Then
                SQLCmd.CommandText = strcmd
                SQLConnect.Open()
                Return SQLCmd.ExecuteNonQuery()
            Else
                Return 0
            End If
        Catch ex1 As SqlException
            MessageBox.Show("当前数据已经存在！" & ex1.Message)

        Catch ex As Exception
            MessageBox.Show(ex.Message)

        Finally
            SQLConnect.Close()
        End Try
    End Function
    Public Function SP_SQLCommand(ByVal tmpcmd As SQLCommand) As Integer   '利用存贮过程进行数据库的插入、删除、修改操作
        Try
            If TestConnect() = True Then
                tmpcmd.Connection = SQLConnect
                tmpcmd.CommandType = CommandType.StoredProcedure
                SQLConnect.Open()
                Return tmpcmd.ExecuteNonQuery()
            Else
                Return 0
            End If
        Catch ex1 As SqlException
            MsgBox(ex1.Message)

        Catch ex As Exception

            MessageBox.Show(ex.Message & ex.ToString())


        Finally
            SQLConnect.Close()
        End Try
    End Function
    Public Function DBToDS(ByVal strcmd As String, ByVal ds As DataSet, ByVal strTableName As String) As Integer '将数据库中的数据带到数据集
        Try
            If TestConnect() = True Then
                SQLCmd.CommandText = strcmd
                SQLDA.SelectCommand = SQLCmd
                SQLConnect.Open()
                ds.Clear()
                SQLDA.SelectCommand.ExecuteNonQuery()
                SQLDA.Fill(ds, strTableName)
                Return 1
            Else
                'MessageBox.Show("数据库连接失败！")
                Return 0
            End If
        Catch ex As Exception
            'MessageBox.Show(ex.Message)
            Return -1
        Finally
            SQLConnect.Close()
        End Try
    End Function
    Public Function DBToDS_Procedure(ByVal strcmd As SQLCommand, ByVal ds As DataSet, ByVal strTableName As String) As Integer '利用存储过程将数据库中的数据带到数据集
        Try
            If TestConnect() = True Then
                strcmd.Connection = SQLConnect
                strcmd.CommandType = CommandType.StoredProcedure
                SQLDA.SelectCommand = strcmd
                SQLConnect.Open()
                ds.Clear()
                SQLDA.SelectCommand.ExecuteNonQuery()
                SQLDA.Fill(ds, strTableName)
                Return 1
            Else
                'MessageBox.Show("数据库连接失败！")
                Return 0
            End If
        Catch ex As Exception
            'MessageBox.Show(ex.Message)
            Return -1
        Finally
            SQLConnect.Close()
        End Try
    End Function
    Public Function DBToDG(ByVal strcmd As String, ByVal dg As DataGrid, ByVal strTable As String) As Integer '将数据库中的数据带到数据表格
        Try
            If TestConnect() = True Then
                SQLCmd.CommandText = strcmd
                SQLDA.SelectCommand = SQLCmd
                SQLConnect.Open()
                SQLDS.Clear()
                SQLDA.SelectCommand.ExecuteNonQuery()
                SQLDA.Fill(SQLDS, strTable)
                dg.DataSource = SQLDS.Tables(strTable)
                Return 1
            Else
                'MessageBox.Show("数据库连接失败！")
                Return 0
            End If
        Catch ex As Exception
            'MessageBox.Show(ex.Message)
            Return -1
        Finally
            SQLConnect.Close()
        End Try
    End Function

    Public Function ResotreFile(ByVal CmdStr As String, ByVal FileName As String, ByVal FileType As String, ByVal FilePath As String, ByVal FileField As String) As Integer  '恢复数据库中的文件,期中FilePath为恢复制定路径,FileField表示存储文件的字段名
        Try


            Dim ds As DataSet = New DataSet("temp")
            If (FileType.Length <= 0) Then Return 0
            DBToDS(CmdStr, ds, "temp")
            Dim b As Byte()
            b = ds.Tables("temp").Rows(0)(FileField)
            If (b.Length > 0) Then

                Dim stream1 As System.IO.FileStream = New System.IO.FileStream(FilePath & FileName & "." & FileType, System.IO.FileMode.OpenOrCreate)
                stream1.Write(b, 0, b.Length)
                Return 1

            Else
                Return 0
            End If
        Catch ex As Exception
            'MsgBox(ex.Message)
            Return -1
        End Try

    End Function
    Public Function BackUpDB(ByVal SourceDBName As String, ByVal path As String) As Integer  '备份数据库，path备份完整的数据库路经
        Try
            SQLCommand("BACKUP DATABASE " & SourceDBName & " TO disk='" & path & ".bak'")
            Return 1
        Catch ex As Exception
            'MessageBox.Show(ex.Message)
            Return -1
        End Try
    End Function
    Public Function RestoreDB(ByVal SourceDBName As String, ByVal path As String) As Integer '还原数据库
        Try
            SQLCommand("RESTORE DATABASE " & SourceDBName & " FROM DISK = '" & path & "'")
            Return 1
        Catch ex As Exception
            Return -1
            'MsgBox(ex.Message)
        End Try
    End Function
  
End Class