public ActionResult SetupDatabase()
        {
            if (!installService.ShoulDatabaseBeSet())
            {
                throw new HttpException(403, "Database is already installed!");
            }

            var model = new SetupDatabaseViewModel();
            return View(model);
        }
        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;
        }
        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 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;
        }