public static ActionResult PatchDatabase(Session session, Dictionary <string, DatabasePatchInfo[]> featurePatchInfos)
{
var version = session.GetDetectedOlderVersion();
RunDatabaseAction(
session,
(featureName) =>
{
if (featurePatchInfos.ContainsKey(featureName))
{
var featurePatchInfo = DatabasePatchInfo.Merge(featurePatchInfos[featureName], version);
if (featurePatchInfo != null)
{
DatabaseSetupInfo setupInfo = DatabaseSetupInfo.CreateFromFeature(session, featureName);
PatchDatabaseForm patchDatabaseForm = new PatchDatabaseForm();
patchDatabaseForm.SetupInfo = setupInfo;
var dialogResult = BaseForm.ShowAsDialog(patchDatabaseForm, session.InstallerWindowWrapper());
if (dialogResult == DialogResult.Cancel)
{
throw new Exception("Cancelled");
}
else
{
ExecuteDDL(featurePatchInfo.SqlScript, patchDatabaseForm.SetupInfo);
RunCustomMethod(featurePatchInfo.PatchAction, patchDatabaseForm.SetupInfo);
}
}
}
}
);
return(ActionResult.Success);
}
public static ActionResult AppSearch_DB(Session session, bool databaseShouldBeNew)
{
RunDatabaseAction(
session,
featureName =>
{
session.SetPropertyValue(DatabaseSetupInfo.FeaturePropertyName, featureName);
if (session.IsRemoving() || session.IsPatching() || session.IsOlderVersionDetected())
{
DatabaseSetupInfo.CopyRegistryToProperties(session, featureName);
DatabaseSetupInfo databaseSetupInfo = DatabaseSetupInfo.CreateFromCurrentDetails(session);
DatabaseSetupInfo.AddFeatureDetails(session, databaseSetupInfo);
}
else if (session.UiLevel() != InstallUILevel.Full)
{
bool allowPropNameWithoutFeature = DatabaseSetupInfo.GetDatabaseFeatureNames(session).Length == 1;
DatabaseSetupInfo databaseSetupInfo = DatabaseSetupInfo.CreateFromCurrentDetails(session, featureName, allowPropNameWithoutFeature);
if (TestConnectionString(session, databaseSetupInfo, databaseShouldBeNew, false))
{
DatabaseSetupInfo.AddFeatureDetails(session, databaseSetupInfo);
}
}
}
);
return(ActionResult.Success);
}
private static void DropDatabase(DatabaseSetupInfo setupInfo)
{
if (!string.IsNullOrEmpty(setupInfo.DatabaseName) && setupInfo.DatabaseExists())
{
using (SqlConnection adminConnection = new SqlConnection(setupInfo.CreateConnectionString(true, false)))
{
adminConnection.Open();
using (SqlCommand selectCommand = new SqlCommand("SELECT spid from sys.sysprocesses WHERE dbid in (SELECT database_id FROM sys.databases WHERE name=@Name)", adminConnection))
{
selectCommand.Parameters.Add("@Name", System.Data.SqlDbType.VarChar).Value = setupInfo.DatabaseName;
DataTable processIdsTable = new DataTable();
SqlDataAdapter processIdsAdapter = new SqlDataAdapter(selectCommand);
processIdsAdapter.Fill(processIdsTable);
using (SqlCommand killCommand = new SqlCommand("", adminConnection))
{
foreach (DataRow dRow in processIdsTable.Rows)
{
killCommand.CommandText += string.Format("KILL {0};\r\n", dRow[0]);
}
killCommand.CommandText += string.Format("DROP DATABASE [{0}]", setupInfo.DatabaseName);
killCommand.ExecuteNonQuery();
}
}
}
}
}
static void RunDatabaseAction(Session session, Action <string> func)
{
foreach (var featureName in DatabaseSetupInfo.GetDatabaseFeatureNames(session))
{
func(featureName);
}
}
public static void SetSuggestedPropertyValues(Session session, string featureName)
{
BaseSetupInfo.SetSuggestedPropertyValues(
session,
featureName,
DatabaseSetupInfo.GetDatabaseFeatureNames(session),
DatabaseSetupInfo.GetSuggestedDatabaseNames(session),
new string[] { DatabaseSetupInfo.Constants.DatabaseName, DatabaseSetupInfo.Constants.AppUsername }
);
}
private static void RunCustomMethod(Action <SqlConnection> method, DatabaseSetupInfo databaseSetupInfo)
{
if (method != null)
{
using (SqlConnection conn = new SqlConnection(databaseSetupInfo.CreateConnectionString(true, true)))
{
conn.Open();
method(conn);
}
}
}
public static ActionResult AfterDatabaseDialog(Session session, bool databaseShouldBeNew)
{
var databaseSetupInfo = DatabaseSetupInfo.CreateFromCurrentDetails(session);
databaseSetupInfo.UseExistingDatabase = false;
if (TestConnectionString(session, databaseSetupInfo, databaseShouldBeNew, true))
{
DatabaseSetupInfo.AddFeatureDetails(session, databaseSetupInfo);
}
return(ActionResult.Success);
}
public static void DropDatabaseUser(DatabaseSetupInfo setupInfo)
{
Action <SqlConnection> dropLoginMethod = (connection) =>
{
using (SqlCommand dropLoginCommand = new SqlCommand("sp_droplogin", connection))
{
dropLoginCommand.CommandType = CommandType.StoredProcedure;
dropLoginCommand.Parameters.Add("@loginame", SqlDbType.VarChar).Value = setupInfo.EffectiveApplicationAuthenticationInfo.UserName;
dropLoginCommand.ExecuteNonQuery();
}
};
using (SqlConnection adminConnection = new SqlConnection(setupInfo.CreateConnectionString(true, false)))
{
adminConnection.Open();
if (!setupInfo.ApplicationAuthenticationSameAsAdmin && !setupInfo.ApplicationAuthenticationInfo.IntegratedSecurity)
{
if (!setupInfo.IsServerRoleMember("sysadmin", setupInfo.ApplicationAuthenticationInfo.UserName, adminConnection))
{
using (SqlCommand selectUsersCommand = new SqlCommand("exec sp_MSloginmappings @User", adminConnection))
{
selectUsersCommand.Parameters.Add("@User", SqlDbType.VarChar).Value = setupInfo.ApplicationAuthenticationInfo.UserName;
selectUsersCommand.CommandTimeout *= 4;
SqlDataAdapter adpt = new SqlDataAdapter(selectUsersCommand);
DataTable usersTable = new DataTable();
adpt.Fill(usersTable);
if (usersTable.Rows.Count == 0)
{
dropLoginMethod(adminConnection);
}
else if (usersTable.Rows.Count == 1 && usersTable.Rows[0]["DBName"].ToString().Equals(setupInfo.DatabaseName, StringComparison.InvariantCultureIgnoreCase))
{
using (var adminConnectionWithDB = new SqlConnection(setupInfo.CreateConnectionString(true, true)))
{
adminConnectionWithDB.Open();
if (!setupInfo.IsDatabaseRoleMember("db_owner", setupInfo.ApplicationAuthenticationInfo.UserName, adminConnectionWithDB))
{
using (var dropUserCommand = new SqlCommand(string.Format("DROP USER [{0}]", setupInfo.ApplicationAuthenticationInfo.UserName), adminConnectionWithDB))
{
dropUserCommand.ExecuteNonQuery();
}
dropLoginMethod(adminConnection);
}
}
}
}
}
}
}
}
public static ActionResult PreDatabaseDialog(Session session)
{
var featureName = session.GetPropertyValue(DatabaseSetupInfo.FeaturePropertyName);
var databaseSetupInfo = DatabaseSetupInfo.CreateFromFeature(session, featureName);
if (databaseSetupInfo != null)
{
databaseSetupInfo.CopyToCurrentDetails(session);
}
else
{
DatabaseSetupInfo.SetSuggestedPropertyValues(session, featureName);
}
return(ActionResult.Success);
}
/// <summary>
/// Creates a new object from a connection string, copying the info to AdminAuthenticationInfo
/// </summary>
/// <param name="connectionString">The connection string to use</param>
/// <returns>The new DatabaseSetupInfo object</returns>
public static DatabaseSetupInfo FromConnectionString(string connectionString)
{
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(connectionString);
DatabaseSetupInfo ret = new DatabaseSetupInfo();
ret.ServerName = builder.DataSource;
ret.DatabaseName = builder.InitialCatalog;
ret.AdminAuthenticationInfo.IntegratedSecurity = builder.IntegratedSecurity;
if (!ret.AdminAuthenticationInfo.IntegratedSecurity)
{
ret.AdminAuthenticationInfo.UserName = builder.UserID;
ret.AdminAuthenticationInfo.Password = builder.Password;
}
return(ret);
}
private static DatabaseSetupInfo[] DeserializeAllFeatures(Session session)
{
var allPropVal = session.GetPropertyValue(AllInfoPropertyName);
if (allPropVal == "-")
{
allPropVal = "";
}
var allInfo = CprBroker.Utilities.Strings.Deserialize <DatabaseSetupInfo[]>(allPropVal);
if (allInfo == null)
{
allInfo = new DatabaseSetupInfo[0];
}
return(allInfo);
}
public static DatabaseSetupInfo CreateFromCurrentDetails(Session session, string featureName, bool tryWithoutFeature)
{
DatabaseSetupInfo ret = new DatabaseSetupInfo();
if (string.IsNullOrEmpty(featureName))
{
ret.FeatureName = session.GetPropertyValue(FeaturePropertyName);
}
else
{
ret.FeatureName = featureName;
session.SetPropertyValue(FeaturePropertyName, ret.FeatureName);
}
ret.ServerName = session.GetPropertyValue(Constants.ServerName, featureName, tryWithoutFeature);
ret.DatabaseName = session.GetPropertyValue(Constants.DatabaseName, featureName, tryWithoutFeature);
ret.UseExistingDatabase = session.GetBooleanPropertyValue(Constants.UseExistingDatabase, featureName, tryWithoutFeature);
ret.EncryptionKey = session.GetPropertyValue(Constants.EncryptionKey, featureName, tryWithoutFeature);
ret.EncryptionKeyEnabled = session.GetBooleanPropertyValue(Constants.EncryptionKeyEnabled, featureName, tryWithoutFeature);
ret.Domain = session.GetPropertyValue(Constants.Domain, featureName, tryWithoutFeature);
ret.DomainEnabled = session.GetBooleanPropertyValue(Constants.DomainEnabled, featureName, tryWithoutFeature);
ret.AdminAuthenticationInfo = new DatabaseSetupInfo.AuthenticationInfo();
ret.AdminAuthenticationInfo.IntegratedSecurity = session.GetBooleanPropertyValue(Constants.AdminIntegratedSecurity, featureName, tryWithoutFeature);
if (!ret.AdminAuthenticationInfo.IntegratedSecurity)
{
ret.AdminAuthenticationInfo.UserName = session.GetPropertyValue(Constants.AdminUsername, featureName, tryWithoutFeature);
ret.AdminAuthenticationInfo.Password = session.GetPropertyValue(Constants.AdminPassword, featureName, tryWithoutFeature);
}
ret.ApplicationIntegratedSecurityAllowed = session.GetBooleanPropertyValue(Constants.AppIntegratedSecurityAllowed, featureName, tryWithoutFeature);
ret.ApplicationAuthenticationSameAsAdmin = session.GetBooleanPropertyValue(Constants.AppSameAsAdmin, featureName, tryWithoutFeature);
if (!ret.ApplicationAuthenticationSameAsAdmin)
{
ret.ApplicationAuthenticationInfo = new DatabaseSetupInfo.AuthenticationInfo();
ret.ApplicationAuthenticationInfo.IntegratedSecurity = session.GetBooleanPropertyValue(Constants.AppIntegratedSecurity, featureName, tryWithoutFeature);
if (!ret.ApplicationAuthenticationInfo.IntegratedSecurity)
{
ret.ApplicationAuthenticationInfo.UserName = session.GetPropertyValue(Constants.AppUsername, featureName, tryWithoutFeature);
ret.ApplicationAuthenticationInfo.Password = session.GetPropertyValue(Constants.AppPassword, featureName, tryWithoutFeature);
}
}
return(ret);
}
public static CustomActionData GetCustomActionData(Session session)
{
var commponProps = BaseSetupInfo.GetCustomActionData(session);
var databaseProps = DatabaseSetupInfo.GetCustomActionData(session);
commponProps.Merge(databaseProps);
var webProps = new CustomActionData();
webProps[WebInstallationInfo.AllInfoPropertyName] = session.GetPropertyValue(WebInstallationInfo.AllInfoPropertyName);
webProps[WebInstallationInfo.FeaturePropertyName] = session.GetPropertyValue(WebInstallationInfo.FeaturePropertyName);
webProps[WebInstallationInfo.AllFeaturesPropertyName] = session.GetPropertyValue(WebInstallationInfo.AllFeaturesPropertyName);
commponProps.Merge(webProps);
return(commponProps);
}
public static void AddFeatureDetails(Session session, DatabaseSetupInfo databaseSetupInfo)
{
var allInfo = DeserializeAllFeatures(session);
var index = Array.FindIndex <DatabaseSetupInfo>(allInfo, inf => inf.FeatureName == databaseSetupInfo.FeatureName);
if (index != -1)
{
allInfo[index] = databaseSetupInfo;
}
else
{
var list = new List <DatabaseSetupInfo>(allInfo);
list.Add(databaseSetupInfo);
allInfo = list.ToArray();
}
SerializeAllFeatures(session, allInfo);
}
public static ActionResult AfterInstallInitialize_DB(Session session)
{
RunDatabaseAction(
session,
(featureName) =>
{
DatabaseSetupInfo databaseSetupInfo = DatabaseSetupInfo.CreateFromFeature(session, featureName);
databaseSetupInfo.CopyToCurrentDetails(session);
DatabaseSetupInfo.AddRegistryEntries(session, featureName);
}
);
var aggregatedProps = DatabaseSetupInfo.GetCustomActionData(session).ToString();
session.SetPropertyValue("RollbackDatabase", aggregatedProps);
session.SetPropertyValue("DeployDatabase", aggregatedProps);
session.SetPropertyValue("RemoveDatabase", aggregatedProps);
session.SetPropertyValue("PatchDatabase", aggregatedProps);
return(ActionResult.Success);
}
private static bool CreateDatabase(DatabaseSetupInfo setupInfo, string createDatabaseObjectsSql, KeyValuePair <string, string>[] lookupDataArray)
{
if (!setupInfo.DatabaseExists())
{
using (SqlConnection adminConnection = new SqlConnection(setupInfo.CreateConnectionString(true, false)))
{
adminConnection.Open();
using (SqlCommand createCommand = new SqlCommand(string.Format("CREATE DATABASE [{0}]", setupInfo.DatabaseName), adminConnection))
{
createCommand.ExecuteNonQuery();
}
}
return(true);
}
else
{
return(false);
}
}
public static void ExecuteDDL(string createDatabaseObjectsSql, DatabaseSetupInfo databaseSetupInfo)
{
string adminConnectionStringWithDb = databaseSetupInfo.CreateConnectionString(true, true);
using (SqlConnection adminConnectionWDb = new SqlConnection(adminConnectionStringWithDb))
{
adminConnectionWDb.Open();
using (SqlCommand createObjectsCommand = new SqlCommand("", adminConnectionWDb))
{
createDatabaseObjectsSql += "\nGO"; // make sure last batch is executed.
string sqlBatch = "";
foreach (string line in createDatabaseObjectsSql.Split(new string[2] {
"\n", "\r"
}, StringSplitOptions.RemoveEmptyEntries))
{
if (line.ToUpperInvariant().Trim() == "GO")
{
if (!string.IsNullOrEmpty(sqlBatch))
{
createObjectsCommand.CommandText = sqlBatch;
try
{
createObjectsCommand.ExecuteNonQuery();
}
catch
{
// TODO: Include parent window ID here
MessageBox.Show(sqlBatch);
throw;
}
}
sqlBatch = string.Empty;
}
else
{
sqlBatch += line + "\n";
}
}
}
}
}
public static bool TestConnectionString(Session session, DatabaseSetupInfo dbInfo, bool databaseShouldBeNew, bool userInterfaceEnabled)
{
string message = "";
dbInfo.UseExistingDatabase = false;
session["DB_Valid"] = "False";
Func <bool> asker = userInterfaceEnabled ?
() => MessageBox.Show(session.InstallerWindowWrapper(), Messages.DatabaseAlreadyExistsDoYouWantToUseExisting, "", MessageBoxButtons.YesNo) == DialogResult.Yes
: null as Func <bool>;
if (dbInfo.Validate(ref message))
{
if (dbInfo.ValidateDatabaseExistence(databaseShouldBeNew, asker, ref message))
{
session["DB_Valid"] = "True";
return(true);
}
else
{
session["DB_Valid"] = message;
if (userInterfaceEnabled && !databaseShouldBeNew)
{
MessageBox.Show(session.InstallerWindowWrapper(), message, "", MessageBoxButtons.OK);
}
return(false);
}
}
else
{
session["DB_Valid"] = message;
if (userInterfaceEnabled)
{
MessageBox.Show(session.InstallerWindowWrapper(), message, "", MessageBoxButtons.OK);
}
return(false);
}
}
public static ActionResult DeployDatabase(Session session, Dictionary <string, string> createDatabaseObjectsSql, Dictionary <string, KeyValuePair <string, string>[]> lookupDataArray, Dictionary <string, Action <SqlConnection> > customMethods = null)
{
customMethods = customMethods != null ? customMethods : new Dictionary <string, Action <SqlConnection> >();
RunDatabaseAction(
session,
(featureName) =>
{
DatabaseSetupInfo databaseSetupInfo = DatabaseSetupInfo.CreateFromFeature(session, featureName);
if (CreateDatabase(databaseSetupInfo, createDatabaseObjectsSql[featureName], lookupDataArray[featureName]))
{
ExecuteDDL(createDatabaseObjectsSql[featureName], databaseSetupInfo);
InsertLookups(lookupDataArray[featureName], databaseSetupInfo);
if (customMethods.ContainsKey(featureName))
{
RunCustomMethod(customMethods[featureName], databaseSetupInfo);
}
}
CreateDatabaseUser(databaseSetupInfo, null);
}
);
return(ActionResult.Success);
}
public static ActionResult RemoveDatabase(Session session, bool askUser)
{
RunDatabaseAction(
session,
(featureName) =>
{
DatabaseSetupInfo setupInfo = DatabaseSetupInfo.CreateFromFeature(session, featureName);
if (!setupInfo.UseExistingDatabase)
{
DropDatabaseForm dropDatabaseForm = new DropDatabaseForm()
{
SetupInfo = setupInfo
};
if (!askUser || BaseForm.ShowAsDialog(dropDatabaseForm, session.InstallerWindowWrapper()) == DialogResult.Yes)
{
DropDatabase(setupInfo);
DropDatabaseUser(setupInfo);
}
}
}
);
return(ActionResult.Success);
}
public void SetDatabaseSetupInfo(DatabaseSetupInfo value)
{
savedState["DatabaseSetupInfo"] = Strings.SerializeObject(value);
}
/// <summary>
/// Creates a new user in the database if needed
/// </summary>
public static void CreateDatabaseUser(DatabaseSetupInfo databaseSetupInfo, string[] neededTables)
{
if (!databaseSetupInfo.ApplicationAuthenticationSameAsAdmin)
{
string userName;
Func <SqlConnection, SqlCommand> createLoginMethod;
if (databaseSetupInfo.EffectiveApplicationAuthenticationInfo.IntegratedSecurity)
{
userName = @"NT AUTHORITY\NETWORK SERVICE";
createLoginMethod = (connection) => new SqlCommand(string.Format("CREATE LOGIN [{0}] FROM WINDOWS", userName), connection);
}
else
{
userName = databaseSetupInfo.EffectiveApplicationAuthenticationInfo.UserName;
createLoginMethod = (connection) => new SqlCommand(
string.Format(
"CREATE LOGIN [{0}] WITH PASSWORD='******', DEFAULT_DATABASE=[{2}], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF",
userName,
databaseSetupInfo.EffectiveApplicationAuthenticationInfo.Password,
databaseSetupInfo.DatabaseName
),
connection
);
}
using (SqlConnection adminConnection = new SqlConnection(databaseSetupInfo.CreateConnectionString(true, false)))
{
adminConnection.Open();
using (SqlConnection adminConnectionWithDb = new SqlConnection(databaseSetupInfo.CreateConnectionString(true, true)))
{
adminConnectionWithDb.Open();
SqlCommand selectUserCommand = new SqlCommand("SELECT COUNT(*) FROM sys.database_principals AS db INNER JOIN sys.server_principals AS S ON db.sid = s.sid WHERE db.name=@UserName OR S.name=@UserName", adminConnectionWithDb);
selectUserCommand.Parameters.Add("@UserName", System.Data.SqlDbType.VarChar).Value = userName;
bool userExists = (int)selectUserCommand.ExecuteScalar() > 0;
if (!userExists)
{
using (SqlCommand loginExistsCommand = new SqlCommand("SELECT COUNT(*) FROM sys.server_principals WHERE NAME=@UserName", adminConnection))
{
loginExistsCommand.Parameters.Add("@UserName", System.Data.SqlDbType.VarChar).Value = userName;
var loginExists = (int)loginExistsCommand.ExecuteScalar() > 0;
if (!loginExists)
{
SqlCommand createLoginCommand = createLoginMethod(adminConnection);
createLoginCommand.ExecuteNonQuery();
}
SqlCommand createUserCommand = new SqlCommand(string.Format("CREATE USER [{0}] FOR LOGIN [{0}]", userName), adminConnectionWithDb);
createUserCommand.ExecuteNonQuery();
}
}
using (SqlCommand permissionsCommand = new SqlCommand("", adminConnectionWithDb))
{
if (neededTables == null || neededTables.Length == 0)
{
if (!databaseSetupInfo.IsDatabaseRoleMember("db_owner", userName, adminConnectionWithDb))
{
permissionsCommand.CommandText = "sp_addrolemember";
permissionsCommand.CommandType = System.Data.CommandType.StoredProcedure;
permissionsCommand.Parameters.Add("@rolename", System.Data.SqlDbType.VarChar).Value = "db_owner";
permissionsCommand.Parameters.Add("@membername", System.Data.SqlDbType.VarChar).Value = userName;
permissionsCommand.ExecuteNonQuery();
}
}
else
{
permissionsCommand.CommandType = System.Data.CommandType.Text;
foreach (string tableName in neededTables)
{
permissionsCommand.CommandText = string.Format("GRANT DELETE,INSERT,REFERENCES,SELECT,UPDATE,VIEW DEFINITION ON [dbo].[{0}] TO [{1}]", tableName, userName);
permissionsCommand.ExecuteNonQuery();
}
}
}
}
}
}
}
public DatabaseForm()
{
InitializeComponent();
SetupInfo = new DatabaseSetupInfo();
}
public static void InsertLookups(KeyValuePair <string, string>[] lookupDataArray, DatabaseSetupInfo databaseSetupInfo)
{
foreach (var lookupData in lookupDataArray)
{
using (SqlConnection conn = new SqlConnection(databaseSetupInfo.CreateConnectionString(true, true)))
{
conn.Open();
InsertLookup(lookupData.Key, lookupData.Value, conn);
}
}
}
