LINQ to SQL - 5 minute introduction.

Whilst perhaps not a fully fledged OR/M solution like NHibernate, LINQ to SQL has a place in this world as it's an excellent tool if your domain entities map 1:1 to your database schema as it provides a visual tool (not always useful, depends on scale) to design your model along and it uses the excellent LINQ query syntax to get data out of the database.

I started this example by creating a separate project for the data access layer. This project is a standard class library, from which I've exposed the entities in our database along with repository objects that will be responsible for managing these entities.

After creating a new class library for our DAL project, I've then added a couple of enum files that correspond to the different types of static data that I'll be needing and then added a LINQ to SQL Classes item.

This creates a DBML file and presents us with a designer surface to start dropping our tables and views from our database onto. A connection to the database needs to be defined in server explorer first, but once that is in place, and with the designer surface and server explorer open, you can now start to drag and drop the database elements onto the LINQ to SQL surface, thereby building up your entity model.

Notice how the designer automatically walks foreign keys and constraints and this in turn then automatically creates properties in your elements. For example, the Category entity in this example has a property named CategoryBudgets automatically created that will load the budgets associated with a category on demand (lazy load).

With your DBML file in place, and after following the post on how to use enums with LINQ from my earlier post, you should have a full object model of entities that can be queried using LINQ in your code;

For example, in your application you would be able to get a list of all categories by querying thus;

   1: DC.Finances.Database.FinanceDataContext dc = 

   2:     new DC.Finances.Database.FinanceDataContext();

   3:  

   4: var cats = from c in dc.Categories select c;

   5: MessageBox.Show(cats.Count().ToString());

However, we wouldn't want to put this LINQ code directly into our UI code as, sticking to the tenets of good design, specifically separation of concerns, we're going to keep that sort of thing in the DAL, so we would wrap this up with a repository class to go ahead and give us data we require, whilst implementing any rules and logic for us. eg: CategoryManager.GetAllCategories()

Configuring SubVersion under Windows.

Whilst I'm an advocate of TFS as a dev process management and version control system, I also like subversion - it's a great, free, source control system with a vibrant community of users and a host of supporting tools. The following post is a quick and handy reference to getting SVN to run over the network.

First steps
First of all get hold of the latest subversion and tortoiseSVN and install them. Once that's complete, create a repository using the tortoiseSVN tools.

Setting up the network
To get it working, you can initially run the SVN server from the command line. Use something like this;

svnserve -d --listen-port 44818 -r e:\myRepository

-d puts the server into daemon (listening) mode
--listen-port parameter and argument is optional and specifies what port to listen on
-r then specifies the repository you've just created above.

With that working, you'll now want to run this as a windows service. In comes the ubiquitous sc.exe (part of your windows O/S)

Installing as a service
Run the SC tool to add scnserve as a service;

sc create "SVNServe" binpath="c:\SubVersion\bin\svnserve.exe --service -r e:\myRepository" displayname="Subversion Repository" depend=Tcpip

From there you can then set your service to start automatically and start the service.

Adding security
Its all well and good having SVN running, but a little security wouldn't go amiss! The files you need to edit are all contained in the conf sub-directory within your repository directory. 3 files - svnserve.conf, authz and passwd. Quick overview below;

svnserve.conf
[general]
anon-access = none
# auth-access = write
password-db = passwd
authz-db = authz
realm = My SVN server

This disables anonymous access to the svn server and forces the passwd and authz files to be used to configure the users and path based security permissions. The realm parameter will be presented to users when the SVN client asks them to login.

authz
#Define some groups of users
[groups]
groupX=user1,user2
groupY=user2

#Set the root of the repository to read
#only by any authenticated user
[/]
* = r

#Set a location in the tree to not available
#to users initially, read/write for groupY users,
#read only for groupX users and specifically grant
#user1 read/write access.
[/someproject/path]
* = 
@groupY = rw
@groupX = r
user1 = rw

# Give all authenticated user read/write access
[/someotherproject]
* = rw

And last, but not least, the passwd file in this instance is where all the usernames and passwords are held.

passwd
[users]
user1=mypassword
user2=anotherpassword

Using enums in LINQ to SQL

A common practice when working with databases is to use enumerations for any static lookup (ID/description) data. These enums are represented both in code and also in the database for referential integrity. The idea being this static data rarely changes so we don't need to pull it back continually from the database.

Taking the following data model as an example;

All of the tables that end with Enum are static tables that consist of reference data only and we don't want this to be pulled back from the database. Instead these enum tables are represented in code as enums and as such we need to tell LINQ to map the fields that refer them to enum values rather than the database tables.

First things first, I mapped out my basic entities as follows (notice the lack of enum tables as objects in this model).

In order to force the entities to use an enum value instead of loading a child entity object, I simply set the Type property for the field to a type in my project, as per the image below; (Also I renamed the fields from FieldXYZID to just FieldXYZ).

Finally, notice that because my enum definition is within the same assembly as my DBML, the type namespace is relative to the DBML. If it was in a different assembly, then you'd specify a full type name here instead.

Fixing the ClickOnce Error - "can't download files"

If after creating a click once deployment, you put it on your web server but find that when you run the .application URL, you get a "can't download files" error instead of your application running, check the details. If you're getting 404 errors for the .manifest or .deploy files, chances are IIS isn't configured correctly to support these file types.

By default IIS doesn't know what .manifest or .deploy files are, so it doesn't serve them out. You need to add them as MIME types by going into IIS manager, right clicking on the server name and selecting properties. Click MIME types and add the following;

