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?

Mistrot

whereslou.com/2008/10/20/… – Scrogan

do you have the option of using SQL Server CE? it has this functionality.. as in just based on the connection string – Shears

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

Credible

This worked. But later, I can't connect to the datafile at all. SQL Connection String works when creating the file, but later connecting to it gives me alot of different errors. If I use that method to create a "mdf" file in CurrentDirectory, how do I later use it? – Mistrot

Right now, I get errors stating that the database already exists but there's no file assossicated with it. Isn't the database, tables and such contained in the "MDF" file? Since there is none, how can there be a database with no file? – Mistrot

this connection string worked for me: "Data Source=(LocalDB)\\v11.0;Initial Catalog=master; Integrated Security=true;" – Roundtheclock

I get errors:"System.Data.SqlClient.SqlException: 'A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections." Could you help me? – Ergocalciferol

Answer 2

Regarding .sdf files (SQL Server CE), you can use the SqlCeEngine class to create a new database, as described in this MSDN article.

Myriam

Answer 3

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

Park

Answer 4

Make sure you have a valid connection string.

The database/catalog that you need must be set to a valid database, usually this can be the "master" which is always available and since you will be using master to create a database.

Xuanxunit

Answer 5

If you need to create a database from scratch programmatically i normal go into the SQL Server Management Studio and create it through the gui in a first step. But instead of clicking on the OK button in the bottom right, i click on the Script button in the top toolbar. This will give me a complete sql script for creating the database i'd like to have. Then i can alter the script and change the parts i'd like dynamically.

Orgeat

Answer 6

I suppose the problem is in the ConnectionString. It should point to the valid instance of the master db (as in the article you refer to). Make sure it is correct, and it should work.

Cameo

Answer 7

Use a connectionString with InitialCatalog = master. Since only master has default access to create a database.

Nonaligned