C# use script to create database

Question

I like to use parameter for FILENAME in the code below, instead of N'D:\DBName.mdf' and N'D:\DBName.ldf'.

How can I change it in the .sql file and how to call it from C# with the parameters? Thanks.

SQL script:

CREATE DATABASE [DBName] ON  PRIMARY 
( NAME = N'DBName', FILENAME = N'D:\DBName.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'DBName_log', FILENAME = N'D:\DBName.ldf' , SIZE = 3456KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
END

C# code

string appPathDB = Path.GetDirectoryName(Application.ExecutablePath) + "\\DBName.mdf";
string appPathLog = Path.GetDirectoryName(Application.ExecutablePath) + "\\DBName_log.ldf";

private void CreateDatabase()
{
   string connection = "Data Source=localhost;Initial Catalog=master;User ID=sa;Password=abcd1234";
   FileInfo file = new FileInfo(Path.GetDirectoryName(Application.ExecutablePath) + "\\DBName.sql");
   string script = file.OpenText().ReadToEnd();
   ExecSql(script, connection, "DBName");
   file.OpenText().Close();
}

public void ExecSql(string sql, string connectionString, string dataBaseNameToPrepend)
{
   using (SqlConnection conn = new SqlConnection(connectionString))
   {
      conn.Open();
      Server server = new Server(new ServerConnection(conn));
      server.ConnectionContext.ExecuteNonQuery(sql);
      server.ConnectionContext.Disconnect();
      server.ConnectionContext.ExecuteNonQuery(sql);
      MessageBox.Show("DB Uploaded.");
   }
}

Solution 1

Make below changes to you script file and save it.

CREATE DATABASE [DBName] ON  PRIMARY 
( NAME = N'DBName', FILENAME = N'DB_NAME_MDF' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'DBName_log', FILENAME = N'DB_NAME_LDF' , SIZE = 3456KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
END

Add the below lines to you code.

private void CreateDatabase()
    {
        string connection = "Data Source=localhost;Initial Catalog=master;User ID=sa;Password=abcd1234";
        FileInfo file = new FileInfo(Path.GetDirectoryName(Application.ExecutablePath) + "\\DBName.sql");
        string script = file.OpenText().ReadToEnd();
        script = script.Replace("DB_NAME_MDF", appPathDB).Replace("DB_NAME_LDF", appPathLog);
        ExecSql(script, connection, "DBName");
        file.OpenText().Close();
    }

Solution 2

I think there are different way to work on this.

But according to me the simple and easy way to find a work around here can be

create a file say dbcreate.sql

CREATE DATABASE [DBName] ON  PRIMARY 
( NAME = N'DBName', FILENAME = XXXMDFXXX , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'DBName_log', FILENAME = XXXLDFXXX , SIZE = 3456KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
END

keep this file at a location in your application and read this file in C# code and replace the values of XXXMDFXXX to your parameter value N'D:\Work\DBLocation\Files\Employee.mdf' and XXXLDFXXX to your second parameter value N'D:\Work\DBLocation\Files\Employee.ldf'

after the replace is done save the new file at new location and with new name say dbMainCreate.sql

once the save is done successfully then follow your code to create a database using new parameters.

NOTE: Before storing the new file you can check if file is already avaialble at the location if yes then delete the existing file and then save. You can also move or copy the file to another location to keep version control.