.deploy      application/octet-stream

.manifest   application/manifest

Database diagram support objects cannot be installed...

Twice is two weeks SQL 2005 has reported the following error when I've clicked on the database diagrams node on certain databases;

"Database diagram support objects cannot be installed because this database does not have a valid owner".

The databases in question were restored onto my local SQL from backups on other machines and servers. I'd restore the database, set the owner using ALTER AUTHORIZATION, and then try to create a database diagram, only to be presented with the above error.

The symptoms actually mask the underlying problem - the database's compatibility mode. On both occasions the compatibility level was set to SQL 2000 (80) for the restored databases and it should be set to SQL 2005 (90). To resolve;

• Open the properties for the affected database
• Go to the options section
• Change the compatibility level to SQL 2005 (90)

Introducing LINQ (to objects) in 10 seconds

The database uses of LINQ (to SQL/to Entities) get all of the glory, but LINQ can be used against your run of the mill objects in .NET. Take for example sorting strings;

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

static void Main(string[] args) {   string[] friends = new string[]{     "Tony Johnson",     "Joe Mangel",     "Helen Daniels",     "Sky Mangel",     "Stingray",     "Dylan Timmins",     "Gerard Rebbeci",     "Pippa Styger"};   IEnumerable<string> sorted = from friend in friends orderby friend select friend;   foreach (string person in sorted)     Console.WriteLine(person); }

The interesting part is on lines 13/14 - we get a list of sorted strings by executing SQL like syntax on our string array. Lets try something else, lets sort the list based on who's got the longest name, sorting from longest to shortest: Notice I'm also using anonymous types in the following example( var ).

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

static void Main(string[] args) {   var friends = new string[]{     "Tony Johnson",     "Joe Mangel",     "Helen Daniels",     "Sky Mangel",     "Stingray",     "Dylan Timmins",     "Gerard Rebbeci",     "Pippa Styger"};   var sorted = from friend in friends          orderby friend.Length descending          select friend;   foreach (var person in sorted)     System.Console.WriteLine(person); }

Easy, but very useful. You can use LINQ to sort collections of objects and do all sorts of powerful and useful things.

App Domains and Dynamic Loading

Eric Gunnerson has posted an interesting and useful article on dynamic assembly loading and unloading using appdomains.

http://blogs.msdn.com/ericgu/archive/2007/06/05/app-domains-and-dynamic-loading-the-lost-columns.aspx

SQL Server - Changing collation order for an entire cluster

An annoying situation arose today with a project - development machines were using legacy SQL collation orders whilst the production cluster was using a windows collation. As all servers and databases should really use the same collation order, cross database joins and tempdb actions will cause you problems if not , it was decided that we'd change the production cluster to use the SQL_ collation orders!

What should have been a straight forward process actually turned out to be more difficult than expected and I found the following information useful, and largely undocumented;

The process for changing the collation order on a SQL server cluster is as follows;

1. Backup all of your databases and then detach them all
2. Take the SQL services offline in cluster management
3. Slap your SQL install disk into a drive and run the following command from it:

   start /wait setup.exe 
      /qn VS=<cluster name> 
      INSTANCENAME=<sql instance name or MSSQLSERVER for default> 
      REINSTALL=SQL_Engine 
      REBUILDDATABASE=1 
      SAPWD=<new strong SA password> 
      SQLCollation=<new collation order> 
      AdminPassword=<strong password> 
      SQLAccount=<domain\account> 
      SQLPassword=<strong password> 
      AGTAccount=<domain\account> 
      AGTPassword=<domain\account>

4. Bring SQL online in cluster management
5. Restore / re-attach your databases

Your master database etc will have been rebuilt and everything should be fine.

The problem I ran into however was when I used a different location to run setup from than was used in the original installation. Setup kept failing saying that it couldn't find valid setup package for "SQL Standard Edition (64 bit)". After trying lots of different combinations of the above, I discovered that despite running setup.exe from a new location, it actually looks at the registry to discover where it was installed from originally and wants to get the MSI files from there!!

The solution? You could change the registry to point to your new location, but easier is this extra parameter on your command line:

REINSTALLMODE=vomus

This tells the setup not to bother looking at the old location, and use the location where the setup is residing. This 60 minute job ended up taking almost 3 hours in the end - but at least I'll know for next time!

How fast is reflection?

Most .NET developers have an affinity to using reflection for things like configuration, inversion of control, dependency injection etc, but I was wondering today just how fast IS reflection - what overhead does it bring? So I wrote a (far from exhaustive) test;

The test consisted of code that would simulate 500,000 cycles of loading 9 different objects from an external assembly, create an instance of each of those objects and then invoke a method on it.

The method itself then resolves and creates 5 instances of 5 different objects before returning. In total this activity equates to 3 million reflection resolutions by type and 3 million object constructions followed by 500,000 method calls.

The results? The total time from start to finish on my laptop was 19.7 seconds. That seems pretty quick to me.

For refernce my laptop is an Intel Centrino duo core, 2Ghz with 2Gb of memory.

Losing intellisense in your content pages?

When using master pages in ASP.NET, visual studio 2005 often gets confused and loses its ability to display intellisense in your content pages. IE: Within your asp:Content tags, if you type asp: you won't get any intellisense for the standard asp controls.

There's a simple work around for this and that's to ensure you have your master page file open in a tab in the editor. If it's open, you'll get the intellisense back in your content page.

I'm sure this will be fixed in a later release, but until then, the above should suffice.