Luis Rocha's Blog

Spring Boot Configuration Properties Localization

noreply@blogger.com (Luis Rocha) — Mon, 10 Oct 2016 04:21:00 +0000

Spring Boot allows to externalize application configuration by using properties files or YAML files. Spring Profiles provide a way to segregate parts of your application configuration and make it available in certain environments (development, qa, production, etc.).

When building applications for multiple countries, you might have some configuration properties that might differ for a specific locale. One way to localize these configuration properties, would be creating a new profile per locale where you can override these values. This approach only works if your application is running for that specific locale, and not supporting multiple locales at the same time. This approach would also increase the number of profiles to be managed (number of application properties files).

Here, I am going to show another approach where you can define the localizable properties inside your existing configuration properties class. For example, if have the following set of properties for a single country in your application.yml file:

region:
  hostSuffix: .com
  currencyCode: USD
  patternShortDate: MM-dd-YYYY

Then, you could localize these properties by defining them for specific locales, for example en_CA and en_GB:

region:
  hostSuffix: .com
  currencyCode: USD
  patternShortDate: MM-dd-YYYY
  locales:
    en_CA:
      hostSuffix: .ca
      currencyCode: CAD
      patternShortDate: dd-MM-YYYY
    en_GB:
      hostSuffix: .co.uk
      currencyCode: GBD
      patternShortDate: dd-MM-YYYY

The idea is to be able to get the desired property by locale:

// Getting a property for the default locale.
regionProperties.getHostSuffix(); // returns ".com"

// Getting a property for supported locales.
regionProperties.fromLocale(Locale.US).getHostSuffix(); // returns ".com"
regionProperties.fromLocale(Locale.CANADA).getHostSuffix(); // returns ".ca"
regionProperties.fromLocale(Locale.UK).getHostSuffix(); // returns ".co.uk"

// Getting a property for unsupported locales (returns the default)
regionProperties.fromLocale(Locale.ITALY).getHostSuffix(); // returns ".com" (default)

Mapping locale String to Locale object

The first thing is to enable Spring to read the locale string, e.g. "en_CA", and convert it to a java.util.Locale object while loading the application configuration properties. For that, we will need to define a Converter class from String to Locale by implementing the Converter interface and adding the @ConfigurationPropertiesBinding annotation to tell Spring to use it when biding the properties.

@Component
@ConfigurationPropertiesBinding
public class LocaleConverter implements Converter<String, Locale> {

    private static final Map<String, Locale> LOCALE_MAP = new HashMap<>(Locale.getAvailableLocales().length);

    static {
        for (Locale locale : Locale.getAvailableLocales()) {
            LOCALE_MAP.put(locale.toString(), locale);
        }
    }

    @Override
    public Locale convert(String s) {
        return LOCALE_MAP.get(s);
    }
}

Loading the properties in your Configuration Proper

Once added this converter, all you have to do in your configuration properties class is to add the following property with its public getter/setter and Spring loads the application.yml (or application.properties) files.

    protected Map<String, Locale> locales = new HashMap<>();

Lets define a generic abstract class with this property and with the method to find the setting by locale (fromLocale):

public abstract class LocalizableProperties<T extends LocalizableProperties> {
    protected Locale locale;
    protected Map<String, Locale> locales = new HashMap<>();

    @PostConstruct
    private void postConstruct() {
        // Set the locale of each entry of the map.
        for (Map.Entry<String, Locale> entry : locales.entrySet()) {
            entry.getValue().setLocale(entry.getKey());
        }
    }

    public T fromLocale(Locale locale) {
        return locales.containsKey(locale) ? locales.get(locale) : (T) this;
    }

    public Locale getLocale() {
        return locale;
    }

    public void setLocale(Locale locale) {
        this.locale = locale;
    }

    public Map<String, Locale&gt getLocales() {
        return locales;
    }

    public void setLocales(Map<String, Locale&gt locales) {
        this.locales = locales;
    }
}

Creating Localizable Configuration Properties

Now, lets define our configuration properties class, RegionProperties, that simply extends our abstract class and define some application properties to be localized:

@Component
@ConfigurationProperties(prefix = "region")
public class RegionProperties extends LocalizableProperties<RegionProperties> {
    private String hostSuffix;
    private String currencyCode;
    private String patternShortDate;

    public String getHostSuffix() {
        return hostSuffix;
    }

    public void setHostSuffix(String hostSuffix) {
        this.hostSuffix = hostSuffix;
    }

    public String getCurrencyCode() {
        return currencyCode;
    }

    public void setCurrencyCode(String currencyCode) {
        this.currencyCode = currencyCode;
    }

    public String getPatternShortDate() {
        return patternShortDate;
    }

    public void setPatternShortDate(String patternShortDate) {
        this.patternShortDate = patternShortDate;
    }
}

That is, any configuration property class that need to be localized can be declared to extend the LocaleProperties class so that it can be localizable.

You can find a running code in my github repo:
https://github.com/lerocha/spring-boot-localizable-properties

Comparing MySQL databases with mysqldbcompare

noreply@blogger.com (Luis Rocha) — Sun, 23 Aug 2015 23:56:00 +0000

If you need to check the differences between two MySQL databases, you can use the mysqldbcompare utility. This utility is part of MySQL utilities and it is very useful when you want to check if databases from different environments (dev, testing, stage, production) are consistent. It compares the objects and data from two databases to find differences between them. The databases can be located on the same host or different hosts. This utility identifies objects having different definitions in the two databases and presents them in a diff-style format.

To use mysqldbcompare, you will need to install MySQL Utilities and MySQL Connector/Python. You can find them here:

I use mysqldbcompare to compare the database objects to identify new tables/columns or anything that has been created or dropped. You can also use it to compare data itself, which would be useful to compare a replica or backup database with its source.

The usage is very simple. I created two identical databases in my localhost using the Chinook Database named Chinook and Chinook2.

The mysqldbcompare syntax is very simple. You use --server1 and server2 to specify the hosts of the databases and then the database names between colon, for example Chinook:Chinook2. You also can use --run-all-tests to avoid stopping at the first object different, and continue to process for all db objects.

mysqldbcompare --server1=root:password@localhost:3306 --server2=root:password@localhost:3306 Chinook:Chinook2 --run-all-tests

The output is:

# server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# Checking databases Chinook on server1 and Chinook2 on server2
#
# Object definitions differ. (--changes-for=server1)
#

--- `Chinook`
+++ `Chinook2`
@@ -1 +1 @@
-CREATE DATABASE `Chinook` /*!40100 DEFAULT CHARACTER SET latin1 */
+CREATE DATABASE `Chinook2` /*!40100 DEFAULT CHARACTER SET latin1 */

#                                                   Defn    Row     Data   
# Type      Object Name                             Diff    Count   Check  
# ------------------------------------------------------------------------- 
# TABLE     Album                                   pass    pass    -       
#           - Compare table checksum                                FAIL    
#           - Find row differences                                  pass    
# TABLE     Artist                                  pass    pass    -       
#           - Compare table checksum                                FAIL    
#           - Find row differences                                  pass    
# TABLE     Customer                                pass    pass    -       
#           - Compare table checksum                                FAIL    
#           - Find row differences                                  pass    
# TABLE     Employee                                pass    pass    -       
#           - Compare table checksum                                FAIL    
#           - Find row differences                                  pass    
# TABLE     Genre                                   pass    pass    -       
#           - Compare table checksum                                FAIL    
#           - Find row differences                                  pass    
# TABLE     Invoice                                 pass    pass    -       
#           - Compare table checksum                                FAIL    
#           - Find row differences                                  pass    
# TABLE     InvoiceLine                             pass    pass    -       
#           - Compare table checksum                                FAIL    
#           - Find row differences                                  pass    
# TABLE     MediaType                               pass    pass    -       
#           - Compare table checksum                                FAIL    
#           - Find row differences                                  pass    
# TABLE     Playlist                                pass    pass    -       
#           - Compare table checksum                                FAIL    
#           - Find row differences                                  pass    
# TABLE     PlaylistTrack                           pass    pass    -       
#           - Compare table checksum                                FAIL    
#           - Find row differences                                  pass    
# TABLE     Track                                   pass    pass    -       
#           - Compare table checksum                                FAIL    
#           - Find row differences                                  pass   

