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);
var type = asm.GetType(Options.TypeName, true);
var model = new AttributeMappingSource().GetModel(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);
var server = new Server(sb.DataSource);
var db = server.Databases[sb.InitialCatalog];
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);
var table = Database.Tables[tableName, schemaName];
if (table == null)
{
table = new Table(Database, tableName, schemaName);
Database.Tables.Add(table);
}
}
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.
History
- 2nd June, 2009: Initial version
