Paulo Morgado [EN]

Web Site Globalization With ASP.NET Routing

Paulo Morgado — Sun, 31 Jan 2010 23:29:08 Z

For those who don’t know, I have this web site http://PauloMorgado.NET/ that I use both as a web presence besides my blogs and a playfield.

Because I write both in English and Portuguese, I wanted the web site to have both English and Portuguese versions. This is easily accomplished by using ASP.NET Globalization and Localization.

But I wanted to do more than guessing the user’s language form her/his web browser languages. I wanted something like the MSDN and TechNet web sites have where the culture is embedded in the URL which makes it easy for the user to choose in which language she/he wants to see the web site.

With the release of the ASP.NET Routing, this is as easy as writing a custom route handler that sets the culture for the request and returns the requested page handler.

Something like this:

public class GlobalizationRouteHandler : global::System.Web.Routing.IRouteHandler
{
    System.Globalization.CultureInfo culture;
    System.Globalization.CultureInfo uiCulture;

    public GlobalizationRouteHandler(System.Globalization.CultureInfo culture)
        : this(culture, culture)
    {
    }

    public GlobalizationRouteHandler(CultureInfo culture, CultureInfo uiCulture)
    {
        if (culture == null)
        {
            throw new ArgumentNullException("cultureInfo", "cultureInfo is null.");
        }

        if (uiCulture == null)
        {
            throw new ArgumentNullException("uiCulture", "uiCulture is null.");
        }

        this.culture = culture;
        this.uiCulture = uiCulture;
    }

    private GlobalizationRouteHandler()
    {
    }

    #region IRouteHandler Members

    public IHttpHandler GetHttpHandler(RequestContext requestContext)
    {
        Thread.CurrentThread.CurrentCulture = this.culture;
        Thread.CurrentThread.CurrentUICulture = this.uiCulture;

        string path = "~/" + (requestContext.RouteData.Values["path"] as string);

        var physicalPath = requestContext.HttpContext.Server.MapPath(path);
        if (System.IO.Directory.Exists(physicalPath))
        {
            path = VirtualPathUtility.Combine(path, "Default.aspx");
        }

        var httpHandler = BuildManager.CreateInstanceFromVirtualPath(path, typeof(IHttpHandler)) as IHttpHandler;

        return httpHandler;
    }

    #endregion
}

And now it’s only a matter of registering the handled cultures:

routes.Add("en", new Route("en/{*path}", new GlobalizationRouteHandler(CultureInfo.GetCultureInfo("en-US"))));
routes.Add("pt", new Route("pt/{*path}", new GlobalizationRouteHandler(CultureInfo.GetCultureInfo("pt-PT"))));

Coupling ASP.NET Session State With Forms Authentication

Paulo Morgado — Thu, 28 Jan 2010 01:18:49 Z

Today I was talking with João about a way to couple the lifetime of the ASP.NET session state with the lifetime of Forms Authentication ticket.

My idea was to store the session ID in the UserData property of the forms authentication ticket upon logon and retrieve it with a custom session ID manager.

The login code would be something like this:

protected void Login1_Authenticate(object sender, AuthenticateEventArgs e)
{
    bool isPersistent = this.Login1.RememberMeSet;
    string username = this.Login1.UserName;
    var ticket = new FormsAuthenticationTicket(
        0,
        username,
        DateTime.Now,
        DateTime.Now.AddMinutes(2),
        isPersistent,
        Guid.NewGuid().ToString("N"));

    // Encrypt the ticket.
    var encryptedTicket = FormsAuthentication.Encrypt(ticket);

    // Create the cookie.
    this.Response.Cookies.Add(new HttpCookie(FormsAuthentication.FormsCookieName, encryptedTicket));

    // Redirect back to original URL.
    this.Response.Redirect(FormsAuthentication.GetRedirectUrl(username, isPersistent));
}

For the purpose of this test I am using a Guid as the session ID.

The session ID manager will return this session ID when queried by the session state HTTP module:

public class SessionIdManager : global::System.Web.SessionState.ISessionIDManager
{
    #region ISessionIDManager Members

    public string CreateSessionID(HttpContext context)
    {
        return GetDummySessionIdOrRedirectToLoginPage(context);
    }

    public string GetSessionID(HttpContext context)
    {
        return GetSessionIdFromFormsIdentity(context);
    }

    public void Initialize()
    {
    }

    public bool InitializeRequest(HttpContext context, bool suppressAutoDetectRedirect, out bool supportSessionIDReissue)
    {
        supportSessionIDReissue = false;
        return GetSessionIdFromFormsIdentity(context) == null;
    }

    public void RemoveSessionID(HttpContext context)
    {
    }

    public void SaveSessionID(HttpContext context, string id, out bool redirected, out bool cookieAdded)
    {
        redirected = false;
        cookieAdded = false;
    }

    public bool Validate(string id)
    {
        return true;
    }

    #endregion

    private static string GetSessionIdFromFormsIdentity(HttpContext context)
    {
        var identity = context.User != null ? context.User.Identity as FormsIdentity : null;

        if ((identity == null) || (identity.Ticket == null) || string.IsNullOrEmpty(identity.Ticket.UserData))
        {
            return GetDummySessionIdOrRedirectToLoginPage(context);
        }
        else
        {
            return identity.Ticket.UserData;
        }
    }

    private static string GetDummySessionIdOrRedirectToLoginPage(HttpContext context)
    {
        if (context.Request.CurrentExecutionFilePath.Equals(FormsAuthentication.DefaultUrl, StringComparison.OrdinalIgnoreCase)
                        || context.Request.CurrentExecutionFilePath.Equals(FormsAuthentication.LoginUrl, StringComparison.OrdinalIgnoreCase))
        {
            return Guid.NewGuid().ToString("N");
        }
        else
        {
            FormsAuthentication.RedirectToLoginPage();
            return null;
        }
    }
}

NOTE: Although this might work, it’s just an intellectual exercise and wasn’t fully tested.

Playing With LINQ: Getting Interface Property Implementations

Paulo Morgado — Wed, 27 Jan 2010 02:22:00 Z

Today, my friend Nuno was writing some code to get the PropertyInfos of a class implementation of an interface.

Given this interface:

public interface ISomeInterface
{
    int IntProperty { get; set; }
    string StringProperty { get; }
    void Method();
}

and this class:

public class SomeClass : ISomeInterface
{
    int ISomeInterface.IntProperty { get; set; }
    public int IntProperty { get; private set; }
    public string StringProperty { get; private set; }
    public void Method() { }
}

Nuno wanted to retrieve:

Int32 ISomeInterface.IntProperty
System.String StringProperty

The code is fairly simple. First we need to get the interface mappings for the class:

typeof(SomeClass).GetInterfaceMap(typeof(ISomeInterface)).TargetMethods

and get all PropertyInfos for which the MethodInfo in the list is part of the implementation of the property (is either the set method or the get method).

Something like his:

public static bool Implements(this MethodInfo methodInfo, PropertyInfo propertyInfo)
{
    return (propertyInfo.GetGetMethod(true) == methodInfo) || (propertyInfo.GetSetMethod(true) == methodInfo);
}

But what caught my eye was that, with the above extension methods, I can use LINQ to retrieve a list of the desired PropertyInfos.

