private void ExecuteUpgradeScripts(EPMData epmData, Guid siteId)
{
using (SqlConnection sqlConnection = epmData.GetSpecificReportingDbConnection(siteId))
{
sqlConnection.FireInfoMessageEventOnUserErrors = true;
sqlConnection.InfoMessage += SqlConnectionInfoMessage;
FileVersionInfo fileVersionInfo =
FileVersionInfo.GetVersionInfo(Assembly.GetExecutingAssembly().Location);
foreach (
string command in new[] { EPMLiveCore.Properties.Resources.ReportingInitDatabaseCreateTables, EPMLiveCore.Properties.Resources.ReportingInitDatabaseCreateProcedures }
.Select(s => s.Replace("@version", "''" + fileVersionInfo.FileVersion + "''")))
{
try
{
using (var sqlCommand = new SqlCommand(command, sqlConnection))
{
sqlCommand.CommandType = CommandType.Text;
sqlCommand.ExecuteNonQuery();
}
}
catch (SqlException exception)
{
foreach (SqlError sqlError in exception.Errors)
{
LogError(string.Format("Msg {0}, Procedure {1}, Line {2}", sqlError.Number,
sqlError.Procedure, sqlError.LineNumber));
LogError(exception.Message);
}
}
}
object username = null;
try
{
using (
var sqlCommand =
new SqlCommand("SELECT ClientUsername FROM dbo.RPTDATABASES WHERE (SiteId = @SiteId)",
epmData.GetEPMLiveConnection))
{
sqlCommand.Parameters.AddWithValue("@SiteId", siteId);
sqlCommand.CommandType = CommandType.Text;
username = sqlCommand.ExecuteScalar();
}
}
catch (SqlException exception)
{
//foreach (SqlError sqlError in exception.Errors)
//{
// LogError(string.Format("Msg {0}, Procedure {1}, Line {2}", sqlError.Number,
// sqlError.Procedure, sqlError.LineNumber));
// LogError(exception.Message);
//}
}
if (username != null && username != DBNull.Value)
{
const string query =
@"EXEC sp_executesql N'IF (ISNULL(OBJECT_ID(''@SP''),'''')) <> '''' BEGIN PRINT ''Granting EXECUTE permission to @Username on @SP'' GRANT EXECUTE ON @SP TO @Username END'";
foreach (string sp in new[] { "spRPTWork", "spRPTGetCapacity", "spRPTGetTotalCapacity" })
{
try
{
username = username.ToString().Replace("'", "''");
using (
var sqlCommand =
new SqlCommand(query.Replace("@SP", sp).Replace("@Username", username.ToString()),
sqlConnection))
{
sqlCommand.CommandType = CommandType.Text;
sqlCommand.ExecuteNonQuery();
}
}
catch (SqlException exception)
{
foreach (SqlError sqlError in exception.Errors)
{
LogError(string.Format("Msg {0}, Procedure {1}, Line {2}", sqlError.Number,
sqlError.Procedure, sqlError.LineNumber));
LogError(exception.Message);
}
}
}
}
}
}
