Export (0) Print
Expand All

How to: Dynamically Create a Database

In LINQ to SQL, an object model is mapped to a relational database. Mapping is enabled by using attribute-based mapping or an external mapping file to describe the structure of the relational database. In both scenarios, there is enough information about the relational database that you can create a new instance of the database using the DataContext.CreateDatabase method.

The DataContext.CreateDatabase method creates a replica of the database only to the extent of the information encoded in the object model. Mapping files and attributes from your object model might not encode everything about the structure of an existing database. Mapping information does not represent the contents of user-defined functions, stored procedures, triggers, or check constraints. This behavior is sufficient for a variety of databases.

You can use the DataContext.CreateDatabase method in any number of scenarios, especially if a known data provider like Microsoft SQL Server 2008 is available. Typical scenarios include the following:

  • You are building an application that automatically installs itself on a customer system.

  • You are building a client application that needs a local database to save its offline state.

You can also use the DataContext.CreateDatabase method with SQL Server by using an .mdf file or a catalog name, depending on your connection string. LINQ to SQL uses the connection string to define the database to be created and on which server the database is to be created.

Note Note

Whenever possible, use Windows Integrated Security to connect to the database so that passwords are not required in the connection string.

The following code provides an example of how to create a new database named MyDVDs.mdf.

public class MyDVDs : DataContext
{
    public Table<DVD> DVDs;
    public MyDVDs(string connection) : base(connection) { }
}

[Table(Name = "DVDTable")]
public class DVD
{
    [Column(IsPrimaryKey = true)]
    public string Title;
    [Column]
    public string Rating;
}

You can use the object model to create a database by doing the following:

public void CreateDatabase()
{
    MyDVDs db = new MyDVDs("c:\\mydvds.mdf");
    db.CreateDatabase();
}

When building an application that automatically installs itself on a customer system, see if the database already exists and drop it before creating a new one. The DataContext class provides the DatabaseExists and DeleteDatabase methods to help you with this process.

The following example shows one way these methods can be used to implement this approach:

public void CreateDatabase2()
{
    MyDVDs db = new MyDVDs(@"c:\mydvds.mdf");
    if (db.DatabaseExists())
    {
        Console.WriteLine("Deleting old database...");
        db.DeleteDatabase();
    }
    db.CreateDatabase();
}
Show:
© 2014 Microsoft