Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Create .mdf/.sdf database dynamically

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?

like image 666
Deukalion Avatar asked May 07 '12 12:05

Deukalion


2 Answers

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();
like image 51
Rae Lee Avatar answered Oct 30 '22 13:10

Rae Lee


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);
}
like image 24
Ehsan Enaloo Avatar answered Oct 30 '22 15:10

Ehsan Enaloo