MDK-Blog

In Visual Studio 2008 and onwards, its very easy to create a DataContext (DBML) with your database entries (tables, views and relations). It is however impossible to use the designer and all the automation to create a Cross-Database (X-DB) DBML layout.

So why would anyone make a X-DB design in the first place ?

The project im working on, uses a rather huge database consisting of 2 “domains”.

The first is the foundational data on which the whole project is based. 4 times a year i receive 11 DVD’s of Data, Images and PDFs resulting in about 16 GB Textfiles. With all indexes applied this ends up becomming a 32GB database with 165mio rows. As you can see, all this data is rather static an seldom changes.

The second, is all the user-data being created: orders, users, shops, suppliers, settings and stuff. around 500 MB of data.

each night, a backup of all the data is done, which means that we copy all ~33GB data every night. With 32GB static data, it seems nuts to do a copy of everything. So i’ve split it up into the two domains.

This enables a lot of nice things that you may easily do. For instance i’ve setup a shadow copy of the whole catalog, with a cloned database and asp.net application. With the new database structure, we are able to copy the user-data VERY fast or even do live replication !

Performance is the same (i guess) the tests i’ve made suggest theres little difference in having 2 databases on the same server, rather then one database. I know that the server will optimize indexes and queries based on the use, but i can’t see why this wouldn’t happen when running parallel databases ?

The Trick

At first, i found a small guide made by Minh T. Nguyen, which shows how to enable X-DB LINQ.

The idea is simple: Open the DBML designer, change every table-reference on each entity to point to the absolute position of the table --> dbo.mytable becomes MyDB1.dbo.mytable and so forth.

This didn’t work for me though !

so i dugg further and found out that i had to go to the *DataContext.designer.cs file to change the tablepath!

It Works… Sometimes

This trick works for all tables that doesnt have strange letters in their name. Which is the case of my project !

All tables are named by a number and a name: 001Headers, 030Descriptions etc.

This results in an attribute like this:

[Table(Name="dbo.[001Headers]")]

And if you simply add the prefix like before:

[Table(Name="Tecdoc.dbo.[001Headers]")]

you’ll get some nasty errors! :-(

So i tried some stuff… and ended up discovering, that removing the brackets solve the problem :D

[Table(Name="Tecdoc.dbo.001Headers")]

who would have thought of that ?

LinqPad Script

Who like doing manual labour when you have Linqpad to the rescue ? :-D

var files = new List<string>()
{
    @"C:\...\DB.designer.cs",
    @"C:\...\SQL.designer.cs"
};

foreach(var file in files)
{
    var file1 = File.ReadAllLines(file);
    var fileout = "";
    
    foreach(var line in file1)
    {
        string txt = line;
        if(line.Contains("[Table(Name=\"dbo."))
        {
            if(line.Contains("dbo.["))
            {
                txt = Regex.Replace(txt, @"(dbo.)\[([\d\w]+)\]", "Database1.$1$2");
                fileout += txt + "\r\n";
            }
            else
            {
                txt = Regex.Replace(txt, @"(dbo.)(\w+)", "Database2.$1$2");
                fileout += txt + "\r\n";
            }
        }
        else
        {
            fileout += txt + "\r\n";
        }
    }
    File.WriteAllText(file, fileout);
}