How to create “embedded” SQL 2008 database file if

Question

I've created a database application using C#, ADO.Net and an embedded MS SQL 2008 database file (that attaches to MS SQL 2008 Express) which I created in Server Management Studio. Can someone point me to a resource that describes how I can programmatically create the database file if it is missing (like right after my application is installed)?

Answer 1

If the "embedded MS SQL" is a "Microsoft SQL Server Compact 3.5":

using (SqlCeEngine sqlCeEngine = new SqlCeEngine(connectionString))
  sqlCeEngine.CreateDatabase();

Answer 2

If it were me (when it is me...):

You don't particularly want to be trying to make database files work by copying them and attaching them - there are reasons why you might want to but I believe these to be exceptions rather than rules.

Accordingly what you need to do is to script creation of the database i.e. to use SQL DDL to create the database and the tables and all the other stuff in your schema.

Pretty much all you need to enable you to do this is appropriate rights to the server instance and then a connection string (which you can probably build apart from the server/instance name).

From here:

1. Is there a database? If not create it.
2. If there is a database, is it the right schema version? If too low either update it or advise the user and back out gracefully depending on how you want things too work. If too high just back out and advise that an updated version of the application is required
3. All is as it should be, carry on.

From a code point of view: method to determine if a database exists; method to create an standard "empty" database with a version table and a version number of 0; methods to bring the schema up to the current version by running the appropriate DDL (we encode ours into C# because it provides more flexibility but you could equally run DDL scripts in sequence).

Does it exist:

    public virtual bool Exists()
    {
        bool exists = false;

        string masterConnectionString = this.CreateConnectionString(this.Server, this.FailoverServer, "master");

        this.DBConnection.ConnectionString = masterConnectionString;
        this.DBConnection.Open();
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = this.DBConnection;
            cmd.CommandText = "SELECT COUNT(name) FROM sysdatabases WHERE name = @DBName";
            cmd.Parameters.AddWithValue("@DBName", this.DBName);

            exists = (Convert.ToInt32(cmd.ExecuteScalar()) == 1);
        }
        finally
        {
            this.DBConnection.Close();
        }

        return exists;
    }

Create a new database:

    public virtual void CreateNew()
    {
        string createDDL = @"CREATE DATABASE [" + this.DBName + "]";

        this.BuildMasterConnectionString();

        this.DBConnection.Open();
        try
        {
            this.ExecuteSQLStmt(createDDL, this.DefaultSQLTimeout, null);
        }
        finally
        {
            this.DBConnection.Close();
        }

        createDDL = @"
                CREATE TABLE AAASchemaVersion 
                (
                    Version         int             NOT NULL,
                    DateCreated     datetime        NOT NULL,
                    Author          nvarchar(30)    NOT NULL,
                    Notes           nvarchar(MAX)   NULL 
                );

                ALTER TABLE AAASchemaVersion ADD CONSTRAINT PK_Version PRIMARY KEY CLUSTERED
                (
                    Version
                );

                INSERT INTO AAASchemaVersion
                    (Version, DateCreated, Author, Notes)
                VALUES
                    (0, GETDATE(), 'James Murphy', 'Empty Database')
            ";

        this.BuildConnectionString();
        this.ConnectionString += ";pooling=false";

        this.DBConnection.Open();
        try
        {
            this.ExecuteSQLStmt(createDDL, this.DefaultSQLTimeout, null);
        }
        catch (Exception ex)
        {
            throw new Exception("Exception while creating / initialising AAASchemaVersion", ex);
        }
        finally
        {
            this.DBConnection.Close();
        }
    }

The update code is a tad more complex but basically runs stuff like this:

CREATE TABLE AuditUser
(    
    ID                  int IDENTITY(1,1)   NOT NULL,
    UserSourceTypeID    tinyint             NOT NULL,
    DateCreated         smalldatetime       NOT NULL,
    UserName            nvarchar(100)       NOT NULL        
);
ALTER TABLE AuditUser
ADD CONSTRAINT
    PK_AuditUser PRIMARY KEY CLUSTERED
    (
        ID
    ),
    CONSTRAINT [FK_AuditUser_UserSourceType] FOREIGN KEY
    (
        UserSourceTypeID
    ) REFERENCES UserSourceType (
        ID
    );

All wrapped up in a transaction per update - so that if the update fails you should leave the database is a known good state.

Why do it this way (in code, which is not without its trials?) well the end result is a high degree of confidence that the schema your app is talking to is the schema your app expects to talk to... right tables, right columns (in the right order, that are the right type and the right length), etc, etc. and that this will continue to be the case over time.

Apologies if this is a bit long - but this is something I'm quite keen on...