public string TestConnectionString(SetupDatabaseViewModel model)
{
string connectionString;
var isMaster = false;
if (model.CreateDatabase)
{
isMaster = true;
connectionString = CreateConnectionString(model, "master");
}
else
{
connectionString = CreateConnectionString(model);
}
try
{
using (var conn = new SqlConnection(connectionString))
{
conn.Open(); // throws if invalid
}
}
catch (Exception exc)
{
var message = exc.Message;
if (isMaster)
{
message = string.Format("{0} {1}", InstallGlobalization.TestDatabaseConnection_AdminIsRequired, message);
}
throw new ValidationException(() => message, message, exc);
}
return(connectionString);
}
public ActionResult SetupDatabase()
{
if (!installService.ShoulDatabaseBeSet())
{
throw new HttpException(403, "Database is already installed!");
}
var model = new SetupDatabaseViewModel();
return(View(model));
}
private string CreateConnectionString(SetupDatabaseViewModel model, string database = null)
{
database = database ?? model.Database;
if (model.EditConnectionString)
{
if (string.IsNullOrWhiteSpace(model.ConnectionString))
{
var message = InstallGlobalization.TestDatabaseConnection_ConnectionStringIsNotSet;
throw new ValidationException(() => message, message);
}
return(model.ConnectionString);
}
if (string.IsNullOrWhiteSpace(model.Server))
{
var message = InstallGlobalization.TestDatabaseConnection_ServerNameIsNotSet;
throw new ValidationException(() => message, message);
}
if (string.IsNullOrWhiteSpace(database))
{
var message = InstallGlobalization.TestDatabaseConnection_DatabaseNameIsNotSet;
throw new ValidationException(() => message, message);
}
if (!model.IsIntegratedSecurity)
{
if (string.IsNullOrWhiteSpace(model.Username))
{
var message = InstallGlobalization.TestDatabaseConnection_UserNameIsNotSet;
throw new ValidationException(() => message, message);
}
if (string.IsNullOrWhiteSpace(model.Password))
{
var message = InstallGlobalization.TestDatabaseConnection_PasswordIsNotSet;
throw new ValidationException(() => message, message);
}
}
var connectionString = string.Format("Server={0};Database={1};", model.Server, database);
connectionString = model.IsIntegratedSecurity
? string.Format("{0}Integrated Security=SSPI;", connectionString)
: string.Format("{0}User Id={1};Password={2};", connectionString, model.Username, model.Password);
return(connectionString);
}
public ActionResult TestConnection(SetupDatabaseViewModel model)
{
var success = false;
if (ModelState.IsValid)
{
success = GetCommand <TestConnectionCommand>().ExecuteCommand(model);
if (success)
{
Messages.AddSuccess(InstallGlobalization.TestDatabaseConnection_ConnectionIsOK);
}
}
return(WireJson(success));
}
public ActionResult SetupDatabase(SetupDatabaseViewModel model)
{
if (!installService.ShoulDatabaseBeSet())
{
throw new HttpException(403, "Database is already installed!");
}
var success = false;
if (ModelState.IsValid)
{
if (GetCommand <SetupDatabaseCommand>().ExecuteCommand(model))
{
// Touch web.config, which causes server to restart
System.IO.File.SetLastWriteTimeUtc(HostingEnvironment.MapPath("~/web.config"), DateTime.UtcNow);
success = true;
Messages.AddSuccess(InstallGlobalization.SetupDatabase_DatabaseIsOK_Reloading);
}
}
return(WireJson(success));
}
public string CreateDatabase(SetupDatabaseViewModel model)
{
var connectionString = CreateConnectionString(model, "master");
// Validating database
var regex = new Regex("^[\\w]*$", RegexOptions.CultureInvariant);
if (!regex.IsMatch(model.Database))
{
var message = InstallGlobalization.CreateDatabase_DatabaseNameIsInvalid;
throw new ValidationException(() => message, message);
}
// Creating database
using (var conn = new SqlConnection(connectionString))
{
try
{
conn.Open();
var sql = string.Format("CREATE DATABASE {0}", model.Database);
var command = new SqlCommand(sql, conn);
command.ExecuteNonQuery();
}
catch (Exception exc)
{
var message = exc.Message;
throw new ValidationException(() => message, message, exc);
}
}
// Creating user
connectionString = CreateConnectionString(model);
using (var conn = new SqlConnection(connectionString))
{
conn.Open();
var transaction = conn.BeginTransaction("BCMSTransaction");
try
{
// Check if user exists
string user = DefaultUserName;
var password = Guid.NewGuid().ToString().Replace("-", string.Empty);
var userCommand = new SqlCommand("select TOP 1 1 from sys.syslogins where name=@user", conn);
userCommand.Transaction = transaction;
userCommand.Parameters.Add("user", SqlDbType.NVarChar).Value = user;
if (userCommand.ExecuteScalar() != null)
{
user = string.Format("BetterCmsUser{0}", DateTime.Now.ToString("yyyyMMddhhmmss"));
}
// Create user
var command = new SqlCommand();
command.Transaction = transaction;
command.Connection = conn;
command.CommandText = string.Format("CREATE LOGIN {0} WITH PASSWORD = '******'", user, password);
command.ExecuteNonQuery();
command.CommandText = string.Format("CREATE USER {0} FOR LOGIN {0}", user);
command.ExecuteNonQuery();
command.CommandText = string.Format("EXEC sp_addrolemember 'db_ddladmin', {0}", user);
command.ExecuteNonQuery();
command.CommandText = string.Format("EXEC sp_addrolemember 'db_securityadmin', {0}", user);
command.ExecuteNonQuery();
command.CommandText = string.Format("EXEC sp_addrolemember 'db_datareader', {0}", user);
command.ExecuteNonQuery();
command.CommandText = string.Format("EXEC sp_addrolemember 'db_datawriter', {0}", user);
command.ExecuteNonQuery();
transaction.Commit();
var clonedModel = (SetupDatabaseViewModel)model.Clone();
clonedModel.Username = user;
clonedModel.Password = password;
clonedModel.IsIntegratedSecurity = false;
clonedModel.CreateDatabase = false;
connectionString = CreateConnectionString(clonedModel);
}
catch (Exception exc)
{
var message = exc.Message;
// Try rollback transaction
try
{
transaction.Rollback();
}
catch
{
// Do nothing
}
// Try close connection
try
{
conn.Close();
conn.Dispose();
}
catch
{
// Do nothing
}
// Trying to drop database, if user creation fails
try
{
using (var dropConn = new SqlConnection(CreateConnectionString(model, "master")))
{
dropConn.Open();
var sql = string.Format("DROP DATABASE {0}", model.Database);
var command = new SqlCommand(sql, dropConn);
command.ExecuteNonQuery();
}
}
catch
{
message = string.Format("{0}. Please drop created database manually.", message);
}
message = string.Format("Failed to create user for database {0}. {1}", model.Database, message);
throw new ValidationException(() => message, message, exc);
}
}
return(connectionString);
}
