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

• Linq to SQL doing it manually: Part 1 - Table and column creation from C#

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!