How to: Dynamically Create a Database (LINQ to SQL)

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

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

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

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 Sub CreateDatabase()
    Dim db As New MyDVDs("c:\...\mydvds.mdf")
    db.CreateDatabase()
End Sub

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

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

See Also

Reference

Attribute-Based Mapping (LINQ to SQL)

External Mapping Reference (LINQ to SQL)

SQL-CLR Type Mapping (LINQ to SQL)

Other Resources

Background Information (LINQ to SQL)

Making and Submitting Data Changes (LINQ to SQL)