getting logical drives from server

  • I'm currently writing a small C# app and one of the things i want to do is to enumerate logical drives on a remote host. SSMS does this when you initiate a backup and browse to save the backup file. I'm currently doing it using WMI but surely there must be some .NET method of doing this, anybody know of anything?

    Regards

    Pez

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Hi Pez

    If you just need the available drives try this:

    using System;

    using System.Collections.Generic;

    using System.Data;

    //using System.Data.SqlClient;

    //using System.IO;

    using System.Linq;

    using System.Text;

    //using System.Net;

    using Microsoft.SqlServer.Management.Smo;

    using Microsoft.SqlServer.Management.Common;

    namespace ConsoleApplication1

    {

    class Program

    {

    static void Main(string[] args)

    {

    ServerConnection cn = new ServerConnection("(local)");

    cn.Connect();

    Server srv = new Server(cn);

    DataTable dt = srv.EnumAvailableMedia();

    foreach (DataRow row in dt.Rows)

    {

    Console.WriteLine("Name: {0} | LowFree: {1} | HighFree: {2} | MediaType: {3}",

    row["Name"],

    row["LowFree"],

    row["HighFree"],

    (MediaTypes)row["MediaTypes"]);

    }

    cn.Disconnect();

    }

    }

    }

    Greets

    Flo

  • i need to populate a treeview object with fixed drives and their folders

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Nice, Flo!

    I've been using SMO for years and I never did figure out what the SMO calls were that were using xp_fixeddrives. Now all we need to do is figure out the SMO calls that use xp_dirtree.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Perry Whittle (4/11/2009)


    i need to populate a treeview object with fixed drives and their folders

    Perry:

    The SQL procs that do that are the undocumented xp_fixeddrives and xp_dirtree. Flo has you halfway there because that call appears to enumerate the disks by calling xp_fixeddrives. Now all we have to do is figure how to get the folders.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Note that if people have mapped drives under the SQL service account, they might not show up with xp_fixeddrives.

    I don't think they should, since network access can be an issue with things like backup, but be aware if someone asks the question.

  • Hah, found it!

    Perry: you want the Server.EnumDirectories() method which calls xp_dirtree undet the hood.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Flo\Barry

    thanks a lot, i only need the drive name and then the folders underneath. I'll check out the items posted and see how i get on

    Regards

    Perry

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • You're welcome. I only did the start-up, Barry found the solution!

    I just came back from shopping and intended to start the .Net Reflector to play Sherlock Holmes... So I put my magnifier back to bureau :hehe:

    Greets

    Flo

  • hmm ok, the enumavailablemedia is pretty straight forward but i cant find any usage info for enumdirectories. Currently my treeview is populated with 2 logical drives and some folder entires but no folder name appears. Either of you guys have any usage info for enumdirectories

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • I apologize, but unfortunately, because of conflicts between VS 2005/.net 2.0 and VS 2008/.net 3.5, my SMO dev environment is down right now so I cannot effectively test anything.

    I have never used EnumDirectories (though I intend to real soon) however from BOL I get this:

    Public Function EnumDirectories ( _

    path As String _

    ) As DataTable

    So I would assume the something like this ought to work:

    dTable = srv.EnumDirectories("C:\")

    Does that not work for you?

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • RBarryYoung (4/13/2009)


    I apologize, but unfortunately, because of conflicts between VS 2005/.net 2.0 and VS 2008/.net 3.5, my SMO dev environment is down right now so I cannot effectively test anything.

    Hi Barry

    Hope your system is up soon! So let me be your remote debugger.

    Just tried and works:

    static void Main(string[] args)

    {

    ServerConnection cn = new ServerConnection("(local)");

    cn.Connect();

    Server srv = new Server(cn);

    DataTable dt = srv.EnumDirectories("C:\\");

    foreach (DataRow row in dt.Rows)

    {

    Console.WriteLine("Name: {0}", row["Name"]);

    }

    cn.Disconnect();

    }

    Greets

    Flo

  • hmm, why two backslashes ("\\"), Florian? Or is that just a C# thing?

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • RBarryYoung (4/13/2009)


    hmm, why two backslashes ("\\"), Florian? Or is that just a C# thing?

    It's a C(#) thing. Like in C or C++ you can specify special characters in strings. The characters are marked with "\" so you have to mask a backslash with an additional one. The other way is to quote the whole string with a @:

    VB:

    Dim str As String = "Hello" & vbCrLf & "World" & vbTab & "Anything"

    C#:

    string str = "Hello\rWorld\t";

    VB:

    Dim str As String = "C:\Temp\Anywhere"

    C#:

    string str = "C:\\Temp\\Anywhere";

    or

    string str = @"C:\Temp\Anywhere";

    Usually I use the doubled backslash except in long directory paths.

    Greets

    Flo

  • RBarryYoung (4/13/2009)


    I apologize, but unfortunately, because of conflicts between VS 2005/.net 2.0 and VS 2008/.net 3.5, my SMO dev environment is down right now so I cannot effectively test anything.

    I have never used EnumDirectories (though I intend to real soon) however from BOL I get this:

    Public Function EnumDirectories ( _

    path As String _

    ) As DataTable

    So I would assume the something like this ought to work:

    dTable = srv.EnumDirectories("C:\")

    Does that not work for you?

    do you not have to provide a depth level too though, i see the output from xp_dirtree. How does that relate to EnumDirectories

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply