Web 服务方法，使用 SQL 备份命令和异步方法调用从移动设备备份数据库

引言

Siccolo - SQL Server 管理工具中提供的功能之一 - 备份数据库的能力。
想法很简单 - 移动设备向 Web 服务发送请求并执行 Web 方法。
Web 方法在远程 SQL Server 上运行“备份”命令，将数据库备份到该 SQL Server 上的文件夹中。

因为备份数据库的过程可能需要一些“漫长”的时间，所以 Siccolo 应用程序使用
对 Web 服务的异步方法调用。

提供的代码允许移动设备在远程 SQL Server 上备份数据库。

服务器代码

首先，是用于在 SQL Server 上备份数据库的 Web 方法。 为此，我们可以使用标准的“备份数据库”命令。

    <webmethod() /> Public Function BackupDatabase(ByVal ServerAddress As String, _
                    ByVal UserName As String, _
                                        ByVal UserPassword As String, _
                                        ByVal DatabaseName As String, _
                                        ByVal BackupFileName As String, _
                                        ByVal BackupName As String, _
                                        ByRef ErrorInfo As String) As Boolean

            Try
                With oLoginInfo
                    .sServer = ServerAddress
                    .sLoginName = UserName
                    .sPassword = UserPassword
                    .sDatabase = ""
                End With

                Dim ToDebugSetting As String = _ 
            System.Configuration.ConfigurationSettings.AppSettings.Get("DebugMode")
                Dim ToDebug As Boolean = (ToDebugSetting <> "")

                If oCon.BackupDatabase(oLoginInfo, _
                                        DatabaseName, _
                                        BackupFileName, _
                                        BackupName, _
                                        ToDebug, _
                                        ErrorInfo) Then

                    Return True
                Else
                    If ToDebug Then
                        oCon.UpdateIncomingStatus("BackupDatabase: failed" & ErrorInfo, EventLogEntryType.Information)
                    End If
                    Return False
                End If

            Catch ex As Exception
                ErrorInfo = ex.Message()
                Return False
            End Try
        End Function

其中
• oCon - 处理所有数据库/SQL Server 交互的类实例
• oLoginInfo - 用于保存 SQL Server 名称、用户凭据的结构实例
• UpdateIncomingStatus - 将内容写入到托管此 Web 服务的服务器上的事件日志的方法

Web 方法本身调用 BackupDatabase()

    Public Function BackupDatabase(ByVal oLogInf As LoginInfo, _
                                    ByVal DatabaseName As String, _
                                    ByVal BackupFileName As String, _
                                    ByVal BackupName As String, _
                                    ByVal ToDebug As Boolean, _
                                    ByRef ErrorInfo As String) As Boolean
            Try
                oLoginInfo = oLogInf

                Dim SqlCommand = BackupDatabaseCommand(DatabaseName, BackupFileName, BackupName)

                If (objConnection.State.ToString() <> "Open") Then
                    Connect(ToDebug, ErrorInfo)
                End If

                Dim objCommand As SqlCommand = New SqlCommand(SqlCommand, objConnection)

                objCommand.CommandType = CommandType.Text
                objCommand.CommandTimeout = 600    '600 seconds = 10 min. 
                        'The time (in seconds) to wait for the command to execute. 
                        'The default is 30 seconds.
                        'A value of 0 indicates no limit


                objCommand.ExecuteNonQuery()

                DisConnect()

                Return True

            Catch ex As Exception
                ErrorInfo = ex.Message
                Return False
            End Try

        End Function

其中 BackupDatabaseCommand() 只是构建一个“备份命令”字符串
基于传入的数据库名称、备份文件名和备份名称

    ' VB.NET 
    Private Function BackupDatabaseCommand(ByVal DatabaseName As String, _
                                                ByVal BackupFileName As String, _
                                                ByVal BackupName As String) As String

            Dim strBackupCommand As String = "Backup Database [" & DatabaseName & "]" & _
                                "TO DISK = N'" & BackupFileName & "'" & _
                                "WITH INIT " & _
                                ", NAME = '" & BackupName & "'" & _
                                ", NOSKIP" & _
                                ", NOFORMAT"

            Return strBackupCommand
            'INIT
            '   Specifies that all backup sets should be overwritten, but preserves the media header. 
            '   If INIT is specified, any existing backup set data on that device is overwritten.
            '
            'NAME = backup_set_name
            '   Specifies the name of the backup set. Names can have a maximum of 128 characters. 
            '   If NAME is not specified, it is blank.
            'Siccolo passes something like this:
            '   DatabaseName + "_SiccoloBackup_" + System.DateTime.Now.ToString("MM_dd_yyyy") 

            'NOSKIP
            '   Instructs the BACKUP statement to check the expiration date of all backup sets on the media before 
            '   allowing them to be overwritten.
            'NOFORMAT
            '   Specifies the media header should not be written on all volumes used for this backup operation 
            '   and does not rewrite the backup device unless INIT is specified.
        End Function

