How to Create "Embedded" SQL 2008 Database File If It Doesn't Exist

How to create embedded SQL 2008 database file if it doesn't exist?

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:

Is there a database? If not create it.
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
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...

Need example how to build SQL database in C#

In terms of creating databases and schemas from code (C# and MS SQL Server), I have sample code here:

How to create "embedded" SQL 2008 database file if it doesn't exist?

creating setup of vb2008

You have to do a few things to set up your application:

Install the .NET framework if required
Install SQL Server 2008 if required
Install your application
Define/configure the connection from your application to the SQL Server instance
Create your database/schema in the SQL Server instance.

Ignore the SQL Server problem for a moment, the easiest way to deal with the .NET framework and installing the application would be to use a setup project - which should be available from within VS.NET under Other Project Types|Setup and Deployment. There are hooks in there to give you options for installing dependencies - of which the .NET framework is one.

Ok, you have a tool to create setups (there are several others, e.g. I'm currently using WiX which I like so far, is very capable but can rapidly becomes complex) - the problem now is that the installer you need to build will depend on how and where your application is to be deployed. Do you want to ship a complete, self-contained, application on a disc? Is it to be downloaded internally within a business or distributed over the internet - each of these suggests a different set of packages at one end "everything" at the other you want the smallest possible pieces pulled down as required or perhaps even a different packaging method (e.g. clickonce).

Next up is SQL Server. You can get a redistributable package for SQL Server 2008 Express, so distributing it is not a problem however you have to determine if the user has an existing instance they want to user or if they want to install.

Once you've got an installed instance - you need to be able to create and to maintain (update) the database/schema within that instance. That I suggest you do using code (see here: How to create "embedded" SQL 2008 database file if it doesn't exist?). Which brings us to another point, you not only have to be able to install the application the first time, but you need to make sure that a) you provide a means to uninstall the application and b) that you can neatly do an upgrade in place.

I hope there are enough pointers there to get you moving - in terms of testing this, Virtual Machines are your friend, they give you the capability to create multiple environments in which to test your deployment and the ability to quickly roll back to a clean environment to test again as its virtually impossible to properly test an installer on a dev box (I've found this out the hard way) as it will already have all the dependencies for your application installed.

Pick your tools and that should let you ask more focused questions.

Deploy/run database changes before automatic build

Self evidently you need to automate running of your database change scripts - the challenge there being how do you decide what to run. The answer there is that you need to store the schema version in the database (or in the database metadata) and choose what scripts to run accordingly - whether that be to bring a database up from scratch or to apply appropriate changes.

My solution is to put my database schema maintenance entirely in code, I think this is the best version of the writeup I've done so far:

How to create "embedded" SQL 2008 database file if it doesn't exist?

Given code smart enough to do upgrade the schema either in-line in your application or in its own console app you should be able to integrate automating schema changes into your CI build scripts.

I've been chasing around this a bit further since I wrote the above - it turns out that what I'm doing is not dissimilar to Ruby Migrations (I believe something similar also features in Subsonic) excepting that I don't currently have any "down" capability to reverse a schema change (an interesting idea I have concerns with having potentially destructive code "live"). There is an open source .NET project Migrator.net that looks useful, but I'm not sure how actively its being maintained.

Addendum

Since I wrote this entity framework code first has appeared and they're starting to make headway with migrations - so that would now be worth a look. However I don't think its quite there yet and like several other options I've seen its not simple. I need to package mine up and make it available!

I'm trying to grasp the concept of creating a program that uses a SQL Server database, but I'm used to running it only on my local machine

Ok, part of the answer, dealing with the SQL Server Database (and frankly SQL Server Express will take you a long way - 4Gb of data) and the server install elements.

Firstly make installation of the SQL an SEP, make it a pre-requisite possibly tweak your installers to test (challenging) but substantially point them at the links to SQL Server express and let them get on with it).

Secondly separate installers, as suggested, for your client and your server elements.

Finally, how to build the database - I'd suggest using code to create and maintain (update) the schema i.e. once you have a connection to a server you can run code that calls DDL that does what is necessary (something like suggested here: How to create "embedded" SQL 2008 database file if it doesn't exist?)

A question - are you intending all communications from the clients to go through you WCF service?

Linq-to-SQL: how to handle database changes

You need to manage your database schema explicitly - that is to say that you should have creation and update of the database schema scripted so that its repeatable. For the scenario your describing I think that your application should (ideally) create and then update the database schema as required. The initial work to set this up isn't too hard and once you have the system in place making schema changes is straightforward

I wrote this up (in terms of what has worked for me for a lot of years now) at some length here:

How to create "embedded" SQL 2008 database file if it doesn't exist?

Which probably ought to be modified to take advantage of this which talks about using database extended properties:

SQL Server Database schema versioning and update

How can you create a login and a user but only if it doesn't exist?

SQL Server doesn't support syntax such as CREATE {object} IF NOT EXISTS. It did introduce CREATE OR ALTER syntax in SQL Server 2017, however, that is only for objects where the object CREATE statement must be the only one in the batch.

For objects that don't need that, such as a LOGIN and USER, you can check if the object exists using an IF and NOT EXISTS. For example:

USE master;
GO

IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE [name] = N'MyLogin')
    CREATE LOGIN [MyLogin]
      WITH PASSWORD=N'MyPassword',
           DEFAULT_DATABASE=[MyNewDatabase],
           DEFAULT_LANGUAGE=[us_english],
           CHECK_EXPIRATION=OFF,
           CHECK_POLICY=OFF;
GO
USE [MyNewDatabase];
GO
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE [name] = N'MyLogin')
BEGIN
    CREATE USER [MyLogin] FOR LOGIN [MyLogin] WITH DEFAULT_SCHEMA=[MyLogin];
    ALTER ROLE db_datareader ADD MEMBER MyLogin; --Don't use sp_addrolemember is has been deprecated for some time
    ALTER ROLE db_datawriter ADD MEMBER MyLogin; --Don't use sp_addrolemember is has been deprecated for some time
END;

Linq-to-SQL: how to handle database changes

I wrote this up (in terms of what has worked for me for a lot of years now) at some length here:

How to create "embedded" SQL 2008 database file if it doesn't exist?

Which probably ought to be modified to take advantage of this which talks about using database extended properties:

SQL Server Database schema versioning and update

How to Create "Embedded" SQL 2008 Database File If It Doesn't Exist