Something like this:

public static IEnumerable<PropertyInfo> GetInterfacePropertyImplementation(Type implementer, Type implemented)
{
    return (from propertyInfo in implementer.GetProperties(BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic).AsEnumerable()
            from methodInfo in implementer.GetInterfaceMap(implemented).TargetMethods.AsEnumerable()
            where methodInfo.Implements(propertyInfo)
            select propertyInfo).Distinct();
}

For the sample class and interface, the usage would be something like this:

var q = GetInterfacePropertyImplementation(typeof(SomeClass), typeof(ISomeInterface));

foreach (var p in q)
{
    Console.WriteLine(p);
}

Which would produce the following output:

Int32 ISomeInterface.IntProperty
System.String StringProperty

UPDATED: The previous implementation was overcomplicated and had some string based logic. Kudos to Nuno.

A TraceListener For Tests

Paulo Morgado — Sun, 24 Jan 2010 23:59:00 Z

In my code, I make extensive use of debug assertions (see System.Diagnostics.Debug.Assert). These assertions are very helpful while debugging because you don’t need to step into every line of code to see if all pre-conditions are met. As soon as a pre-condition fails, an assertion failed window will pop up and will allow us to either abort, ignore or go to the assertion instruction (retry).

Imagine you have this code:

private void IKnowForSureThatANullStringWillNeverBePassed(string text)
{
    System.Diagnostics.Debug.Assert(string != null, "text is null.");

    // ...
}

Because this method is private, I have full control of what is passed to the text parameter, therefore I’m asserting that it will never be null. Because it might not be obvious that text will never be null, the assertion also acts as documentation.

I usually run my unit tests and integration test on debug compilations and these assertions would be helpful by making the test fail on an assertion fail instead of running through the method and failing with an NullReferenceException. That’s why I (and more people out there) wrote this simple TraceListener:

public class TraceListener : global::System.Diagnostics.TraceListener
{
    public static readonly TraceListener Default = new TraceListener();

    protected TraceListener()
    {
        this.Name = "Testing Trace Listener";    
    }

    protected TraceListener(string name)
        : base(name)
    {
    }

    public override void Write(string message)
    {
    }

    public override void WriteLine(string message)
    {
    }

    public override void Fail(string message, string detailMessage)
    {
        var builder = new global::System.Text.StringBuilder();

        builder.Append(message);

        if (detailMessage != null)
        {
            builder.Append(" ");
            builder.Append(detailMessage);
        }

        throw new global::Microsoft.VisualStudio.TestTools.UnitTesting.AssertFailedException(builder.ToString());

    }
}

This trace listener won’t write anything to anywhere. It will just throw an AssertFailedException if the Fail method is called, which is what happens when an assertion fails.

Because an assertion window is not desired when running the tests (specially if they are automated tests ran as part of a build process) it’s better to disable the assert UI on the configuration file of the test project.

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <system.diagnostics>
        <assert assertuienabled="false"/>
        <trace>
            <listeners>
                <add name="TestTraceListener"
                     type="PauloMorgado.TestTools.VisualStudio.UnitTesting.Diagnostics.TraceListener, PauloMorgado.TestTools.VisualStudio" />
            </listeners>
        </trace>
    </system.diagnostics>
</configuration>

You can find this and other tools on the PauloMorgado.TestTools on CodePlex.

How To Set Elements Of An Array Of A Private Type Using Visual Studio Shadows

Paulo Morgado — Tue, 19 Jan 2010 00:26:00 Z

Visual Studio uses Publicize to create accessors public for private members and types of a type.

But when you try to set elements of a private array of elements of a private type, things get complicated.

Imagine this hypothetic class to test:

public static class MyClass
{
    private static readonly MyInnerClass[] myArray = new MyInnerClass[10];

    public static bool IsEmpty()
    {
        foreach (var item in myArray)
        {
            if ((item != null) && (!string.IsNullOrEmpty(item.Field)))
            {
                return false;
            }
        }

        return true;
    }

    private class MyInnerClass
    {
        public string Field;
    }
}

If I want to write a test for the case when the array has “non empty” entries, I need to setup the array first.

Using the accessors generated by Visual Studio, I would write something like this:

[TestClass()]
public class MyClassTest
{
    [TestMethod()]
    public void IsEmpty_NotEmpty_ReturnsFalse()
    {
        for (int i = 0; i < 10; i++)
        {
            MyClass_Accessor.myArray[i] = new MyClass_Accessor.MyInnerClass { Field = i.ToString() };
        }

        bool expected = false;
        bool actual;

        actual = MyClass.IsEmpty();

        Assert.AreEqual(expected, actual);
    }
}

But the test will fail because, although the elements of the private array myArray can be read as MyClass_Accessor.MyInnerClass instances, they can’t be written as such.

To do so, the test would have to be written like this:

[TestClass()]
public class MyClassTest
{
    [TestMethod()]
    public void IsEmpty_NotEmpty_ReturnsFalse()
    {
        for (int i = 0; i < 10; i++)
        {
            MyClass_Accessor.ShadowedType.SetStaticArrayElement("myArray", new MyClass_Accessor.MyInnerClass { Field = i.ToString() }.Target, i);
        }

        bool expected = false;
        bool actual;

        actual = MyClass.IsEmpty();

        Assert.AreEqual(expected, actual);
    }
}

But, this way, we loose all the strong typing of the accessors because we need to write the name of the array field.

Because the accessor for the field is a property, we could write a set of extension methods that take care of getting the field name for us. Something like this:

public static class PrivateypeExtensions
{
    public static void SetStaticArrayElement<T>(this PrivateType self, Expression<Func<T[]>> expression, T value, params int[] indices)
    {
        object elementValue = (value is BaseShadow) ? (value as BaseShadow).Target : value;

        self.SetStaticArrayElement(
            ((PropertyInfo)((MemberExpression)(expression.Body)).Member).Name,
            elementValue,
            indices);
    }

    public static void SetStaticArrayElement<T>(this PrivateType self, Expression<Func<T[]>> expression, BindingFlags invokeAttr, T value, params int[] indices)
    {
        object elementValue = (value is BaseShadow) ? (value as BaseShadow).Target : value;

        self.SetStaticArrayElement(
            ((PropertyInfo)((MemberExpression)(expression.Body)).Member).Name,
            invokeAttr,
            elementValue,
            indices);
    }
}

Now, we can write the test like this:

[TestClass()]
public class MyClassTest
{
    [TestMethod()]
    public void IsEmpty_NotEmpty_ReturnsFalse()
    {
        for (int i = 0; i < 10; i++)
        {
            MyClass_Accessor.ShadowedType.SetStaticArrayElement(() => MyClass_Accessor.myArray, new MyClass_Accessor.MyInnerClass { Field = i.ToString() }, i);
        }

        bool expected = false;
        bool actual;

        actual = MyClass.IsEmpty();

        Assert.AreEqual(expected, actual);
    }
}

It’s not the same as the first form, but it’s strongly typed and we’ll get a compiler error instead of a test run error if we change the name of the myArray field.

You can find this and other tools on the PauloMorgado.TestTools on CodePlex.

Extended WebBrowser Control - Version 0.0.0.0 Uploaded

