Creating a database programmatically in SQL Server

How can I create a database programmatically and what is the minimum information I need to do this?

Please no "SQL Server Management Object API " suggestions.

Solution 1:

You can either use the SQL Server Management Object API (see task "creating, altering and removing databases"):

 var srv = new Server();
 var db = new Database(srv, "mydb");
 db.Create();

Information on how to get started is here. During SQL server installation you need to install the client SDK, the SMO assemblies are in C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies

Or if you don't want the dependency on these assemblies, you can also simply run DDL statements using ADO.Net (e.g. see this question):

using (var connection = new SqlConnection(myConnectionString))
{
    connection.Open();
    var command = connection.CreateCommand();
    command.CommandText = "CREATE DATABASE mydb";
    command.ExecuteNonQuery();
}  

Obviously you need a correct connection string: known sql server instance and a user with CREATE DATABASE permission.

Solution 2:

Create database 'Databasename'

Solution 3:

From the creators:

// your connection string
string connectionString = "Server=(local)\\netsdk;uid=sa;pwd=;database=master";

// your query:
var query = GetDbCreationQuery();

var conn = new SqlConnection(connectionString);
var command = new SqlCommand(query, conn);

try
{
    conn.Open();
    command.ExecuteNonQuery();
    MessageBox.Show("Database is created successfully", "MyProgram", 
                    MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
    MessageBox.Show(ex.ToString());
}
finally
{
    if ((conn.State == ConnectionState.Open))
    {
        conn.Close();
    }
}

To create in default location with default settings, just:

static string GetDbCreationQuery()
{
    // your db name
    string dbName = "MyDatabase";

    // db creation query
    string query = "CREATE DATABASE " + dbName + ";";

    return query;
}

Or, to create it in a specific location:

static string GetDbCreationQuery()
{
    // your db name
    string dbName = "MyDatabase";

    // path to your db files:
    // ensure that the directory exists and you have read write permission.
    string[] files = { Path.Combine(Application.StartupPath, dbName + ".mdf"), 
                       Path.Combine(Application.StartupPath, dbName + ".ldf") };

    // db creation query:
    // note that the data file and log file have different logical names
    string query = "CREATE DATABASE " + dbName +
        " ON PRIMARY" +
        " (NAME = " + dbName + "_data," +
        " FILENAME = '" + files[0] + "'," +
        " SIZE = 3MB," +
        " MAXSIZE = 10MB," +
        " FILEGROWTH = 10%)" +

        " LOG ON" +
        " (NAME = " + dbName + "_log," +
        " FILENAME = '" + files[1] + "'," +
        " SIZE = 1MB," +
        " MAXSIZE = 5MB," +
        " FILEGROWTH = 10%)" +
        ";";

    return query;
}

Even in case the execution fails, give it another try. The db files might have got created.