Because process of backing up a database may take a few "lengthy" moments, Siccolo application is using asynchronous method call to a web service.
The code presented allows mobile device to backup a database on a remote SQL Server.
Title: Perform Backup SQL Server Database using Web Method/Web Service from Mobile Device Author: Greg Dubinovskiy Email: [email protected] Environment: VB.NET Keywords: Web Service, SQL Server, Backup Database Level: Intermediate Description: Perform Backup SQL Server Database using Web Method/Web Service from Mobile Device Section Miscellaneous SubSection General
One of the features available in Siccolo - Management Tool For SQL Server - ability to backup a database.
Idea is trivial - mobile device sends request to a web service and executes web method. Web method runs "backup" command on a remote SQL Server
to backup a database to/in a folder on that SQL Server:
Because process of backing up a database may take a few "lengthy" moments, Siccolo application is using asynchronous method call to a web service.
The code presented allows mobile device to backup a database on a remote SQL Server.
First, web method to backup a database on a SQL Server. For this we can use standard "backup database" command.
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 - instance of a class handling all database/SQL Server interactions
oLoginInfo - instance of a structure to hold SQL Server name, user credentials
UpdateIncomingStatus - method that writes to event log on a server hosting this web serviceBackupDatabase():
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
where BackupDatabaseCommand() simply builds a "backup command" string based on passed Database Name,
Backup File Name and Backup Name:
' 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
And that's it for a web method/web service.
Now the client.
User interface (form frmBackupDatabase, in my application):
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
Where objSQLManager - class on the client, handling all interactions with web service.
...
...
Private m_objUIBackupDatabaseForm As frmBackupDatabase
Friend WriteOnly Property BackupDatabaseForm() As frmBackupDatabase
Set(ByVal value As frmBackupDatabase)
m_objUIBackupDatabaseForm = value
End Set
End Property
And BackupDatabase_Async is the actual asynchronous method call.
'taken from Reference.vb:
...
_
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
'''
BeginBackupDatabase method signature also includes two additional parameters -
first of these defines an AsyncCallback delegate that references a method
BackupDatabase_Async_CallBack that is called
when the asynchronous operation completes:
Private Delegate Sub AsyncCallHandler_BackupDatabase(ByVal CallBackResult As Boolean, _
ByVal ErrorInfo As String)
The second additional parameter is a user-defined object.
This object can be used to pass application-specific state information to the method invoked
when the asynchronous operation completes.
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
BeginBackupDatabase()
BackupDatabase_Async_CallBack is executed
BackupDatabase_Async_CallBack passes control back to form via 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
And the form:
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
If you would like to read more on this story - please take a look at Siccolo - Free Mobile Management Tool For SQL Server
and more articles at Siccolo Articles
So, as we can see Web Services is quickly finding its way into major system integration development efforts.
In back office and legacy systems alike,
Web Servicess has become a main focus in providing mechanisms to make data move more freely between and across long-closed boundaries.
An entire industry has joined forces and pushes forward in overcoming both technical hurdles such as security and market specific hurdles such as the need of standardized message formats.
While these efforts continue, an entirely different market is rapidly maturing: the mobile devices, software and services market.
Very interesting solution options arise in the cross section, where Web Services meets mobility.
The Microsoft® Windows® .NET Compact Framework opens up the world of Web Services to Pocket PCs, and the wireless nature of Pocket PCs opens up the world of mobility to Web Services.
This means that new fuel is added to the expansion of the XML-based Web Services provider market.
In this article, by using simple Web Service Windows Mobile device is able to backup SQL Server database!
Questions? Suggestions? Concerns? - email me to [email protected]
Greg Dubinovsky © 2006