Paulo Morgado — Wed, 02 Dec 2009 02:03:48 Z

After a long time, I finally managed to upload a version of the Extended WebBrowser Control to CodePlex.

It's still a work in progress, but it's usable. Feel free to download, comment and file issues. A nice tabbed browser demo is included.

Compiling .NET 1.1 Projects In Visual Studio 2008

Paulo Morgado — Sun, 25 Oct 2009 23:09:44 Z

After having put my .NET 1.1 application running on the .NET 2.0 runtime (^), I’m planning on migrating it to .NET 2.0, but not all at once.

Because I don’t want to have 2 solutions (one on Visual Studio 2003 for the .NET 1.1 assemblies and another on Visual Studio 2008 for the .NET 2.0 assemblies) I decide to try using MSBee and have only one Visual Studio 2008 solution.

MSBee has a CodePlex project. You can download it from there or from Microsoft Downloads. Because the build on Microsoft Downloads seemed to be the most stable one, that was the one I downloaded and installed. The package comes with a Word document that explains all that needs to be done.

Before you can install and use MSBee you’ll need to install the .NET 1.1 SDK.

Having everything installed, I just opened the Visual Studio 2003 solution in Visual Studio 2008 and let it convert the solution and projects (near 30).

After the conversion, for building the projects with the .NET 1.1 C# compiler, the project files need to be edited to add the override the default targets with the MSBee ones by adding the MSBee imports after the default imports for the language:

<Import Project="$(MSBuildBinPath)\Microsoft.CSharp.targets" />
<Import Project="$(MSBuildExtensionsPath)\MSBee\MSBuildExtras.FX1_1.CSharp.targets" />