# Databases are consistent.
#
# ...done

Note that object definitions, row count and row content match. It shows that table checksum are different, but my guess is that they are not the exactly replica since I created Chinook2 by running the same sql script again.

You can use the option --skip-data-check if you only want to compare objects definition and not data. After I made some changes in Chinook2 (drop and create table and columns), I run mysqldbcompare with --skip-data-check:

mysqldbcompare --server1=root:password@localhost:3306 --server2=root:password@localhost:3306 Chinook:Chinook2 --run-all-tests --skip-data-check

The output is:


# server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# Checking databases Chinook on server1 and Chinook2 on server2
#
# Object definitions differ. (--changes-for=server1)
#

--- `Chinook`
+++ `Chinook2`
@@ -1 +1 @@
-CREATE DATABASE `Chinook` /*!40100 DEFAULT CHARACTER SET latin1 */
+CREATE DATABASE `Chinook2` /*!40100 DEFAULT CHARACTER SET latin1 */

# WARNING: Objects in server1.Chinook but not in server1.Chinook2:
#        TABLE: PlaylistTrack
#
# WARNING: Objects in server1.Chinook2 but not in server1.Chinook:
#        TABLE: Address
#
#                                                   Defn    Row     Data   
# Type      Object Name                             Diff    Count   Check  
# ------------------------------------------------------------------------- 
# TABLE     Album                                   pass    pass    SKIP    
# TABLE     Artist                                  pass    pass    SKIP    
# TABLE     Customer                                FAIL    pass    SKIP    
#
# Object definitions differ. (--changes-for=server1)
#

--- `Chinook`.`Customer`
+++ `Chinook2`.`Customer`
@@ -3,16 +3,14 @@
   `FirstName` varchar(40) CHARACTER SET utf8 NOT NULL,
   `LastName` varchar(20) CHARACTER SET utf8 NOT NULL,
   `Company` varchar(80) CHARACTER SET utf8 DEFAULT NULL,
-  `Address` varchar(70) CHARACTER SET utf8 DEFAULT NULL,
-  `City` varchar(40) CHARACTER SET utf8 DEFAULT NULL,
-  `State` varchar(40) CHARACTER SET utf8 DEFAULT NULL,
-  `Country` varchar(40) CHARACTER SET utf8 DEFAULT NULL,
-  `PostalCode` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
   `Phone` varchar(24) CHARACTER SET utf8 DEFAULT NULL,
   `Fax` varchar(24) CHARACTER SET utf8 DEFAULT NULL,
   `Email` varchar(60) CHARACTER SET utf8 NOT NULL,
   `SupportRepId` int(11) DEFAULT NULL,
+  `AddressId` int(11) DEFAULT NULL,
   PRIMARY KEY (`CustomerId`),
   KEY `IFK_CustomerSupportRepId` (`SupportRepId`),
+  KEY `FK_CustomerAddressId` (`AddressId`),
+  CONSTRAINT `FK_CustomerAddressId` FOREIGN KEY (`AddressId`) REFERENCES `Address` (`AddressId`) ON DELETE NO ACTION ON UPDATE NO ACTION,
   CONSTRAINT `FK_CustomerSupportRepId` FOREIGN KEY (`SupportRepId`) REFERENCES `Employee` (`EmployeeId`) ON DELETE NO ACTION ON UPDATE NO ACTION
 ) ENGINE=InnoDB AUTO_INCREMENT=60 DEFAULT CHARSET=latin1

# TABLE     Employee                                FAIL    pass    SKIP    
#
# Object definitions differ. (--changes-for=server1)
#

--- `Chinook`.`Employee`
+++ `Chinook2`.`Employee`
@@ -6,15 +6,13 @@
   `ReportsTo` int(11) DEFAULT NULL,
   `BirthDate` datetime DEFAULT NULL,
   `HireDate` datetime DEFAULT NULL,
-  `Address` varchar(70) CHARACTER SET utf8 DEFAULT NULL,
-  `City` varchar(40) CHARACTER SET utf8 DEFAULT NULL,
-  `State` varchar(40) CHARACTER SET utf8 DEFAULT NULL,
-  `Country` varchar(40) CHARACTER SET utf8 DEFAULT NULL,
-  `PostalCode` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
   `Phone` varchar(24) CHARACTER SET utf8 DEFAULT NULL,
   `Fax` varchar(24) CHARACTER SET utf8 DEFAULT NULL,
   `Email` varchar(60) CHARACTER SET utf8 DEFAULT NULL,
+  `AddressId` int(11) DEFAULT NULL,
   PRIMARY KEY (`EmployeeId`),
   KEY `IFK_EmployeeReportsTo` (`ReportsTo`),
+  KEY `FK_EmployeeAddressId` (`AddressId`),
+  CONSTRAINT `FK_EmployeeAddressId` FOREIGN KEY (`AddressId`) REFERENCES `Address` (`AddressId`) ON DELETE NO ACTION ON UPDATE NO ACTION,
   CONSTRAINT `FK_EmployeeReportsTo` FOREIGN KEY (`ReportsTo`) REFERENCES `Employee` (`EmployeeId`) ON DELETE NO ACTION ON UPDATE NO ACTION
 ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1

# TABLE     Genre                                   pass    pass    SKIP    
# TABLE     Invoice                                 pass    pass    SKIP    
# TABLE     InvoiceLine                             pass    pass    SKIP    
# TABLE     MediaType                               pass    pass    SKIP    
# TABLE     Playlist                                pass    pass    SKIP    
# TABLE     Track                                   pass    pass    SKIP   

# Database consistency check failed.
#
# ...done

