Create SQL Server Database using C#

2010-08-24


Here is a sample from codeproject :

Create SQL Server Database using C#

private void CreateDatabase(DatabaseParam DBParam)
{
    System.Data.SqlClient.SqlConnection tmpConn;
    string sqlCreateDBQuery;
    tmpConn = new SqlConnection();
    tmpConn.ConnectionString = "SERVER = " + DBParam.ServerName + 
                         "; DATABASE = master; User ID = sa; Pwd = sa";
    sqlCreateDBQuery = " CREATE DATABASE "
                       + DBParam.DatabaseName
                       + " ON PRIMARY " 
                       + " (NAME = " + DBParam.DataFileName +", "
                       + " FILENAME = '" + DBParam.DataPathName +"', " 
                       + " SIZE = 2MB,"
                       + " FILEGROWTH =" + DBParam.DataFileGrowth +") "
                       + " LOG ON (NAME =" + DBParam.LogFileName +", "
                       + " FILENAME = '" + DBParam.LogPathName + "', " 
                       + " SIZE = 1MB, "
                       + " FILEGROWTH =" + DBParam.LogFileGrowth +") ";
     SqlCommand myCommand = new SqlCommand(sqlCreateDBQuery, tmpConn);
     try
     {
         tmpConn.Open();
         MessageBox.Show(sqlCreateDBQuery);
         myCommand.ExecuteNonQuery();
         MessageBox.Show("Database has been created successfully!", 
                           "Create Database", MessageBoxButtons.OK, 
                                       MessageBoxIcon.Information);
      }
     catch (System.Exception ex)
     {
         MessageBox.Show(ex.ToString(), "Create Database", 
                                     MessageBoxButtons.OK, 
                              MessageBoxIcon.Information);
     }
     finally
     {
         tmpConn.Close();
     }
     return;
}

There are many others. Another sample from Microsoft :

using System;
using System.Data.SqlClient;

String str;
    SqlConnection myConn = new SqlConnection ("Server=localhost;Integrated security=SSPI;database=master");

    str = "CREATE DATABASE MyDatabase ON PRIMARY " +
        "(NAME = MyDatabase_Data, " +
        "FILENAME = 'C:\\MyDatabaseData.mdf', " +
        "SIZE = 2MB, MAXSIZE = 10MB, FILEGROWTH = 10%) " +
        "LOG ON (NAME = MyDatabase_Log, " +
        "FILENAME = 'C:\\MyDatabaseLog.ldf', " +
        "SIZE = 1MB, " +
        "MAXSIZE = 5MB, " +
        "FILEGROWTH = 10%)";

    SqlCommand myCommand = new SqlCommand(str, myConn);
    try 
    {
        myConn.Open();
    myCommand.ExecuteNonQuery();
    MessageBox.Show("DataBase is Created Successfully", "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
    }
    catch (System.Exception ex)
    {
    MessageBox.Show(ex.ToString(), "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
    }
    finally
    {
    if (myConn.State == ConnectionState.Open)
    {
        myConn.Close();
    }
    }

If you want to create a database that is similar to the SQL Server Model database, and you want the database in the default location, then change the str variable in the code, as in the following sample code:

str = "CREATE DATABASE MyDatabase"

Also, the 3rd sample is using Microsoft "SQLDMO.dll":

(you can find the SQLDMO.dll in C:\Program Files\Microsoft SQL Server\80\Tools\Binn or download it from http://www.driverskit.com/dll/sqldmo.dll/3449.html to add refference to a project , From project menu select "add refference" -> select the browse tab and select the dll file from the location and add to the project )

public bool CreateDb(string serverName, string Uid, string Pwd, string dbName)
        {
            bool result = true;
            SQLDMO.SQLServer gSQLServerDMO = new SQLDMO.SQLServer();
            SQLDMO.Database nDatabase = new SQLDMO.Database();
            SQLDMO.DBFile nDBFileData = new SQLDMO.DBFile();
            SQLDMO.LogFile nLogFile = new SQLDMO.LogFile();
          
            try
            {
                gSQLServerDMO.LoginSecure = true;
                gSQLServerDMO.Connect(serverName, Uid, Pwd);

                nDatabase.Name = dbName;
                nDBFileData.Name = dbName;
                nDBFileData.PhysicalName = gSQLServerDMO.Registry.SQLDataRoot + "\\DATA\\" + dbName + "_Data.mdf";               
                nDBFileData.PrimaryFile = true;
                nDBFileData.Size = 2;
                nDBFileData.FileGrowthType = SQLDMO.SQLDMO_GROWTH_TYPE.SQLDMOGrowth_MB;
                nDBFileData.FileGrowth = 1;

                //Add the DBFile object
                nDatabase.FileGroups.Item("PRIMARY").DBFiles.Add(nDBFileData);
               //
                nLogFile.Name = dbName + "Log";
                nLogFile.PhysicalName = gSQLServerDMO.Registry.SQLDataRoot + "\\DATA\\" + dbName + "_Log.ldf";
                nLogFile.Size = 2;
                nDatabase.TransactionLog.LogFiles.Add(nLogFile);
                gSQLServerDMO.Databases.Add(nDatabase);
                MessageBox.Show("Database Created Sucessfully);
                gSQLServerDMO.DisConnect();
            }
            catch (Exception SQLDBException)
            {
                MessageBox.Show(SQLDBException.Message);
                result = false;
            }
            finally
            {
            }
            return (result);
        }

Above 3rd sample from here