Click here to Skip to main content
Click here to Skip to main content

LINQ to SQL Database Synchronizer

, 1 Jun 2009 Ms-PL
Rate this:
Please Sign up or sign in to vote.
An open source utility that synchronizes your database structure with a LINQ to SQL model.

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:

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:

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.

License

This article, along with any associated source code and files, is licensed under The Microsoft Public License (Ms-PL)

Share

About the Author

Dan-el Khen
Architect SharpKit
Israel Israel
Founder of SharpKit
Follow on   Twitter

Comments and Discussions

 
GeneralAn Easier Way to synchronize database table updates using LINQ PinmemberBobRI29-Apr-11 4:59 
GeneralMy vote of 5 Pinmembermobtadi12-Dec-10 3:12 
GeneralMy vote of 5 PinmemberDo Quang2-Nov-10 18:44 
GeneralSome key improvements [modified] PinmemberAlphons van der Heijden4-Jun-10 8:43 
QuestionDate type "Time" not supported? PinmemberAlexander Quinte17-Mar-10 12:18 
AnswerRe: Date type "Time" not supported? PinmemberAlexander Quinte27-Mar-10 1:40 
Hi,
I fix my problem in the follow way:
 
In the class DatabaseBuilder I change the method ParseDbType like follow:
 
private DataType ParseDbType(string dbType)
{
if (dbType == "time") dbType = "time(7)"; <-- added this code
string dbTypeName = dbType.Replace("(", "").Replace(")", "");
......
 
The ParseDbType-method need the value in the bracket for the correct parsing.
This solution is not perfect. Its work only for dbType == "time".
The same problem exist for the dbType dateTime2 too.
 

Also I change the method GetDbType in the follow way:
 
else
{
switch (Type.GetTypeCode(type))
{
case TypeCode.Object:
//if (type != typeof(Guid))
//{
// if (type == typeof(byte[]))
// {
// builder.Append("VarBinary(8000)");
// }
// else
// {
// if (type != typeof(char[]))
// {
// throw Common.CouldNotDetermineSqlType(type);
// }
// builder.Append("NVarChar(4000)");
// }
//}
//else
//{
// builder.Append("UniqueIdentifier");
//}
if (type == typeof(Guid))
{
builder.Append("UniqueIdentifier");
break;
}
if (type == typeof(byte[]))
{
builder.Append("VarBinary(8000)");
break;
}
if (type == typeof(char[]))
{
builder.Append("NVarChar(4000)");
break;
}
if (type == typeof(TimeSpan))
{
builder.Append("time");
break;
}
 
// Datentyp konnte nicht ermittelt werden
throw Common.CouldNotDetermineSqlType(type);
 
break;
 
Alex
GeneralMore problems... Pinmemberlexugax11-Feb-10 23:29 
GeneralRe: More problems... PinmemberDan-el Khen11-Feb-10 23:39 
GeneralRe: More problems... Pinmemberlexugax11-Feb-10 23:57 
GeneralProblems when using external database server Pinmemberlexugax2-Feb-10 23:58 
GeneralRe: Problems when using external database server Pinmemberlexugax5-Feb-10 4:37 
GeneralProblem with nvarchar(MAX) PinmemberLinosoft25-Nov-09 7:50 
GeneralRe: Problem with nvarchar(MAX) PinmemberDan-el Khen25-Nov-09 8:14 
GeneralMuchas Gracias and Thank You 10x PinmemberclintonG6-Jun-09 5:54 
GeneralDitto PinmentorNick Butler1-Jun-09 11:26 
GeneralNice PinmemberMember 27270981-Jun-09 7:15 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.141015.1 | Last Updated 2 Jun 2009
Article Copyright 2009 by Dan-el Khen
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid