Making custom changes in LINQ-to-SQL designer

This post describes a scenario I encountered recently that would require making custom changes in the LINQ-to-SQL designer.

Even given that the Microsoft rumor-mill has tagged LINQ-to-SQL as terminal given the rise of LINQ-to-Entity, I still find LINQ-to-SQL useful for those non-enterprise solutions. As many developers know, the LINQ-to-SQL class designer doesn’t provide much automated support for keeping your database schema up-to-date with the class designer objects (tables, relationships, etc.)

I haven’t found this to be a huge issue since I work around this limitation by deleting and re-adding tables (which automatically updates the dbml code). I’ve steered clear of making any custom changes through the designer so I can perform this simple work around.

Recently I ran into an issue that makes me want to make manual changes in the designer. It has to do to the way the relationship property names are generated. Let’s look at an example.

Consider you have a table named Grades that represents school grades like 1, 2, 3, and so forth. Now assume you have another table named Worksheets that represents individual worksheets for each school grade. This is a typical one-to-many relationship. Two of the Worksheets columns are StoreGrade (where a PDF worksheet is stored on disk) and DisplayGrade (which grade to display this worksheet for). This allows a worksheet to be reused for 4th grade even though it is stored in the 3rd grade folder. The StoreGrade and DisplayGrade are foreign keys of the Grades table.

When using the LINQ-to-SQL designer, the above scenario will create a Grade object will two properties called Worksheets and Worksheets1. When using intellisense, it really isn’t possible to know which of these properties is for StoreGrade and which is for DisplayGrade. It is possible to change the property names created by using the designer properties for each relationship, but that would mean I lose the ability to delete/re-add a table with ease.

I’m continuing to hold off making custom designer changes, but this is one scenario I’ve run across that gives a reason to make custom changes via the LINQ-to-SQL designer.

Measuring Stability and Abstractness

I've always felt that a proper Object-Oriented design must be focused on package and class relationships, and that those relationships should be loosely coupled.

Bob Martin wrote a book back in 1995 called Designing Object-Oriented C++ Applications Using the Booch Method. Chapter 3 of this book explained how to design a loosely coupled architecture based on cohesion and closure. Then in 2006 Bob and his son wrote Agile Principles, Patterns, and Practices in C#. Chapter 28 of this book revisited the topic of loosely coupled architectures.

I have always felt that this topic is crucial to both architecture and design of Object-Oriented solutions, and thus I often revisit the chapters from these books. I finally decided to encapsulate Bob's content into a single page diagram (taken from the second book).

Click here to get it!

Keep this single-page diagram near and dear to your heart...and read Bob's second book, twice.

Visual Studio macro to insert curly braces

I think the most frequently used set of keystrokes I use in development is the process of inserting two curly braces with a blank line in between them.

I found the following Visual Studio macro once on the Internet. You can add this to your list of Visual Studio macros, then use the keyboard configuration to assign it to a keystroke. I've assigned the macro so that when I press Shift-Return, it inserts two curly braces, a blank line in between them, then places the cursor indented on the blank line...ready to type in a statement. I use this keystroke constantly in development.

The macro works fine in both Visual Studio 2005 and 2008.

Sub InsertCurlies()
   DTE.ActiveDocument.Selection.NewLine()
   DTE.ActiveDocument.Selection.Text = "{"
   DTE.ActiveDocument.Selection.NewLine()
   DTE.ActiveDocument.Selection.Text = "}"
   DTE.ActiveDocument.Selection.LineUp()
   DTE.ActiveDocument.Selection.NewLine()
End Sub

Using LINQ's ToLookup

Often times you have a set of data and want to process the data in sets based on some property of the data. For example, you may want to process all employee records based on what state the employee lives in (presumably for tax purposes).

To organize the employees, we need something like a .NET dictionary class, but be able to store multiple employees per key (i.e. state). A dictionary only stores a single object per key so we're out of luck using that class.

This is where the LINQ ToLookup and GroupBy operators come to the rescue. ToLookup is a non-deferred operator and GroupBy is a deferred operator. The example below illustrates how ToLookup can be used to process employee objects, together, based on what state the employee lives in. The GroupBy is basically the same thing, but the data is grouped during enumeration because of its deferred nature.

private class Employee
{
   public int ID { get; set; }
   public string LastName { get; set; }
   public string FirstName { get; set; }
   public string StateAbbr { get; set; }
}

static void Main(string[] args)
{
   new Program().Run(args);
}

private void Run(string[] args)
{
   List<Employee> employees = new List<Employee>
   {
      new Employee { ID = 1, LastName = "Scott", FirstName = "Michael", StateAbbr = "CO" },
      new Employee { ID = 1, LastName = "Smoe", FirstName = "Joe", StateAbbr = "CA" },
      new Employee { ID = 1, LastName = "Lampton", FirstName = "Todd", StateAbbr = "CO" },
      new Employee { ID = 1, LastName = "Morgan", FirstName = "Jared", StateAbbr = "WA" },
      new Employee { ID = 1, LastName = "Smart", FirstName = "Laura", StateAbbr = "ID" },
      new Employee { ID = 1, LastName = "Seashell", FirstName = "Sally", StateAbbr = "CO" },
   };

   ILookup<string, Employee> folksByState = employees
      .ToLookup(e => e.StateAbbr);

   foreach (IGrouping<string, Employee> stateGroup in folksByState)
   {
      Console.WriteLine("\nState: {0}", stateGroup.Key);
      foreach (Employee e in stateGroup)
      {
         Console.WriteLine("{0}, {1}, {2}", e.LastName, e.FirstName, e.ID);
      }
   }
}

Install Windows Media Player 11 on Windows Server 2003 SP2

I finally found a method that actually works to install Windows Media Player on Windows Server 2003 SP2. I've executed these instructions several times on a VPC, then on my normal server and it was easy. Works great!

1) Download the Windows Media Player 11 setup file and save to disk (e.g. wmp11-windowsxp-x86-enu.exe).

2) Run the setup file.

3) You should see if a Validation Checker dialog. Just leave this dialog box displayed this without clicking any of the buttons.

4) Search your hard drive for the file "wmp11.exe". Once found, use Windows Explorer and go to the directory where this file resides.

5) Copy all the files from this directory to another temporary directory, then close the Validation Checker dialog.

6) Use Windows Explorer and go to the temporary directory you created.

7) Right-click and change Properties on the following files; set Run in Compatibility Mode (on the Compatibility Tab) to Windows XP:

umdf.exe
wmfdist11.exe
wmdbexport.exe
wmp11.exe

8) Run wmfdist11.exe and follow the setup instructions.

9) Run umdf.exe and follow the setup instructions.

10) Reboot the computer.

11) Run wmdbexport.exe (it will execute without anything being displayed).

12) Run wmp11.exe and follow the setup instructions.

13) Reboot the computer.

14) Windows Media Player 11 should now be installed and ready to use.

15) Remove your temporary directory when you are sure everything is working.

Fast file compare using .NET HashAlgorithm.ComputeHash

I recently had a huge set of files in dozens of directories that I knew were somehow different from a backup. I needed to know which files were different (I just needed the file names). Normally I would use software like Beyond Compare, but this was not an option since the two sets of files were in two different remote locations without an option of copying 5G worth of data from one location to the other.

The solution I came up with, which worked out great, was to modify a set of code from my C# Recipe book that computed a hash of two files and compared the hash values. The new code accepts a file pattern, recursively finds all matching files (from the current directory), then writes out each files hash value and file name.

I executed the program on both systems, redirecting the output to a text file, then just had to compare the two text files. Note: Be careful that the FileHash program itself and any redirected output files are not included in the file pattern that you are searching on.

Example:

..\FileHash.exe *.* > ..\hashvalues.systemA.txt

using System;
using System.IO;
using System.Security.Cryptography;

namespace FileHash
{
   class Program
   {
      static void Main(string[] args)
      {
         new Program().Run(args);
      }

      private void Run(string[] args)
      {
         // Create the hashing object.
         string[] files = Directory.GetFiles(@".\", args[0], SearchOption.AllDirectories);

         foreach (string file in files)
         {
            using (HashAlgorithm hashAlg = HashAlgorithm.Create())
            {
               using (FileStream fs = new FileStream(file, FileMode.Open))
               {
                  // Calculate the hash for the files.
                  byte[] hashBytes = hashAlg.ComputeHash(fs);

                  // Compare the hashes.
                  Console.WriteLine(string.Format(
                     "{0} {1}",
                     BitConverter.ToString(hashBytes),
                     file));
               }
            }
         }
      }
   }
}

Zip XML in memory for Web Service transport (SharpZipLib)

Here is a full test program that demonstrates how to use SharpZipLib to zip an XElement into a byte array. This allows you to transfer large XML items over web services, and then unzip then on the web service side. I included two methods to unzip, both back to an XElement and to an XML file. IIS 6 does allow compression as well, but the reason I had to have the functionality below was that a PC client application was required to send a host web service a large set of XML (rather than the host sending the client XML).

using System;
using System.IO;
using System.Xml.Linq;
using ICSharpCode.SharpZipLib.Zip;

namespace ConsoleTest
{
   class Program
   {
      static void Main(string[] args)
      {
         new Program().Run(args);
      }

      private void Run(string[] args)
      {
         // create some xml
         XElement xml = XElement.Parse("<xml><element>whatever</element></xml>");

         // zip xml
         string startXml = xml.ToString();
         byte[] bytes = ZipContent(xml, "TestXML");

         // unzip xml
         xml = UnzipContent(bytes);
         string endXml = xml.ToString();

         // sanity check
         System.Diagnostics.Debug.Assert(startXml == endXml);
      }

      /// <summary>
      /// Convert XML to zipped byte array.
      /// </summary>
      /// <param name="xml">XML to zip.</param>
      /// <param name="entryName">The zip entry name.</param>
      /// <returns>A byte array that contains the xml zipped.</returns>
      private byte[] ZipContent(XElement xml, string entryName)
      {
         // remove whitespace from xml and convert to byte array
         byte[] normalBytes;
         using (StringWriter writer = new StringWriter())
         {
            xml.Save(writer, SaveOptions.DisableFormatting);
            System.Text.ASCIIEncoding encoding = new System.Text.ASCIIEncoding();
            normalBytes = encoding.GetBytes(writer.ToString());
         }

         // zip into new, zipped, byte array
         using (Stream memOutput = new MemoryStream())
         using (ZipOutputStream zipOutput = new ZipOutputStream(memOutput))
         {
            zipOutput.SetLevel(9);

            ZipEntry entry = new ZipEntry(entryName);
            entry.DateTime = DateTime.Now;
            zipOutput.PutNextEntry(entry);

            zipOutput.Write(normalBytes, 0, normalBytes.Length);
            zipOutput.Finish();

            byte[] newBytes = new byte[memOutput.Length];
            memOutput.Seek(0, SeekOrigin.Begin);
            memOutput.Read(newBytes, 0, newBytes.Length);

            zipOutput.Close();

            return newBytes;
         }
      }

      /// <summary>
      /// Return zipped bytes as unzipped XML.
      /// </summary>
      /// <param name="bytes">Zipped content.</param>
      /// <returns>Unzipped XML.</returns>
      private XElement UnzipContent(byte[] bytes)
      {
         // unzip bytes into unzipped byte array
         using (Stream memInput = new MemoryStream(bytes))
         using (ZipInputStream input = new ZipInputStream(memInput))
         {
            ZipEntry entry = input.GetNextEntry();

            byte[] newBytes = new byte[entry.Size];
            int count = input.Read(newBytes, 0, newBytes.Length);
            if (count != entry.Size)
            {
               throw new Exception("Invalid read: " + count);
            }

            // convert bytes to string, then to xml
            string xmlString = System.Text.ASCIIEncoding.ASCII.GetString(newBytes);
            return XElement.Parse(xmlString);
         }
      }

