Tom’s Blog

January 2, 2010

Using Extension Methods and the Win32 API to Efficiently Enumerate the File System

Filed under: .NET,C#,P/Invoke,Uncategorized — Tom Shelton @ 11:52 am

I’ve seen the question come up a couple of times lately on various forums, asking how to get only the first file from a directory listing in a directory with a large number of files.   This is problematic, because as of .NET 3.5 the System.IO methods that are responsible for enumerating the file system (System.IO.Directory.GetFiles for example) do a complete enumeration before returning.  That means if you just want the first 5 files, GetFiles will still enumerate the entire directory before returning.  Further, wouldn’t it be nice to have methods where you could use LINQ queries on file attributes, etc?  If your paying attention to .NET 4.0, you will notice that there are methods now of the Directory/DirectoryInfo classes that will provide just these options – System.Directory.EnumerateFiles, etc.  These methods provide IEnumerable<> return values rather then the fixed array.  Basically, they enumerate and return each value one at a time.

The good news is that with C# 3.0 – we don’t actually have to wait until .NET 4.0 to have these methods now.  By using a little P/Invoke magic and C# 3.0’s extension method capability, we can write code like this today:

   1: class Program
   2: {
   3:     static void Main ( string[] args )
   4:     {
   5:         string path = Environment.GetFolderPath ( Environment.SpecialFolder.MyDocuments );
   6:         DirectoryInfo root = new DirectoryInfo ( path );
   7:
   8:         Console.WriteLine ( root.EnumerateFiles ().First ().Name );
   9:     }
  10: }

Or even better, we can do stuff like this:

   1: class Program
   2: {
   3:    static void Main ( string[] args )
   4:    {
   5:        string path = Environment.GetFolderPath ( Environment.SpecialFolder.MyDocuments );
   6:        DirectoryInfo root = new DirectoryInfo ( path );
   7:
   8:        // get all doc files created before Nov. 1st 2009
   9:        var fileQuery = from fileInfo in root.EnumerateFiles ( "*.doc" )
  10:                        where fileInfo.CreationTime.Date.CompareTo ( new DateTime ( 2009, 11, 1 ) ) == -1
  11:                        select fileInfo;
  12:
  13:        foreach ( var fileInfo in fileQuery )
  14:            Console.WriteLine ( fileInfo.Name );
  15:    }
  16: }

To handle the actual enumeration of the files we will use the Windows API.  The functions we are interested in are FindFirstFile, FindNextFile, and FindClose.  Here are the Native Win32 calls and classes used by the demo extension methods above:

   1: using System;
   2: using System.Collections.Generic;
   3: using System.Text;
   4: using System.IO;
   5: using System.Runtime.InteropServices;
   6: using Microsoft.Win32.SafeHandles;
   7:
   8: namespace FireAnt.IO
   9: {
  10:     internal static class NativeWin32
  11:     {
  12:         public const int MAX_PATH = 260;
  13:
  14:         /// <summary>
  15:         /// Win32 FILETIME structure.  The win32 documentation says this:
  16:         /// "Contains a 64-bit value representing the number of 100-nanosecond intervals since January 1, 1601 (UTC)."
  17:         /// </summary>
  18:         /// <see cref="http://msdn.microsoft.com/en-us/library/ms724284%28VS.85%29.aspx"/>
  19:         [StructLayout ( LayoutKind.Sequential )]
  20:         public struct FILETIME
  21:         {
  22:             public uint dwLowDateTime;
  23:             public uint dwHighDateTime;
  24:         }
  25:
  26:         /// <summary>
  27:         /// The Win32 find data structure.  The documentation says:
  28:         /// "Contains information about the file that is found by the FindFirstFile, FindFirstFileEx, or FindNextFile function."
  29:         /// </summary>
  30:         /// <see cref="http://msdn.microsoft.com/en-us/library/aa365740%28VS.85%29.aspx"/>
  31:         [StructLayout(LayoutKind.Sequential, CharSet=CharSet.Auto)]
  32:         public struct WIN32_FIND_DATA
  33:         {
  34:             public FileAttributes dwFileAttributes;
  35:             public FILETIME ftCreationTime;
  36:             public FILETIME ftLastAccessTime;
  37:             public FILETIME ftLastWriteTime;
  38:             public uint nFileSizeHigh;
  39:             public uint nFileSizeLow;
  40:             public uint dwReserved0;
  41:             public uint dwReserved1;
  42:
  43:             [MarshalAs(UnmanagedType.ByValTStr, SizeConst=MAX_PATH)]
  44:             public string cFileName;
  45:
  46:             [MarshalAs ( UnmanagedType.ByValTStr, SizeConst=14)]
  47:             public string cAlternateFileName;
  48:         }
  49:
  50:         /// <summary>
  51:         /// Searches a directory for a file or subdirectory with a name that matches a specific name (or partial name if wildcards are used).
  52:         /// </summary>
  53:         /// <param name="lpFileName">The directory or path, and the file name, which can include wildcard characters, for example, an asterisk (*) or a question mark (?). </param>
  54:         /// <param name="lpFindData">A pointer to the WIN32_FIND_DATA structure that receives information about a found file or directory.</param>
  55:         /// <returns>
  56:         /// If the function succeeds, the return value is a search handle used in a subsequent call to FindNextFile or FindClose, and the lpFindFileData parameter contains information about the first file or directory found.
  57:         /// If the function fails or fails to locate files from the search string in the lpFileName parameter, the return value is INVALID_HANDLE_VALUE and the contents of lpFindFileData are indeterminate.
  58:         ///</returns>
  59:         ///<see cref="http://msdn.microsoft.com/en-us/library/aa364418%28VS.85%29.aspx"/>
  60:         [DllImport("kernel32", CharSet=CharSet.Auto, SetLastError=true)]
  61:         public static extern SafeSearchHandle FindFirstFile ( string lpFileName, out WIN32_FIND_DATA lpFindData );
  62:
  63:         /// <summary>
  64:         /// Continues a file search from a previous call to the FindFirstFile or FindFirstFileEx function.
  65:         /// </summary>
  66:         /// <param name="hFindFile">The search handle returned by a previous call to the FindFirstFile or FindFirstFileEx function.</param>
  67:         /// <param name="lpFindData">A pointer to the WIN32_FIND_DATA structure that receives information about the found file or subdirectory.
  68:         /// The structure can be used in subsequent calls to FindNextFile to indicate from which file to continue the search.
  69:         /// </param>
  70:         /// <returns>
  71:         /// If the function succeeds, the return value is nonzero and the lpFindFileData parameter contains information about the next file or directory found.
  72:         /// If the function fails, the return value is zero and the contents of lpFindFileData are indeterminate.
  73:         /// </returns>
  74:         /// <see cref="http://msdn.microsoft.com/en-us/library/aa364428%28VS.85%29.aspx"/>
  75:         [DllImport("kernel32", CharSet=CharSet.Auto, SetLastError=true)]
  76:         public static extern bool FindNextFile ( SafeSearchHandle hFindFile, out WIN32_FIND_DATA lpFindData );
  77:
  78:         /// <summary>
  79:         /// Closes a file search handle opened by the FindFirstFile, FindFirstFileEx, or FindFirstStreamW function.
  80:         /// </summary>
  81:         /// <param name="hFindFile">The file search handle.</param>
  82:         /// <returns>
  83:         /// If the function succeeds, the return value is nonzero.
  84:         /// If the function fails, the return value is zero. 
  85:         /// </returns>
  86:         /// <see cref="http://msdn.microsoft.com/en-us/library/aa364413%28VS.85%29.aspx"/>
  87:         [DllImport("kernel32", SetLastError=true)]
  88:         public static extern bool FindClose ( IntPtr hFindFile );
  89:
  90:         /// <summary>
  91:         /// Class to encapsulate a seach handle returned from FindFirstFile.  Using a wrapper
  92:         /// like this ensures that the handle is properly cleaned up with FindClose.
  93:         /// </summary>
  94:         public class SafeSearchHandle : SafeHandleZeroOrMinusOneIsInvalid
  95:         {
  96:             public SafeSearchHandle () : base ( true ) { }
  97:
  98:             protected override bool ReleaseHandle ()
  99:             {
 100:                 return NativeWin32.FindClose ( base.handle );
 101:             }
 102:         }
 103:     }
 104: }

