The Problem Solver

Tell me and I will forget
Show me and I will remember
Involve me and I will understand
- Confucius -

Google Ads

This Blog

Syndication

Search

Tags

News





  • View Maurice De Beijer's profile on LinkedIn

Community

Email Notifications

Explore

Archives

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 Smile. 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

Published Wed, Apr 9 2008 12:19 by Maurice
Filed under: , , ,

Comments

# LINQ to SQL, SQL Compact and the |DataDirectory| macro@ Wednesday, April 09, 2008 7:27 AM

As I previously mentioned having the LINQ DataContext create the database if it doesn't exist yet

# re: LINQ to SQL and creating databases@ Thursday, April 10, 2008 8:13 AM

Hi, Maurice

I would like to use VB2008 express to connect SQL2000. How can I do it ? If you have solution, please send me stevepoon2000@yahoo.com.hk

Thanks

Steve.

by Steve

# re: LINQ to SQL and creating databases@ Tuesday, May 06, 2008 12:33 PM

thanks, thanks, thanks!

by Kuba