      /// <summary>
      /// Save zipped bytes as unzipped file.
      /// </summary>
      /// <param name="bytes">Zipped content.</param>
      /// <param name="path">File path to save unzipped XML.</param>
      private void UnzipContent(byte[] bytes, string path)
      {
         // unzip bytes into unzipped byte array
         using (Stream memInput = new MemoryStream(bytes))
         using (ZipInputStream zipInput = new ZipInputStream(memInput))
         using (BinaryWriter writer = new BinaryWriter(File.Create(path)))
         {
            ZipEntry entry = zipInput.GetNextEntry();

            int count;
            byte[] input = new byte[1024 * 10];
            while ((count = zipInput.Read(input, 0, input.Length)) > 0)
            {
               writer.Write(input, 0, count);
            }
         }
      }
   }
}

Use PowerShell to capture database schema

Here is a PowerShell script to capture a database schema. Output is written to a directory/datetime file. Multiple databases/servers can be specified via the XML input file.

To run: ./CaptureSchema.ps1 databases.xml

Here is the PowerShell code:

param ([string]$xmlConfig = $(throw '%argument 1 must be XML configuration file path'))

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
[System.Reflection.Assembly]::LoadWithPartialName("System.Data") | out-null
[System.Reflection.Assembly]::LoadWithPartialName("System.Core") | out-null
[System.Reflection.Assembly]::LoadWithPartialName("System.Linq") | out-null
[System.Reflection.Assembly]::LoadWithPartialName("System.Xml.Linq") | out-null

function ScriptDatabase([string]$serverName, [string]$dbName)
{
   $fileName = [String]::Format("{0} {1}.sql", [DateTime]::Now.ToString("yyyyMMdd_HHmmss"), $dbName)
   [Console]::Write("Server: $serverName, Database: $dbName, Output: `"$fileName`" . . . ")

   $srv = new-object "Microsoft.SqlServer.Management.SMO.Server" $serverName
   $db = new-object "Microsoft.SqlServer.Management.SMO.Database"
   $scr = New-Object "Microsoft.SqlServer.Management.Smo.Scripter"

   $db = $srv.Databases[$dbName]

   $scr.Server = $srv
   $options = New-Object "Microsoft.SqlServer.Management.SMO.ScriptingOptions"
   
   $options.ClusteredIndexes = $true
   $options.Default = $true
   $options.DriAll = $true
   $options.Indexes = $true
   $options.IncludeHeaders = $true
   $options.Triggers = $true
   $options.AppendToFile = $false
   $options.FileName = "$pwd\$fileName"
   $options.ToFileOnly = $true
   
   # output all db tables
   $scr.Options = $options
   $tables = $db.Tables
   if ($tables -ne $null)
   {
      $scr.Script($db.Tables)
   }

   # output all sprocs
   $options.AppendToFile = $true
   $sprocs = $db.StoredProcedures | where {$_.IsSystemObject -eq $false}
   if ($sprocs -ne $null)
   {
      $scr.Script($sprocs)
   }
   
   # output all db views
   $views = $db.Views | where {$_.IsSystemObject -eq $false}
   if ($views -ne $null)
   {
      $scr.Script($views)
   }
   
   "done."
   
}

function SaveSchema($xmlDb)
{
   # make folder if not exists yet
   $dbName = $xmlDb.Element("Name").Value
   $dirName = ".\$dbName"
   if ((Test-Path -path $dirName) -eq $False)
   {
      "Creating directory $dirName..."
      ni -type directory $dirName | out-null
   }
   
   # save the schema
   $serverName = $xmlDb.Element("Server").Value
   
   $prevDir = $pwd
   $prevDir
   set-location $dirName
   ScriptDatabase $serverName $dbName
   set-location $prevDir
}

#
# main
#

$xml = [System.Xml.Linq.XElement]::Load((Resolve-Path "$xmlConfig"))

foreach($db in $xml.Elements("Database"))
{
   if ($db.Attribute("Enabled").Value -eq $true)
   {
      SaveSchema $db
   }
}

exit

Here is the XML input file:

<Databases>
   <Database Enabled="true">
      <Name>DatabaseName</Name>
      <Server>ServerName</Server>
   </Database>
   <!-- repeat the Database element if more
        than one database schema to capture -->
</Databases>

Validate a file path using C#

I finally found a good algorithm to validate a file path (had to fix two bugs in it though). I use this static method in Windows Forms when the user is allowed to enter a path. I add a text changed event on the textbox and call this method to enable or disable an OK button.

public static bool ValidateFilepath(string path)
{
   if (path.Trim() == string.Empty)
   {
      return false;
   }

   string pathname;
   string filename;
   try
   {
      pathname = Path.GetPathRoot(path);
      filename = Path.GetFileName(path);
   }
   catch (ArgumentException)
   {
      // GetPathRoot() and GetFileName() above will throw exceptions
      // if pathname/filename could not be parsed.

      return false;
   }

   // Make sure the filename part was actually specified
   if (filename.Trim() == string.Empty)
   {
      return false;
   }

   // Not sure if additional checking below is needed, but no harm done
   if (pathname.IndexOfAny(Path.GetInvalidPathChars()) >= 0)
   {
      return false;
   }

   if (filename.IndexOfAny(Path.GetInvalidFileNameChars()) >= 0)
   {
      return false;
   }
   
   return true;
}

Plug-in architecture (dynamically loading DLLs) using LINQ

For implementing a plug-in architecture using the strategy pattern, this is my preferred way of loading some interfaces (DLLs) at runtime. Make sure you look at the second code example showing how to do the same thing in LINQ.

public List<T> LoadDLL<T>(string path, string pattern)
{
    List<T> plugins = new List<T>();
    foreach (string s in Directory.GetFiles(Path.GetFullPath(path), pattern))
    {
        foreach (Type t in Assembly.LoadFile(s).GetTypes())
        {
            if (!t.IsAbstract && typeof(T).IsAssignableFrom(t))
            {
                plugins.Add((T)Activator.CreateInstance(t));
            }
        }
    }

    return plugins;
}

Now using LINQ...

public List<T> LoadDLL<T>(string path, string pattern)
{
    return Directory.GetFiles(Path.GetFullPath(path), pattern)
        .SelectMany(f => Assembly.LoadFile(f).GetTypes()
            .Where(t => !t.IsAbstract && typeof(T).IsAssignableFrom(t))
            .Select(t => (T)Activator.CreateInstance(t)))
        .ToList();
}

If you want to load an assembly and all the dependent DLLs, you can use the same LINQ query, but use LoadFrom rather than LoadFile.

public List<T> LoadDLL<T>(string path, string pattern)
{
    return Directory.GetFiles(Path.GetFullPath(path), pattern)
        .SelectMany(f => Assembly.LoadFrom(f).GetTypes()
            .Where(t => !t.IsAbstract && typeof(T).IsAssignableFrom(t))
            .Select(t => (T)Activator.CreateInstance(t)))
        .ToList();
}

This can then be called using:

List<Foo> foos = LoadDLL<Foo>(@".\", "*.dll");

Replacing delegates with lamda expressions

If you understand a lamda expression, you realized that it is just another step in the evolution of delegates. Now that Visual Studio intellisense and the .NET compiler can infer from a delegate declaration what parameters are required for a delegate, and their types, we no longer have to use the "delegate" keyword or the parameter types...we just need to specify some parameter names.

For example, an older style event delegate would be done like:

x.Completed += delegate(object sender, EventArgs e) { ... };

Now all we need to code is:

x.Completed += (sender, e) => { ... };

Again, the development environment already knows that the Completed event needs two parameters, a object and a EventArgs; there is no need for us to supply those pieces of information.