这就是 Web 方法/Web 服务的全部内容。

---

客户端代码

现在是客户端。
用户界面 (我的应用程序中的窗体 frmBackupDatabase)

   Private Sub PerformBackup_Async(ByVal DatabaseName As String, _
                                    ByVal BackupFileName As String, _
                                    ByVal BackupName As String)

        Cursor.Current = Cursors.WaitCursor

        Dim ErrorInfo As String = ""

        objSQLManager.BackupDatabaseForm = Me            'sets which form to invoke

    'call class handling interactions with web service:
        objSQLManager.BackupDatabase_Async(DatabaseName, _    
                                            BackupFileName, _
                                            BackupName, _
                                            ErrorInfo)
    End Sub

其中 objSQLManager - 客户端上的类，处理与 Web 服务的所有交互。

    ...
    ...
    Private m_objUIBackupDatabaseForm As frmBackupDatabase
    
    Friend WriteOnly Property BackupDatabaseForm() As frmBackupDatabase
        Set(ByVal value As frmBackupDatabase)
            m_objUIBackupDatabaseForm = value
        End Set
    End Property

并且 BackupDatabase_Async 是实际的异步方法调用。

备份数据库异步操作实现为两个名为 BeginBackupDatabase 的方法，以及
EndBackupDatabase，它们分别启动和结束异步操作 BackupDatabase。
BeginBackupDatabase 方法采用与该方法的同步版本签名中声明的参数一样多的参数
这些参数按值或按引用传递

    'taken from Reference.vb:
        ...
    <system.web.services.protocols.soapdocumentmethodattribute
("http:parameterstyle:="System.Web.Services.Protocols.SoapParameterStyle.Wrapped)" 
use:="System.Web.Services.Description.SoapBindingUse.Literal,"
 responsenamespace:="http://tempuri.org/" 
requestnamespace:="http://tempuri.org/" />  _
        Public Function BackupDatabase(ByVal ServerAddress As String, _
                        ByVal UserName As String, _
                        ByVal UserPassword As String, _
                        ByVal DatabaseName As String, _
                        ByVal BackupFileName As String, _
                        ByVal BackupName As String, _
                        ByRef ErrorInfo As String) As Boolean
            Dim results() As Object = Me.Invoke("BackupDatabase", _
                        New Object() {ServerAddress, _
                                UserName, _
                                UserPassword, _
                                DatabaseName, _
                                BackupFileName, _
                                BackupName, _
                                ErrorInfo})
            ErrorInfo = CType(results(1),String)
            Return CType(results(0),Boolean)
        End Function
        
        '''<remarks />
        Public Function BeginBackupDatabase(ByVal ServerAddress As String, _
                        ByVal UserName As String, _
                        ByVal UserPassword As String, _
                        ByVal DatabaseName As String, _
                        ByVal BackupFileName As String, _
                        ByVal BackupName As String, _
                        ByVal ErrorInfo As String, _
                        ByVal callback As System.AsyncCallback, _
                        ByVal asyncState As Object) As System.IAsyncResult
            Return Me.BeginInvoke("BackupDatabase", _
                    New Object() {ServerAddress, _
                            UserName, _
                            UserPassword, _
                            DatabaseName, _
                            BackupFileName, _
                            BackupName, _
                            ErrorInfo}, _
                    callback, _
                    asyncState)
        End Function
        
        '''<remarks />
        Public Function EndBackupDatabase(ByVal asyncResult As System.IAsyncResult, _
                        ByRef ErrorInfo As String) As Boolean
            Dim results() As Object = Me.EndInvoke(asyncResult)
            ErrorInfo = CType(results(1),String)
            Return CType(results(0),Boolean)
        End Function
        ...

BeginBackupDatabase 方法签名还包括两个附加参数 - 其中第一个参数定义了一个
AsyncCallback 委托，该委托引用一个方法 BackupDatabase_Async_CallBack，该方法在异步操作完成时被调用
操作完成

