Introduction
One of the features available in the Siccolo - Management Tool For SQL Server is the ability to backup a SQL Server database from a Windows Mobile device (see my previous article, Web Method to Backup Database Remotely, at Siccolo Articles). To make the backup process experience friendlier and more similar to that of the Microsoft SQL Server Enterprise Manager, I needed to allow user to select where - which directory - the actual database backup file is to be saved. But first, the users needs to select a disk:
In the code presented, the client (mobile device) retrieves the list of disks from a web service.
1. Server Code - Retrieve List of Disks
First, the web method to allow the client to retrieve the list of logical disks on the server:
<WebMethod()> Public Function LocalDisksList(ByVal ServerAddress As String, _
ByRef ErrorInfo As String) As String
Try
Dim DiskList_XML As String = ""
If DirectoryTree.GetFixedLocalDisks_XML(CType(User, WindowsPrincipal), _
ServerAddress, _
ToDebug, _
DiskList_XML, _
ErrorInfo) Then
ErrorInfo = ""
Return DiskList_XML
Else
Return ""
End If
Catch ex As Exception
ErrorInfo = ex.Message
Return ""
End Try
End Function
where GetFixedLocalDisks_XML() creates an XML string from a list of disks:
Public Shared Function GetFixedLocalDisks_XML(ByVal WebServiceUser As WindowsPrincipal, _
ByVal ServerName As String, _
ByVal ToDebug As String, _
ByRef ServerLocalDisksList_XML As String, _
ByRef ErrorInfo As String) As Boolean
Dim collection As ManagementObjectCollection
Dim impersonationContext As System.Security.Principal.WindowsImpersonationContext = _
CType(WebServiceUser.Identity, _
System.Security.Principal.WindowsIdentity).Impersonate()
If Not GetFixedLocalDisks(ServerName, _
ToDebug, _
collection, _
ErrorInfo) Then
ServerLocalDisksList_XML = ""
impersonationContext.Undo()
Return False
Else
Dim objDisks As DataSet = New DataSet("QueryResults")
objDisks.Tables.Add("LocalDisks")
objDisks.Tables(0).Columns.Add("disk_path", _
System.Type.GetType("System.String"))
Dim management_object As ManagementObject
For Each management_object In collection
Dim r As Object = New Object() {management_object("deviceID")}
objDisks.Tables(0).Rows.Add(r)
Next
Dim objStringWriter As New System.IO.StringWriter()
objDisks.WriteXml(objStringWriter, XmlWriteMode.WriteSchema)
ServerLocalDisksList_XML = "<?xml version='1.0' ?>" & objStringWriter.ToString()
impersonationContext.Undo()
Return True
End If
End Function
As you can notice, I'm using impersonationContext because the web service needs to "assume" the identity of an authenticated user to be able to access resources on the machine other than web service host. On the server side, to retrieve the authenticated user, we need to use System.Web.Services.WebService.User and then "impersonate" - Impersonate() - to "Impersonate the user represented by the WindowsIdentity object".
In this method, GetFixedLocalDisks_XML(), I'm using the DataSet class to create an XML string using the WriteXML() method:
...
Dim objDisks As DataSet = New DataSet("QueryResults")
...
...
Dim objStringWriter As New System.IO.StringWriter()
objDisks.WriteXml(objStringWriter, XmlWriteMode.WriteSchema)
...
where WriteXml() writes the current data, and the schema, for the DataSet using the specified System.IO.Stream and XmlWriteMode. XmlWriteMode.WriteSchema tells application to write the current contents of the DataSet as XML data, with the relational structure as the inline XSD schema.
The DataSet contains one table with one column:
- [disk_path] - the actual name (path) of a logical disk
To add/create columns in the DataSet:
...
objDisks.Tables(0).Columns.Add("disk_path", System.Type.GetType("System.String"))
...
Columns.Add() creates and adds a DataColumn object to the DataColumnCollection. Here, Add() creates and adds the DataColumn object with the specified name and type to the DataColumnCollection. To let Add() know the type of the column, we need to pass a parameter of type DataColumn.DataType. To do that, we can use the GetType() method which gets the Type with the specified name - System.Type.GetType("System.String") and GetFixedLocalDisks():
Private Shared Function GetFixedLocalDisks(ByVal ServerName As String, _
ByVal ToDebug As String, _
ByRef ServerLocalDisks As ManagementObjectCollection, _
ByRef ErrorInfo As String) As Boolean
Try
Dim query As ManagementObjectSearcher
Dim queryCollection As ManagementObjectCollection
Dim msc As ManagementScope = New ManagementScope("\\" & ServerName & "\root\cimv2")
Dim query_command As String = "SELECT * FROM Win32_LogicalDisk where MediaType=12 "
Dim select_query As SelectQuery = New SelectQuery(query_command)
query = New ManagementObjectSearcher(msc, select_query)
ServerLocalDisks = query.Get()
Return True
Catch ex As Exception
ServerLocalDisks = Nothing
ErrorInfo = ex.Message
Return False
End Try
End Function
(For more on what can be done with the Windows WMI - see Siccolo Articles).
2. Client Code - Display List of Disks
Friend Function PopulateLocalDiskList(ByVal cboListControl As Windows.Forms.ComboBox, _
ByVal SelectedDatabase As String, _
ByRef ErrorInfo As String) As Boolean
Try
ErrorInfo = ""
Dim DiskList_XML As String = ""
DiskList_XML = objSQLWebServiceManager.LocalDisksList(objLoginInfo.ServerAddress, _
ErrorInfo)
If ErrorInfo <> "" Or DiskList_XML = "" Then Return False
Dim objResponse As New DataSet()
Dim Reader As New System.IO.StringReader(DiskList_XML.ToString())
Dim XMLReader As New System.Xml.XmlTextReader(Reader)
XMLReader.Read()
objResponse.ReadXml(XMLReader, XmlReadMode.Auto)
objResponse.AcceptChanges()
XMLReader.Close()
cboListControl.DataSource = objResponse.Tables(0)
cboListControl.DisplayMember = "disk_path"
Return True
Catch ex As Exception
ErrorInfo = ex.Message
Me.SoundError()
If m_LogErrorToFile Then LogError("PopulateLocalDiskList():" & ex.ToString)
Return False
End Try
End Function
and on the screen:
Points of Interest
If you would like to read more on this story, please take a look at Siccolo - Free Mobile Management Tool For SQL Server and you can see more articles at Siccolo Articles.
