Tips And Tricks

A TraceListener For Tests

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

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.

LINQ To SQL Tips & Tricks: String Operations

LINQ With C# (Portuguese)

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.

Tip: Connecting To A Database Using Windows Authentication With Different Credentials Using SQL Server Management Studio

It is a good security practice to use Windows Authentication to connect to SQL Server because you don’t need to write the password on some configuration file or registry entry.

This practice also brings governance benefits. Managing users becomes part of domain administration and not part of each SQL Server instance’s administration.

But this becomes an hassle to users (in this context, the user is someone that needs to perform administration task of some kind – a SQL Server Management Studio user) who need to connect to databases using different credentials.

One workaround is using the runas command:

runas /user:DOMAIN\USERNAME "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"

But if you are working on an environment were there are several domains and your machine does not belong to the domain of the ser account you want to use, you’ll get the folloing error:

RUNAS ERROR: Unable to run - C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe
1787: The security database on the server does not have a computer account for this workstation trust relationship.

But not everything is lost. Because you want to make a remote connection, you can use the /netonly switch, and it works just fine:

runas /netonly /user:DOMAIN\USERNAME "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"

SQL Server Management Studio still shows all the databases in the server (unlike what happens if you connect from a machine logged in as the user you want to access to the SQL Server instance), but ApexSQL Edit will only show the databases that account has access to.

VS REGEX: Commenting Generated Assert Instructions

Visual Studio's Unit Test generator generates a call to Assert.Inconclusive but, usually generates a call to another method of the Assert class. I find that very annoying because, some times, this makes the test fail instead of being reported as inconclusive.


To comment out these extra calls to Assert methods, the following regular expression can be used:


Find what:


{:b*}{Assert\.~(Inconclusive).*\n:b*Assert\.Inconclusive}

Replace with:


\1//\2

Check out the complete list.

VS REGEX: Joining Concatenated Strings

I don't like to see constant strings being concatenated in source code. I know the compiler compiles it into a single string, but I think it sends out the wrong message.

However, code generators (like Visual Studio's Unit Test generator) usually generate code with long strings concatenated across several lines of source code.

In order to replace this:

text = "This is a one " + "line concatenation." +
       "This is a multiline concatenation."
       + "This is a multiline concatenation."


       +



       "This is a multiline concatenation with multiple blank lines." +
       "This looks like a string concatenation \" + " + "."

into this:

text = "This is a one line concatenation.This is a multiline concatenation.This is a multiline concatenation.This is a multiline concatenation with multiple blank lines.This looks like a string concatenation \" + ."



The following regular expression can be used:

Find what:
~(\\)\":b*(:b*\n)*\+(:b*\n)*:b*\"
Replace with:
 

There's one caveat, though. This Regular expression only works with regular C# string literals. It doesn't work with Visual Basic or @-quoted C# strings.

Check out the complete list.

Visual Studio Find And Replace Regular Expression Patterns

Visual Studio comes with the a specific set of regular expressions can be used in the Find what field of the its Find and Replace Window.

Over the time I've been using Visual Studio I've come up with some tricky regular expressions that I'll be publishing in this page. Feel free to comment and contribute.