Linq to SQL doing it manually: Part 2 - Database creation

This week I started a series on Linq to SQL doing it manually. The serie contains of the following parts:

This part I'll introduce the actual creation of the database, tables and columns. The people that already have made use of Linq to SQL through the dbml-designer, know the generation of a class that inherits from DataContext. The DataContext class contains an method called CreateDatabase.

But the documentation mentions the algorithm that's used:

DataContext.CreateDatabase Method

  • If a database is identified in the connection string, its name is used.
  • If a DatabaseAttribute attribute is present, its Name property is used as the name of the database.
  • If there is no database tag in the connection string and a strongly typed DataContext is used, a database that has the same name as the DataContext inheriting class is checked.
  • If a weakly typed DataContext is used, an exception is thrown.
  • If the DataContext has been created by using a file name, the database corresponding to that file name is created.

I just tried something out to get to know more about it. What I first did, was create a mapping for a few classes so that tables for those classes could be created. I also created a database where I wanted to get generated tables.

So my first approach was the following:

  1. Instantiate a DataContext class with a connection-string.
  2. Call CreateDatabase method.

1 var context = new DataContext(@"..."); 2 context.CreateDatabase();

What happened was an exception. Just according to the algorithm in the documentation. Although the exception message was somewhat strange: "Unable to create database because data context 'DataContext' has no tables." I think this is kind of a strange message, but the documentation already mentions this wasn't going to work.

My second approach.

  1. Create a class derived from DataContext.
  2. Instantiate the derived class with a connection-string.
  3. Call CreateDatabase method on the derived class.
1 public class EmptyContext : DataContext 2 { 3 public EmptyContext(string fileOrServerOrConnection) 4 : base(fileOrServerOrConnection) 5 { 6 } 7 }

var context = new EmptyContext(@"..."); context.CreateDatabase();

Still no luck, I got an exception again. The message was the same though: "Unable to create database because data context 'DataContext' has no tables." This got me thinking. The Context needs to know about the mapped classes in some way. So I took a look at the context that's generated by the designer.

I found get properties for each table/class that was mapped.

My third approach, let's add some properties.

  1. Create a class derived from DataContext.
  2. Add properties of Table<mappedClassType> to the DataContext.
  3. Call CreateDatabase method on the derived class.

1 public class Context : DataContext 2 { 3 public Context(string fileOrServerOrConnection) : base(fileOrServerOrConnection) 4 { 5 } 6 7 8 public Table<Tag> Tags 9 { 10 get { return GetTable<Tag>(); } 11 } 12 13 public Table<Post> Posts 14 { 15 get { return GetTable<Post>(); } 16 } 17 18 public Table<PostTagRelation> PostTagRelations 19 { 20 get { return GetTable<PostTagRelation>(); } 21 } 22 }

1 var context = new Context(@"..."); 2 context.CreateDatabase();

Very nice, this solution almost created the database. I got an error that the database already existed, so I deleted it, and tried again. Succes!