Another change needed (for Visual Studio 2008 - I don't know if it was needed for Visual Studio 2005) is the tools version. MSBee needs version 2.0. To change that you'll have to change the ToolsVersion attribute of the project’s root element:

<Project DefaultTargets="Build" ToolsVersion="2.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">

MSBee likes has own idea about output paths and I had set up custom output paths on my project. There’s information about this on the documentation but I decided to simply comment that out of the $(MSBuildExtensionsPath)\MSBee\MSBuildExtras.FX1_1.Common.targets file:

<!-- Paulo
<Choose>
  <When Condition=" '$(BaseFX1_1OutputPath)' == '' ">
    <PropertyGroup>
      <OutputPath>bin\FX1_1\</OutputPath>
    </PropertyGroup>
  </When>
  <Otherwise>
    <PropertyGroup>
      <OutputPath>$(BaseFX1_1OutputPath)</OutputPath>
      <OutputPath Condition=" !HasTrailingSlash('$(OutputPath)') ">$(OutputPath)\</OutputPath>
    </PropertyGroup>
  </Otherwise>
</Choose>
-->

<!-- Paulo
<PropertyGroup>
  <BaseIntermediateOutputPath>obj\FX1_1\</BaseIntermediateOutputPath>
  <IntermediateOutputPath Condition=" '$(PlatformName)' == 'AnyCPU' ">$(BaseIntermediateOutputPath)$(Configuration)\</IntermediateOutputPath>
  <IntermediateOutputPath Condition=" '$(PlatformName)' != 'AnyCPU' ">$(BaseIntermediateOutputPath)$(PlatformName)\$(Configuration)\</IntermediateOutputPath>

  <OutputPath Condition=" '$(PlatformName)' == 'AnyCPU' ">$(OutputPath)$(Configuration)\</OutputPath>
  <OutputPath Condition=" '$(PlatformName)' != 'AnyCPU' ">$(OutputPath)$(PlatformName)\$(Configuration)\</OutputPath>
  
  <- Once OutputPath is determined, set OutDir to its value. ->
  <OutDir>$(OutputPath)</OutDir>
</PropertyGroup>
-->

This all seemed to work fine on my old Windows XP machine without any third party Visual Studio plug-ins, but when I tried it on my Windows Vista X64 machine, I came across some problems:

License Compiler

Because I'm using Infragistics' controls, there's a licences.licx file and the build will compile it. And that proved to be a problem.

MSBee copies all the files it needs to the build process to a temporary folder, builds it in there and then copies the result to the output path.

LC.exe seemed to never be able to find all the assemblies it needed. Searching seemed to me to be an old issue (even from the .NET 1.1 times) and the solution always pointed to not compile the license file. So, I commented that part out of the $(MSBuildExtensionsPath)\MSBee\MSBuildExtras.FX1_1.Common.targets file:

<Target
    Name="CompileLicxFiles"  Condition="'@(_LicxFile)'!=''"
    DependsOnTargets="$(CompileLicxFilesDependsOn)"
    Inputs="$(MSBuildAllProjects);@(_LicxFile);@(ReferencePath);@(ReferenceDependencyPaths)"
    Outputs="$(IntermediateOutputPath)$(TargetFileName).licenses">

  <!--
  <LC
      Sources="@(_LicxFile)"
      LicenseTarget="$(TargetFileName)"
      OutputDirectory="$(IntermediateOutputPath)"
      OutputLicense="$(IntermediateOutputPath)$(TargetFileName).licenses"
      ReferencedAssemblies="@(ReferencePath);@(ReferenceDependencyPaths)"
      ToolPath="$(TargetFrameworkSDKDirectory)bin\">

    <Output TaskParameter="OutputLicense" ItemName="CompiledLicenseFile"/>
    <Output TaskParameter="OutputLicense" ItemName="FileWrites"/>

  </LC>
  -->
</Target>

Resource Generator

Although this worked fine on the command line, inside Visual Studio ResGen.exe would throw some error and needed to be closed.

Looking at the Windows Application Log I found out this:

Faulting application Resgen.exe, version 1.1.4322.573, time stamp 0x3e559b5f, faulting module MockWeaver.dll, version 0.0.0.0, time stamp 0x4adb072e, exception code 0xc0000005, fault offset 0x00018fac, process id 0x4a50, application start time 0x01ca53c14488a2fb.

MockWeaver.dll belongs to Isolator and I just disable it when building inside Visual Studio. I was hoping to start using Isolator on this project, but, for now, I can't.

I hope this can be of some help and, if you need more, you’ll probably find it at the MSBee’s CodePlex forum.

The bottom line is: You don’t need Visual Studio 2003!

Running .NET 1.1 Applications On .NET 2.0

Paulo Morgado — Tue, 20 Oct 2009 00:40:20 Z

One of the applications I develop is a .NET 1.1 Windows Forms application used by more than 5000 users and critical for the business.

Being a complex and critical application, porting it to the 2.0 runtime just because it was not an option because it would mean installing the new runtime and framework on the stable environment of the workstations (Windows XP) and test fully the application. That was not an option.

As time went by, a developer received a brand new laptop with Windows Vista. Since he only needed ,NET 2.0 for his developments, he never installed .NET 1.1.

Another developer on my team had already tried to port the application to .NET 2.0 and run into some issues:

The main component of this application is the Web Browser Control. This control derives from AxHost, which changed going from 1.1 to 2.0 and needed major changes to compile for the 2.0 framework.
Another change was that mixing synchronous and asynchronous calls is not allowed in the 2.0 framework and we had, at least, one of those in our use of HttpWebRequest/HttpWebResponse.

The .NET 2.0 runtime and frameworks were developed to be highly compatible with applications written and compiled to the 1.1 runtime and frameworks. In fact, some of the changes were just applying the ObsoleteAttribute set to throw a compiler error when used, which doesn’t prevent its use by already compiled assemblies. This was the case of the WebBrowserControl/AxHost and just using the assembly compiled for .NET 1.1 would probably run fine. And it did.

The synchronous/asynchronous was also very easy to fix. All it took was changing this:

request.GetRequestStream()

into this:

request.EndGetRequestStream(response.BeginGetRequestStream(null, null))

And it all worked as if it was running on .NET 1.1.

But that’s not the end of it. Latter came a requirement to use, in one of the web pages that ran in the web browser control, an ActiveX component developed in .NET 2.0.

But that time, the 2.0 runtime and framework were already installed on the workstations.

But how would we force the application to run in the 2.0 runtime if the 1.1 runtime was still there?

As simple as adding this to the configuration file (App.config):

<configuration>
  <startup>
    <requiredRuntime version="v2.0.50727" safemode="true"/>
  </startup>
</configuration>

LINQ To SQL Tips & Tricks: String Operations

Paulo Morgado — Tue, 13 Oct 2009 00:51:43 Z

LINQ brought developers a very user friendly and domain independent style of writing queries.

The fact that the way queries are written is domain independent doesn’t mean that any query will compile the same way or even run the same way. You’ll always need to know how the provider will behave.

LINQ To Objects, for example, will compile queries as a Func<> delegate and the query methods will return IEnumerable(T) implementations.

On the other hand, LINQ To SQL will compile queries as an Expression<Func<>> (which is, in fact, an expression tree) instance and the query methods will return IQueryable(T) implementations.

Because LINQ To SQL queries are compiled to an expression tree, that allows the provider to treat the query elements as it sees fit.

In this case, this means that all operations that can be done on the database will be done on the database and the developer must be aware of this when she/he is writing the queries.

Lets take an example using the AdventureWorks database (if you don’t have it, you can download it from here).

I want to build a list of salutation for every employee that has the SalariedFlag set, in the form of:

[Mr.|Mrs.|Miss] <first name> <middle name> <last name>

But there’s also one detail about the data in the database: FirstName, MiddleName and LastName may have trailing spaces and I don’t want them.

This is a simple query like this:

var q1 = from e in context.Employees
         where e.SalariedFlag
         select
            ((e.Gender == 'F') ? ((e.MaritalStatus == 'S') ? "Miss" : "Mrs.") : "Mr.") + " " +
            e.Person.FirstName.Trim() +
            (e.Person.MiddleName == null || e.Person.MiddleName.Trim().Length == 0 ? " " : " " + e.Person.MiddleName.Trim() + " ") +
            e.Person.LastName.Trim();

and it will be executed as:

SELECT ((((
    (CASE
        WHEN UNICODE([t0].[Gender]) = @p0 THEN
            (CASE
                WHEN UNICODE([t0].[MaritalStatus]) = @p1 THEN @p2
                ELSE @p3
             END)
        ELSE CONVERT(NVarChar(4),@p4)
     END)) + @p5) + LTRIM(RTRIM([t1].[FirstName]))) + (
    (CASE
        WHEN ([t1].[MiddleName] IS NULL) OR (LEN(LTRIM(RTRIM([t1].[MiddleName]))) = @p6) THEN CONVERT(NVarChar(MAX),@p7)
        ELSE (@p8 + LTRIM(RTRIM([t1].[MiddleName]))) + @p9
     END))) + LTRIM(RTRIM([t1].[LastName])) AS [value]
FROM [HumanResources].[Employee] AS [t0]
INNER JOIN [Person].[Person] AS [t1] ON [t1].[BusinessEntityID] = [t0].[BusinessEntityID]
WHERE [t0].[SalariedFlag] = 1
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [70]
-- @p1: Input Int (Size = 0; Prec = 0; Scale = 0) [83]
-- @p2: Input NVarChar (Size = 4; Prec = 0; Scale = 0) [Miss]
-- @p3: Input NVarChar (Size = 4; Prec = 0; Scale = 0) [Mrs.]
-- @p4: Input NVarChar (Size = 3; Prec = 0; Scale = 0) [Mr.]
-- @p5: Input NVarChar (Size = 1; Prec = 0; Scale = 0) [ ]
-- @p6: Input Int (Size = 0; Prec = 0; Scale = 0) [0]
-- @p7: Input NVarChar (Size = 1; Prec = 0; Scale = 0) [ ]
-- @p8: Input NVarChar (Size = 1; Prec = 0; Scale = 0) [ ]
-- @p9: Input NVarChar (Size = 1; Prec = 0; Scale = 0) [ ]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.4926

If you notice the query, there are a lot of text operations going on for each row.

Depending on the number of rows or database load this can prove to be very bad. The result might even be just a timeout.

So, how do we force the string operations to occur on the client instead of the database?

Only IQueryable<T> will be translated to T-SQL. So, all we need to do is change the type of the enumerator being iterated.

One way to do this is using the the AsEnumerable method of the Enumerable class.

The query would now be written as:

var q2 = from e in context.Employees.Where(e => e.SalariedFlag).AsEnumerable()
         select
            ((e.Gender == 'F') ? ((e.MaritalStatus == 'S') ? "Miss" : "Mrs.") : "Mr.") + " " + e.Person.FirstName.Trim() +
            (e.Person.MiddleName == null || e.Person.MiddleName.Trim().Length == 0 ? " " : " " + e.Person.MiddleName.Trim() + " ") +
            e.Person.LastName.Trim();

and it will be executed as:

SELECT
    [t0].[BusinessEntityID],
    [t0].[LoginID],
    [t0].[NationalIDNumber],
    [t0].[JobTitle],
    [t0].[MaritalStatus],
    [t0].[BirthDate],
    [t0].[Gender],
    [t0].[HireDate],
    [t0].[SalariedFlag],
    [t0].[VacationHours],
    [t0].[SickLeaveHours],
    [t0].[CurrentFlag],
    [t0].[rowguid],
    [t0].[ModifiedDate],
    [t1].[BusinessEntityID] AS [BusinessEntityID2],
    [t1].[PersonType],
    [t1].[NameStyle],
    [t1].[Title],
    [t1].[FirstName],
    [t1].[MiddleName],
    [t1].[LastName],
    [t1].[Suffix],
    [t1].[EmailPromotion],
    [t1].[AdditionalContactInfo],
    [t1].[Demographics],
    [t1].[rowguid] AS [rowguid2],
    [t1].[ModifiedDate] AS [ModifiedDate2]
FROM [HumanResources].[Employee] AS [t0]
INNER JOIN [Person].[Person] AS [t1] ON [t1].[BusinessEntityID] = [t0].[BusinessEntityID]
WHERE [t0].[SalariedFlag] = 1
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.4926

As you can notice, text operations are no longer done on the database, but all the columns of both tables are being returned. And this is still a bad thing because we are using network bandwidth with data that won’t be used.

The way to choose the columns that will be retrieved from the database is by selecting only the ones wanted in the select statement. But because we still want string operations the be done on the client, we’ll need to project the desired columns into an intermediary object. Since we won’t need this object outside the query, we’ll use an anonymous type.

The query would now be written as:

var q3 = from n in
             (
                 from e in context.Employees
                 where e.SalariedFlag
                 select new
                 {
                     Gender = e.Gender,
                     MaritalStatus = e.MaritalStatus,
                     FirstName = e.Person.FirstName,
                     MiddleName = e.Person.MiddleName,
                     LastName = e.Person.LastName
                 }
             ).AsEnumerable()
         select ((n.Gender == 'F') ? ((n.MaritalStatus == 'S') ? "Miss" : "Mrs.") : "Mr.") + " " + n.FirstName.Trim()
         + (n.MiddleName == null || n.MiddleName.Trim().Length == 0 ? " " : " " + n.MiddleName.Trim() + " ")
         + n.LastName.Trim();

and it will be executed as:

SELECT
    [t0].[Gender],
    [t0].[MaritalStatus],
    [t1].[FirstName],
    [t1].[MiddleName],
    [t1].[LastName]
FROM [HumanResources].[Employee] AS [t0]
INNER JOIN [Person].[Person] AS [t1] ON [t1].[BusinessEntityID] = [t0].[BusinessEntityID]
WHERE [t0].[SalariedFlag] = 1
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.4926

Notice the call to Enumerable.As Enumerable to translate the LINQ To SQL query into a LINQ To Objects query.

And, to end this long blog post, if you don’t use any string operations on the query, they, obviously, won’t be translated to T-SQL:

var q4 = from e in context.Employees
         where e.SalariedFlag
         select BuildSalutation(e.Gender, e.MaritalStatus, e.Person.FirstName, e.Person.MiddleName, e.Person.LastName);

where BuildSalutation is implemented as:

private static object BuildSalutation(char gender, char maritalStatus, string firstName, string middleName, string lastName)
{
    return ((gender == 'F') ? ((maritalStatus == 'S') ? "Miss" : "Mrs.") : "Mr.") + " "
        + firstName.Trim()
        + (middleName == null || middleName.Trim().Length == 0 ? " " : " " + middleName.Trim() + " ")
        + lastName.Trim();
}

and it will be executed as:

SELECT
    [t0].[Gender] AS [gender],
    [t0].[MaritalStatus] AS [maritalStatus],
    [t1].[FirstName] AS [firstName],
    [t1].[MiddleName] AS [middleName],
    [t1].[LastName] AS [lastName]
FROM [HumanResources].[Employee] AS [t0]
INNER JOIN [Person].[Person] AS [t1] ON [t1].[BusinessEntityID] = [t0].[BusinessEntityID]
WHERE [t0].[SalariedFlag] = 1
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.4926

Have you noticed that this T-SQL query is pretty much the same in the previous example?

If you are still reading this, I hope you now aware of how you write your LINQ To SQL queries affect the generated T-SQL.

PowerShell For The .NET Developer

Paulo Morgado — Fri, 18 Sep 2009 00:03:12 Z

Some time ago I needed to have the validationKey of the machineKey element of an ASP.NET application changed and found out that ASP.NET doesn’t provide a command-line tool (or any other) to do this.

Looking around I found several applications and code samples to do it, but to have a system administrator do this I needed to test and document the application and it was to much work for such task.

I’ve always been a supporter of the idea of PowerShell but I never used it my self. Just because I almost always have Visual Studio open and writing a simple console application is quicker and easier than learning PowerShell.

This time I decide that I would do a PowerShell script instead.

In C# I would have done something like this:

class Program
{
    private static string GenerateKey()
    {
        var buff = new byte[64];
        (new System.Security.Cryptography.RNGCryptoServiceProvider()).GetBytes(buff);
        var sb = new System.Text.StringBuilder();
        foreach (var b in buff)
        {
            sb.AppendFormat("{0:X2}", b);
        }
        return sb.ToString();
    }

    private static void Main(string[] args)
    {
        var path = args[0];
        var config = System.Web.Configuration.WebConfigurationManager.OpenMachineConfiguration(path);
        var systemWeb = config.GetSectionGroup("system.web") as System.Web.Configuration.SystemWebSectionGroup;
        var machineKey = systemWeb.MachineKey;
        machineKey.ValidationKey = GenerateKey();
        config.Save(System.Configuration.ConfigurationSaveMode.Modified);
    }
}

How would it be in PowerShell? As simple as this:

function GenerateKey
{
    [System.Byte[]]$buff = 0..63
    (new-object System.Security.Cryptography.RNGCryptoServiceProvider).GetBytes($buff)
    $sb = new-object System.Text.StringBuilder(128)
    for($i = 0; ($i -lt $buff.Length); $i++)
    {
        $sb = $sb.AppendFormat("{0:X2}", $buff[$i])
    }
    return $sb.ToString()
}

[System.Reflection.Assembly]::LoadWithPartialName("System.Web")
$config = [System.Web.Configuration.WebConfigurationManager]::OpenWebConfiguration("<path>")
$systemWeb = $config.GetSectionGroup("system.web");
$machineKey = $systemWeb.MachineKey
$machineKey.ValidationKey=GenerateKey
$config.save("Modified")

Wonder how I got from no knowledge of PowerShell to this? Simple. Something that every real .NET developer has and loves: .NET Reflector (with a PowerShell add-in, of course).

From Vista To 7 With WET And WHS

Paulo Morgado — Wed, 16 Sep 2009 23:19:52 Z

My son inherits my old machines.

His current one was a machine that started with Windows Vista x86 Ultimate RTM, then SP1 and finally SP2. Also, along the way, several versions of Microsoft Office, .NET, Visual Studio, SQL Server and much more.

I usually have dozens of applications installed. Retrieving license keys and installing them a gain is such an hassle that I choose to upgrade whenever I can.

Now that Windows 7 is out and there’s a beta of Office 2010, my son wanted to upgrade the machine.

Because the machine had already gone through all those updates and he didn’t like the way I had partitioned the disk, I recommended him to format the disk and do a clean install.

One of the hassles of a clean install is that you loose all your settings such as Internet Explorer settings and favorites and Microsoft Outlook accounts and PSTs (specially if you have IMAP accounts).

Since I had already successfully used Windows Easy Transfer to migrate my work laptop from Windows XP (x86) to Windows Vista x64 Enterprise, I told him not to worry.

Windows Easy Transfer guides you through the process of transferring files and settings from one Windows installation to another.

With Windows Easy Transfer you can transfer:

Files and folders.

Everything within the Documents, Pictures, and Shared Documents folders. Using advanced options, you can select additional files and folders to transfer.
E‑mail settings, contacts, and messages.

Messages, account settings, and address books from Microsoft Outlook Express, Outlook, Windows Mail, and other e‑mail programs.
Program settings.

Settings that keep your programs configured as you had them on your old installation. You must first install the programs on your new computer, because Windows Easy Transfer does not transfer the programs themselves. Some programs might not work on this version of Windows, including security programs, antivirus programs, firewall programs (your new computer should already have a firewall running to help ensure safety during the transfer), and programs with software drivers.
User accounts and settings.

Color schemes, desktop backgrounds, network connections, screen savers, fonts, Start menu options, taskbar options, folders, specific files, network printers and drives, and accessibility options.
Internet settings and favorites.

Internet connection settings, favorites, and cookies.
Music.

Electronic music files, playlists, and album art.
Pictures and video.

Pictures—which includes any visual file type (for example, .jpg, .bmp, .gif) - and personal videos.

After saving everything to the .MIG file, all it took was installing Windows 7, Office 2010 and import the settings back.

To get the other files and folders that were on the disk before being formatted, since we have a Windows Home Server that backs up all the PCs in the house , all it was needed was mounting one of the old backups as a disk and copying the files back.

It’s so easy that he did it al by himself while and he just turned 14. So, if you need to do something like this, don’t stress. It’s that easy.

ReMIX 09 Is Coming To Lisbon, Portugal

Paulo Morgado — Wed, 09 Sep 2009 18:50:28 Z

For the first time, ReMIX is coming to Portugal.

The event will have a keynote and 3 tracks (Web Developer, UX and Architect) with 4 sessions each by the best speakers in each field.

Don’t miss it! Register now!

Windows Live Messenger Unable To Connect With Error Code 80040200

Paulo Morgado — Tue, 01 Sep 2009 23:55:17 Z

Today I came across this issue when trying to sign in to Windows Live Messenger.

Everyone I talked to was able to sign in, though, so I tried another account and was also able to sign in.

Binging around, I found several blog posts pointing to a post in the Messenger Support blog with the solution to this problem. In my case (Windows 7 x64) the solution would be to remove the %LOCALAPPDATA%\Microsoft\Windows Live Contacts (usually C:\Users\<Windows Logon name>\AppData\Local\Microsoft\Windows Live Contacts) folder.

Instead of removing the folder, I thought of renaming it to avoid removing my contacts for all account that were able to sign in. When I tried it, I got an error stating the the folder was already being used by another application.

Since I had Windows Live Mail open and assumed it uses the same contact store, I closed it and was able to sign in to messenger with no problems.

As a developer and architect, I find it disturbing that such errors are presented to the user. The message hinted that there was a problem signing in to the server and, as it turned out, it was a problem with only one account for only one Windows user in only one machine.

So, developers and architects out there (me included), always give the user an error message meaningful to what problem he/she is running into. Adding technical data to help support is nice but should be expressly accessed by the user (Windows Live Messenger got that part right).

Hurrah! MVP for the seventh consecutive year!

Paulo Morgado — Thu, 02 Jul 2009 00:31:46 Z

After all these years, it's still an honor, a privilege ... and a few stressful days.

Microsoft Tech•Ed Europe 2009 Registration Opened

Paulo Morgado — Mon, 22 Jun 2009 22:42:44 Z

Microsoft Tech•Ed Europe 2009 Registration is now open.

There are a few novelties this year:

Due to attendee feedback, both the Developer and IT Pro editions will take place at the same time (November, 9-13).
The registration site uses Windows Live™ ID for authentication.
Registration prices are based on a tiered approach with the earlier you registration the more you will save. Register first, save the most!

Top Reasons To Attend Tech·Ed Europe 2009

EVERYONE CAN LEARN MORE ABOUT:
- MICROSOFT WINDOWS 7 - Learn about Microsoft's next-generation operating system
  
  Advancements Windows 7 brings to user productivity, security and PC management - all of which save businesses time and money.
- MICROSOFT OFFICE and new ways to maximize business productivity
  
  The Office System track will help you deploy, adopt and develop on business productivity applications.
  
  Learn about deploying Microsoft Office to your users, building pervasive Microsoft Office SharePoint solutions and managing your work with Microsoft Business Productivity Visio.
DEVELOPERS CAN LEARN MORE ABOUT:
- STREAMLINING your way to cloud applications
  
  Find out how The Azure™ Services Platform reduces the need for up-front technology purchases, and enables developers to quickly and easily create applications running in the cloud.
- SECURITY that helps cut application support costs
  
  Find out how Microsoft’s ForeFront technologies can secure your IT environment faster, and help you write more secure code to eliminate application support costs.
- TRUTH about maximizing deployments and search
  
  Find out how SharePoint can help create that central repository, maximizing deployments and search that eliminates multiple versions and misunderstandings about the source of the "truth" on any given project.
IT PROFESSIONALS CAN LEARN MORE ABOUT:
- VIRTUALISATION that helps leverage existing investments
  
  Get an in-depth understanding of today’s low-cost virtualization technologies and how they can help you make the most of your current software and hardware investments.
- HOSTING for economies of scale
  
  Learn how to take advantage of Microsoft’s hosted services to scale your IT department’s communications and collaboration offering. You’ll also lower your hardware, management and power costs.
- UNIFYING to reduce costs and help increase efficiencies
  
  Truly unified communications create a single communication infrastructure. That means reduced costs for PBX enhancements while improving productivity through unified inboxes along with rules that help workers manage interruptions.
- DEPLOYMENT - maximize your existing IT infrastructure and do more with what you've got
  
  Learn how to get maximum value out of your existing IT infrastructure.

Playing With SQL Server CLR Integration – Part IV (Deploying To SQL Server 2005)

Paulo Morgado — Sat, 13 Jun 2009 08:39:10 Z

With all developed and tested on my laptop using SQL Server 2008, it’s time to deploy to the company’s test machine running SQL Server 2005.

The first thing I ran into when executing:

CREATE ASSEMBLY [MyAssembly]
AUTHORIZATION [dbo]
FROM '...\MyAssembly.dll'
WITH PERMISSION_SET = SAFE
GO

was:

Msg 10327, Level 14, State 1, Line 1
Assembly 'MyAssembly' references assembly 'system.xml.linq, version=3.5.0.0, culture=neutral, publickeytoken=b77a5c561934e089.',
which is not present in the current database.
SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from,
but that operation has failed (reason: 2(The system cannot find the file specified.)).
Please load the referenced assembly into the current database and retry your request.

Looks like SQL Server 2005 doesn’t know about .NET Framework 3.5. I’d just load the assemblies being used: System.Core and System.Linq.Xml:

CREATE ASSEMBLY [System.Core]
AUTHORIZATION [dbo]
FROM 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.5\System.Core.dll'
WITH PERMISSION_SET = SAFE
GO

Not so easy:

Warning: The Microsoft .Net frameworks assembly 'system.core, version=3.5.0.0, culture=neutral, publickeytoken=b77a5c561934e089, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.
Msg 6218, Level 16, State 2, Line 1
CREATE ASSEMBLY for assembly 'System.Core' failed because assembly 'System.Core' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database. CLR Verifier error messages if any will follow this message
[ : System.Diagnostics.Eventing.EventProvider::EtwRegister][mdToken=0x600003b][offset 0x0000003D][found Native Int][expected unmanaged pointer] Unexpected type on the stack.
[ : System.Diagnostics.Eventing.EventProvider::EncodeObject][mdToken=0x6000046][offset 0x00000000] Unmanaged pointers are not a verifiable type.
[ : System.Diagnostics.Eventing.EventProvider::WriteMessageEvent][mdToken=0x6000047][offset 0x0000003C][found ref 'System.String'] Expected numeric type on the stack.
[ : System.Diagnostics.Eventing.EventProvider::WriteEvent][mdToken=0x6000049][offset 0x0000012E] Instruction cannot be verified.
[ : System.Diagnostics.Eventing.EventProvider::WriteEvent][mdToken=0x6000049][offset 0x00000030] Instruction cannot be verified.
[ : System.Diagnostics.Eventing.EventProvider::WriteEvent][mdToken=0x600004a][offset 0x0000005F][found ref 'System.String'] Expected numeric type on the stack.
[ : System.Diagnostics.Eventing.EventProvider::WriteEvent][mdToken=0x600004b][offset 0x00000010][found unmanaged pointer][expected unmanaged pointer] Unexpected type on the stack.
[ : System.Diagnostics.Eventing.EventProvider::WriteTransferEvent][mdToken=0x600004c][offset 0x0000007D] Instruction cannot be verified.
[ : System.Diagnostics.Eventing.EventProvider::WriteTransferEvent][mdToken=0x600004c][offset 0x00000309][found Native Int][expected unmanaged pointer] Unexpected type on the stack.
[ : System.Diagnostics.Eventing.EventProvider::WriteTransferEvent][mdToken=0x600004d][offset 0x0000001B][found unmanaged pointer][expected unmanaged pointer] Unexpected type on the stack.
[ : System.Security.Cryptography.CapiNative::ImportSymmetricKey][mdToken=0x60007c2][offset 0x00000071][found address of Byte] Expected numeric type on the stac...

Ok. I’d just load it with PERMISSION_SET = UNSAFE:

CREATE ASSEMBLY [MyAssembly]
AUTHORIZATION [dbo]
FROM '...\MyAssembly.dll'
WITH PERMISSION_SET = UNSAFE
GO

Not yet:

Warning: The Microsoft .Net frameworks assembly 'system.core, version=3.5.0.0, culture=neutral, publickeytoken=b77a5c561934e089, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.
Msg 10327, Level 14, State 1, Line 1
CREATE ASSEMBLY for assembly 'System.Core' failed because assembly 'System.Core' is not authorized for PERMISSION_SET = UNSAFE.
The assembly is authorized when either of the following is true: the database owner (DBO) has UNSAFE ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission.
If you have restored or attached this database, make sure the database owner is mapped to the correct login on this server.
If not, use sp_changedbowner to fix the problem.

Solved:

ALTER DATABASE MyDatabase SET TRUSTWORTHY ON
GO

Only than I was able to load the .NET 3.5 assemblies:

CREATE ASSEMBLY [System.Core]
AUTHORIZATION [dbo]
FROM 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.5\System.Core.dll'
WITH PERMISSION_SET = UNSAFE
GO

CREATE ASSEMBLY [System.Xml.Linq]
AUTHORIZATION [dbo]
FROM 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.5\System.Xml.Linq.dll'
WITH PERMISSION_SET = UNSAFE
GO

With all dependencies in place, I tried to load the assembly with PERMISSION_SET = SAFE with no luck:

Msg 6212, Level 16, State 1, Line 1
CREATE ASSEMBLY failed because method 'ShortPropsToXml' on type 'ShortProps'  in safe assembly 'Esi.SA.Encyclopedia' is storing to a static field.
Storing to a static field is not allowed in safe assemblies.

It had to be with PERMISSION_SET = UNSAFE.

After successfully loading the assemblies, I was finally able to create the Transact-SQL definitions for the functions (see Part I and Part II).

Now the DBAs won’t definitely let me use this, but it was fun to build it.

Playing With SQL Server CLR Integration – Part III

Paulo Morgado — Fri, 12 Jun 2009 00:11:00 Z

You might have noticed that I used LINQ in my last Playing With SQL Server CLR Integration posts (Part I, Part II).

I couldn’t make it work with the standard Visual Studio 2008 SQL CLR project template. Changing the Target Framework to .NET Framework 3.5 wasn’t enough. I had to edit the .csproj file by hand:

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="3.5" DefaultTargets="Build" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <!— ... -->
  <ItemGroup>
    <!— ... -->
    <Reference Include="System.Core">
      <RequiredTargetFramework>3.5</RequiredTargetFramework>
    </Reference>
    <Reference Include="System.Xml.Linq">
      <RequiredTargetFramework>3.5</RequiredTargetFramework>
    </Reference>
  </ItemGroup>
  <!— ... -->
</Project>

Playing With SQL Server CLR Integration – Part II

Paulo Morgado — Thu, 11 Jun 2009 23:52:37 Z

On my last post, I showed how to convert a property bag stored in text to a CLR Table-Valued Function.

I started thinking that I could retrieve the property values, but I couldn’t change them or add new properties.

Passing a table as a parameter is still not possible in SQL Server 2005. And that would force me to load the table into a variable, change it and load it back into the property bag.

Or I could create functions to Create, Update and Delete properties from the property bag.

Or I could just use XML. All it takes is creating a CLR Scalar-Valued Function to convert the property bag to an XML representation and another to convert the XML back to the property bag format.

It still makes me load the property bag into a variable if I want to change it, but it’s usable in SQL Server 2005, which was the targeted platform.

Converting the property bag to an XML document is easy using the previously created enumerator:

[Microsoft.SqlServer.Server.SqlFunction(
    Name = "ShortPropsToXml",
    IsDeterministic = true,
    IsPrecise = false,
    DataAccess = DataAccessKind.None,
    SystemDataAccess = SystemDataAccessKind.None)]
public static SqlXml ShortPropsToXml(string shortPropsText)
{
    var xml = new XElement("ShortProps",
        from shortProp in ShortPropsEnumerable(shortPropsText)
        select new XElement("p",
            new XAttribute("n", shortProp.Key),
            new XCData(shortProp.Value)));

    using (var buffer = new MemoryStream())
    {
        using (var xmlWriter = XmlWriter.Create(buffer, new XmlWriterSettings { CheckCharacters = false }))
        {
            xml.WriteTo(xmlWriter);
        }

        buffer.Position = 0;

        using (XmlReader xmlReader = XmlReader.Create(buffer, new XmlReaderSettings { CheckCharacters = false }))
        {
            return new SqlXml(xmlReader);
        }
    }
}

Converting the an XML document back into the property bag format is also easy:

[Microsoft.SqlServer.Server.SqlFunction(
    Name = "XmlToShortProps",
    IsDeterministic = true,
    IsPrecise = false,
    DataAccess = DataAccessKind.None,
    SystemDataAccess = SystemDataAccessKind.None)]
public static SqlChars XmlToShortProps(SqlXml shortPropsXml)
{
    var xml = XDocument.Parse(shortPropsXml.Value);

    var textBuilder = new StringBuilder();
    foreach (var item in xml.Document.Element("ShortProps").Elements("p"))
    {
        textBuilder.AppendFormat("[[[{1}]]]{0}{2}{0}", Environment.NewLine, item.Attribute("n").Value, item.Value);
    }

    return new SqlChars(textBuilder.ToString().ToCharArray());
}

Now it’s just uploading the new version of the assembly to the database:

ALTER ASSEMBLY [MyAssembly]
FROM '...\MyAssembly.dll'
GO

And defining the Scalar-Valued Functions in Transact-SQL:

CREATE FUNCTION [dbo].[ShortPropsToXml](@shortPropsText [nvarchar](4000))
RETURNS [xml] WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [Esi.SA.Encyclopedia].[ShortProps].[ShortPropsToXml]
GO

CREATE FUNCTION [dbo].[XmlToShortProps](@shortPropsXml [xml])
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [Esi.SA.Encyclopedia].[ShortProps].[XmlToShortProps]
GO

And it’s all set to go.

Now, given this property bag definition:

declare @text nvarchar(max)='[[[name1]]]
value1
[[[name2]]]
value2
[[[name3]]]
value3
'

I can change it into XML:

DECLARE @xml [xml] = dbo.ShortPropsToXml(@text)

Update:

set @xml.modify('replace value of (/ShortProps/p[@n="name2"]/text())[1] with "new value2"')

Insert:

set @xml.modify('insert <p n="name4">Value4.1
Value4.2</p> after (/ShortProps/p[@n="name2"])[1]')

Delete

set @xml.modify('delete (/ShortProps/p[@n="name3"])[1]')

Convert back to the property bag format:

print dbo.XmlToShortProps(@xml)

[[[name1]]]
value1
[[[name2]]]
new value2
[[[name4]]]
Value4.1
Value4.2

Query as a table:

select T.C.value('./@n', 'nvarchar(max)') as Name, T.C.value('.', 'nvarchar(max)') as Value from @xml.nodes('/ShortProps/p') T(C)

Name	Value
name1	value1
name2	new value2
name4	Value4.1 Value4.2

Unfortunately, the application uses characters that are invalid to SQL Server as XML characters and I can’t use it on that application. I’ll have to understand those values better and add other fields to the table and XML.

Playing With SQL Server CLR Integration – Part I

Paulo Morgado — Thu, 11 Jun 2009 23:41:14 Z

I’m currently working with an application that stores a property bag in a SQL Server column like this:

[[[name1]]]
value1
[[[name2]]]
value2.1
value2.2
[[[name3]]]
value3

Don’t ask me why it’s done like this. It just is.

The application decodes this property bag into its inner structures and all works fine.

Sometimes I would like to query the database directly or do some reporting on those properties and just can’t.

So, I thought this was a good use case for SQL Server CLR Integration. I decided to build a CLR Table-Valued Function that would return the property bag as a two column table.

Parsing the property bag text can easily be achieved with a simple regular expression:

new Regex(
        string.Format(@"(?<Name>(?<=\[\[\[).*(?=\]\]\]{0}))\]\]\]{0}(?<Value>(([\s\S]*?(?={0}\[\[\[))|([\s\S]*?(?={0}$))))", Environment.NewLine),
        RegexOptions.Multiline | RegexOptions.ExplicitCapture | RegexOptions.CultureInvariant | RegexOptions.Compiled);

Ultrapico’s Expresso was a big help when developing this regular expression.

In case you don’t know, the way CLR Table-Valued Function are built is using an initial method to take the input and return an IEnumerable and row filler method the receives the enumerator item and outputs the column values.

Since these are really key-value pairs of strings, I decided to use )>) Structure" href="http://msdn.microsoft.com/library/5tbh8a42.aspx" target=_blank>KeyValuePair<string, string> instances to store each item and the enumerator became simply this:

