if you want to dynamically create a database with C # code, then here is the code:
You can also do this:
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 for the 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 for db in the 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 working code, I hope it will work for you too.