Edit

How to: Dynamically Create a Database

  • Article

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

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

Example 1

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;
}

Public Class MyDVDs
    Inherits DataContext
    Public DVDs As Table(Of DVD)
    Public Sub New(ByVal connection As String)
        MyBase.New(connection)
    End Sub
End Class

<Table(Name:="DVDTable")> _
Public Class DVD
    <Column(IsPrimaryKey:=True)> _
    Public Title As String
    <Column()> _
    Public Rating As String
End Class

Example 2

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();
}

Public Sub CreateDatabase()
    Dim db As New MyDVDs("c:\...\mydvds.mdf")
    db.CreateDatabase()
End Sub

Example 3

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();
}

Public Sub CreateDatabase2()
    Dim db As MyDVDs = New MyDVDs("c:\...\mydvds.mdf")
    If db.DatabaseExists() Then
        Console.WriteLine("Deleting old database...")
        db.DeleteDatabase()
    End If
    db.CreateDatabase()
End Sub

See also