LINQ to SQL and creating databases

LINQ to SQL has a really nice feature when making sample and demos in the ability to create the database if it doesn’t exist yet. It also has a function called DatabaseExists() to check if a database exists. Both are defined on the DataContext class.

string connectionString 
    = @"Data Source=.\sqlexpress;Initial Catalog=MyNewDatabase;Integrated Security=True";

MyNewDatabase context = new MyNewDatabase(connectionString);
if (!context.DatabaseExists())
    context.CreateDatabase();

And best of all they work both for SQL Server and for SQL Compact so it is real easy to make demo’s that work with both the full SQL Server as well as the lightweight SQL Compact. All you need to do is change the connection string.

connectionString = @"Data Source=MyNewDatabase.sdf";

Now I specially like the SQL Compact option for samples as there is no installation requirement, just copy the files and you are good to go [:)]. In fact I have been using LINQ to SQL to create a SQL Compact Workflow persistence service but because of the very nature of LINQ to SQL I can use it just as well with the full blown SQL Server as SQL Compact.


So why do I consider the DatabaseExists() function only suitable for demo’s? Well in the case of SQL Server it makes a connection to the server and sees if it can use the database. Hardly a very reliable check as it can fail for any number of reasons. And if the database exists it still doesn’t mean that the schema is correct. For SQL Compact the check is even worse, it only checks if a file with the same name exists, no matter what it is.


Enjoy!

www.TheProblemSolver.nl
http://wiki.WindowsWorkflowFoundation.eu

2 thoughts on “LINQ to SQL and creating databases

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>