And here are the actual extension methods:

   1: using System;
   2: using System.Collections.Generic;
   3: using System.Text;
   4: using System.IO;
   5:
   6: namespace FireAnt.IO
   7: {
   8:     /// <summary>
   9:     /// Static class to contain extension methods
  10:     /// </summary>
  11:     public static class FileSystemExtensions
  12:     {
  13:
  14:         public static IEnumerable<DirectoryInfo> EnumerateDirectories ( this DirectoryInfo target )
  15:         {
  16:             return EnumerateDirectories ( target, "*" );
  17:         }
  18:
  19:         public static IEnumerable<DirectoryInfo> EnumerateDirectories ( this DirectoryInfo target, string searchPattern )
  20:         {
  21:             string searchPath = Path.Combine ( target.FullName, searchPattern );
  22:             NativeWin32.WIN32_FIND_DATA findData;
  23:             using (NativeWin32.SafeSearchHandle hFindFile = NativeWin32.FindFirstFile ( searchPath, out findData ))
  24:             {
  25:                 if ( !hFindFile.IsInvalid )
  26:                 {
  27:                     do
  28:                     {
  29:                         if ( ( findData.dwFileAttributes & FileAttributes.Directory ) != 0 && findData.cFileName != "." && findData.cFileName != ".." )
  30:                         {
  31:                             yield return new DirectoryInfo ( Path.Combine ( target.FullName, findData.cFileName ) );
  32:                         }
  33:                     } while ( NativeWin32.FindNextFile ( hFindFile, out findData ) );
  34:                 }
  35:             }
  36:
  37:         }
  38:
  39:         public static IEnumerable<FileInfo> EnumerateFiles ( this DirectoryInfo target )
  40:         {
  41:            return EnumerateFiles ( target, "*" );
  42:         }
  43:
  44:         public static IEnumerable<FileInfo> EnumerateFiles ( this DirectoryInfo target, string searchPattern )
  45:         {
  46:             string searchPath = Path.Combine ( target.FullName, searchPattern );
  47:             NativeWin32.WIN32_FIND_DATA findData;
  48:             using ( NativeWin32.SafeSearchHandle hFindFile = NativeWin32.FindFirstFile ( searchPath, out findData ) )
  49:             {
  50:                 if ( !hFindFile.IsInvalid )
  51:                 {
  52:                     do
  53:                     {
  54:                         if ( ( findData.dwFileAttributes & FileAttributes.Directory ) == 0 && findData.cFileName != "." && findData.cFileName != ".." )
  55:                         {
  56:                             yield return new FileInfo ( Path.Combine ( target.FullName, findData.cFileName ) );
  57:                         }
  58:                     } while ( NativeWin32.FindNextFile ( hFindFile, out findData ) );
  59:                 }
  60:             }
  61:
  62:         }
  63:     }
  64: }

With a bit more work, the full range of file search operations could be added to these extension methods – for instance supporting the SearchOptions overloads.  I hope this demo illustrates the power of Extension methods and C# iterators ( a feature introduced in C# 2.0).

Article Source Code.

October 7, 2009

Creating a Simple Hotkey Component using RegisterHotKey

Filed under: .NET,C#,P/Invoke,Visual Basic — Tom Shelton @ 12:47 pm

A while ago, I needed to create a simple tray application that had to be able be brought into focus using a global hotkey.  I did a bit of research and came across the RegisterHotKey API function.  On my first attempt at using it, I created a simple class that I used to subclass any window handle that was passed into the constructor.  It  worked well – until you wanted to have more then one hotkey and it seemed to me it might be more user friendly as a component then as a class.  So, I rewrote the code as a windows forms component – and in the process I even created a custom type editor for the HotKey.Modifiers property.

Anyway, I’m posting two projects – the first is the HotKeyLib project that contains the Hotkey component.  The second is a project (both C# and VB.NET) that make use of the component.  You will need to compile the HotKeyLib project and then add it to your IDE’s toolbox to compile the second project.  The reason I did it this way was to show that the custom icon of the hotkey component will indeed show up in the IDE toolbox.

Anyway, hope this code proves helpful.

August 17, 2009

ByVal vs ByRef – What is the Difference?

Filed under: General,Visual Basic — Tom Shelton @ 10:44 pm

Over the years, I’ve noticed that there seems to be a lot of confusion about the difference between passing a parameter ByVal vs. ByRef.    So, I thought I’d spend some time writing something on this topic.  It should be noted, that while I will be discussing this from the view point of VB.NET, the same basic concepts also apply to VB.CLASSIC – it’s just that some of the terminology is different.

First, let’s cover the definition of these two keywords as they are presented in the Visual Basic documentation.

ByVal

Specifies that an argument is passed in such a way that the called procedure or property cannot change the value of a variable underlying the argument in the calling code.

ByRef

Specifies that an argument is passed in such a way that the called procedure can change the value of a variable underlying the argument in the calling code.

On the surface, these two statements seem simple enough.  ByVal (short for By Value) means that the called procedure can not alter the value that was passed.  In essence, when you pass ByVal – a copy of the original value is made and passed to the procedure.  ByRef (short for By Reference) on the other hand is just the opposite.  When, a value is passed ByRef, then a reference – aka pointer (in fact it is more accurate to say that it is a C++ reference then a pointer) – to the original value is passed, so any changes made to this value alter the callers state.  Just to test this out, lets create a little test code and see what happens:

 

   1: Option Explicit On
   2: Option Strict On
   3:  
   4: Module Module1
   5:  
   6:     Sub Main()
   7:         Dim i As Integer = 0
   8:  
   9:         ' Pass an integer ByVal
  10:         Console.WriteLine("Before ByVal Call: i = {0}", i)
  11:         PassIntByVal(i)
  12:         Console.WriteLine("After ByVal Call: i = {0}", i)
  13:  
  14:         Console.WriteLine()
  15:  
  16:         ' Pass an integer ByRef
  17:         Console.WriteLine("Before ByRef Call: i = {0}", i)
  18:         PassIntByRef(i)
  19:         Console.WriteLine("After ByRef Call: i = {0}", i)
  20:  
  21:     End Sub
  22:  
  23:     Sub PassIntByVal(ByVal i As Integer)
  24:         i = 5
  25:     End Sub
  26:  
  27:     Sub PassIntByRef(ByRef i As Integer)
  28:         i = 5
  29:     End Sub
  30:  
  31: End Module

And our output:

output001

So far so good.  It looks exactly like we should expect given the definitions of ByVal and ByRef.  So, if things work according to the definition, why the confusion?   Well, lets expand this simple example a bit and see if things continue to hold up – this time by creating a structure and putting it to the same test as above:

   1: Option Explicit On
   2: Option Strict On
   3:  
   4: Module Module1
   5:  
   6:     Sub Main()
   7:         Dim i As Integer = 0
   8:  
   9:         ' Pass an integer ByVal
  10:         Console.WriteLine("Before ByVal Call: i = {0}", i)
  11:         PassIntByVal(i)
  12:         Console.WriteLine("After ByVal Call: i = {0}", i)
  13:  
  14:         Console.WriteLine()
  15:  
  16:         ' Pass an integer ByRef
  17:         Console.WriteLine("Before ByRef Call: i = {0}", i)
  18:         PassIntByRef(i)
  19:         Console.WriteLine("After ByRef Call: i = {0}", i)
  20:  
  21:         Console.WriteLine()
  22:  
  23:         Dim ts As TestStructure
  24:  
  25:         ' Pass a structure ByVal
  26:         Console.WriteLine("Before ByVal Call: ts.i = {0}, ts.j={1}", ts.i, ts.j)
  27:         PassStructureByVal(ts)
  28:         Console.WriteLine("After ByVal Call: ts.i = {0}, ts.j={1}", ts.i, ts.j)
  29:  
  30:         Console.WriteLine()
  31:  
  32:         ' Pass a strucutre ByRef
  33:         Console.WriteLine("Before ByRef Call: ts.i = {0}, ts.j={1}", ts.i, ts.j)
  34:         PassStructureByRef(ts)
  35:         Console.WriteLine("After ByRef Call: ts.i = {0}, ts.j={1}", ts.i, ts.j)
  36:  
  37:     End Sub
  38:  
  39:     Sub PassIntByVal(ByVal i As Integer)
  40:         i = 5
  41:     End Sub
  42:  
  43:     Sub PassIntByRef(ByRef i As Integer)
  44:         i = 5
  45:     End Sub
  46:  
  47:     Sub PassStructureByVal(ByVal s As TestStructure)
  48:         s.i = 5
  49:         s.j = 10
  50:     End Sub
  51:  
  52:     Sub PassStructureByRef(ByRef s As TestStructure)
  53:         s.i = 5
  54:         s.j = 10
  55:     End Sub
  56:  
  57:     Structure TestStructure
  58:         Public i As Integer
  59:         Public j As Integer
  60:     End Structure
  61: End Module

And our output:

output002

The output should be consistent with our expectations.  Again, what’s the problem?  I’m going to do one more expansion of our code – lets add a custom class into the mix:

   1: Option Explicit On
   2: Option Strict On
   3:  
   4: Module Module1
   5:  
   6:     Sub Main()
   7:         Dim i As Integer = 0
   8:  
   9:         ' Pass an integer ByVal
  10:         Console.WriteLine("Before ByVal Call: i = {0}", i)
  11:         PassIntByVal(i)
  12:         Console.WriteLine("After ByVal Call: i = {0}", i)
  13:  
  14:         Console.WriteLine()
  15:  
  16:         ' Pass an integer ByRef
  17:         Console.WriteLine("Before ByRef Call: i = {0}", i)
  18:         PassIntByRef(i)
  19:         Console.WriteLine("After ByRef Call: i = {0}", i)
  20:  
  21:         Console.WriteLine()
  22:  
  23:         Dim ts As TestStructure
  24:  
  25:         ' Pass a structure ByVal
  26:         Console.WriteLine("Before ByVal Call: ts.i = {0}, ts.j={1}", ts.i, ts.j)
  27:         PassStructureByVal(ts)
  28:         Console.WriteLine("After ByVal Call: ts.i = {0}, ts.j={1}", ts.i, ts.j)
  29:  
  30:         Console.WriteLine()
  31:  
  32:         ' Pass a strucutre ByRef
  33:         Console.WriteLine("Before ByRef Call: ts.i = {0}, ts.j={1}", ts.i, ts.j)
  34:         PassStructureByRef(ts)
  35:         Console.WriteLine("After ByRef Call: ts.i = {0}, ts.j={1}", ts.i, ts.j)
  36:  
  37:         Console.WriteLine()
  38:  
  39:         Dim tc As New TestClass
  40:  
  41:         ' Pass a class ByVal
  42:         Console.WriteLine("Before ByVal Call: tc.i = {0}, tc.j={1}", tc.i, tc.j)
  43:         PassClassByVal(tc)
  44:         Console.WriteLine("After ByVal Call: tc.i = {0}, tc.j={1}", tc.i, tc.j)
  45:  
  46:         Console.WriteLine()
  47:  
  48:         ' Pass a class ByRef
  49:         Console.WriteLine("Before ByRef Call: tc.i = {0}, tc.j={1}", tc.i, tc.j)
  50:         PassClassByRef(tc)
  51:         Console.WriteLine("After ByRef Call: tc.i = {0}, tc.j={1}", tc.i, tc.j)
  52:  
  53:     End Sub
  54:  
  55:     Sub PassIntByVal(ByVal i As Integer)
  56:         i = 5
  57:     End Sub
  58:  
  59:     Sub PassIntByRef(ByRef i As Integer)
  60:         i = 5
  61:     End Sub
  62:  
  63:     Sub PassStructureByVal(ByVal s As TestStructure)
  64:         s.i = 5
  65:         s.j = 10
  66:     End Sub
  67:  
  68:     Sub PassStructureByRef(ByRef s As TestStructure)
  69:         s.i = 5
  70:         s.j = 10
  71:     End Sub
  72:  
  73:     Sub PassClassByVal(ByVal c As TestClass)
  74:         c.i = 5
  75:         c.j = 10
  76:     End Sub
  77:  
  78:     Sub PassClassByRef(ByRef c As TestClass)
  79:         c.i = 6
  80:         c.j = 11
  81:     End Sub
  82:  
  83:     Structure TestStructure
  84:         Public i As Integer
  85:         Public j As Integer
  86:     End Structure
  87:  
  88:     Class TestClass
  89:         Public i As Integer
  90:         Public j As Integer
  91:     End Class
  92:  
  93: End Module

And the output:

output003

Take a look at the lines of output for the tc variable.  Have we been mislead by the documentation?   Because, on the surface at least, it sure appears as if tc was modified when passed ByVal.   The answer to this question is no – ByVal still worked exactly as it was supposed to. 

To understand why these results are in fact correct, requires a bit of understanding of the .NET type system.  The .NET CTS – or Common Type System – defines two basic type categories (each of which is further subdivided).  These categories are Value types and Reference types.  Here is what the documentation has to say regarding these two categories:

    • Value types

      Value types directly contain their data, and instances of value types are either allocated on the stack or allocated inline in a structure. Value types can be built-in (implemented by the runtime), user-defined, or enumerations. For a list of built-in value types, see the .NET Framework Class Library.

    • Reference types

      Reference types store a reference to the value’s memory address, and are allocated on the heap. Reference types can be self-describing types, pointer types, or interface types. The type of a reference type can be determined from values of self-describing types. Self-describing types are further split into arrays and class types. The class types are user-defined classes, boxed value types, and delegates.

Summary:  A value type variable directly contains it’s data, a reference type variable does not.   In practical terms, this means that the behavior demonstrated by the example code is in fact completely consistent with the documented behavior of ByVal and ByRef.  When passing ByVal, the copy that is made is the contents of the variable – in the case of a reference type, the value that the variable contains is not the instance, but the memory address of the instance on the heap.  Maybe a small diagram will help clarify:

heap

What this means in practical terms is that, if you pass an object ByVal – the objects properties can be modified, but the reference can not:

   1: Option Explicit On
   2: Option Strict On
   3:  
   4: Module Module1
   5:  
   6:     Sub Main()
   7:         Dim tc As TestClass = Nothing
   8:  
   9:         ' pass by value
  10:         Console.WriteLine("Passing tc ByVal")
  11:         CreateTCByVal(tc)
  12:         Console.WriteLine(If(tc IsNot Nothing, String.Format("tc.i = {0}, tc.j={1}", tc.i, tc.j), "tc is Nothing"))
  13:  
  14:         Console.WriteLine()
  15:  
  16:         ' pass by reference
  17:         Console.WriteLine("Passing tc ByRef")
  18:         CreateTCByRef(tc)
  19:         Console.WriteLine(If(tc IsNot Nothing, String.Format("tc.i = {0}, tc.j={1}", tc.i, tc.j), "tc is Nothing"))
  20:     End Sub
  21:  
  22:     Sub CreateTCByVal(ByVal c As TestClass)
  23:         c = New TestClass()
  24:         c.i = 5
  25:         c.j = 6
  26:     End Sub
  27:  
  28:     Sub CreateTCByRef(ByRef c As TestClass)
  29:         c = New TestClass()
  30:         c.i = 5
  31:         c.j = 6
  32:     End Sub
  33:  
  34:     Class TestClass
  35:         Public i As Integer
  36:         Public j As Integer
  37:     End Class
  38:  
  39: End Module

And the output:

output004

I hope this brief explanation of ByVal and ByRef can help clear up some of the confusion that seems to exist regarding these to argument passing methods.

May 11, 2009

A P/Invoke Primer for the Experienced VB6 Programmer

Filed under: .NET,P/Invoke,Visual Basic — Tom Shelton @ 10:52 pm

"In VB6 I used to use SomeSuchAPI to perform task X – I have tried to use SomeSuchAPI in my new SuperVelocityStrumCruncher app written in VB.NET, but it won’t work!  Help!"  And on goes the refrain. 

When I see these types of questions, nine times out of ten, I know the answer before I even read the question.  Or, that is to say, I can pretty much surmise that the answer will fall into one of a couple of typical traps that the experienced VB6 user faces when deciding to use P/Invoke in VB.NET (which, is a pretty relevant question because many times the task you were using the API for in VB6, may just be taken care of by the .NET framework).

So, I’m going to cover a few tips that should help the experienced VB6 user successfully make use of Windows or other API calls from VB.NET.


Know Your Data Types

That’s right – know your data types.  For better or for worse, many of the integral data types that you used to know and love in VB6 have changed sizes in VB.NET.  For example, Long is no longer a 32-bit Integer – it’s 64-bit.  So, forget what you knew about integer types and their sizes in VB6 and learn them a new in VB.NET.

Here is a quick conversion table that might help speed along this task:

Size/Sign VB6 Data Type VB.NET Data Type
8 bits/unsigned Byte Byte
8 bits/signed N/A SByte
16 bits/unsigned N/A UShort
16 bits/signed Integer Short
32 bits/unsigned N/A UInteger
32 bits/signed Long Integer
64 bits/unsigned N/A ULong
64 bits/signed N/A Long
This table is based on the information found here.

So…  What does all of this mean in the actual context of a Windows API call?  Well, lets look at the GetWindowsDirectory API call.  A typical declaration for this in VB6 might be:

   1: Private Declare Function GetWindowsDirectory Lib "KERNEL32" Alias "GetWindowsDirectoryA" _
   2:     (ByVal lpBuffer As String, ByVal nSize As Long) As Long

This call is incorrect in VB.NET – we can see that we are passing a Long value to the nSize parameter, which is a 32-bit integer in VB6, but is 64-bit in VB.NET.  This can cause problems as it can unbalance the functions call stack.  To correct this we can declare the function in VB.NET like so:

   1: Private Declare Function GetWindowsDirectory Lib "KERNEL32" Alias "GetWindowsDirectoryA" _
   2:     (ByVal lpBuffer As String, ByVal nSize As Integer) As Integer

If you were to use this declaration in VB.NET, it would work – but, there are a couple of other levels of enlightenment that can be achieved here on our way to API Nirvana! 

Drop The Alias

Any one else remember the bad old days when a seemingly simple call to an API call from VB could cause gyrations such as these to occur on NT based OS’s?

funny_conversion_flow_chart

Good Times!  Good Times!

Well, fortunately, the VB.NET marshaller has improved significantly when it comes to handling of strings.  For instance, it can call the W functions directly without the need for hacks using StrPtr or creating custom API typelibs.  And, as if that wasn’t good news, it actually gets better!  You don’t even have to alias your functions anymore. 

The venerable old Declare statement has a new option in VB.NET that lets you declare the character set that should be used when calling the function.  There are 3 choices – Ansi, Unicode, and Auto.  When dealing with the A/W function set of the Windows API, Auto is often a good choice because you can just declare the function and let the .NET runtime worry about if it should call the A or the W version.

Here’s our function declaration again with a bit more .NET goodness:

   1: Private Declare Auto Function GetWindowsDirectory Lib "KERNEL32" _
   2:     (ByVal lpBuffer As String, ByVal nSize As Integer) As Integer

Isn’t that nice!

Mind Your Buffers

Ok, I want to point out one more minor adjustment that probably should be made to our Declare statement.  In the name of correctness, since the buffer we are passing in to this function is intended to be filled by the API call, we should not be passing the lpBuffer parameter as String.  See in a nod to compatibility with it’s predecessor, the VB.NET marshaller – unlike C#’s – does not respect the immutability of strings and will go through the extra gyrations necessary to make sure you get the result you have come to expect in your long association with VB.CLASSIC.  With out getting into arguments about computability vs correctness – I’m simply going to show you the recommended way of declaring string buffers that are intended to be filled by called API’s and you are free to make your own decision as to which way to go with this:

   1: Private Declare Auto Function GetWindowsDirectory Lib "KERNEL32" _
   2:     (ByVal lpBuffer As System.Text.StringBuilder, ByVal nSize As Integer) As Integer

Keep in mind, the use of StringBuilder is only recommended when the string is intended to be modified by the called function – if it’s a simple constant string buffer, then String is definitely the appropriate type.  Here is a complete sample using our declare statement:

   1: Option Strict On
   2: Option Explicit On
   3:  
   4: Imports System
   5: Imports System.Text
   6:  
   7: Module Module1
   8:     Private Const MAX_PATH As Integer = 260
   9:     Private Declare Auto Function GetWindowsDirectory Lib "KERNEL32" _
  10:         (ByVal lpBuffer As StringBuilder, ByVal nSize As Integer) As Integer
  11:  
  12:     Sub Main()
  13:         Console.WriteLine(GetWindowsDirectory())
  14:     End Sub
  15:  
  16:     Private Function GetWindowsDirectory() As String
  17:         Dim buffer As New StringBuilder(MAX_PATH)
  18:         GetWindowsDirectory(buffer, buffer.Capacity)
  19:         Return buffer.ToString()
  20:     End Function
  21: End Module

Handle Your Handles

The last bit of advice that I’m going to render in this article is about handles.  In the Windows API the concept of handles is very important, and handles are used all over the place.  When ever you are dealing with a handle type (such as HWND), then it is recommended that you use System.IntPtr as the type for that parameter over Integer.  The main reason for this is that the IntPtr is a wrapper over an unsigned system sized integer – so that means that it is 32-bit on 32-bit systems and 64-bit on 64-bit systems.  See how that works?  Anyway, it might make your porting life in the future easier if you remember that little tip.

May 5, 2009

Using C# and WMI to Create and Configure IIS6 Web Sites

Filed under: C# — Tom Shelton @ 10:53 pm

Recently, I was worked on a project that required me to automate the installation and creation of web sites on IIS6.  I didn’t want to do this using VBScript or another WSH language because this was going to be used as part of a larger application.  I made some exploratory attempts using the ADSI interfaces, but did not find them satisfactory due to performance concerns.  After a little research, I discovered that since I was dealing with IIS6, that there were WMI interfaces available – so I decided to give that a whirl.  What I discovered, was that the while the performance using WMI was not what I would call spectacular, it was definitely a large improvement over using the ADSI interfaces.  The classes and code I’m sharing here are basic implementations, that accomplish only the basic tasks that I needed – so, there is definitely room for expansion on the part of the reader.  This is only meant as a starting point – I am still actively extending and refactoring these implementations my self.

What I was creating was basically a generic deployment tool – basically, this tool takes a package (a zip file) that contains the web site content and directory structure and XML document that describes the details of the deployment based on the machine name in the infrastructure.  This contains things like the name of directory to extract the package to, any web.config changes that need to be applied for the environment, and most importantly to this article a description of the IIS web site that should be created.

The first class I will share is the base class for most of the classes in my hierarchy – WmiObjectBase.

 

   1: using System.Collections.Generic;
   2: using System.Management;
   3:  
   4: namespace FireAnt.IIS.Util
   5: {
   6:     /// <summary>
   7:     /// Base object for WMI objects
   8:     /// </summary>
   9:     public abstract class WmiObjectBase
  10:     {
  11:         /// <summary>
  12:         /// Create and connect a management scope for use by subclasses
  13:         /// </summary>
  14:         /// <param name="path">The management path</param>
  15:         protected WmiObjectBase(string path)
  16:         {
  17:             ConnectionOptions connectionOptions = new ConnectionOptions ();
  18:             connectionOptions.Authentication = AuthenticationLevel.PacketPrivacy;
  19:             this.Scope = new ManagementScope (path , connectionOptions );
  20:             this.Scope.Connect ();
  21:         }
  22:  
  23:         /// <summary>
  24:         /// Allow subclasses to pass in an existing scope
  25:         /// </summary>
  26:         /// <param name="scope">A management scope object</param>
  27:         protected WmiObjectBase ( ManagementScope scope )
  28:         {
  29:             this.Scope = scope;
  30:             if ( !this.Scope.IsConnected )
  31:                 this.Scope.Connect ();
  32:         }
  33:         
  34:         /// <summary>
  35:         /// Let subclasses get their scope object
  36:         /// </summary>
  37:         protected ManagementScope Scope { get; private set; }
  38:  
  39:         /// <summary>
  40:         /// Allow subobjects to peform queries
  41:         /// </summary>
  42:         /// <param name="query">The object query to perform</param>
  43:         /// <returns>A ManagementObjectCollection containing the results of the query</returns>
  44:         protected ManagementObjectCollection this[ObjectQuery query]
  45:         {
  46:             get
  47:             {
  48:                 return new ManagementObjectSearcher ( this.Scope, query ).Get ();
  49:             }
  50:         }
  51:  
  52:  
  53:         /// <summary>
  54:         /// Apply settings to a Management object.
  55:         /// </summary>
  56:         /// <param name="target">The ManagamentObject to apply the settings to</param>
  57:         /// <param name="properties">A Dictionary object containing the properties and their value.</param>
  58:         /// <remarks>
  59:         /// The properties Dictionary contains the name of the property as the key, and the value of the property as the value.
  60:         /// </remarks>
  61:         protected void ApplySettings ( ManagementObject target, Dictionary<string, object> properties )
  62:         {
  63:             foreach ( var kv in properties )
  64:             {
  65:                 target.Properties[kv.Key].Value = kv.Value;
  66:             }
  67:             target.Put ();
  68:         }
  69:  
  70:         /// <summary>
  71:         /// Allow subclasses and easy method of creating new ManagementObject instances
  72:         /// </summary>
  73:         /// <param name="path">The management path of the object</param>
  74:         /// <returns>The instance of the object</returns>
  75:         protected ManagementObject CreateManagementObject ( string path )
  76:         {
  77:             ManagementClass template = new ManagementClass ( this.Scope, new ManagementPath ( path ), null );
  78:             return template.CreateInstance ();
  79:         }
  80:  
  81:         /// <summary>
  82:         /// Allow subclasses to get instances of existing objects
  83:         /// </summary>
  84:         /// <param name="path">the management path</param>
  85:         /// <returns>An instance of the object</returns>
  86:         protected ManagementObject GetInstance ( string path )
  87:         {
  88:             return new ManagementObject ( this.Scope, new ManagementPath ( path ), null );
  89:         }
  90:     }
  91: }

This class simply provides some common functionality for the various WMI based objects in my library.

The main class in the library, is the InternetInformationServer class.  This class holds and manages the connection to an IIS6 instance.  It allows you to create and manipulate the various server objects, such as web sites and application pools:

 

   1: using System;
   2: using System.Collections.Generic;
   3: using System.Linq;
   4: using System.Text;
   5: using System.Management;
   6: using System.Text.RegularExpressions;
   7:  
   8: namespace FireAnt.IIS.Util
   9: {
  10:     public class InternetInformationServer : WmiObjectBase
  11:     {
  12:         /// <summary>
  13:         /// Create an instance of the InternetInformationServer class that is connected to the
  14:         /// local IIS instance.
  15:         /// </summary>
  16:         public InternetInformationServer () : base ( @"\\.\root\MicrosoftIISV2" ) { }
  17:  
  18:         /// <summary>
  19:         /// Create an instance of the InternetInformationServer class that is connected to the
  20:         /// instance defined by target.
  21:         /// </summary>
  22:         /// <param name="target">The name of the machine to connect to.</param>
  23:         public InternetInformationServer ( string target ) : base ( string.Format ( @"\\{0}\root\MicrosoftIISV2", target ) ) { }
  24:  
  25:         /// <summary>
  26:         /// Create a new website on the IIS instance.
  27:         /// </summary>
  28:         /// <param name="serverComment">The server comment.</param>
  29:         /// <param name="rootPath">The path to the root virtual directory of the web site.</param>
  30:         /// <returns>A new WebSite instance.</returns>
  31:         public WebSite CreateWebSite ( string serverComment, string rootPath )
  32:         {
  33:             return this.CreateWebSite ( serverComment, rootPath, new ServerBinding[] { new ServerBinding () } );
  34:         }
  35:  
  36:         /// <summary>
  37:         /// Create a new web site on the IIS instance.
  38:         /// </summary>
  39:         /// <param name="serverComment">The server comment.</param>
  40:         /// <param name="rootPath">The path to the root virtual directory of the web site.</param>
  41:         /// <param name="serverBindings">A list of ServerBinding objects to apply to the web site.</param>
  42:         /// <returns></returns>
  43:         public WebSite CreateWebSite ( string serverComment, string rootPath, ServerBinding[] serverBindings )
  44:         {
  45:             const string METHOD = "CreateNewSite";
  46:  
  47:             // create a list of serverbindings...
  48:             List<ManagementObject> bindings = new List<ManagementObject> ();
  49:             Array.ForEach ( serverBindings, binding => bindings.Add ( CreateServerBinding ( binding ) ) );
  50:  
  51:             // create the site..
  52:             ManagementObject w3svc = new ManagementObject ( this.Scope, new ManagementPath ( @"IISWebService='W3SVC'" ), null );
  53:             ManagementBaseObject parameters = w3svc.GetMethodParameters ( METHOD );
  54:  
  55:             parameters["ServerComment"] = serverComment;
  56:             parameters["ServerBindings"] = bindings.ToArray ();
  57:             parameters["PathOfRootVirtualDir"] = rootPath;
  58:  
  59:             ManagementBaseObject result = w3svc.InvokeMethod ( METHOD, parameters, null );
  60:             Match m = Regex.Match ( (string)result.Properties["ReturnValue"].Value, "'(.*?)'" );
  61:  
  62:             return new WebSite ( this.Scope, serverComment, m.Groups[1].Value );
  63:         }
  64:  
  65:         /// <summary>
  66:         /// Create a new application pool on the IIS instance
  67:         /// </summary>
  68:         /// <param name="name">The name of the application pool.</param>
  69:         /// <returns></returns>
  70:         public ApplicationPool CreateAppliationPool ( string name )
  71:         {
  72:             ApplicationPool appPool = new ApplicationPool ( this.Scope );
  73:             appPool.Name = string.Format ( "W3SVC/AppPools/{0}", name );
  74:  
  75:             ManagementObject poolTemplate = CreateManagementObject ( "IIsApplicationPoolSetting" );
  76:             poolTemplate.Properties["Name"].Value = appPool.Name;
  77:             poolTemplate.Put ();
  78:  
  79:             return appPool;
  80:         }
  81:  
  82:         private ManagementObject CreateServerBinding ( ServerBinding binding )
  83:         {
  84:             ManagementObject managementObject = CreateManagementObject ( "ServerBinding" );
  85:             managementObject.Properties["Hostname"].Value = binding.HostName;
  86:             managementObject.Properties["IP"].Value = binding.IPAddress;
  87:             managementObject.Properties["Port"].Value = binding.Port;
  88:  
  89:             return managementObject;
  90:         }
  91:  
  92:         /// <summary>
  93:         /// Iterator of all the sites on the IIS instance.
  94:         /// </summary>
  95:         public IEnumerable<WebSite> Sites
  96:         {
  97:             get
  98:             {
  99:                 ObjectQuery query = new ObjectQuery ( "SELECT * FROM IISWebServerSetting" );
 100:                 foreach ( ManagementObject item in this[query] )
 101:                 {
 102:                     yield return new WebSite ( this.Scope, item["ServerComment"].ToString (), item["Name"].ToString () );
 103:                 }
 104:             }
 105:         }
 106:  
 107:  
 108:         /// <summary>
 109:         /// Get a reference to an appliation pool on the IIS instance.
 110:         /// </summary>
 111:         /// <param name="applicationPoolId">The id of the application pool.</param>
 112:         /// <returns>An ApplicationPool object or null if not found.</returns>
 113:         public ApplicationPool GetApplicationPool ( string applicationPoolId )
 114:         {
 115:             ObjectQuery query = new ObjectQuery ( string.Format ( "SELECT * FROM IIsApplicationPoolSetting WHERE Name='W3SVC/AppPools/{0}'", applicationPoolId ) );
 116:             foreach ( ManagementObject item in this[query] )
 117:             {
 118:                 return new ApplicationPool ( this.Scope, (string)item.Properties["Name"].Value );
 119:             }
 120:             return null;
 121:         }
 122:  
 123:  
 124:         /// <summary>
 125:         /// Get a reference to an existing web site on the IIS instance based on the server comment
 126:         /// </summary>
 127:         /// <param name="serverComment">The server comment of the site you want to get.</param>
 128:         /// <returns>A WebSite object or null if not found.</returns>
 129:         /// <remarks>
 130:         /// This method is really a special purpose method - the server comment is not required to be unique in most cases.  But,
 131:         /// in my case it is.  If you can't guarentee uniqueness then you will either need to make this return the a list, or
 132:         /// uses the site id rather then the server comment.
 133:         /// </remarks>
 134:         public WebSite GetWebSite ( string serverComment )
 135:         {
 136:             ObjectQuery query = new ObjectQuery ( "SELECT * FROM IISWebServerSetting WHERE ServerComment = '" + serverComment + "'" );
 137:             foreach ( ManagementObject item in this[query] )
 138:             {
 139:                 return new WebSite ( this.Scope, item["ServerComment"].ToString (), item["Name"].ToString () );
 140:             }
 141:  
 142:             return null;
 143:         }
 144:  
 145:     }
 146: }

Please not the comments on the GetWebSite method…  In the infrastructure I’m working in, I can guarentee that the server comment is unique for a given web site.  In many cases, this can’t be guarenteed – so, really this method should be changed either to return a list of WebSite objects, or it should be refactored to take the site id – which is unique.

There are two overloads for the CreateWebSite method – one that takes a list of ServerBindings and one that doesn’t.  You’ll notice that the overload that does not take a list of bindings simply delegates to the method that does, and passes in a default binding object.

Here are the ApplicationPool, WebSite, and ServerBinding objects as I currently have them:

WebSite:

   1: using System;
   2: using System.Collections.Generic;
   3: using System.Linq;
   4: using System.Text;
   5: using System.Management;
   6:  
   7: namespace FireAnt.IIS.Util
   8: {
   9:     public class WebSite : WmiObjectBase
  10:     {
  11:         private readonly string IdentityQuery;
  12:  
  13:         internal WebSite ( ManagementScope scope, string serverComment, string siteName ) : base(scope)
  14:         {
  15:             this.ServerComment = serverComment;
  16:             this.Name = siteName;
  17:             this.IdentityQuery = string.Format ( "SELECT * FROM IISWebServer WHERE Name = '{0}'", this.Name );
  18:         }
  19:  
  20:         public string ServerComment { get; set; }
  21:         public string Name { get; set; }
  22:  
  23:         //public void Continue ()
  24:         //{
  25:         //}
  26:  
  27:         //public void Pause ()
  28:         //{
  29:         //}
  30:  
  31:         public void Start ()
  32:         {
  33:             ServerState state = CurrentState;
  34:             if ( state == ServerState.Stopped )
  35:             {
  36:                 ObjectQuery query = new ObjectQuery ( IdentityQuery );
  37:                 foreach ( ManagementObject site in this[query] )
  38:                 {
  39:                     site.InvokeMethod ( "Start", null );
  40:                 }
  41:             }
  42:  
  43:         }
  44:  
  45:         public void Stop ()
  46:         {
  47:             ServerState state = CurrentState;
  48:             if ( state == ServerState.Started )
  49:             {
  50:                 ObjectQuery query = new ObjectQuery ( IdentityQuery );
  51:                 foreach ( ManagementObject site in this[query] )
  52:                 {
  53:                     site.InvokeMethod ( "Stop", null );
  54:                 }
  55:             }
  56:         }
  57:  
  58:         public WebVirtualDirectory DirectorySettings
  59:         {
  60:             get { return new WebVirtualDirectory ( this.Scope, this.Name ); }
  61:         }
  62:  
  63:         public ServerState CurrentState
  64:         {
  65:             get
  66:             {
  67:                 ObjectQuery query = new ObjectQuery ( string.Format ( "SELECT ServerState FROM IISWebServer WHERE Name = '{0}'", this.Name ) );
  68:  
  69:                 foreach ( ManagementObject item in this[query] )
  70:                 {
  71:                     return (ServerState)Convert.ToInt32 ( item["ServerState"] );
  72:                 }
  73:                 throw new Exception ( "Can't determine server's current state" );
  74:             }
  75:         }
  76:  
  77:         public override string ToString ()
  78:         {
  79:             return string.Format ( "{0} ({1})", ServerComment, Name );
  80:         }
  81:     }
  82:  
  83:     public enum ServerState
  84:     {
  85:         Starting = 1,
  86:         Started = 2,
  87:         Stopping = 3,
  88:         Stopped = 4,
  89:         Pausing = 5,
  90:         Paused = 6,
  91:         Continuing = 7,
  92:     }
  93: }

ApplicationPool:

   1: using System;
   2: using System.Collections.Generic;
   3: using System.Linq;
   4: using System.Text;
   5: using System.Management;
   6:  
   7: namespace FireAnt.IIS.Util
   8: {
   9:     public class ApplicationPool : WmiObjectBase
  10:     {
  11:         internal ApplicationPool ( ManagementScope scope )
  12:             : base ( scope )
  13:         {
  14:         }
  15:  
  16:         internal ApplicationPool ( ManagementScope scope, string name )
  17:             : base ( scope )
  18:         {
  19:             this.Name = name;
  20:         }
  21:  
  22:         public string Name { get; internal set; }
  23:     }
  24: }

ServerBinding:

   1: using System;
   2: using System.Collections.Generic;
   3: using System.Linq;
   4: using System.Text;
   5:  
   6: namespace FireAnt.IIS.Util
   7: {
   8:     public class ServerBinding
   9:     {
  10:         public ServerBinding () : this ( string.Empty, string.Empty, "80" ) { }
  11:         public ServerBinding ( string hostName, string ipAddress, string port )
  12:         {
  13:             this.HostName = hostName;
  14:             this.IPAddress = ipAddress;
  15:             this.Port = port;
  16:         }
  17:  
  18:         public string HostName { get; set; }
  19:         public string IPAddress { get; set; }
  20:         public string Port { get; set; }
  21:     }
  22: }

Again these are relatively simple classes – but, again they have room to expand.

WebSite does have some properties and methods that maybe of interest.  It allows you to start and stop the individual site – so you don’t have to bring down the entire IIS server, just to configure your site.  It also allows you to get a reference to it’s root virtual directory so you can change settings – such as the framework version the site should use.  Other properties of the root directory can be set via a dictionary of name value pairs.  This was for me the most convenient method, since I was using xml files to store my configuration settings:

 

   1: using System;
   2: using System.Collections.Generic;
   3: using System.Linq;
   4: using System.Text;
   5: using System.Management;
   6: using System.Text.RegularExpressions;
   7:  
   8: namespace FireAnt.IIS.Util
   9: {
  10:     public class WebVirtualDirectory : WmiObjectBase
  11:     {
  12:         internal WebVirtualDirectory ( ManagementScope scope, string siteName ) : base(scope)
  13:         {
  14:             this.Path = string.Format ( "IIsWebVirtualDirSetting='{0}/ROOT'", siteName );
  15:         }
  16:  
  17:         private string Path { get; set; }
  18:  
  19:         public void SetFrameworkVersion ( string version )
  20:         {
  21:             ManagementObject root = this.GetInstance ( this.Path );
  22:             foreach ( PropertyData property in root.Properties )
  23:             {
  24:                 if ( property.Name == "ScriptMaps" )
  25:                 {
  26:                     ManagementBaseObject[] scriptMaps = (ManagementBaseObject[])property.Value;
  27:                     foreach ( ManagementBaseObject scriptMap in scriptMaps )
  28:                     {
  29:                         string value = (string)scriptMap["ScriptProcessor"];
  30:                         if ( value.ToLower ().Contains ( "framework" ) )
  31:                         {
  32:                             if ( !value.Contains ( version ) )
  33:                             {
  34:                                 string currentVersion = Regex.Match ( value, @"(v\d+\.\d+\.\d+)" ).Value;
  35:                                 value = value.Replace ( currentVersion, version );
  36:                                 scriptMap.SetPropertyValue ( "ScriptProcessor", value );
  37:                             }
  38:                             else
  39:                             {
  40:                                 return;
  41:                             }
  42:                         }
  43:                     }
  44:  
  45:                     property.Value = scriptMaps;
  46:                     root.Put ();
  47:                     break;
  48:                 }
  49:             }
  50:         }
  51:  
  52:         public void ApplySettings ( Dictionary<string, object> properties )
  53:         {
  54:             ManagementObject root = this.GetInstance ( this.Path );
  55:             ApplySettings ( root, properties );
  56:         }
  57:     }
  58: }

Here is some code that demonstrates how you might use the above library (Air-Code Warning!):

 

   1: static class Program
   2: {
   3:     internal static string EnvironmentName { get; set; }
   4:     static int Main ( string[] args )
   5:     {
   6:         EnvironmentName = args[0];
   7:         InternetInformationServer iis = new InternetInformationServer ( Program.EnvironmentName );
   8:         WebSite webSite = iis.GetWebSite ( ServerComment );
   9:         ShutdownSite ( webSite );
  10:  
  11:         // DO SOME STUFF
  12:  
  13:         // create the website
  14:          if ( webSite == null )
  15:          {
  16:                 ApplicationPool pool = iis.GetApplicationPool ( poolName );
  17:                  if ( pool == null )
  18:                      iis.CreateAppliationPool ( poolName );
  19:         
  20:              webSite = iis.CreateWebSite ( ServerComment, webRootPath );
  21:                 
  22:              // apply any virtual root properties here
  23:              WebVirtualDirectory virtualDirectory = webSite.DirectorySettings;
  24:              virtualDirectory.SetFrameworkVersion ( "v2.0.50727" );         }
  25:         
  26:          // start up the website...
  27:          webSite.Start ();
  28:     }
  29:  
  30:     private static void ShutdownSite ( WebSite webSite )
  31:      {
  32:          if ( webSite != null )
  33:          {
  34:              webSite.Stop ();
  35:          }
  36:      }
  37: }

Anyway,  I apologize if the level of detail here is less then expected – but I had to throw this together fairly quickly.  I wanted to get it out there in the hopes that someone might find some of this useful.

February 21, 2009

Exploring SQL Server Schema Information With ADO.NET

Filed under: ADO.NET,C# — Tom Shelton @ 7:00 pm

If you read my article on finding SQL Server instances, you’ll know that I mentioned that I planned on writing a series of articles around a code generator that I have been playing with off and on for the last couple of years.  Since this generator was designed to create class definitions around tables, views, and procedures in an SQL Server database, I needed to figure out and write the code to get schema information from a given connection.  As it turns out, ADO.NET provides a fairly robust mechanism for implementers of data providers to return this information from the underlying data store.  In this article, I will be focusing on the information available from the System.Data.SqlClient namespace – but, techniques shown here apply to all of the data providers built into the .NET framework.  I have similar code using System.Data.OracleClient that I can provide if it is of interest.

I’ll start off simple and create a small C# console application that can list all of the databases in an SQL Server instance.

 

   1: using System;
   2: using System.Data;
   3: using System.Data.SqlClient;
   4:  
   5: namespace SchemaInfo
   6: {
   7:     class Program
   8:     {
   9:         static void Main ( string[] args )
  10:         {
  11:             // build a connection string to a sql server instance
  12:             SqlConnectionStringBuilder connectionBuilder = new SqlConnectionStringBuilder ();
  13:             connectionBuilder.DataSource = "SAURON";
  14:             connectionBuilder.IntegratedSecurity = true;
  15:  
  16:             // now, lets list all of the databases in this instance
  17:             using ( SqlConnection connection = new SqlConnection ( connectionBuilder.ConnectionString ) )
  18:             {
  19:                 connection.Open ();
  20:  
  21:                 // get the database information
  22:                 DataTable databases = connection.GetSchema ( SqlClientMetaDataCollectionNames.Databases );
  23:  
  24:                 // print out the connections
  25:                 foreach ( DataColumn column in databases.Columns )
  26:                 {
  27:                     Console.Write ( "{0,-25}", column.ColumnName );
  28:                 }
  29:                 Console.WriteLine ();
  30:                 
  31:                 // print out the rows...
  32:                 foreach ( DataRow database in databases.Rows )
  33:                 {
  34:                     for ( int i = 0; i < database.ItemArray.Length; i++ )
  35:                     {
  36:                         Console.Write ( "{0,-25}", database.ItemArray[i] );
  37:                     }
  38:                     Console.WriteLine ();
  39:                 }
  40:             }
  41:         }
  42:     }
  43: }

schema001

The key to the above codes success is the SqlConnection.GetSchema method.  By passing in a string that represents the name of one of the provider’s supported schema collections, you can get a DataTable back containing the requested information.  There are essentially two types of schema collections – Common Schema Collections and Provider Specific Schema Collections.   As the names imply, the common schema collections are the schema collections implemented by all of the .NET framework providers, where as the provider specific collections apply to a specific provider.

You can get a list of the schema collections supported by your provider by calling the GetSchema method with no arguments.  Here is the above example, with the schema collection name commented out:

 

   1: using System;
   2: using System.Data;
   3: using System.Data.SqlClient;
   4: using System.Windows.Forms;
   5:  
   6: namespace SchemaInfo
   7: {
   8:     class Program
   9:     {
  10:         static void Main ( string[] args )
  11:         {
  12:             // build a connection string to a sql server instance
  13:             SqlConnectionStringBuilder connectionBuilder = new SqlConnectionStringBuilder ();
  14:             connectionBuilder.DataSource = "SAURON";
  15:             connectionBuilder.IntegratedSecurity = true;
  16:  
  17:             // now, lets list all of the databases in this instance
  18:             using ( SqlConnection connection = new SqlConnection ( connectionBuilder.ConnectionString ) )
  19:             {
  20:                 connection.Open ();
  21:  
  22:                 // get the database information
  23:                 DataTable databases = connection.GetSchema ( /*SqlClientMetaDataCollectionNames.Databases*/ );
  24:  
  25:                 // print out the connections
  26:                 foreach ( DataColumn column in databases.Columns )
  27:                 {
  28:                     Console.Write ( "{0,-25}", column.ColumnName );
  29:                 }
  30:                 Console.WriteLine ();
  31:  
  32:                 // print out the rows...
  33:                 foreach ( DataRow database in databases.Rows )
  34:                 {
  35:                     for ( int i = 0; i < database.ItemArray.Length; i++ )
  36:                     {
  37:                         Console.Write ( "{0,-25}", database.ItemArray[i] );
  38:                     }
  39:                     Console.WriteLine ();
  40:                 }
  41:             }
  42:         }
  43:     }
  44: }

schema002

You can find a lot of information on using the GetSchema method and the various schema collections here.

So, now that we’ve introduced the concept GetSchema and schema collections, let’s do something a little more complex.  Let’s build a database explorer type control.  Something like a very simplified version of the Object Explorer in SQL Server Management Studio.

 

1.

Start Visual Studio, and start a new blank solution.  Do this by selecting File -> New -> Project and then selecting "Other Project Types -> Visual Studio Solutions" in the "Project Types" tree view in the "New Project" dialog.

  schema003
   

2.

Add a Windows Forms application to the blank project to serve as a test harness for the control.  Right click on the solution in "Solution Explorer" and select "Add -> New Project…".

  schema004
   

3.

Now, following the same steps as before, add a new "Windows Forms Control Library" to the solution.

  schema005
   

4.

In the windows forms project add a reference to the control library project.  To do this, right click on the windows form project in the "Solution Explorer" and select "Add Reference…".  Select the control library on the Projects tab of the "Add Reference" dialog.  Select OK.

  schema006
   

5.

Rename the default UserControl1 in the windows control library to something more appropriate.  The easiest way to do this is simply right click on the control in the "Solution Explorer" and select "Rename".  I called mine "DBExplorer".  Compile the solution.

   

6.

Now, we create a simple interface so we can see the control in action.  I added a SplitContainer to the default Form1 in the windows forms application.  Then added an instance of the DBExplorer control to Panel1 of the SplitContainer, and set it’s Dock property to Fill.  I named the DBExplorer instance uxDBExplorer.

  schema007
 

Not much to look at – but, this is just a test harness :)

   

7.

Go back to the control library project and add a treeview control to the DBExplorer interface.  Set it’s Dock property to fill and give it an appropriate name.  I gave mine the name of uxExplorer.  Compile the solution.

   

8.

Here is the code for the DBExplorer user control:

 
   1: using System;
   2: using System.Data;
   3: using System.Data.SqlClient;
   4: using System.Windows.Forms;
   5:  
   6: namespace DatabaseExplorer
   7: {