Paulo Morgado : .NET

Extended WebBrowser Control - Version 0.0.0.0 Uploaded

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

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 GMT

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 GMT

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 GMT

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.

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

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

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 GMT

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 GMT

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 GMT

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.

Typemock Is Launching The ASP.NET Bundle – Get Free Licenses

Paulo Morgado — Mon, 18 May 2009 22:57:00 GMT

Typemock is launching a new product for ASP.NET developers – the ASP.NET Bundle - and for the launch they are giving out FREE licenses to bloggers and their readers.

The ASP.NET Bundle is the ultimate ASP.NET unit testing solution, and offers both Typemock Isolator and Ivonna, the ASP.NET Isolator add-on, for a discounted price.

ASP.NET Bundle launch giveaway:
For the ASP.NET Bundle launch Typemock are giving away free licenses to bloggers and their readers.

How do I get the free license?

Are you a blogger, webmaster, or internet columnist? Get your free license by helping Typemock launch their new ASP.NET Bundle, the ultimate ASP.NET unit testing solution.

Post the text below on your blog (as is, with links) and tell them about it . If you are in the first 60 to mail us after posting, your license will be on its way!

If you have an ASP.NET dedicated blog, you'll get a license automatically (even if more than 60 submit) during the first week of this announcement.

8 bloggers will also get an additional 2 licenses (each) to give away / raffle to their readers or friends.

A few simple requirements:

You must own a website, blog space or internet column, older than 2 months, or get permission from the owner of such a site.
Your post must include the text below (including the links) between *Begin* and *End*.

*Begin*

Unit Testing ASP.NET? ASP.NET unit testing has never been this easy.

Typemock is launching a new product for ASP.NET developers – the ASP.NET Bundle - and for the launch will be giving out FREE licenses to bloggers and their readers.

The ASP.NET Bundle is the ultimate ASP.NET unit testing solution, and offers both Typemock Isolator, a unit test tool and Ivonna, the Isolator add-on for ASP.NET unit testing, for a bargain price.

