Create .mdf/.sdf database dynamically

Question

How can I with "code" create a new .mdf/.sdf database?

I've tried this: http://support.microsoft.com/kb/307283

All it does is fail on the ConnectionString. Since I have no connection to a file that exists before I create it, how can I only connect to the SQL Express Server just to create a mdf/sdf database?

I want to be able to just connect to the server and create the file, from there it probably will be easier to create the tables and such.

Any suggestions?

Answer 1

public static void CreateSqlDatabase(string filename)
{
    string databaseName = System.IO.Path.GetFileNameWithoutExtension(filename);
    using (var connection = new System.Data.SqlClient.SqlConnection(
        "Data Source=.\\sqlexpress;Initial Catalog=tempdb; Integrated Security=true;User Instance=True;"))
    {
        connection.Open();
        using (var command = connection.CreateCommand())
        {
            command.CommandText =
                String.Format("CREATE DATABASE {0} ON PRIMARY (NAME={0}, FILENAME='{1}')", databaseName, filename);
            command.ExecuteNonQuery();

            command.CommandText =
                String.Format("EXEC sp_detach_db '{0}', 'true'", databaseName);
            command.ExecuteNonQuery();
        }
    }
}

Change Catalog=tempdb to Catalog=master, its good worked

Sample use:

var filename = System.IO.Path.Combine("D:\\", "testdb.mdf");
if (!System.IO.File.Exists(filename))
{
    CreateSqlDatabase(filename);
}