LINQ to SQL Database Synchronizer

• Open code in an online IDE
• CodePlex Project
• Download source - 60.16 KB

Introduction

LINQ to SQL Database Synchronizer (csdb.exe) is a tool that provides database structure synchronization from a LINQ to SQL model. It supports both creating new databases and updating existing ones. It is important to note that this tool never deletes any data or structure; it simply adds missing tables, columns and indexes and modifies existing columns.

Main Benefits

• Single click O\R model synchronization
• Better team productivity, streamline database changes between your team members
• Easier product installations and upgrades, no complicated SQL scripts needed
• Simpler maintenance of a single model instead of two

Background

The LINQ to SQL Framework provides a simple and convenient way to communicate with databases, using simple C# classes and LINQ, without writing any sophisticated SQL commands. While working with it, I've stumbled upon a major drawback: The DataContext.CreateDatabase method only supports creating new databases. This means that in order to modify an existing LINQ to SQL model over an existing database, one must apply the structure changes manually on the database, or delete the database entirely and invoke the CreateDatabase method again. This drawback may really slow a team down, when each member of the team has his own database for testing, every feature that another member adds that requires a database structure change, forces the other members to run various scripts or perform manual changes on their own database. The same goes for live servers that need to be updated with a new product version.

The DataContext.CreateDatabase method I've mentioned earlier uses SQL scripts to create the database and the tables inside it. This tool uses the SQL Server Management Objects (SMO) library instead. It analyzes a LINQ to SQL model, compares it to an existing database structure and finally applies all of the necessary changes to the database structure.

Inside the Code

We basically have two models we want to synchronize - one is the LINQ to SQL model, and the other is the database model. So let's start off by loading the LINQ to SQL model:

var asm = Assembly.LoadFrom(Options.AssemblyFile);       //Load an assembly file
var type = asm.GetType(Options.TypeName, true);          //Find the DataContext class
						  //using reflection
var model = new AttributeMappingSource().GetModel(type); //Load the LINQ to SQL mapping
                                                         //model from the specified type

Review this in an online IDE here and here.

Now, we'll load the database model using SMO and the connection string we have.

var sb = new SqlConnectionStringBuilder(ConnectionString);   //Parse the 
						      //connection string
var server = new Server(sb.DataSource);                      //Connect to the 
                                                             //database server
var db = server.Databases[sb.InitialCatalog];                //Get the database

Review this in an online IDE here and here.

Now we have all the data we need, and it's simply a matter of iterating the LINQ to SQL model, and locating the corresponding database objects. If they don't exist, we simply create them, otherwise we verify their definition.

foreach (MetaTable table4 in model.GetTables())
{
  string schemaName;
  string tableName;
  ParseFullTableName(mt.TableName, out schemaName, out tableName);  //Split the schema 
							    //and table name
  var table = Database.Tables[tableName, schemaName];               //Find the table
  if (table == null)                                                //If the table 
                                                                    //doesn't exist
  {
    table = new Table(Database, tableName, schemaName);             //Create the table
    Database.Tables.Add(table);
  }
  //Now we can synchronize the table columns...
}

Review this in an online IDE here and here.

Using the Code

This tool works as a simple command line tool (*.exe), simply specify an assembly and a type name of a LINQ to SQL DataContext class and run it, your database structure will be synchronized by this model.

Prerequisites

This tool uses SQL Server Management Objects (SMO), the latest version of it can be found at the Microsoft SQL Server 2008 Feature Pack download page, but here are the direct links:

• SQLSysClrTypes.msi
• SharedManagementObjects.msi

Usage

This tool is used like any standard command line tool, with the following syntax:

csdb.exe /assembly:[AssemblyFile] /type:[TypeName] /autocreate 
	/autoupdate /cs:[ConnectionString] /dbfilename:[DatabaseFilename]
	
  /assembly:[AssemblyFile]    The assembly filename that contains the LINQ to SQL
			   (http://msdn.microsoft.com/en-us/library/bb425822.aspx) 
			   DataContext class, e.g.: bin\debug\MyApp.exe
  /type:[TypeName]            Optional, The type name of the LINQ to SQL
			   (http://msdn.microsoft.com/en-us/library/bb425822.aspx) 
			   DataContext class, e.g.: MyApp.MyDataContext. 
			   if not provided, 
			   any class that inherits from the DataContext 
			   class will be processed.
  /autocreate                 When specified, the tool will create the database 
			   if it doesn't exist.
  /autoupdate                 When specified, the tool will update the existing 
			   database structure.
  /cs:[ConnectionString]      Optional, The connection string of the database 
			   to be synchronized, if not provided, the default 
			   connection string will be used if it exists 
			   in your settings.
  @[Arguments Filename]       Read the command line arguments from an 
			   external text file.

Example

This statement will synchronize (create and update) the MyDb database in the local machine's SqlExpress instance using the MyApp.MyDataContext class located in the MyApp.exe assembly.

csdb.exe /assembly:"bin\debug\MyApp.exe" /type:"MyApp.MyDataContext" 
	/autocreate /autoupdate /cs:"Data Source=.\SQLEXPRESS;Initial 
	Catalog=MyDb;Integrated Security=True"

Recommendations

• Specify both the /autocreate and /autoupdate options for maximum automation.
• Create a batch file that executes this tool, and include it in your project.
• In the early stages of development, run this batch file as a post-build step in your project file.

Running the Sample

The sources include a sample project with a LINQ to SQL model of the well known Northwind database. To run the sample:

• Install the prerequisites - SQLSysClrTypes.msi, SharedManagementObjects.msi
• Open the solution and build it
• Run the Samples\SyncMyNorthwindDb.bat batch file to create the database
• Run the Samples project

To change the LINQ to SQL model and synchronize the database:

• Modify the LINQ to SQL model by modifying the MyNorthwind.dbml file using Visual Studio, you can add a column, add a table, change the data type of a column, allow nulls on a column, etc...
• Build the Samples project to reflect your changes.
• Run the SyncMyNorthwindDb.bat batch file to synchronize your database.

Points of Interest

I think that this tool answers a basic need when using the LINQ to SQL Framework. We use it extensively at CodeRun in both development and deployment. In this way, modifying the database is as simple as adding a property.