|
//------------------------------------------------------------------------------
// <copyright file="Configuration.cs" company="Microsoft">
// Copyright (c) Microsoft Corporation. All rights reserved.
// </copyright>
//------------------------------------------------------------------------------
namespace System.Web.Management {
using System;
using System.Web.Util;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Threading;
using System.Text.RegularExpressions;
using System.Text;
using System.Security;
using System.Security.Permissions;
using System.Globalization;
using System.Runtime.Serialization;
using System.Collections;
[Flags]
public enum SqlFeatures : int {
None = 0x00000000,
Membership = 0x00000001,
Profile = 0x00000002,
RoleManager = 0x00000004,
Personalization = 0x00000008,
SqlWebEventProvider= 0x00000010,
// We need to add 0x40000000 to All in order to differentiate between All, and
// an combination of all other flags. Because if the user pass in All, we have
// to remove the common tables too.
All = Membership|Profile|RoleManager|
Personalization|SqlWebEventProvider|(int)0x40000000,
};
public enum SessionStateType {
Temporary,
Persisted,
Custom,
}
[Serializable()]
public sealed class SqlExecutionException : SystemException {
string _server;
string _database;
string _sqlFile;
string _commands;
SqlException _sqlException;
public SqlExecutionException(string message, string server, string database,
string sqlFile, string commands, SqlException sqlException)
: base(message) {
_server = server;
_database = database;
_sqlFile = sqlFile;
_commands = commands;
_sqlException = sqlException;
}
public SqlExecutionException(String message)
: base(message) {
}
public SqlExecutionException(string message, Exception innerException)
: base (message, innerException) {
}
public SqlExecutionException() {
}
private SqlExecutionException(SerializationInfo info, StreamingContext context)
:base(info, context) {
_server = info.GetString("_server");
_database = info.GetString("_database");
_sqlFile = info.GetString("_sqlFile");
_commands = info.GetString("_commands");
_sqlException = (SqlException)info.GetValue("_sqlException", typeof(SqlException));
}
[SecurityPermissionAttribute(SecurityAction.Demand, SerializationFormatter=true)]
public override void GetObjectData(SerializationInfo info, StreamingContext context)
{
base.GetObjectData(info, context);
info.AddValue("_server", _server);
info.AddValue("_database", _database);
info.AddValue("_sqlFile", _sqlFile);
info.AddValue("_commands", _commands);
info.AddValue("_sqlException", _sqlException);
}
// Name of the server we're connecting to
public string Server {
get { return _server; }
}
// Name of database we use
public string Database {
get { return _database; }
}
// Name of the SQL file we load
public string SqlFile {
get { return _sqlFile; }
}
// The set of batched commands that failed
public string Commands {
get { return _commands; }
}
// The SQL exeception from the failure
public SqlException Exception {
get { return _sqlException; }
}
}
[AspNetHostingPermission(SecurityAction.LinkDemand, Level=AspNetHostingPermissionLevel.High)]
public static class SqlServices {
public static void Install(string server, string user, string password, string database, SqlFeatures features) {
SetupApplicationServices(server, user, password, false, null, database, null, features, true);
}
// For trusted connection
public static void Install(string server, string database, SqlFeatures features) {
SetupApplicationServices(server, null, null, true, null, database, null, features, true);
}
internal static void Install( string database, string dbFileName, string connectionString ) {
SetupApplicationServices( null, null, null, false, connectionString, database, dbFileName, SqlFeatures.All, true );
}
// For connection using connection string
public static void Install(string database, SqlFeatures features, string connectionString) {
SetupApplicationServices(null, null, null, true, connectionString, database, null, features, true);
}
public static void Uninstall(string server, string user, string password, string database, SqlFeatures features) {
SetupApplicationServices(server, user, password, false, null, database, null, features, false);
}
// For trusted connection
public static void Uninstall(string server, string database, SqlFeatures features) {
SetupApplicationServices(server, null, null, true, null, database, null, features, false);
}
// For connection using connection string
public static void Uninstall(string database, SqlFeatures features, string connectionString) {
SetupApplicationServices(null, null, null, true, connectionString, database, null, features, false);
}
public static void InstallSessionState(string server, string user, string password, string customDatabase, SessionStateType type) {
SetupSessionState(server, user, password, false, null, customDatabase, type, true);
}
// For trusted connection
public static void InstallSessionState(string server, string customDatabase, SessionStateType type) {
SetupSessionState(server, null, null, true, null, customDatabase, type, true);
}
// For connection using connection string
public static void InstallSessionState(string customDatabase, SessionStateType type, string connectionString) {
SetupSessionState(null, null, null, true, connectionString, customDatabase, type, true);
}
public static void UninstallSessionState(string server, string user, string password, string customDatabase, SessionStateType type) {
SetupSessionState(server, user, password, false, null, customDatabase, type, false);
}
// For trusted connection
public static void UninstallSessionState(string server, string customDatabase, SessionStateType type) {
SetupSessionState(server, null, null, true, null, customDatabase, type, false);
}
// For connection using connection string
public static void UninstallSessionState(string customDatabase, SessionStateType type, string connectionString) {
SetupSessionState(null, null, null, true, connectionString, customDatabase, type, false);
}
// Used by suite
internal static ArrayList ApplicationServiceTables {
get {
ArrayList tables = new ArrayList();
for (int i=0; i < s_featureInfos.Length; i++) {
tables.InsertRange(tables.Count, s_featureInfos[i]._tablesRemovedInUninstall);
}
return tables;
}
}
public static string GenerateSessionStateScripts(bool install, SessionStateType type, string customDatabase) {
SessionStateParamCheck(type, ref customDatabase);
string fullpath = Path.Combine(HttpRuntime.AspInstallDirectory, install ? SESSION_STATE_INSTALL_FILE : SESSION_STATE_UNINSTALL_FILE);
string content = File.ReadAllText(fullpath);
return FixContent(content, customDatabase, null, true, type);
}
// Information about a feature.
internal struct FeatureInfo {
internal SqlFeatures _feature;
internal string[] _installFiles; // SQL file to run to install the feature
internal string[] _uninstallFiles; // SQL file to run to uninstall the feature
internal string[] _tablesRemovedInUninstall; // SQL tables to be removed during uninstall
internal int _dataCheckBitMask;
internal FeatureInfo(SqlFeatures feature, string[] installFiles,
string[] uninstallFiles, string[] tablesRemovedInUninstall, int dataCheckBitMask) {
_feature = feature;
_installFiles = installFiles;
_uninstallFiles = uninstallFiles;
_tablesRemovedInUninstall = tablesRemovedInUninstall;
_dataCheckBitMask = dataCheckBitMask;
}
};
static string INSTALL_COMMON_SQL = "InstallCommon.sql";
static FeatureInfo[] s_featureInfos = {
new FeatureInfo(SqlFeatures.Membership,
new string[] {INSTALL_COMMON_SQL, "InstallMembership.sql"},
new string[] {"UninstallMembership.sql"},
new string[] {"aspnet_Membership"},
1),
new FeatureInfo(SqlFeatures.Profile,
new string[] {INSTALL_COMMON_SQL, "InstallProfile.sql"},
new string[] {"UninstallProfile.sql"},
new string[] {"aspnet_Profile"},
4),
new FeatureInfo(SqlFeatures.RoleManager,
new string[] {INSTALL_COMMON_SQL, "InstallRoles.sql"},
new string[] {"UninstallRoles.sql"},
new string[] {"aspnet_Roles", "aspnet_UsersInRoles"},
2),
new FeatureInfo(SqlFeatures.Personalization,
new string[] {INSTALL_COMMON_SQL, "InstallPersonalization.sql"},
new string[] {"UninstallPersonalization.sql"},
new string[] {"aspnet_PersonalizationPerUser", "aspnet_Paths", "aspnet_PersonalizationAllUsers"},
8),
new FeatureInfo(SqlFeatures.SqlWebEventProvider,
new string[] {INSTALL_COMMON_SQL, "InstallWebEventSqlProvider.sql"},
new string[] {"UninstallWebEventSqlProvider.sql"},
new string[] {"aspnet_WebEvent_Events"},
16),
// The following are the files to run in addition to those listed in other feature
new FeatureInfo(SqlFeatures.All,
new string[] {},
new string[] {"UninstallCommon.sql"},
new string[] {"aspnet_Applications", "aspnet_Users", "aspnet_SchemaVersions"},
0x7FFFFFFF),
};
// Default database to use if the database name isn't supplied by the user
static string DEFAULT_DB = "aspnetdb";
static string ASPSTATE_DB = "ASPState";
static string SSTYPE_PERSISTED = "sstype_persisted";
static string SSTYPE_CUSTOM = "sstype_custom";
static string SESSION_STATE_INSTALL_FILE = "InstallSqlState.sql";
static string SESSION_STATE_UNINSTALL_FILE = "UninstallSqlState.sql";
// Return a list of files based on install (bool) and features
static ArrayList GetFiles(bool install, SqlFeatures features) {
ArrayList results = new ArrayList();
bool installCommonProcessed = false;
// Load and modify the sql file for each feature
for (int i=0; i < s_featureInfos.Length; i++) {
string[] sqlFiles = null;
if (((int)s_featureInfos[i]._feature & (int)features) == (int)s_featureInfos[i]._feature) {
// We found one feature
if (install) {
sqlFiles = s_featureInfos[i]._installFiles;
}
else {
sqlFiles = s_featureInfos[i]._uninstallFiles;
}
}
if (sqlFiles != null) {
for(int j = 0; j < sqlFiles.Length; j++) {
string sqlFile = sqlFiles[j];
if (sqlFile != null) {
// We only need to process InstallCommon.sql once
if (sqlFile == INSTALL_COMMON_SQL && installCommonProcessed) {
continue;
}
results.Add(sqlFile);
if (!installCommonProcessed && sqlFile == INSTALL_COMMON_SQL) {
installCommonProcessed = true;
}
}
}
}
}
return results;
}
// Replace the name of the database with the one specified by the caller
static string FixContent(
string content,
string database,
string dbFileName,
bool sessionState,
SessionStateType sessionStatetype
)
{
if (database != null) {
database = RemoveSquareBrackets(database);
}
if (sessionState) {
if (sessionStatetype == SessionStateType.Temporary) {
// No change
}
else if (sessionStatetype == SessionStateType.Persisted) {
content = content.Replace("'sstype_temp'", "'" + SSTYPE_PERSISTED + "'");
content = content.Replace("[tempdb]", "[" + ASPSTATE_DB + "]");
}
else if (sessionStatetype == SessionStateType.Custom) {
content = content.Replace("'sstype_temp'", "'" + SSTYPE_CUSTOM + "'");
content = content.Replace("[tempdb]", "[" + database + "]");
content = content.Replace("'ASPState'", "'" + database + "'");
content = content.Replace("[ASPState]", "[" + database + "]");
}
}
else {
content = content.Replace("'aspnetdb'", "'" + database.Replace("'", "''") + "'");
content = content.Replace("[aspnetdb]", "[" + database + "]");
}
if( dbFileName != null )
{
if (dbFileName.Contains("[") || dbFileName.Contains("]") || dbFileName.Contains("'"))
throw new ArgumentException(SR.GetString(SR.DbFileName_can_not_contain_invalid_chars));
database = database.TrimStart( '[' );
database = database.TrimEnd( ']' );
string logicalFileName = database + "_DAT";
if (!char.IsLetter(logicalFileName[0]))
logicalFileName = "A" + logicalFileName;
//
// Build the database options string for SQL Express database
//
string dbOptions = "ON ( NAME = " + logicalFileName + ", FILENAME = ''" +
dbFileName + "'', " + "SIZE = 10MB, FILEGROWTH = 5MB )";
content = content.Replace("SET @dboptions = N'/**/'",
"SET @dboptions = N'" + dbOptions + "'");
}
return content;
}
static void ExecuteSessionFile(
string file,
string server,
string database,
string dbFileName,
SqlConnection connection,
bool isInstall,
SessionStateType sessionStatetype
) {
ExecuteFile(file, server, database, dbFileName, connection, true, isInstall, sessionStatetype);
}
// Load the SQL file, change the database name within, and execute it.
static void ExecuteFile(
string file,
string server,
string database,
string dbFileName,
SqlConnection connection,
bool sessionState,
bool isInstall,
SessionStateType sessionStatetype
)
{
string fullpath = Path.Combine(HttpRuntime.AspInstallDirectory, file);
string content = File.ReadAllText(fullpath);
StringReader sr;
string cmdText = null;
string cur;
SqlCommand sqlCmd;
Debug.Trace("SqlServices", "Execute File: about to run " + fullpath);
// We need to replace the name of the database with the one specified by the caller
if( file.Equals( INSTALL_COMMON_SQL ) )
{
content = FixContent(content, database, dbFileName, sessionState, sessionStatetype);
}
else
{
content = FixContent(content, database, null, sessionState, sessionStatetype);
}
sr = new StringReader(content);
sqlCmd = new SqlCommand(null, connection);
do {
bool run = false;
// Read a line from a file. If it's not a "GO", batch it up.
// It it's a "GO" (or the last line), send over all batched
// commands over to SQL.
cur = sr.ReadLine();
if (cur == null) {
run = true;
}
else {
if (StringUtil.EqualsIgnoreCase(cur.Trim(), "GO"))
{
run = true;
}
else {
if (cmdText != null) {
cmdText += "\n";
}
cmdText += cur;
}
}
if (run & cmdText != null) {
sqlCmd.CommandText = cmdText;
try {
sqlCmd.ExecuteNonQuery();
}
catch (Exception e) {
SqlException sqlExpt = e as SqlException;
if (sqlExpt != null) {
Debug.Trace("SqlServices", "Error executing command. SqlException:" +
"\nMessage=" + sqlExpt.Message +
"\nNumber=" + sqlExpt.Number);
int expectedError = -1;
// There are some errors we might expect.
// See VSWhidbey 376433
if (cmdText.IndexOf("sp_add_category", StringComparison.Ordinal) > -1) {
expectedError = 14261; /* already exists */
}
else if (cmdText.IndexOf("sp_delete_job", StringComparison.Ordinal) > -1) {
expectedError = 14262; /* doesn't exists */
if (sessionState && !isInstall) {
throw new SqlExecutionException(SR.GetString(SR.SQL_Services_Error_Deleting_Session_Job),
server, database, file, cmdText, sqlExpt);
}
}
if (sqlExpt.Number == expectedError) {
Debug.Trace("SqlServices", "Got expected error: " + expectedError + "; not throwing");
}
else {
throw new SqlExecutionException(
SR.GetString(SR.SQL_Services_Error_Executing_Command,
file, sqlExpt.Number.ToString(CultureInfo.CurrentCulture), sqlExpt.Message),
server, database, file, cmdText, sqlExpt);
}
}
}
#pragma warning disable 1058
catch {
throw;
}
#pragma warning restore 1058
cmdText = null;
}
} while (cur != null);
}
static void ApplicationServicesParamCheck(SqlFeatures features, ref string database) {
if (features == SqlFeatures.None) {
return;
}
if ((features & SqlFeatures.All) != features) {
throw new ArgumentException(SR.GetString(SR.SQL_Services_Invalid_Feature));
}
// VSWhidbey 355946:
// SQL will ignore trailing space of database names. It's easier to just
// trim those spaces here than to fix all the SQL statements to handle
// errors due to trailing space.
CheckDatabaseName(ref database);
}
private static void CheckDatabaseName(ref string database)
{
if (database != null) {
database = database.TrimEnd();
if (database.Length == 0)
throw new ArgumentException(SR.GetString(SR.SQL_Services_Database_Empty_Or_Space_Only_Arg));
database = RemoveSquareBrackets(database);
if (database.Contains("'") || database.Contains("[") || database.Contains("]"))
throw new ArgumentException(SR.GetString(SR.SQL_Services_Database_contains_invalid_chars));
}
if (database == null) {
database = DEFAULT_DB;
}
else {
// Wrap it with [] if not already
//if (!(StringUtil.StringStartsWith(database, '[') && StringUtil.StringEndsWith(database, ']'))) {
database = "[" + database + "]";
//}
}
}
public static string GenerateApplicationServicesScripts(bool install, SqlFeatures features, string database) {
string content;
StringBuilder sb = new StringBuilder();
ArrayList files;
ApplicationServicesParamCheck(features, ref database);
files = GetFiles(install, features);
foreach (string sqlFile in files) {
string fullpath = Path.Combine(HttpRuntime.AspInstallDirectory, sqlFile);
content = File.ReadAllText(fullpath);
sb.Append(FixContent(content, database, null, false, SessionStateType.Temporary));
}
return sb.ToString();
}
static string RemoveSquareBrackets(string database) {
if (database != null && StringUtil.StringStartsWith(database, '[') && StringUtil.StringEndsWith(database, ']'))
return database.Substring(1, database.Length-2);
return database;
}
static void EnsureDatabaseExists(string database, SqlConnection sqlConnection) {
SqlCommand cmd;
string databaseNoSquareBrackets = RemoveSquareBrackets(database);
// First, make sure the database exists
cmd = new SqlCommand("SELECT DB_ID(@database)", sqlConnection);
cmd.Parameters.Add(new SqlParameter("@database", databaseNoSquareBrackets));
object res = cmd.ExecuteScalar();
if (res == null || res == System.DBNull.Value) {
// The database doesn't even exist.
throw new HttpException(
SR.GetString(SR.SQL_Services_Error_Cant_Uninstall_Nonexisting_Database,
databaseNoSquareBrackets));
}
}
// Add/Remove all requested general features
static void SetupApplicationServices(
string server,
string user,
string password,
bool trusted,
string connectionString,
string database,
string dbFileName,
SqlFeatures features,
bool install )
{
SqlConnection sqlConnection = null;
ArrayList files;
Debug.Trace("SqlServices",
"SetupApplicationServices called: server=" + server + ", database=" +
database + ", user=" + user + ", password=" + password +
", trusted=" + trusted + ", connectionString=" + connectionString +
", features=" + features + ", install=" + install);
ApplicationServicesParamCheck(features, ref database);
files = GetFiles(install, features);
try {
sqlConnection = GetSqlConnection(server, user, password, trusted, connectionString);
// If uninstall, make sure all the asp.net tables are empty
if (!install) {
EnsureDatabaseExists(database, sqlConnection);
string databaseNoSquareBrackets = RemoveSquareBrackets(database);
if (sqlConnection.Database != databaseNoSquareBrackets)
sqlConnection.ChangeDatabase(databaseNoSquareBrackets);
int itablesToCheck = 0;
for (int i=0; i < s_featureInfos.Length; i++)
if (((int)s_featureInfos[i]._feature & (int)features) == (int)s_featureInfos[i]._feature)
itablesToCheck |= s_featureInfos[i]._dataCheckBitMask;
SqlCommand cmd = new SqlCommand("dbo.aspnet_AnyDataInTables", sqlConnection);
cmd.Parameters.Add(new SqlParameter("@TablesToCheck", itablesToCheck));
cmd.CommandType = CommandType.StoredProcedure;
string table = null;
try {
table = cmd.ExecuteScalar() as string;
} catch (SqlException e) {
if (e.Number != 2812)
throw;
}
if (!string.IsNullOrEmpty(table))
throw new NotSupportedException(
SR.GetString(SR.SQL_Services_Error_Cant_Uninstall_Nonempty_Table,
table, database));
}
// Load and run the sql file for each feature
foreach (string sqlFile in files) {
ExecuteFile(sqlFile, server, database, dbFileName, sqlConnection, false, false, SessionStateType.Temporary);
}
}
finally {
if (sqlConnection != null) {
try {
sqlConnection.Close();
}
catch {
}
finally {
sqlConnection = null;
}
}
}
}
static void SessionStateParamCheck(SessionStateType type, ref string customDatabase) {
if (type == SessionStateType.Custom && String.IsNullOrEmpty(customDatabase)) {
throw new ArgumentException(
SR.GetString(SR.SQL_Services_Error_missing_custom_database), "customDatabase");
}
if (type != SessionStateType.Custom && customDatabase != null) {
throw new ArgumentException(
SR.GetString(SR.SQL_Services_Error_Cant_use_custom_database), "customDatabase");
}
CheckDatabaseName(ref customDatabase);
}
static void SetupSessionState(string server, string user, string password, bool trusted,
string connectionString, string customDatabase, SessionStateType type, bool install) {
SqlConnection sqlConnection = null;
Debug.Trace("SqlServices",
"SetupSessionState called: server=" + server + ", customDatabase=" +
customDatabase + ", user=" + user + ", password=" + password +
", trusted=" + trusted + ", connectionString=" + connectionString +
", type=" + type + ", install=" + install);
SessionStateParamCheck(type, ref customDatabase);
try {
sqlConnection = GetSqlConnection(server, user, password, trusted, connectionString);
if (!install && type == SessionStateType.Custom) {
EnsureDatabaseExists(customDatabase, sqlConnection);
}
// Load and run the sql file for each feature
ExecuteSessionFile(install ? SESSION_STATE_INSTALL_FILE : SESSION_STATE_UNINSTALL_FILE,
server, customDatabase, null, sqlConnection, install, type);
}
finally {
if (sqlConnection != null) {
try {
sqlConnection.Close();
}
catch {
}
finally {
sqlConnection = null;
}
}
}
}
static string ConstructConnectionString(string server, string user, string password, bool trusted) {
string connectionString = null;
// Construct the connection string
if (String.IsNullOrEmpty(server)) {
throw ExceptionUtil.ParameterNullOrEmpty("server");
}
connectionString += "server=" + server;
if (trusted) {
connectionString += ";Trusted_Connection=true;";
}
else {
if (String.IsNullOrEmpty(user)) {
throw ExceptionUtil.ParameterNullOrEmpty("user");
}
connectionString += ";UID=" + user + ";" + "PWD=" + password + ";";
}
return connectionString;
}
static SqlConnection GetSqlConnection(string server, string user, string password,
bool trusted, string connectionString) {
SqlConnection sqlConnection;
if (connectionString == null) {
connectionString = ConstructConnectionString(server, user, password, trusted);
}
try {
Debug.Trace("SqlServices", "Connecting to SQL: " + connectionString);
sqlConnection = new SqlConnection(connectionString);
sqlConnection.Open();
}
catch (Exception e) {
sqlConnection = null;
throw new HttpException(
SR.GetString(SR.SQL_Services_Cant_connect_sql_database),
e);
}
return sqlConnection;
}
}
}
|