Create Database

Database is created when there is an attempt to open connection to non existent file with valid path and read write permissions on that file e.g. directory, folder or memory location. This default behavior can be changed through DatabaseFileMode property. If the specified file exists then connection is opened to specified database file other wise an empty database is created.

Library provides an embedded database system there is no need to even have create database command. However there are attach and detach commands to add or remove schemas from connection. Attach Database will add either on disk or in memory database file. Each connection can have up-to 50 schemas.

The database file path which is provided in connection string is known as primary, main or connection database with default schema name of sdbn and can be change it using SchemaName key in connection string. Schema Name should be short under 40 characters long with Alpha Numeric characters only. Each schema name must be unique with-in that connection. Schema names are not stored in database and are temporary, once the connection is closed or database is detached name can be reused. The initial connection database can not be detached.

Each database connection have minimum of two databases one Primary connection database and one temporary database, all attached databases use the same temporary database for temporary tables or other processing, temp database schema name is "temp" e.g. temp.TableName

Following sql code creates or removes databases from connection. The required arguments [ Path or @memory ] and [Schema Name] must be enclosed in single quote e.g. '@memory' and 'Sales'

ATTACH Database [ Path or @memory ] AS [Schema Name] ;
DETACH DATABASE [Schema Name] ;

/* @memory keyword will create in-memory database */
ATTACH DATABASE '@memory' AS 'memdb1';

/* Detaches database with schema name memdb1  */
DETACH DATABASE 'memdb1';

/* Attach physical database file with schema name db1 */                
ATTACH DATABASE 'C:\\Database File Path' AS 'db1';

/* Detaches database with schema name db1 */
DETACH DATABASE 'db1';
                

Create using .Net

Once Connection is opened, you can attach more databases and use them in JOIN statement e.g. SchemaName1.TableName Join SchemaName2.Table , there is a limit of 50 schemas per connection and maximum 60 tables in single join statement.

                    
using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("SchemaName=memdb;datasource=file://@memory;"))
 {
   // DatabaseFileMode.OpenOrCreate will create an empty database file
  cnn.DatabaseFileMode = DatabaseFileMode.OpenOrCreate; //Default and Optional 
  cnn.DatabaseMode = DatabaseMode.ReadWrite; //Optional
  cnn.Open(); //database is created as soon as open is issued and neccessary permissions are present.
       using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(string.Empty, cnn))
         {

            //Following are optional if you want to attach another schema.
            cmd.CommandText = "ATTACH DATABASE '@memory' AS 'memdb1';";
            cmd.ExecuteNonQuery();
            //Do your work here
            cmd.CommandText = "DETACH DATABASE 'memdb1';";
            cmd.ExecuteNonQuery();
        }
  }