private static IEnumerable<KeyValuePair<string, string>> ShortPropsEnumerable(string shortPropsText)
{
    return from Match m in shortPropsRegex.Matches(shortPropsText)
           select new KeyValuePair<string, string>(m.Groups["Name"].Value, m.Groups["Value"].Value);
}

And the implementation of the CLR Table-Valued Function is as simple as this:

[Microsoft.SqlServer.Server.SqlFunction(
    Name = "ShortPropsToTable",
    FillRowMethodName = "ShortPropsToTableFillRow",
    TableDefinition = "Name NVARCHAR(4000), Value NVARCHAR(4000)",
    IsDeterministic = true,
    IsPrecise = false,
    DataAccess = DataAccessKind.None,
    SystemDataAccess = SystemDataAccessKind.None)]
public static IEnumerable ShortPropsToTable(string shortPropsText)
{
    return ShortPropsEnumerable(shortPropsText);
}

public static void ShortPropsToTableFillRow(object item, out SqlChars name, out SqlChars value)
{
    KeyValuePair<string, string> shortProp = (KeyValuePair<string, string>)item;

    name = new SqlChars(shortProp.Key);
    value = new SqlChars(shortProp.Value);
}

To use this in SQL Server a few simple steps are need:

Load the assembly into the database:

CREATE ASSEMBLY [MyAssembly]
AUTHORIZATION [dbo]
FROM '...\MyAssembly.dll'
WITH PERMISSION_SET = SAFE
GO