Typemock Isolator is a leading .NET unit testing tool (C# and VB.NET) for many ‘hard to test’ technologies such as SharePoint, ASP.NET, MVC, WCF, WPF, Silverlight and more. Note that for unit testing Silverlight there is an open source Isolator add-on called SilverUnit.

The first 60 bloggers who will blog this text in their blog and tell us about it, will get a Free Isolator ASP.NET Bundle license (Typemock Isolator + Ivonna). If you post this in an ASP.NET dedicated blog, you'll get a license automatically (even if more than 60 submit) during the first week of this announcement.

Also 8 bloggers will get an additional 2 licenses (each) to give away to their readers / friends.

Go ahead, click the following link for more information on how to get your free license.

*End*

Once you post this in your site, just drop them a line at asp@typemock.com with your name and the post URL to get your free license.

Hurry up – this offer is limited to the first 60 bloggers, or to ASP.NET dedicated bloggers who will post this in the first week.

Make sure to follow their Blog, Site or Twitter for updates on this event.

LINQ With C# Book Is Finally Out

Paulo Morgado — Sun, 17 May 2009 23:39:51 GMT

It’s finally out!

The LINQ Com C# (LINQ With C#) book that Luís and I wrote is out. Well, mostly Luís than I.

This book, published by FCA, is targeted at anyone that already knows C# 2.0 and wants to know learn the new features introduced with C# 3.0 that made possible LINQ (Language INtegrated Query). The examples in the book are written in C#, but Visual Basic get be get from the book’s site.

	Title:	LINQ Com C#
	Authors:	Luís Abreu / Paulo Morgado
	ISBN:	978-972-722-547-7
	Number of Pages:	216
	Format:	17,0 x 24,0 x 1,3 cm (6.7 x 9.4 x 0.5 in)

The book can be found here:

redgate .NET Reflector 5.1.5.0 Released

Paulo Morgado — Mon, 30 Mar 2009 22:47:35 GMT

Today redgate released version 5.1.5.0 of .NET Reflector.

It was a bumpy release. Early today, at work, as soon as @antsprofiler was announced the deployment of the new version I tried to upgrade and was offered an upgrade to the version 5.1.4.0 over and over again.

Early tonight, at home, when I tried to upgrade I was offered to upgrade to 5.1.5.0.

The funny thing is that, if I try to run it from C:\Program Files I get a UAC dialog the mentions Reflector 5.1.3.

Just moved to my C:\Program Files (Utils) and it all works fine.

ASP.NET Futures: Control ClientID Generation

Paulo Morgado — Wed, 18 Mar 2009 01:44:50 GMT

ASP.NET is expected to have some improvements on the generation of client IDs.

Although this is a major improvement, it comes short by not allowing the generation of shorter client IDs for server controls.

My good friend Nuno Gomes has done some work on generating shorter client IDs for controls (*).

Jeff has taken it one step further with his How to shorten ASP.NET automatically generated control IDs article on CodeProject.

If you want to see a running example, check out http://www.biocompare.com/.

(*)

Setting A Web Proxy Through Configuration In .NET Applications

Paulo Morgado — Mon, 12 Jan 2009 19:06:00 GMT

Specially in enterprise environments, proxy servers are used to access the Internet.

In a Windows / Internet Explorer environments there is a proxy server configuration in Internet Properties > Connections > LAN settings > Proxy server.

Although these configurations are tightly connected to Internet Explorer, any well behaved Windows application should, at least, allow the user to choose to use them.

In applications targeting the .NET framework, these the proxy server can be set on a per call basis. Several networking classes have a Proxy property that receives a value implementing the IWebProxy interface.

In order for the Windows/ Internet Explorer configuration to be used, the application must be configured to use the default proxy settings.

This configuration is done in the machine or application file in the proxy element of the defaultProxy configuration section in the system.net section group:

<configuration>
  <system.net>
    <defaultProxy enabled="true">
      <proxy usesystemdefault="True"/>
    </defaultProxy>
  </system.net>
</configuration>

You can use this configuration also to set a specific proxy to be used by your application. In the following example, a proxy setting for using the Fiddler Tool is used:

<configuration>
  <system.net>
    <defaultProxy enabled="true">
      <proxy proxyaddress="http://ipv4.fiddler"/>
    </defaultProxy>
  </system.net>
</configuration>

Unfortunately, this is a MachineToApplication setting and, for that reason, is not allowed in the user settings configuration file, when in a shared installation. In these type of installations, the default Windows / Internet Explorer settings should be used as a default. If a user needs or wants to specify proxy server settings, application specific proxy server settings must be used but, assigning the user defined proxy server configuration to the GlobalProxySelection.Select property will allow its use for the entire application.

Another Great Example Of Silverlight Deep Zoom – From ViaTecla Innovation Laboratories

Paulo Morgado — Wed, 24 Dec 2008 14:16:00 GMT

This innovative use of Deep Zoom uses screenshots of sites powered by ViaTecla’s software solutions to form a picture of a Santa Claus.

Saved By The .NET Framework Cleanup Tool – Again!

Paulo Morgado — Mon, 22 Dec 2008 00:23:29 GMT

I don’t know what’s wrong with my XP system (besides the fact that I’m still using it) but I couldn’t install .NET 3.5 without removing .NET 2.0 and now I couldn’t install apply SP1 without removing .NET 3.0 and 3.5.

Fortunately, I was rescued by Aaron Stebner’s .NET Framework cleanup Tool – both times.

Kudos Aaron!

Merry Christmas And A Happy New Year!

Paulo Morgado — Sun, 21 Dec 2008 20:19:54 GMT

Use the mouse wheel to zoom in and out and the left mouse click and drag to move around.

Improving Debugging And Testing Through Assertions

Paulo Morgado — Wed, 03 Dec 2008 02:56:52 GMT

Reading through the The Typemock Insider blog, I came across this post from Gil Zilberfeld.

I myself tend to fall in Gil’s practice ("binary search" debugging), but I don’t think Kent Beck has the right solution.

Gil’s suggestion of using Isolator is tempting (I don’t miss an opportunity to use it), but still not my favorite one.

I prefer to use debug assertions. Debug assertions can be used when running a debug version of the application to pop-up assertion messages and when running unit tests to fail tests.

In order to use debug assertions in unit tests a “special” trace listener is needed to make the test fail when its Fail method is called.

public class UnitTestTraceListener : global::System.Diagnostics.DefaultTraceListener
{
     public UnitTestTraceListener() : base()
    {
        this.Name = "UnitTest";
        this.AssertUiEnabled = false;
    }

    public override void Fail(string message, string detailMessage)
    {
        Microsoft.VisualStudio.TestTools.UnitTesting.Assert.Fail("Debug.Assert Failed: " + message + " " + detailMessage);
    }
}

Now, all you need to do is register it.

Registering the trace listener can either be done in code:

System.Diagnostics.Trace.Listeners.Remove("Default");
System.Diagnostics.Trace.Listeners.Add(new UnitTestTraceListener());

or configuration:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <system.diagnostics>
    <assert assertuienabled="false"/>
    <trace>
      <listeners>
        <clear/>
        <add name="UnitTest" type="UnitTestTraceListener"/>
      </listeners>
    </trace>
  </system.diagnostics>
</configuration>

And if I’m using Isolator I have the take in account the accesses made in the call to the Assert method. More fun to me.

The Future Of C#

Paulo Morgado — Wed, 03 Dec 2008 01:17:00 GMT

If you were able to attend this session at PDC or Tech-Ed EMEA Developers, you were presented with a first class presentation of the future of C#, presented, respectively, by Anders Hejlsberg and Mads Torgersen.

For the near future (.NET 4.0) C# will have:

Dynamically Typed Objects
Optional and Named Parameters
Improved COM Interoperability
Co- and Contra-variance

A preview of the compiler as a service was shown, but that’s not for the .NET 4.0 / Visual Studio 2010 timeframe. Probably, not even for the next.

Starting with .NET 4.0, C# and Visual Basic will converge in terms of features and follow a path of co-evolution going into the future.

No! That doesn’t mean that XML literals will be in C# in a foreseeable future. What that means is that the above list also applies to Visual Basic.

Talking of Visual Basic evolution, the _ line continuation character has been retired. If you have any use for the underscore, please visit http://www.unemployedunderscores.com/.

Resources:

C# Future
Microsoft Pre-release Software Visual Studio 2010 and .NET Framework 4.0 Community Technology Preview (CTP)
Charlie Calvert's Community Blog
PDC Session: TL16 - The Future of C#

Presenter: Anders Hejlsberg

PPTX | WMV-HQ | WMV | Zune | MP4
PDC Session: TL12 - Future Directions for Microsoft Visual Basic

Presenter(s): Paul Vick, Lucian Wischik

PPTX | WMV-HQ | WMV | Zune | MP4

PDC And Tech-Ed Wrap-Up

Paulo Morgado — Tue, 02 Dec 2008 00:15:57 GMT

(It might seem a bit late for this, but, lately, I’ve been having a lot on my mind. So here it goes.)

This was my first PDC. It was just as I had been told.

For those who don’t know, the PDC is all about the future. The near future (.NET 4.0 and Windows 7) and the further future (Windows Azure, “Oslo”, “Dublin”, “Geneva”).

Next year’s PDC (Yes! Apparently, there’ll be one next year) will be also held in Los Angeles from November 17 to 20, and (I suspect) will be the commercial launch of the Azure Services Platform and a better story to tell about “Oslo”.

Tech-ED EMEA Developers, on the other hand, is more about the present and the near future. But, this year, attendees were able to have a sneak peek at what had bee shown at the PDC.

Next year’s Tech-ED EMEA Developers will be held in Berlin from November 2 to 6. Probably, like in 2006, it will be the launch of .NET 4.0 and Visual Studio 2010.

And I intend to attend both.

PDC2008: Are Windows Forms Dead?

Paulo Morgado — Thu, 09 Oct 2008 00:33:59 GMT

This year’s PDC is all about the cloud (or cloudy as I call it) and the .NET Framework 4.0.

No one at Microsoft has ever told me that Windows Forms are dead (quite the opposite) but looking at the list of sessions for this year’s PDC I can only see WPF.

So the question remains: Are Windows Forms dead?