As you can see in the diff output above, I dropped the PlaylistTrack table and created the Address table (see # WARNING). I also dropped the address related columns in Employee and Customer tables (see lines starting with -) and added the addressId foreign key (see lines starting with +).

For more information, see:
http://dev.mysql.com/doc/mysql-utilities/1.3/en/mysqldbcompare.html#option_mysqldbcompare_run-all-tests

Converting a local Subversion repository to Git

noreply@blogger.com (Luis Rocha) — Mon, 03 Jun 2013 03:03:00 +0000

There many articles there about converting a subversion repository to git, but I decided to write my article that describes the conversion of a simple subversion repository (trunk only) stored in the local file system. I have a couple of these repositories that I needed to convert to git and then push them to bitbucket.org.

Note that you will still need svn installed in addition to git. If you are using Mac OSX, svn used to be installed by default but that changed in Mountain Lion (10.8). Since I have XCode already installed, I just went to Xcode > Preferences > Downloads > Command Line Tools > Install. See details here.

Create the Authors Mapping File

The first step is to create a mapping file for the subversion users to the git users. With the help from here, I run the command below to create the initial mapping file. In my case, the local subversion repository is located at //Users/lerocha/svn-repo:

svn log file:///Users/lerocha/svn-repo | sed -ne 's/^r[^|]*| \([^ ]*\) |.*$/\1 = \1 <\1@example.com>/p' | sort -u > ~/authors.txt

Open the generated file (~/authors.txt) in a text editor and change it from this:

lerocha = lerocha <lerocha@example.com>

Into this:

lerocha = Luis Rocha <luis@luisrocha.net>

Convert to a local Git repository

With the help from this stackoverflow answer, run the following commands to initialize the local git repository and import the revisions from subversion:

mkdir repo
cd repo
git svn init file:///Users/lerocha/svn-repo --no-metadata
git config svn.authorsfile ~/authors.txt
git svn fetch

Your local git repository should contain all revisions from your local subversion.

Push to a remote repository

Since we want to push it to bitbucket.org (private repositories are free!), you first create an empty repository in bitbucket.org and then run the following commands below to push to it:

git remote add origin ssh://git@bitbucket.org/youruser/yourrepo.git
git push -u origin --all   # to push up the repo for the first time

Chinook Database is now available on NuGet

noreply@blogger.com (Luis Rocha) — Mon, 10 Dec 2012 04:59:00 +0000

Chinook sample database is now available on NuGet. There are 3 packages available:

Chinook Database SQL Scripts: contains the SQL scripts for all supported databases (SQL Server, SQL Server Compact Edition, SQLite, Oracle, MySQL, PostgreSQL, DB2 and EffiProz. In addition, it also contains batch files to run these scripts with the respective command prompt tools.
Chinook Database for SQLite: contains a SQLite file with the Chinook database.
Chinook Database for SQL Server Compact 4.0: contains a SQL Compact 4.0 file with the Chinook database.

You can install the Chinook database within Visual Studio using the following instructions and searching for 'Chinook':

For now on, new releases will be available on Codeplex download page and also on Nuget.

Enjoy!

Chinook Sample Database 1.4 Released

noreply@blogger.com (Luis Rocha) — Mon, 03 Dec 2012 05:24:00 +0000

The Chinook Database is a sample database available for multiple database systems. It can be created by running a single SQL script. Chinook database is an alternative to the Northwind database, being ideal for demos and testing ORM tools targeting single and multiple database servers.

The Chinook Database represents a media store, including tables for artists, albums, media tracks, invoices and customers. The media information was generated from my iTunes library file, but anyone can download its source code and use their own iTunes library information. The sales information is auto-generated for a 4 years period. You can see the Chinook data model here.

This new version includes support for DB2 and PostgreSQL. Thanks to @brice_lambson for implementing the PostgreSQL support! Chinook database is available for:

DB2 (new)
EffiProz
MySQL
Oracle
PostgreSQL (new)
SQL Server
SQL Server Compact
SQLite

You can download the latest release from here. There are available SQL scripts for each database system and also embedded database files for SQL Server Compact and SQLite.

Unit Testing an Entity Framework Data Access Layer

noreply@blogger.com (Luis Rocha) — Mon, 05 Mar 2012 07:16:00 +0000

One approach to write unit tests for a data access layer implemented using Entity Framework that targets SQL Server is to use a local test database SQL Server Compact Edition. The test database will have the same schema as the real application database, and it will contain the minimun necessary data for your test cases.

The only thing to keep in mind is that you will need to restore the previous state of the test database after each unit test run. There are multiple approaches such as to recreate the database before each test or simply restore a backup file. One approach that I like is to run the test under a TransactionScope. In this approach, you create a TransactionScope object before the test runs and call TransactionScope.Dispose after the test is completed. Since TransactionScope.Complete is not called, the Dispose method will rollback the changes. That is, you SQL Compact database will be back to the state before running the test.

If you are using xUnit, then your unit test class will need to implement the IDisposable interface, create the TransactionScope in the constructor and dispose it in the Dispose method as shown below:

public class MyDataAccessUnitTests : IDisposable
{
  private TransactionScope _transactionScope;

  public MyDataAccessUnitTests()
  {
    _transactionScope = new TransactionScope();
  }

  [Fact]
  void Test1()
  {
    // TODO: implement it.
  }

  [Fact]
  void Test2()
  {
    // TODO: implement it.
  }

  public void Dispose()
  {
    _transactionScope.Dispose();
  }
}

If you are using NUnit or MSTest, then you can create and dispose the TransactionScope object in the following methods:

	xUnit	NUnit	MSTest
new TransactionScope()	Constructor	[SetUp]	[TestInitialize]
TransactionScope.Dispose	IDisposable.Dispose	[TearDown]	[TestCleanup]

I hope this helps!

Data Access Performance Comparison in .NET

noreply@blogger.com (Luis Rocha) — Wed, 07 Dec 2011 06:30:00 +0000

When writing data access code you are probably using some sort of ORM or data mapper to make your life easier while retrieving and persisting data. We all know that this does not come for free, ORMs allow you to move faster, but there is always a performance hit. I recently started reading about Dapper .NET, an open-source, lightweight, single-file object mapper that was developed for the stackoverflow.com site to execute high performance queries. After seeing its amazing performance results on their project page, I decided to write my owns performance tests to compare it to other ORMs (Entity Framework, NHibernate), and also to plain ADO.NET code.

For these tests, I am using the Chinook Database for SQL Server. I defined a simple repository interface and implemented it using different data access approaches. The interface implemented is:

public interface IRepository
{
 Artist GetArtistById(int id);
 IEnumerable<Song> GetSongsByArtist(string name);
}

The first method executes a simple query in a single table by the primary key. The second method executes a query in multiple tables using joins.

There are multiple implementations of this repository interface including plain ADO.NET code with DataReaders, Entity Framework, NHibernate and Dapper .NET. For the Entity Framework, there are four implementations: Linq To Entities, compiled Linq To Entities, native SQL query and stored procedures. For NHibernate, there are implementations with HQL query, native SQL query, and stored procedure.

All the code is available at https://github.com/lerocha/DotNetDataAccessPerformance.

Single Table Query by Primary Key

The first set of tests is to execute a simple query in a single table to get one record by the primary key. The SQL query is:

SELECT ArtistId, Name FROM Artist WHERE Artist.ArtistId=@id

The following table contains the results per multiple runs:

	1 run	10 runs	100 runs	500 runs	1000 runs
DataReaderNativeQuery	0.0	2.0	24.9	134.1	264.4
DataReaderStoredProcedure	0.0	2.2	26.3	133.8	266.4
DapperNativeQuery	0.1	2.5	28.0	141.5	285.6
DapperStoredProcedure	0.0	2.4	28.0	137.5	276.1
EntityFrameworkCompiledLinqQuery	2.2	22.5	207.2	1041.0	2099.8
EntityFrameworkLinqToEntities	5.5	25.2	267.7	1263.6	2522.5
EntityFrameworkNativeQuery	0.7	8.2	85.2	426.0	859.5
EntityFrameworkStoredProcedure	1.4	5.2	58.3	275.8	552.8
NHibernateHqlQuery	0.8	4.4	48.1	253.6	509.5
NHibernateHqlQueryStrongType	0.1	4.1	47.2	251.4	508.7
NHibernateNativeQuery	1.1	4.4	46.1	256.9	490.1
NHibernateStoredProcedure	0.0	5.2	57.2	294.0	589.8

The 1-run test is executed twice, the first execution result is not shown since I am only considering hot start. The following chart shows the results of 1,000 runs. As you can see, using Entity Framework with Linq To Entities (non-compiled) has the worst performance result (2,552.5 ms). It gets a little bit better if you use EF compiled Linq query (2,099.8), then improvements starts when using EF with native query (859.5 ms). EF using stored procedure and all NHibernate approaches are way better, all around 500 ms. And finally, the performance of Dapper is very close to the plain ADO.NET code, providing an level of abstraction with minimal performance impact.

Multiple Table Query with Joins

The second test executes joins in multiple tables and filters by a non-key column. The SQL query is:

 SELECT Album.Title as AlbumName, 
 Track.Name as SongName, Artist.Name as ArtistName
 FROM Artist
 INNER JOIN Album ON Album.ArtistId = Artist.ArtistId
 INNER JOIN Track ON Track.AlbumId = Album.AlbumId
 WHERE Artist.Name=@name

The following table contains the results per multiple runs:

	1 run	10 runs	100 runs	500 runs	1000 runs
DataReaderNativeQuery	0.0	5.9	63.0	321.8	644.2
DataReaderStoredProcedure	0.0	6.2	65.0	332.3	658.6
DapperNativeQuery	0.4	6.4	68.0	346.1	685.5
DapperStoredProcedure	0.1	6.7	67.2	342.3	679.7
EntityFrameworkCompiledLinqQuery	1.0	9.9	103.3	525.6	1071.0
EntityFrameworkLinqToEntities	5.6	58.2	539.5	2699.9	5398.3
EntityFrameworkNativeQuery	1.1	13.1	135.0	658.8	1328.3
EntityFrameworkStoredProcedure	1.1	9.7	99.0	489.1	983.7
NHibernateHqlQuery	3.4	35.6	359.5	1806.2	3647.1
NHibernateHqlQueryStrongType	6.8	68.3	681.5	3430.0	6841.4
NHibernateNativeQuery	1.1	10.6	103.2	517.7	998.7
NHibernateStoredProcedure	0.8	10.1	102.0	516.2	1033.8

The following chart shows the results of 1,000 runs. As you can see, the slowest implementation is NHibernate with strong type query, e.g. using NHibernate.IQuery.List(), it took 6,841 ms. EF with LinqToEntities (non-compiled) is a little bit faster (5,398 ms). Using NHibernate without strong type query, e.g. using NHibernate.IQuery.List() is significant faster (3,647 ms) than its strong type equivalent (6,841 ms). Here, we can see the performance improvement when using EF compiled Linq queries (1,071 ms) in comparison with EF non compiled Linq To Entities (5,398 ms). The EF compiled Linq query implementation showed performance equivalent to EF with stored procedures, and NHibernate with native query and stored procedure. And finally, the performance of Dapper (679-685 ms) is again very close to the plain ADO.NET code (644-658 ms), with minimal performance impact.

The performance improvements using Dapper in comparison with Entity Framework and NHibernate is very significant. For database reads that need a high performance execution, you might consider using Dapper to get the best performance with some good abstraction from plain ADO.NET code.

The source code of these tests is available at github and I will continue to work on it to incorporate any feedback and also add additional tests for adding, deleting and updating records. Stay tunned!

Using Powershell with Application Pools

noreply@blogger.com (Luis Rocha) — Wed, 12 Oct 2011 05:24:00 +0000

While developing Web applications and services using IIS, you need to recycle the application once in a while. You could just run iisreset, but sometimes you only want to affect one specific application pool. If you are using IIS7 or later, you could use appcmd.exe, but if you have some legacy then one option would be powershell. For example, to recycle the ASP.NET 4 application pool using powershell, you can run:

# Recycle the ASP.NET 4 application pool
([ADSI] "IIS://localhost/W3SVC/AppPools/ASP.NET v4.0").psbase.invoke("recycle")

# Stop the ASP.NET 4 application pool:
([ADSI] "IIS://localhost/W3SVC/AppPools/ASP.NET v4.0").psbase.invoke("stop")

# Start the ASP.NET 4 application pool:
([ADSI] "IIS://localhost/W3SVC/AppPools/ASP.NET v4.0").psbase.invoke("start")

# Verify the ASP.NET 4 application pool state: 1 = starting, 2 = started, 3 = stopping, 4 = stopped
([ADSI] "IIS://localhost/W3SVC/AppPools/ASP.NET v4.0").AppPoolState

Enjoy!

Avoiding MvcBuildViews build time impact in developers environment by using ASP.NET compiler as an external tool

noreply@blogger.com (Luis Rocha) — Sun, 09 Oct 2011 07:04:00 +0000

ASP.NET Razor views (.cshtml) and Web Form views (.aspx) are only compiled by the Web server when they are needed (runtime) and not by Visual Studio when you build your solution (build time). The MvcBuildViews option enables a post-build task (AspNetCompiler Task) to compile your ASP.NET views during build time, thus enabling you to catch syntax errors in the views earlier in the development process. However, enabling MvcBuildViews can significantly increase your build time depending on the size of the Web project you are working on.

To avoid the build time increase, you can enable MvcBuildViews in your build environment only (Release builds) and developers can use the aspnet_compiler.exe as an Visual Studio external tool to precompile ASP.NET views on demand instead of on every build to avoid the building time impact. This way, you will get the best of both worlds: be able to compile views at build time, but not on every build, only when you need it.

To enable MvcBuildViews in release builds only, you can follow the steps described here. To use aspnet_compiler.exe as an external tool in Visual Studio:

Go to Visual Studio Tools menu, and click on External Tools.
Enter a title and consider adding a shortcut. I am using Compile ASP.NET &Views and I just use the short cut ALT+T+V to run it.
Enter the ASP.NET compiler command. For .NET 4, it is C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319\aspnet_compiler.exe.
Add the following arguments: -c -v temp -p $(ProjectDir) - (option -c forces a full rebuild; option -v defines the virtual path of the application to be compiled, if you use temp, it will be in C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319\Temporary ASP.NET Files\temp; option -p defines the physical path of the application to be compiled, by using $(ProjectDir), it will compile the views of the current project in Visual Studio).
Enter the initial directory: $(ProjectDir)
I use the output window, which makes easier if you have errors and want to open the file with errors by double clicking on it.
Press OK and you are all set. Just make sure to run this command when you are in your ASP.NET web project, or selecting or editing any of its files.

Alternately, you could import the following registry key:

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\VisualStudio\10.0\External Tools\Compile ASP.NET &Views]
"ToolCmd"="C:\\WINDOWS\\Microsoft.NET\\Framework\\v4.0.30319\\aspnet_compiler.exe"
"ToolDir"="$(ProjectDir)"
"ToolArg"="-c -v temp -p $(ProjectDir)"
"ToolOpt"=dword:00000018

One advantage of using the aspnet_compiler.exe as external tool is to be able to continue work on Visual Studio 2010 while watching the results in the output window. This is way better than using MvcBuildViews in developers build since Visual Studio 2010 hangs and display that annoying message that it is busy.

Happy building!

Managing Transactions with Entity Framework 4

noreply@blogger.com (Luis Rocha) — Wed, 24 Aug 2011 05:06:00 +0000

The Entity Framework already supports native database transactions. When using the ObjectContext.SaveChanges method, it already operates within a database transaction. If any dirty ObjectStateEntry object cannot be persisted, then ObjectContext.SaveChanges will roll back the transaction and throw an exception. So, if you are working with only one object context then you have already built-in support for database transactions when using the ObjectContext.SaveChanges method.

However, there are other complex scenarios where you want to update a record in a database and add an entry to a message queue; or add, update, or delete records in different databases. In this situation, if you need these operations to be ACID, then you will need a distributed transaction that spans multiple databases and systems. A distributed transaction is a kind of transaction that requires the enlistment of additional resource managers.

If you have a complex persistence scenario as mentioned above, then you can use the TransactionScope class. This class uses the Microsoft Distributed Transaction Coordinator (MSDTC or DTC) which is a Windows service that coordinate transactions over different resource managers across multiple computers. Using MSDTC is an expensive task, however TransactionScope only uses MSDTC if it is necessary. If multiple instances of ObjectContext connect to the same database, then it uses a standard database transaction (SqlTransaction, OleDbTransaction). If there are multiple databases to be connected, then the transaction is promoted to MSDTC. If you are using SQL Server, transaction promotions in SQL Server 2008 happens less frequently than in SQL Server 2005, so SQL Server 2008 is more efficient in this matter. You can use the SQL Profiler tool to monitor how many transactions are promoted to MSDTC.

When using TransactionScope with multiple instances of ObjectContext, you should not use the parameterless ObjectContext.SaveChanges() method. Instead, you should use the ObjectContext.SaveChanges(SaveOptions) method and pass SaveOptions.DetectChangesBeforeSave. The reason is to maintain the ObjectContext state if something fails, otherwise the ObjectContext will be in a bad state, i.e., thinking that the changes were successfully committed when they were actually rolled back. When all operations within the transaction are successfully completed, then you should call the TransactionScope.Complete method, and after that it is safe to call ObjectContext.AcceptAllChanges().

Below you can see an example based on this post, but using the SaveChanges(SaveOptions) method instead of the obsolete SaveOptions(Boolean):

using (TransactionScope scope = new TransactionScope())
{
    // Save changes but maintain context1 current state.
    context1.SaveChanges(SaveOptions.DetectChangesBeforeSave);

    // Save changes but maintain context2 current state.
    context2.SaveChanges(SaveOptions.DetectChangesBeforeSave);

    // Commit succeeded since we got here, then completes the transaction.
    scope.Complete();

    // Now it is safe to update context state.
    context1.AcceptAllChanges();
    context2.AcceptAllChanges();
}

That is, the example above updates two databases within a distributed transaction and ObjectContext instances are only updated if the transaction is successfully committed. For an example of a transaction fail-retry scenario and using a database and a message queue, see the following link: http://msdn.microsoft.com/en-us/library/bb738523.aspx

Extending the jQuery UI datepicker with new shortcuts

noreply@blogger.com (Luis Rocha) — Sun, 10 Apr 2011 02:00:00 +0000

The jQuery UI datepicker has a set of default shortcuts to move to the next/previous month (PageDown/PageUp), to today's date (Ctrl+Home), etc. After working with QuickBooks for so long, I got used to the QuickBooks date control shortcuts that use letters instead of Ctrl+key combination like the jQuery UI datepicker. These shortcuts are very intuitive, e.g. the letter W is used for the first day of the Week and the letter K is used for the last day of the weeK. Here, we will show how to extend the datepicker to support the additional shortcuts below.

Shortcut	Description
+	Next Day
-	Previous Day
T	Today
W	First day of the Week
K	Last day of the weeK
M	First day of the Month
H	Last day of the montH
Y	First day of the Year
R	Last day of the yeaR

The ideal solution would be to use the widget factory to subclass the datepicker and extend the _doKeyPress function with the new shortcuts. However, the datepicker does not use the widget factory in jquery-ui 1.8, see more details here. The solution is to extend the datepicker with a new function customKeyPress and then bind it to the datepicker keypress event.

/*!
 * Extensions for jQuery UI 1.8.7 datepicker
 *
 * Copyright 2011, Luis Rocha
 * Released under the MIT license.
 *
 */

// Extends datepicker with shortcuts for today, begin and end of the year and month.
$.extend($.datepicker, { customKeyPress: function (event) {
    var inst = $.datepicker._getInst(event.target);
    var c = String.fromCharCode(event.which).toLowerCase();
    switch (c) {
        case "y":
            // First day of the (y)ear.
            if (inst.selectedDay == 1 && inst.selectedMonth == 0) {
                // First day of previous year.
                $.datepicker._adjustDate(event.target, -12, "M");
            } else if (inst.selectedMonth == 0) {
                // First day of the current year.
                $.datepicker._adjustDate(event.target, 1 - inst.selectedDay, "D");
            } else {
                // First day of the current year.
                inst.selectedDay = 1;
                $.datepicker._adjustDate(event.target, -inst.selectedMonth, "M");
            }
            break;
        case "r":
            // Last day of the yea(r).
            if (inst.selectedDay == 31 && inst.selectedMonth == 11) {
                // Last day of next year.
                $.datepicker._adjustDate(event.target, +12, "M");
            } else if (inst.selectedMonth == 11) {
                // Last day of current year.
                $.datepicker._adjustDate(event.target, 31 - inst.selectedDay, "D");
            } else {
                // Last day of current year.
                inst.selectedDay = 31;
                $.datepicker._adjustDate(event.target, 11 - inst.selectedMonth, "M");
            }
            break;
        case "m":
            // First day of the (m)onth.
            if (inst.selectedDay == 1) {
                $.datepicker._adjustDate(event.target, -1, "M");
            } else {
                $.datepicker._adjustDate(event.target, 1 - inst.selectedDay, "D");
            }
            break;
        case "h":
            // Last day of the mont(h).
            var end = $.datepicker._getDaysInMonth(inst.selectedYear, inst.selectedMonth);
            if (inst.selectedDay == end) {
                var month = (inst.selectedMonth + 1) % 11;
                var year = inst.selectedYear + Math.floor((inst.selectedMonth + 1) / 11);
                inst.selectedDay = $.datepicker._getDaysInMonth(year, month);
                $.datepicker._adjustDate(event.target, +1, "M");
            } else {
                $.datepicker._adjustDate(event.target, end - inst.selectedDay, "D");
            }
            break;
        case "w":
            // First day of the (w)eek.
            var date = new Date(inst.selectedYear, inst.selectedMonth, inst.selectedDay);
            var offset = (date.getDay() > 0 ? date.getDay() : 7);
            $.datepicker._adjustDate(event.target, -offset, "D");
            break;
        case "k":
            // Last day of the wee(k).
            var date = new Date(inst.selectedYear, inst.selectedMonth, inst.selectedDay);
            var offset = (date.getDay() < 6 ? 6 - date.getDay() : 7);
            $.datepicker._adjustDate(event.target, offset, "D");
            break;
        case "t":
            // Today (same as Ctrl+Home).
            $.datepicker._gotoToday(event.target);
            break;
        case "+":
            // Increase day (same as Ctrl+Right).
            $.datepicker._adjustDate(event.target, +1, 'D');
            break;
        case "-":
            // Decrease day (same as Ctrl+Left).
            $.datepicker._adjustDate(event.target, -1, 'D');
            break;
    }
}
});

The following code snippet shows how to bind the customKeyPress function to the datepicker keypress:

$(function() {
    $("#datepicker").datepicker().keypress(function (event) { 
        $.datepicker.customKeyPress(event);
    });
});

Once the datepicker is refactored to use the widget factory (maybe in jquery-ui 1.9??), I will update this code to use the widget factory solution instead. For future updates, see https://github.com/lerocha/jquery-ui-extensions.

A demo of this extended datepicker is available here.

Creating Composite Keys using Code First with Entity Framework

noreply@blogger.com (Luis Rocha) — Sat, 20 Nov 2010 08:13:00 +0000

Update: For Entity Framework 4.1 RTM, the exception message is a little bit different. It suggests to use the ColumnAttribute instead of DataMemberAttribute. At the time of EF CTP 4, the Column attribute did not exist yet, so they were temporary using the DataMember attribute. I have updated this post to use Column attribute as the latest EF version.

I am starting using the Entity Framework CTP 4, that includes code first approach with data annotations.
When creating composite primary keys, you need specify the order of the primary keys, otherwise you will get an exception similar to:

System.InvalidOperationException : Unable to determine composite primary key ordering for type 'PlaylistTrack'. Use the ColumnAttribute or the HasKey method to specify an order for composite primary keys.

As the exception message describes, one option is to use data annotations and add the Key and Column attributes to define the composite key as shown below:

public class PlaylistTrack
    {
        [Key, Column(Order=1)]
        public int PlaylistId { get; set; }

        [Key, Column(Order = 2)]
        public int TrackId { get; set; }

        [RelatedTo(ForeignKey = "PlaylistId")]
        public Playlist Playlist { get; set; }

        [RelatedTo(ForeignKey = "TrackId")]
        public Track Track { get; set; }
    }

Another option is to define the composite key using the HasKey method. In this option, the entity class will be:

public class PlaylistTrack
    {
        public int PlaylistId { get; set; }
        public int TrackId { get; set; }

        [RelatedTo(ForeignKey = "PlaylistId")]
        public Playlist Playlist { get; set; }

        [RelatedTo(ForeignKey = "TrackId")]
        public Track Track { get; set; }
    }

And the composite key is defined using the HasKey method when building the model:

var builder = new ModelBuilder();

    //...

    builder.Entity<PlaylistTrack>().HasKey(p=>new {p.PlaylistId, p.TrackId});

    //...

    model = builder.CreateModel();

My personal choice is to use data annotations which were introduced since EF4 CTP3.

Chinook Sample Database 1.3 Released

noreply@blogger.com (Luis Rocha) — Tue, 16 Nov 2010 18:31:00 +0000

The Chinook Database is a sample database available for multiple database systems. It can be created by running a single SQL script. Chinook database is an alternative to the Northwind database, being ideal for demos and testing ORM tools targeting single and multiple database servers.

The Chinook Database represents a media store, including tables for artists, albums, media tracks, invoices and customers. The media information was generated from my iTunes library file, but anyone can download its source code and use their own iTunes library information. The sales information is auto-generated for a 4 years period. You can see the Chinook data model here.

This new version includes support for SQLite and EffiProz. EffiProz is a cross-platform embedded database written entirely in C#. Chinook database is available for:

EffiProz
MySQL
Oracle
SQL Server
SQL Server Compact
SQLite

You can download the latest release from here. There are available SQL scripts for each database system and also embedded database files for SQL Server Compact and SQLite.

How to Map Skydrive as a Network Drive in Windows 7

noreply@blogger.com (Luis Rocha) — Tue, 21 Sep 2010 05:01:00 +0000

There are already many posts about mapping Skydrive as a network drive in Windows and you are wondering why I am creating a new one. The reason is very simple, none of these existing posts worked for me. So, I decided to put together all the information that worked for me, and hopefully it can help someone else with the same problem.

Check Your Windows Environment

The first step is to make sure your Windows environment is ready for this mapping. I am using Windows 7 x64, so I am not sure if this applies to other Windows versions, but it does not hurt to check.

1. WebClient Windows Service must be running as discussed here.

Open the Services management console (Start / Run / services.msc) and locate the WebClient service.
Start the WebClient service if it is stopped.
Open its Properties window, and set the Startup type to Automatic.

Note: Although this service was already running for me, I still had to restart it and set it to automatic startup in order to be able to map Skydrive.

2. Internet Explorer - LAN Settings should have Automatically detect settings unchecked

Open Internet Explorer.
Go to Tools, Internet Options.
Click on the Connections tab, and click on the LAN Settings.
Uncheck the Automatically detect settings.

Note: This might sound weird to check, but I also had to change this setting in order to make it work. When trying to map Skydrive using Windows Explorer, it was asking for my Windows Live credentials multiple times (about 3 times) until it fails.

Determine the WebDAV access address

The second step is to determine the proper address (WebDAV access address) you should use to connect to a Skydrive folder. You cannot directly use the URL shown in your browser since it does not work. You will need to get the WebDAV access address by using one of the following options:

Manually figure out the address based on the browser URL as described here. I do not recommend this method since the folder name might be different than the one seen on the browser. For example, here it mentions that "Documents" folder should be "^2Documents", but for me it was "^.Documents".
Use MS Office 2010 to save a document to Skydrive and then be able to see the WebDAV access address as described here. It would be a good option if you have this version of MS Office.
Build your own application that access http://docs.live.net/SkyDocsService.svc and query for the WebDAV folders. It is good to know about this WCF service, but it will take sometime to build a client application.
Use an existing application that uses the SkyDocsService and retrieve the information we need: http://skydrivesimpleviewer.codeplex.com/. This open source project provides a command prompt application dumpurls.exe and also a WPF application SkyDriveSimpleViewer.exe.

I decided to use the simplest way, e.g. the dumpurls.exe command prompt application. Download dumpurls.exe, and run it from a Command Prompt window by passing your email (Windows Live or Hotmail) and your password. If you are not confident about passing your credentials to this application, you can temporary change your password in Windows Live before running this application, and restore it after.

DumpUrls.exe me@hotmail.com p4ssw0rd

The output is something like:

https://zzzzzz.docs.live.net/yyyyyyyyyyyyyyyy/^.Documents
https://xxxxxx.docs.live.net/yyyyyyyyyyyyyyyy/MyFolder

You will not use these URLs directly, but the corresponding paths instead:

\\zzzzzz.docs.live.net@SSL\yyyyyyyyyyyyyyyy\^.Documents
\\xxxxxx.docs.live.net@SSL\yyyyyyyyyyyyyyyy\MyFolder

Map Skydrive as Network Drive

Although you can map it by using Windows Explorer, I mapped using a single command line in the Command Prompt window. So, open a Command Prompt window and run the following command to map a folder named MyFolder to the drive Z by using your credentials:

net use Z: "\\xxxxxx.docs.live.net@SSL\yyyyyyyyyyyyyyyy\MyFolder" /user:me@hotmail.com p4ssw0rd /persistent:yes

The expected output is:

The command completed successfully.

Note: these are the errors I had before I fixed my Windows environment as explained in the beginning of this article:

System error 5 has occurred: the solution for me was to change the IE setting described above.
System error 1920 has occurred: the solution was to restart the WebClient service as described above.

Now, you can just open Windows Explorer and start using your new drive on the cloud. I noticed that Windows Explorer does not report the proper used and free space, it might show that you have more than 25GB available.

Microsoft ASP.NET Security Vulnerability: The 'Padding Oracle' Attack

noreply@blogger.com (Luis Rocha) — Sat, 18 Sep 2010 19:42:00 +0000

Early this week, a couple of security researchers, Juliano Rizzo and Thai Duong, have implemented an attack that exploits the way ASP.NET applications handle encrypted session cookies (see more details here). They have discussed this in detail during the Ekoparty conference in Argentina. Their research paper is Practical Padding Oracle Attacks.

In the context of cryptography, an oracle is a system that provides hints as you ask it questions. And this attack explores a vulnerability in ASP.NET which acts as a padding oracle. This vulnerability is explained in details in Understanding the ASP.NET Vulnerability.

This vulnerability allows attackers to have access to decrypt the information stored in the ViewState object. If sensitive information is stored there, such as passwords or database connection strings, then this data is compromised. If an ASP.NET application is using ASP.NET 3.5 SP1 or above, the attacker could use this encryption vulnerability to request the contents of an arbitrary file which the worker process has access to, for example, the web.config file.

Microsoft has released a security advisory about the problem: Microsoft Security Advisory (2416728). If you or your clients have ASP.NET Web sites, then you must apply the necessary changes as described here: Scott Guthrie: ASP.NET Security Vulnerability

Update: Also take a look at Scott Guthrie's FAQ about AS.NET Security Vulnerability.

JavaScript syntax error with telerik.grid.min.js

noreply@blogger.com (Luis Rocha) — Tue, 15 Jun 2010 00:43:00 +0000

When implementing custom binding with the Telerik ASP.NET MVC, I based my code on this example described here under the Controller tab.

The CustomBinding action is called when the whole page is loaded or refreshed, then showing on the grid the default data, e.g. the first page. This action should return a view with the model object (IEnumerable<Order> in this example).

The _CustomBinding action is called from an AJAX call when the user clicks on the grid controls, like page number, sort, or filter. This action should return a view with a GridModel object and not with the view model (IEnumerable<Order>). If you do not use a GridModel object, then you will get the JavaScript syntax error char 4048 in telerik.grid.min.js.

Although the Telerik example is right, when I wrote my code based on it, I mistakenly used the wrong model object, and couldn't figure out what was wrong. Thanks for this forum post that enlighted me of what was going on.

Using SyntaxHighlighter (version 2.1.364) with Blogger

noreply@blogger.com (Luis Rocha) — Sat, 12 Jun 2010 07:00:00 +0000

After spending sometime to make SyntaxHighlighter work with Blogger, I finally found an article that has the solution that perfectly worked for me. The other posts I found were not using the latest version (version 2.1.364 when this post was published), so no support for latest languages such as PowerShell, or they were using the latest version but the instructions didn't work 100%.The following article worked for me:

http://geektalkin.blogspot.com/2009/11/embed-code-syntax-highlighting-in-blog.html

Checking the type and size of RAM with Windows PowerShell

noreply@blogger.com (Luis Rocha) — Wed, 09 Jun 2010 14:45:00 +0000

I subscribe to the PowerTip of the day email of PowerShell.com and I got this tip today. If you want to know the type and size of RAM your PC uses, and available banks, you can use the following script:

$memorytype = "Unknown", "Other", "DRAM", "Synchronous DRAM", "Cache DRAM",
"EDO", "EDRAM", "VRAM", "SRAM", "RAM", "ROM", "Flash", "EEPROM", "FEPROM",
"EPROM", "CDRAM", "3DRAM", "SDRAM", "SGRAM", "RDRAM", "DDR", "DDR-2"
$formfactor = "Unknown", "Other", "SIP", "DIP", "ZIP", "SOJ", "Proprietary",
"SIMM", "DIMM", "TSOP", "PGA", "RIMM", "SODIMM", "SRIMM", "SMD", "SSMP",
"QFP", "TQFP", "SOIC", "LCC", "PLCC", "BGA", "FPBGA", "LGA"
$col1 = @{Name='Size (GB)'; Expression={ $_.Capacity/1GB } }
$col2 = @{Name='Form Factor'; Expression={$formfactor[$_.FormFactor]} }
$col3 = @{Name='Memory Type'; Expression={ $memorytype[$_.MemoryType] } }

Get-WmiObject Win32_PhysicalMemory | Select-Object BankLabel, $col1, $col2, $col3

The output would be something like:

BankLabel                     Size (GB) Form Factor         Memory Type
---------                     --------- -----------         -----------
                                      2 DIMM                DDR-2
                                      2 DIMM                DDR-2
                                      2 DIMM                DDR-2
                                      2 DIMM                DDR-2

It works pretty nice, doesn't it?

MVC Music Store and Chinook Database

noreply@blogger.com (Luis Rocha) — Tue, 25 May 2010 00:54:00 +0000

The MVC Music Store is a sample application built on ASP.NET MVC Framework 2. It includes the source code and a tutorial explaining the steps to build it. This is a great tutorial if you want to get started on ASP.NET MVC 2. When I looked at the documentation, I noticed that the data used by the application is very familiar. Jon Galloway, the author, mentioned on this post that the MVC Music Store data is based on the Chinook Database. The Chinook Database was built using my iTunes library as sample data, and that is why I recognized some particular artists and albums: a mix of rock, heavy metal, classical music and Brazilian music.
As to the Chinook Database I have a list of improvements to be done and need to get sometime to work on a new release. I am also thinking about adding support to document-oriented databases such as Raven DB and MongoDB.

Setting Assembly Version with Windows PowerShell

noreply@blogger.com (Luis Rocha) — Sat, 28 Nov 2009 22:31:00 +0000

I've been using the Build Version Increament add-in for Visual Studio to automatically set the assembly and file versions. It works fine, however, it only works when using the Visual Studio IDE and it requires you to setup every single project of your solution. If you need to increment the assembly version on an automated build (MS Build, NAnt, PSake), then a PowerShell script would be a better solution.

The following script, SetVersion.ps1, searches for AssemblyInfo.cs files in the current directory and its sub directories, and then updates the AssemblyVersion and AssemblyFileVersion. You can optionally provide the version number to use, or it will auto generate one for you. You can customize the Generate-VersionNumber function to use your own version schema. Also, if you are using a source control that needs to check-out files before editing them, such as TFS and Perforce, then add the check-out command to the Update-AssemblyInfoFiles function.

#-------------------------------------------------------------------------------
# Displays how to use this script.
#-------------------------------------------------------------------------------
function Help {
    "Sets the AssemblyVersion and AssemblyFileVersion of AssemblyInfo.cs files`n"
    ".\SetVersion.ps1 [VersionNumber]`n"
    "   [VersionNumber]     The version number to set, for example: 1.1.9301.0"
    "                       If not provided, a version number will be generated.`n"
}

#-------------------------------------------------------------------------------
# Generate a version number.
# Note: customize this function to generate it using your version schema.
#-------------------------------------------------------------------------------
function Generate-VersionNumber {
    $today = Get-Date
    return "1.0." + ( ($today.year - 2000) * 1000 + $today.DayOfYear )+ ".0"
}
 
#-------------------------------------------------------------------------------
# Update version numbers of AssemblyInfo.cs
#-------------------------------------------------------------------------------
function Update-AssemblyInfoFiles ([string] $version) {
    $assemblyVersionPattern = 'AssemblyVersion\("[0-9]+(\.([0-9]+|\*)){1,3}"\)'
    $fileVersionPattern = 'AssemblyFileVersion\("[0-9]+(\.([0-9]+|\*)){1,3}"\)'
    $assemblyVersion = 'AssemblyVersion("' + $version + '")';
    $fileVersion = 'AssemblyFileVersion("' + $version + '")';
    
    Get-ChildItem -r -filter AssemblyInfo.cs | ForEach-Object {
        $filename = $_.Directory.ToString() + '\' + $_.Name
        $filename + ' -> ' + $version
        
        # If you are using a source control that requires to check-out files before 
        # modifying them, make sure to check-out the file here.
        # For example, TFS will require the following command:
        # tf checkout $filename
    
        (Get-Content $filename) | ForEach-Object {
            % {$_ -replace $assemblyVersionPattern, $assemblyVersion } |
            % {$_ -replace $fileVersionPattern, $fileVersion }
        } | Set-Content $filename
    }
}

#-------------------------------------------------------------------------------
# Parse arguments.
#-------------------------------------------------------------------------------
if ($args -ne $null) {
    $version = $args[0]
    if (($version -eq '/?') -or ($version -notmatch "[0-9]+(\.([0-9]+|\*)){1,3}")) {
        Help
        return;
    }
} else {
    $version =  Generate-VersionNumber
}

Update-AssemblyInfoFiles $version

And finally, before running this script, or any PowerShell script, make sure that you are allowed to execute scripts by running Get-ExecutionPolicy. If it returns Restricted, you need to run the following command:

Set-ExecutionPolicy RemoteSigned

Download the entire script from here.

Enjoy it!

The Channel 9 Learning Center

noreply@blogger.com (Luis Rocha) — Wed, 18 Nov 2009 06:10:00 +0000

If you want to learn the latest Microsoft technologies such as V2010 and .NET 4.0, Windows Azure, and SharePoint 2010, then check it out the training course available at Channel 9 Learning Center:

http://channel9.msdn.com/learn/

Creating a SharePoint List Parent / Child Relationship – Out of the Box

noreply@blogger.com (Luis Rocha) — Mon, 12 Oct 2009 21:35:00 +0000

I just came across this article about creating SharePoint lists with parent/child relationship using out-of-the-box functionality:

http://www.endusersharepoint.com/2009/10/02/creating-a-sharepoint-list-parent-child-relationship-out-of-the-box/

Unit Testing CRM Plug-ins

noreply@blogger.com (Luis Rocha) — Mon, 29 Jun 2009 00:15:00 +0000

What is a CRM plug-in?

A plug-in is custom business logic that you can integrate with Microsoft Dynamics CRM 4.0 to modify or augment the standard behavior of the platform. This custom business logic can be executed based on a message pipeline execution model called Event Execution Pipeline. A plug-in can be executed before or after a MS CRM platform event. For example, you can create a plug-in to validate the attributes of an account entity before the create and update operations.

To create plug-ins, you need to create a normal .NET class library and reference the MS CRM SDK libraries. Then add a class that implements the Microsoft.Crm.Sdk.IPlugin interface.

public interface IPlugin
{
    void Execute(IPluginExecutionContext context);
}

Plug-in Unit Testing

In order to write unit tests for your plug-in, you need to create at least a mock of the IPluginExecutionContext. Depending on your plug-in implementation, you will also need to mock ICrmService or IMetadataService if you are calling IPluginExecutionContext.CreateCrmService or IPluginExecutionContext.CreateMetadataService.

There is the MS CRM Plug-in Debugger, which consists of a small EXE container that implements a mock of the IPluginExecutionContext interface. You could use this container to unit test your plug-ins. However, IMHO, I do not see any advantage in using it versus a unit test and a mock framework. I posted a comment on the CRM Team Blog: Testing CRM Plug-in asking about that, but didn't get a response yet.

To unit test a CRM plug in, you can use your favorite unit test framework (NUnit, MbUnit, Visual Studio Tests) and your favorite mock framework (Rhino Mocks, NMock, Typemocks). In this article, I will be using NUnit and RhinoMocks.

The Plug-in Code

In the following example, adapted from the "Programming Microsoft Dynamics CRM 4.0" book, the plug-in validates the account number attribute before saving the account entity.

public class AccountNumberValidator : IPlugin
{
    public void Execute(IPluginExecutionContext context)
    {
        var target = (DynamicEntity) context.InputParameters[ParameterName.Target];

        if (target.Properties.Contains("accountnumber"))
        {
            var accountNumber = target["accountnumber"].ToString();
            var regex = new Regex("[A-Z]{2}-[0-9]{6}");

            if (!regex.IsMatch(accountNumber))
            {
                throw new InvalidPluginExecutionException("Invalid account number.");
            }
        }
    }
}

The code above checks to see if the account number attribute is in the right format. If not, it throws an InvalidPluginExecutionException. Since we will register this plug-in as a pre-event of creating and updating the account entity, this exception will be handled by the CRM platform, and the create/update operation is aborted.

Writing the Plug-in Unit Test

The following code is a simple test using NUnit to verify that an InvalidPluginExecutionException is thrown when the account entity has invalid account number:

[Test]
[ExpectedException(typeof(InvalidPluginExecutionException))]
public void ShouldHandleInvalidAccountNumber([Values("",
                                                    "AB123456",
                                                    "A123456",
                                                    "ABC123456",
                                                    "AB-12345",
                                                    "AB123456",
                                                    "AB-123",
                                                    "AB-1234",
                                                    "aa-012345",
                                                    "aa-000000",
                                                    "Za-999999",
                                                    "wW-936187")]
                                                    string number)
{
    // Create necessary mocks for the plug-in.
    var mocks = new MockRepository();
    var context = mocks.DynamicMock<IPluginExecutionContext>();

    // Creates a property bag for the plugin execution context mock.
    var target = new DynamicEntity();
    target.Properties["accountnumber"] = number;
    var inputParameters = new PropertyBag();
    inputParameters.Properties[ParameterName.Target] = target;

    // Set expectations of mocks.
    Expect.Call(context.InputParameters).Return(inputParameters).Repeat.Any();
    mocks.ReplayAll();

    // Test the plug-in using the context mock.
    IPlugin plugin = new AccountNumberValidator();
    plugin.Execute(context);

    // Verify all the mocks.
    mocks.VerifyAll();
}

Now, we will go through all the details of this unit test:

The ExpectedException attribute defines the type of exception that this test expects to be raised. In our case, it is an InvalidPluginExecutionException.
This is a parameterized test that uses the Values attribute to define a set of invalid account numbers. This test will run once for each value that we define. The Values attribute is specific to NUnit, but other frameworks have similar mechanisms: MbUnit uses RowTest for example.
We create a mock of the IPluginExecutionContext interface by using the MockRepository.DynamicMock method. We are using a DynamicMock because we are only interested in a small piece of the functionality (InputParameters property of the context object). If we want a complete control of the mock object behavior, then we would use a StrickMock. For more information about the types of mocks that you can create with Rhino Mocks, see here.
The InputParameters property of the plug-in context, is a property bag that will contain the account number attribute. So, we create this property bag, and add the account number defined by the Values attribute parameter.
Now, we set the expectations of the mock object. This step is called the Record state. When the InputParameters property is called, we expect it to return the property bag we created on the previous step. Note that we are using Repeat.Any() that means this property can be called more than once. In our test, we just want to make sure that InputParameters is called, no matter how many times.
The Record state is finish by calling ReplayAll(). This will move to the Replay state.
Now, we are ready to instantiate our plug-in object and call its Execute method using the plug-in context mock object.
Finally, we call VerifyAll() method, to verify that the mock expectations were satisfied. In our case, it will make sure that InputParameters property was called during the Replay state.

This test will assert that the plug-in Execute method will throw an InvalidPluginExecutionException with the account number values supplied.

We also should write a test to assert that no InvalidPluginExecutionException is thrown when using valid account numbers. I will not include this test here, but you can see it on the solution source code files.

Mocking the ICrmService Interface

In our previous test, we only need to mock the plug-in context interface. However, in more complex plug-ins, you might need to mock other interfaces such as the ICrmService. The CreateCrmService method of the IPluginExecutionContext creates an ICrmService object. If you use the CreateCrmService method on your plug-in, you will need to create a mock of ICrmService.

Our validate account number plug-in has been changed to also detect duplicate account numbers. If an account number already exists, then the validation will fail by throwing an InvalidPluginExecutionException. To verify that the account number exists, we query CRM using the ICrmService.Fetch method with a FetchXML query. The following code demonstrate these changes:

public class AccountNumberValidator : IPlugin
{
    /// <summary>
    /// 
    /// </summary>
    /// <param name="context"></param>
    public void Execute(IPluginExecutionContext context)
    {
        var target = (DynamicEntity) context.InputParameters[ParameterName.Target];

        if (target.Properties.Contains("accountnumber"))
        {
            var accountNumber = target["accountnumber"].ToString();

            // Validates the account number format.
            var regex = new Regex("[A-Z]{2}-[0-9]{6}");
            if (!regex.IsMatch(accountNumber))
            {
                throw new InvalidPluginExecutionException("Invalid account number.");
            }

            // Validates the account number is unique.
            using (var service = context.CreateCrmService(true))
            {
                var query = string.Format(@"<fetch mapping='logical'>
                                                <entity name='account'>
                                                <attribute name='accountnumber' />
                                                <filter>
                                                <condition attribute='accountnumber'
                                                    operator='eq' value='{0}' />
                                                </filter>
                                                </entity>
                                            </fetch>",
                                            accountNumber);

            var results = service.Fetch(query);
            var xdocument = XDocument.Parse(results);
            var existingNumbers = from item in xdocument.Descendants("accountnumber")
            select item.Value;

            if (existingNumbers.Count() > 0)
                throw new InvalidPluginExecutionException("Account number already exist.");
            }
        }
    }
}

Now, we will create a unit test to verify that our plug-in detects duplicate account numbers.

[Test]
[ExpectedException(typeof(InvalidPluginExecutionException))]
public void ShoulRejectDuplicateAccountNumber()
{
    // Create necessary mocks for the plug-in.
    var mocks = new MockRepository();
    var context = mocks.DynamicMock<IPluginExecutionContext>();
    var service = mocks.DynamicMock<ICrmService>();

    // Creates a property bag for the plugin execution context mock.
    var target = new DynamicEntity();
    target.Properties["accountnumber"] = "AB-123456";
    var inputParameters = new PropertyBag();
    inputParameters.Properties[ParameterName.Target] = target;

    // Set expectations of mocks.
    Expect.Call(context.InputParameters).Return(inputParameters).Repeat.Any();
    Expect.Call(context.CreateCrmService(true)).Return(service);
    Expect.Call(service.Fetch(null)).IgnoreArguments()
                .Return(@"<resultset>
                            <result>
                                <accountnumber>AB-123456</accountnumber>
                            </result>
                        </resultset>");
    mocks.ReplayAll();

    // Test the plug-in using the context mock.
    IPlugin plugin = new AccountNumberValidator();
    plugin.Execute(context);

    // Verify all the mocks.
    mocks.VerifyAll();
}

In the test above, we are using Rhino Mocks to create a mock for the ICrmService. This object will be returned by the CreateCrmService method of the plug-in execution context. We are also recording that when ICrmService.Fetch method is called, it will return a XML file containing a duplicated account number. This will simulate the CRM behavior of detecting that an account number already exists, and we can assert that our plug-in will fail the validation by throwing an exception.

I hope this post helps you to unit test your CRM plug-ins. Although I demonstrated it using NUnit and Rhino Mocks, you can use any unit testing framework (NUnit, MbUnit, Visual Studio Tests, etc.) and any mock framework (Rhino Mocks, NMock, Typemocks, etc.).

Crm4Plugin.zip

Using Embedded Files for FetchXML Queries

noreply@blogger.com (Luis Rocha) — Tue, 16 Jun 2009 00:17:00 +0000

FetchXML is a proprietary language that it is used in Microsoft Dynamics CRM. All examples that I've seen so far, always show the FetchXML query hard coded into the C# file. Instead of keeping the queries mixed with the source code, a bad practice IMHO, I prefer placing queries in separate XML files. These files can be embedded resources of the assembly. By placing them on a separate file, it isolates them from the code, making easier to locate, share and test them. In order to embed your query in the assembly, you will need to add an XML file with the query into your project. Make sure to change its build action to Embedded Resource. Then, use the following code to read the embedded XML file. The code below assumes that the file was placed in the subfolder Queries of the project. It refers to the embedded file by using the assembly name and the related path to the file. Notice that it uses "." instead of "\" to refer to the embedded file.

// Read the embedded fetch xml query file.
var assembly = Assembly.GetExecutingAssembly();
var stream = assembly.GetManifestResourceStream("MyAssembly.Queries.MyQuery.xml");

if (stream == null)
{
    throw new FileLoadException("Cannot load fetchXML embedded file");
}

// Gets the Fetch XML query string.
var reader = new StreamReader(stream);
string query = reader.ReadToEnd();

// Removing leading spaces to reduce the size of the xml request.
query = Regex.Replace(fetchXml, @"\s+", @" ");

// Fetches the results.
string results = crmService.Fetch(query);

The code above uses a static query. If you need to use a dynamic query, then the XML can contain the string format of the query, and you can use String.Format to pass the parameters needed to build your dynamic query.

Visual Studio 2010 and .NET 4 Beta 1

noreply@blogger.com (Luis Rocha) — Thu, 21 May 2009 03:14:00 +0000

Visual Studio 2010 and .NET 4 Beta 1 is available today for the general public. Note that the new .NET version is 4 and not 4.0. You can download the beta from here.

Also, check out Jason Zander's post where he highlights the new functionalities, and Brad Adams post with .NET 4 poster.

I am currently preparing a virtual machine with Windows 7 RC 1 and VS 2010 for trying out the new features.