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

Create .sdf database

using System.Data.SqlServerCe;
using System.IO;
  string folderPath="D:\\Compact_DB"
  string connectionString;
  string fileName =folderPath+"\\School.sdf";
  string password = "12345";

  if (File.Exists(fileName))
  {
    File.Delete(fileName);
  }

  connectionString = string.Format("DataSource=\"{0}\"; Password='{1}'",    fileName, password);
  SqlCeEngine obj_ceEngine = new SqlCeEngine(connectionString);
  obj_ceEngine.CreateDatabase();

Answer 2

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);
}