accesstodatabase.vb

单机版餐饮服务系统,结合了SqlServer数据库

Imports System
Imports SQLDMO
Imports System.IO
Imports System.Data
Imports System.DBNull
Imports System.Data.SqlClient

Namespace HZC.DinningService

    Public Class AccessToDatabase

        Private Shared database_UserName As String = String.Empty
        Private Shared database_Password As String = String.Empty
        Private Shared database_Name As String = String.Empty
        Private Shared database_Provider As String = String.Empty
        Private Shared StartupPath As String = String.Empty
        Public Shared objDataSet As DataSet
        '// 定义一个数据集对象
        Public Shared objDataView As DataView
        '// 定义一个数据视图对象
        Public Shared objDataAdpter As SqlDataAdapter
        '// 定义一个数据适配器对象
        Public Shared objSqlConnection As SqlConnection 
        '// 定义一个数据连接对象，并初始化
        Private Shared SqlQueryString As String = String.Empty

        Public Shared Property GetStartupPath() As String
            Get
                Return StartupPath
            End Get
            Set(ByVal Value As String)
                StartupPath = Value
            End Set
        End Property

        Public Shared Function InitializeSqlServer( _
                               ByVal fileName As String _
                               ) As String
            '// 如果配置文件不存在，则创建一个新的配置文件
            '// 否则则从配置文件中读取相关参数
            '// 来初始化数据库的配置参数
            If Not System.IO.File.Exists(fileName) Then
                '// 如下为默认参数，用户可以修改为自己的默认参数
                database_UserName = "sa"
                database_Password = "sa"
                database_Name = "FoodSvc"
                database_Provider = "TSINGHUA-HZC"
                Dim ws As StreamWriter = File.CreateText(fileName)
                ws.AutoFlush = True
                ws.WriteLine("<DataBase_Provider>=" & database_Provider)
                ws.WriteLine("<DataBase_Name>=" & database_Name)
                ws.WriteLine("<DataBase_UserID>=" & database_UserName)
                ws.WriteLine("<DataBase_Password>=" & database_Password)
                ws.WriteLine("<StartupPath>=" & StartupPath)
                ws.Close()
            Else
                Dim ws As StreamReader = File.OpenText(fileName)
                Dim Reads As String = String.Empty
                Do While ws.Peek > 0
                    Reads = ws.ReadLine()
                    If Not Reads = String.Empty Then
                        Dim Parms As String()
                        Parms = Reads.Split("=")
                        If Parms.Length >= 1 Then
                            Select Case Parms(0).Trim
                                Case "<DataBase_Provider>"
                                    database_Provider = Parms(1).Trim
                                Case "<DataBase_Name>"
                                    database_Name = Parms(1).Trim
                                Case "<DataBase_UserID>"
                                    database_UserName = Parms(1).Trim
                                Case "<DataBase_Password>"
                                    database_Password = Parms(1).Trim
                                Case "<StartupPath>"
                                    StartupPath = Parms(1).Trim
                            End Select
                        End If
                    End If
                Loop
                ws.Close()
            End If
            SqlQueryString = "data source=" & database_Provider & _
            ";initial catalog=" & database_Name & ";user id= " _
             & database_UserName & ";password=" & database_Password
            Return SqlQueryString
        End Function

        '// 连接数据库
        Public Shared Function ConnectToDataBase() As Boolean
            If objSqlConnection Is Nothing Then
                If Not SqlQueryString = String.Empty Then
                    Try
                        objSqlConnection = New SqlConnection(SqlQueryString)
                        If objSqlConnection.State <> ConnectionState.Open Then
                            objSqlConnection.Open()
                        End If
                        Return True
                    Catch ex As Exception
                        Return False
                    End Try
                Else
                    Return False
                End If
            Else
                Return True
            End If
        End Function

        '// 断开数据库连接
        Public Shared Function DisConnectToDataBase() As Boolean
            If objSqlConnection Is Nothing Then
                Return True
            Else
                Try
                    If objSqlConnection.State <> ConnectionState.Closed Then
                        objSqlConnection.Close()
                        Return True
                    End If
                Catch ex As Exception
                    Return False
                End Try
            End If
        End Function

        '// 定义了查询语句
        Public Shared Function GetDataFromDB(ByVal sqlstr As String) As DataSet
            objDataSet = New DataSet
            objDataSet.Clear()
            '// 实例化一个数据集对象
            Try
                If objSqlConnection.State <> ConnectionState.Open Then
                    objSqlConnection.Open()
                    '// 如果数据库没有打开则打开数据库连接
                End If
                objDataAdpter = New SqlDataAdapter(sqlstr, objSqlConnection)
                '// 将数据库的数据映射到数据适配器
                objDataAdpter.Fill(objDataSet)
                '// 填充数据集的数据
                objSqlConnection.Close()
                '// 关闭数据连接
            Catch
                MsgBox("错误号：" & Err.Number & "错误描述：" & Err.Description)
            End Try
            Return objDataSet
        End Function

        Public Shared Function UpdateData(ByVal sqlstr As String) As Boolean
            Dim objCommand As SqlCommand = New SqlCommand
            Try
                If objSqlConnection.State <> ConnectionState.Open Then
                    objSqlConnection.Open()
                End If
                objCommand.Connection = objSqlConnection
                objCommand.CommandText = sqlstr
                objCommand.CommandType = CommandType.Text
                objCommand.ExecuteNonQuery()
                objSqlConnection.Close()
            Catch
                MsgBox("错误号：" & Err.Number & "错误描述：" & Err.Description)
                Return False
            End Try
            Return True
        End Function

        '// 数据库备份，将数据库备份到指定的文件
        Public Shared Function BackupDataBase(ByVal FilePath As String) As Boolean
            Try
                SqlQueryString = "BACKUP DATABASE FoodSvc TO DISK= '" & FilePath & "'"
                If Not UpdateData(SqlQueryString) Then
                    Return False
                End If
            Catch ex As Exception
                Return False
            End Try
            Return True
        End Function

        Public Shared Function RestoreDataBase(ByVal FilePath As String) As Boolean
            '// 定义一个新的 SQLDMO.SQLServer 对象
            Dim Server As New SQLDMO.SQLServer
            '// 定义一个新的 SQLDMO.RestoreClass 对象
            Dim Restore As New SQLDMO.RestoreClass
            Try
                '// 初始化连接服务器对象
                Server.Connect(database_Provider, database_UserName, database_Password)
                '// 为了不让其它进程访问指定要还原的数据库，
                '// 需要杀死所有的访问进程

                '// 定义SQLDMO 对象的查询表
                Dim qr As SQLDMO.QueryResults
                '// 获取查询表
                qr = Server.EnumProcesses(-1)
                Dim SPIDs As Integer = -1
                Dim DBNMs As Integer = -1
                Dim Index As Integer
                For Index = 1 To qr.Columns
                    Dim tempName As String = qr.ColumnName(Index)
                    If tempName.ToUpper.Trim = "SPID" Then
                        SPIDs = Index
                    ElseIf tempName.ToUpper.Trim = "DBNAME" Then
                        DBNMs = Index
                    End If
                    If SPIDs <> -1 And DBNMs <> -1 Then
                        Exit For
                    End If
                Next
                For Index = 1 To qr.Rows
                    '// 获取进程 ID 号
                    Dim IPID As Integer = qr.GetColumnLong(Index, SPIDs)
                    '// 获取数据库名称
                    Dim DBName As String = qr.GetColumnString(Index, DBNMs)
                    '// 如果该名称与需要还原的数据库名称一致
                    If DBName.ToUpper.Equals("FoodSvc".ToUpper) Then
                        '// 则杀死所有的访问进程
                        Server.KillProcess(IPID)
                    End If
                Next
                '// 指定需要还原的类型为数据库
                Restore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database
                '// 指定要还原的数据库名称
                Restore.Database = "FoodSvc"
                '// 是否覆盖原来的数据库
                Restore.ReplaceDatabase = True
                '// 指定还原的数据库文件路径以及文件数
                Restore.Files = FilePath
                Restore.FileNumber = 1
                '// 开始还原
                Restore.SQLRestore(Server)
                Return True
            Catch ex As Exception
                MsgBox(ex.Message)
                Server.DisConnect()
                Return False
            End Try
        End Function

        '// 压缩数据库，是否截断数据库的日志文件
        Public Shared Function ShrinkDataBase(ByVal IsTruncate As Boolean) As Boolean
            Try
                If IsTruncate Then
                    '// 如果截断，则执行如下事务
                    SqlQueryString = "BACKUP LOG FoodSvc WITH TRUNCATE_ONLY"
                Else
                    '// 如果不截断，则压缩数据库
                    SqlQueryString = "DBCC SHRINKDATABASE (FoodSvc)"
                End If
                Return UpdateData(SqlQueryString)
            Catch ex As Exception
                Return False
            End Try
            Return True
        End Function

    End Class

End Namespace