Is it possible to generate the database creation scripts for a SQL server database from .NET?
I am using C# and I would like to create some sort of an installer project for my application on which I can select an existing database, generate the creation scripts and run them on another SQL server instance.
Yes, it is possible. It's easy to do this with SMO, see Transfer class for scripting operations and Database class for database operations (create, drop, etc). Usage looks like this:
private StringCollection GetTransferScript(Database database)
{
var transfer = new Transfer(database);
transfer.CopyAllObjects = true;
transfer.CopyAllSynonyms = true;
transfer.CopyData = false;
// additional options
transfer.Options.WithDependencies = true;
transfer.Options.DriAll = true;
transfer.Options.Triggers = true;
transfer.Options.Indexes = true;
transfer.Options.SchemaQualifyForeignKeysReferences = true;
transfer.Options.ExtendedProperties = true;
transfer.Options.IncludeDatabaseRoleMemberships = true;
transfer.Options.Permissions = true;
transfer.PreserveDbo = true;
// generates script
return transfer.ScriptTransfer();
}
if you want to create database dynamically with c# code then here is the code:
you can do it like this also:
String Connectionstring = CCMMUtility.CreateConnectionString(false, txt_DbDataSource.Text, "master", "sa", "happytimes", 1000);
SqlConnection con = new SqlConnection();
con.ConnectionString = Connectionstring;
bool resultdbexistencx = CCMMUtility.CheckDatabaseExists(con, txt_DbName.Text);
if (!resultdbexistencx)
{
// if not exists create it check the user name for sub-admin avialibe or not.
if (txt_DbName.Text.Trim() == string.Empty) return;
string strDbCreate;
strDbCreate = "CREATE DATABASE " + txt_DbName.Text + " ON PRIMARY " +
"(NAME = " + txt_DbName.Text + "_Data, " +
"FILENAME = 'D:\\" + txt_DbName.Text + "Data.mdf', " +
"SIZE = 4MB, MAXSIZE = 10GB, FILEGROWTH = 100%) " +
"LOG ON (NAME = " + txt_DbName.Text + "_Log, " +
"FILENAME = 'D:\\" + txt_DbName.Text + ".ldf', " +
"SIZE = 4MB, " +
"MAXSIZE = 10GB, " +
"FILEGROWTH = 100%)";
SqlConnection sqlconn = new SqlConnection(Connectionstring);
SqlCommand cmd = new SqlCommand(strDbCreate, sqlconn);
try
{
sqlconn.Open();
sqlconn.ChangeDatabase("master");
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Int32 dbRollbackResult = RollBackTheWholetransaction(txt_DbName.Text.Trim(), Convert.ToInt32(HospitalResult));
if (dbRollbackResult == 1)
{
Response.Write(ex.Message);
lblMessage.DisplayMessage(StatusMessages.ErrorMessage, "There is some problem while generating the database or database name doesn't avialible.");
}
}
Here is the code of "RollBackTheWholetransaction" method :
private Int32 RollBackTheWholetransaction(String DbName, Int32 HospitalId)
{
Int32 result = 0;
try
{
String Connectionstring = CCMMUtility.CreateConnectionString(false, txt_DbDataSource.Text, "master", "sa", "happytimes", 1000);
SqlConnection con = new SqlConnection();
con.ConnectionString = Connectionstring;
String sqlCommandText = "ALTER DATABASE [" + DbName + "] SET SINGLE_USER WITH ROLLBACK IMMEDIATE";
String sqlCommandText1 = "DROP DATABASE [" + DbName + "]";
if (con.State == ConnectionState.Closed)
{
con.Open();
SqlConnection.ClearPool(con);
con.ChangeDatabase("master");
SqlCommand sqlCommand = new SqlCommand(sqlCommandText, con);
sqlCommand.ExecuteNonQuery();
SqlCommand sqlCommand1 = new SqlCommand(sqlCommandText1, con);
sqlCommand1.ExecuteNonQuery();
ClsHospitals objHospiitals = new ClsHospitals();
String resultDbdelete = objHospiitals.DeleteHospital(HospitalId, Session["devSuperAdmin"].ToString());
if (resultDbdelete == "1")
{
result = 1;
}
else
{
result = 2;
}
}
else
{
SqlConnection.ClearPool(con);
con.ChangeDatabase("master");
SqlCommand sqlCommand = new SqlCommand(sqlCommandText, con);
sqlCommand.ExecuteNonQuery();
SqlCommand sqlCommand1 = new SqlCommand(sqlCommandText1, con);
sqlCommand1.ExecuteNonQuery();
}
con.Close();
con.Dispose();
result = 1;
}
catch (Exception ex)
{
result = 0;
}
return result;
}
And here is the code to check existence of db in Database :
public static bool CheckDatabaseExists(SqlConnection tmpConn, string databaseName)
{
string sqlCreateDBQuery;
bool result = false;
try
{
// tmpConn = new SqlConnection("server=(local)\\SQLEXPRESS;Trusted_Connection=yes");
sqlCreateDBQuery = string.Format("SELECT database_id FROM sys.databases WHERE Name = '{0}'", databaseName);
using (tmpConn)
{
using (SqlCommand sqlCmd = new SqlCommand(sqlCreateDBQuery, tmpConn))
{
if (tmpConn.State == System.Data.ConnectionState.Open)
{
tmpConn.Close();
tmpConn.Dispose();
}
tmpConn.Open();
tmpConn.ChangeDatabase("master");
int databaseID = (int)sqlCmd.ExecuteScalar();
tmpConn.Close();
result = (databaseID > 0);
}
}
}
catch (Exception ex)
{
result = false;
}
return result;
}
its the working code, hope it will work for you too....
You have to create your own installer by coding it all yourself. there are frameworks out there that make it much easyier.
I would suggest you to have a look at WiX, worked with it and its quite easy and you can do much. Can be integrated in Visual Studio
