SQL Server example of a C# stored procedure

Below is a simple example of using CLR.

Source code for procedure…

using System.IO;
using Microsoft.SqlServer.Server;

public partial class spaceProc {
  [Microsoft.SqlServer.Server.SqlProcedure]
  public static void freeSpace() {
    foreach (DriveInfo drive in DriveInfo.GetDrives()) { 
      if (drive.IsReady) {
        SqlPipe p = SqlContext.Pipe; 
        p.Send(drive.Name + " " + drive.TotalFreeSpace / 1024 / 1024 + "MB free, "+ drive.TotalSize / 1024 / 1024 + "MB total");
      }
    } 
  }
}

…and the command to create the library…

C:\Users\showard>C:\Windows\Microsoft.NET\Framework\v2.0.50727\csc.exe /target:library getFree.cs
Microsoft (R) Visual C# 2010 Compiler version 2.0.50727
Copyright (C) Microsoft Corporation. All rights reserved.


C:\Users\showard>

…and SQL commands to create it…

EXEC sp_configure 'clr enabled' , 1 
RECONFIGURE 
go
ALTER DATABASE test SET TRUSTWORTHY ON
go
CREATE ASSEMBLY freeSpace from 'c:\Users\showard\getFree.dll' WITH PERMISSION_SET = UNSAFE
go
CREATE PROCEDURE getFreeSpace AS EXTERNAL NAME freeSpace.spaceProc.freeSpace
go
exec getFreeSpace

…with the output below…

C:\ 21356MB free, 289782MB total
D:\ 0MB free, 4177MB total

You can also “sign” the assembly, which would be more secure, as what is above opens it up to everything. This is just a quick and dirty way to get it up and running. I may flag a follow-up for this in a few weeks to see if I can take it over the goal line with it totally secure.