Introduction
I have looked everywhere to get either some direction or help on how to get the lists of folders and drives of a computer installed with Microsoft SQL Server and/or MSDE from a Windows based application on a remote computer connecting to the server. I have finally drawn a solution to accomplish this task. Most of the code is self-explanatory and documented, so I will leave the detailed explanation out.
To kick things off, first we need to get connected to the Master database of the server, since we will be using some Stored Procedure that only exists in the Master database.
Let's dissect the 'SQLServerFolderBrowse class' a bit. These are some un-documented procedures, and the names are self-explanatory:
xp_fixeddrivesxp_availablemediaxp_subdirs
I have created some properties to set the connection property so that the class structure is maintained. These properties are set in the function fxnConnection; however, I would recommend that you apply the properties when calling the dialog from your calling form.
There are four functions in this class that carry out the entire work of getting the drives, folders, and sub-folders, namely:
GetFixedDrives - gets the installed drive letters on the MS SQL computer.GetAvailableDrives - gets all the drives on which the writing can be carried out.GetSubFolders - this function requires a parameter, parent, and as the name specifies is used to get the folders in the parent.InitializeConnection - checks if the connection string is not empty, and sets the connection string otherwise.
We have completed the algorithm of getting the required solution. Now, we need an interface between the program and the user. I have created a dialog box that mimics the Windows folder browser. Please look through the code for a complete documentation. Briefly, the code-behind is divided into regions for readability. In the function region, you set the parameters to connect to the master database of the server in the function fxnConnect. However, I recommend that you use the dialog browser object to set these properties. In the function region, the treeview is filled with the list of drives, folders, and sub-folders. Folders that contain the sub folders are assigned a subfolder 'Dummy' to increase the performance of the control. Once the user expands a folder, the sub-folders are populated under the parent folder. Now, your interface is created and you are ready to use the dialog. See 'frmTest' on how to use the control.
A timer is used to check if the dialog box has been closed to fill the appropriate control on the calling windows. The browser dialog can be opened as modal, but for MSI projects, you may have to use the mentioned procedure and provide a parameter indicating the calling form with the initializing dialog form (Me). Please browse through the code, and if you find any difficulty, please feel free to contact me.