Private Delegate Sub AsyncCallHandler_BackupDatabase(ByVal CallBackResult As Boolean, _
                                                     ByVal ErrorInfo As String)

第二个附加参数是一个用户定义的对象。 此对象可用于将特定于应用程序的状态信息传递
到异步操作完成时调用的方法。
BeginBackupDatabase 立即将控制权返回给调用线程 frmBackupDatabase。 如果 BeginBackupDatabase
方法抛出异常，则在异步操作开始之前抛出异常。
并且如果 BeginBackupDatabase 方法抛出异常，则不会调用回调方法。

    Friend Sub  BackupDatabase_Async(ByVal DatabaseName As String, _
                                    ByVal BackupFileName As String, _
                                    ByVal BackupName As String, _
                                    ByRef ErrorInfo As String)

        Try

            If m_objUIBackupDatabaseForm Is Nothing Then
                Throw New Exception("User Interface Form is not set!")
            End If

            ErrorInfo = ""
        
        'm_objSiccoloProcessorAsync - reference to a web method
            m_objSiccoloProcessorAsync.Timeout = System.Threading.Timeout.Infinite
            

            m_objSiccoloProcessorAsync.BeginBackupDatabase(objLoginInfo.ServerAddress, _
                                                objLoginInfo.UserName, _
                                                objLoginInfo.UserPassword, _
                                                DatabaseName, _
                                                BackupFileName, _
                                                BackupName, _
                                                ErrorInfo, _
                                                New AsyncCallback(AddressOf Me.BackupDatabase_Async_CallBack), _
                                                Nothing)

        Catch ex As Exception
            ErrorInfo = ex.Message

            m_objUIBackupDatabaseForm.Invoke( _
            New AsyncCallHandler_BackupDatabase _
                            (AddressOf m_objUIBackupDatabaseForm.PerformBackupDatabase_Async_CallBack), _
                        False, _
                        ErrorInfo)

        End Try
    End Sub

事件序列
1. 使用 BeginBackupDatabase() 启动异步调用
2. 执行 BackupDatabase_Async_CallBack
3. BackupDatabase_Async_CallBack 通过 Invoke() 将控制权返回到窗体

    Private Sub BackupDatabase_Async_CallBack(ByVal result As IAsyncResult)
        Try
            Dim ErrorInfo As String = ""

            Dim CallBackResult As Boolean = True

            CallBackResult = m_objSiccoloProcessorAsync.EndBackupDatabase(result, _
                                                                           ErrorInfo)

            m_objUIBackupDatabaseForm.Invoke( _
            New AsyncCallHandler_BackupDatabase _
                                   (AddressOf m_objUIBackupDatabaseForm.PerformBackupDatabase_Async_CallBack), _
                        CallBackResult, _
                        ErrorInfo)

        Catch ex_callback As Exception

            m_objUIBackupDatabaseForm.Invoke( _ 
            New AsyncCallHandler_BackupDatabase _
                            (AddressOf m_objUIBackupDatabaseForm.PerformBackupDatabase_Async_CallBack), _
                        False, _
                        "BackupDatabase_Async_CallBack(): " & ex_callback.Message)
        End Try
    End Sub

以及窗体

   Friend Sub PerformBackupDatabase_Async_CallBack _
                                        (ByVal CallBackResult As Boolean, _
                                        ByVal ErrorInfo As String)

        Try

            If Not CallBackResult Then
                Throw New Exception(ErrorInfo)
            End If

            MessageBox.Show("Backup completed (async)" & vbCrLf, _
                         "Siccolo - Backup Database", _
                        MessageBoxButtons.OK, _
                        MessageBoxIcon.Asterisk, _
                        MessageBoxDefaultButton.Button1)

        Catch ex As Exception

            MessageBox.Show("Failed to perform database backup (async):" & vbCrLf & _
                            "-----------------------------------" & vbCrLf & _
                           ErrorInfo & vbCrLf & _
                           "-----------------------------------", _
                            "Siccolo - Backup Database", _
                           MessageBoxButtons.OK, _
                           MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1)

        Finally

            Cursor.Current = Cursors.Default

        End Try
    End Sub

关注点

如果您想了解更多关于此故事的信息 - 请查看 Siccolo - 免费 SQL Server 移动管理工具和
Siccolo 文章中的更多文章