Defining the Table-Valued User-Defined Function in Transact-SQL:

CREATE FUNCTION [dbo].[ShortPropsToTable](@shortPropsText [nvarchar](4000))
RETURNS  TABLE (
    [Name] [nvarchar](4000) NULL,
    [Value] [nvarchar](4000) NULL
) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [MyAssembly].[ShortProps].[ShortPropsToTable]
GO

Enable CLR Integration:

EXEC sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

And all is set to go.

Now I can just query the property bag as a table:

SELECT
    e.[ID],
    e.[Name],
    e.[Class],
    e.[Type],
    p.[Name],
    p.[Value]
FROM
    dbo.Entity as e
    CROSS APPLY dbo.ShortPropsToTable(e.[ShortProps]) as p

Just for curiosity, for a little over 50000 entities (that can be retrieved on about 1 second on my laptop), I got a little under 630000 properties in les then 40 seconds.

40 seconds might seem a lot compared to the 1 second, but I would like to see better times using T-SQL. And develop and test the TVF in just a couple of hours.

Now, if only he DBAs would allow me to use it. It doesn’t fit into the company’s “security policy”, you know.

Microsoft URL Rewrite Module 1.1 For IIS 7 To The Rescue

Paulo Morgado — Mon, 01 Jun 2009 00:10:00 Z

We are migrating the PontoNetPT community from an old .TEXT version to the latest Community Server (CS).

Because PontoNetPT has nearly 200 blogs with I don’t know how any posts, commentaries, trackbacks, etc., we are using the Community Server REST API.

The problem with using this API is that it doesn’t create the folder that CS can be configured to create with a default.aspx file for each blog created using it’s web site administration.

The importance of this folder and file is for IIS to be able to handle the requests http://<your community>/blogs/<your blog> or http://<your community>/blogs/<your blog>/ as if was a request to http://<your community>/blogs/<your blog>/default.aspx.

Fortunately, we are running on Windows 2008 and IIS 7 and all it took was to install the Microsoft URL Rewrite Module 1.1 For IIS 7 and configure it:

<configuration>
  <!--...-->
  <system.webServer>
    <!--...-->
    <rewrite>
      <rules>
        <rule name="Add Default.aspx to blog root URLs" stopProcessing="false">
          <match url="^blogs/([^/]*)(/?)$" />
          <action type="Rewrite" url="blogs/{R:1}/Default.aspx" />
        </rule>
      </rules>
    </rewrite>
    <!--...-->
  </system.webServer>
  <!--...-->
</configuration>