private string ExecuteScriptInternal(string connectionString, string script, int timeoutSec = 0)
{
string exceptions = string.Empty;
var sqlDelimiterRegex = RegexUtils.GetCachedRegex(ScriptDelimiter, RegexOptions.IgnoreCase | RegexOptions.Multiline);
string[] sqlStatements = sqlDelimiterRegex.Split(script);
foreach (string statement in sqlStatements)
{
var sql = statement.Trim();
if (!string.IsNullOrEmpty(sql))
{
// script dynamic substitution
sql = DataUtil.ReplaceTokens(sql);
try
{
Logger.Trace("Executing SQL Script " + sql);
_dbConnectionProvider.ExecuteNonQuery(connectionString, CommandType.Text, timeoutSec, sql);
}
catch (SqlException objException)
{
Logger.Error(objException);
exceptions += objException + Environment.NewLine + Environment.NewLine + sql + Environment.NewLine + Environment.NewLine;
}
}
}
return(exceptions);
}
private IDataReader ExecuteSQLInternal(string connectionString, string sql, int timeoutSec, out string errorMessage)
{
try
{
sql = DataUtil.ReplaceTokens(sql);
errorMessage = string.Empty;
if (string.IsNullOrEmpty(connectionString))
{
throw new ArgumentNullException(nameof(connectionString));
}
return(_dbConnectionProvider.ExecuteSql(connectionString, CommandType.Text, timeoutSec, sql));
}
catch (SqlException sqlException)
{
// error in SQL query
Logger.Error(sqlException);
errorMessage = sqlException.Message;
}
catch (Exception ex)
{
Logger.Error(ex);
errorMessage = ex.ToString();
}
errorMessage += Environment.NewLine + Environment.NewLine + sql + Environment.NewLine + Environment.NewLine;
return(null);
}
private string ExecuteScriptInternal(string connectionString, string script, int timeout = 0)
{
string exceptions = "";
var sqlDelimiterRegex = RegexUtils.GetCachedRegex(ScriptDelimiter, RegexOptions.IgnoreCase | RegexOptions.Multiline);
string[] sqlStatements = sqlDelimiterRegex.Split(script);
foreach (string statement in sqlStatements)
{
var sql = statement.Trim();
if (!String.IsNullOrEmpty(sql))
{
// script dynamic substitution
sql = DataUtil.ReplaceTokens(sql);
//Clean up some SQL Azure incompatabilities
var query = GetAzureCompactScript(sql);
if (query != sql)
{
var props = new LogProperties {
new LogDetailInfo("SQL Script Modified", query)
};
EventLogController.Instance.AddLog(props,
PortalController.Instance.GetCurrentPortalSettings(),
UserController.Instance.GetCurrentUserInfo().UserID,
EventLogController.EventLogType.HOST_ALERT.ToString(),
true);
}
try
{
Logger.Trace("Executing SQL Script " + query);
//Create a new connection
using (var connection = new SqlConnection(connectionString))
{
//Create a new command
using (var command = new SqlCommand(query, connection)
{
CommandTimeout = timeout
})
{
connection.Open();
command.ExecuteNonQuery();
connection.Close();
}
}
}
catch (SqlException objException)
{
Logger.Error(objException);
exceptions += objException + Environment.NewLine + Environment.NewLine + query + Environment.NewLine + Environment.NewLine;
}
}
}
return(exceptions);
}
private IDataReader ExecuteSQLInternal(string connectionString, string sql)
{
try
{
sql = DataUtil.ReplaceTokens(sql);
return(PetaPocoHelper.ExecuteReader(connectionString, CommandType.Text, sql));
}
catch
{
//error in SQL query
return(null);
}
}
private IDataReader ExecuteSQLInternal(string connectionString, string sql, int timeout, out string errorMessage)
{
try
{
sql = DataUtil.ReplaceTokens(sql);
errorMessage = "";
if (string.IsNullOrEmpty(connectionString))
{
throw new ArgumentNullException(nameof(connectionString));
}
if (timeout > 0)
{
var builder = GetConnectionStringBuilder();
builder.ConnectionString = connectionString;
builder["Connect Timeout"] = null;
builder["Connection Timeout"] = timeout;
connectionString = builder.ConnectionString;
}
SqlConnection connection = null;
try
{
connection = new SqlConnection(connectionString);
connection.Open();
return(SqlHelper.ExecuteReader(connection, CommandType.Text, sql));
}
catch (Exception)
{
connection?.Dispose();
throw;
}
}
catch (SqlException sqlException)
{
//error in SQL query
Logger.Error(sqlException);
errorMessage = sqlException.Message;
}
catch (Exception ex)
{
Logger.Error(ex);
errorMessage = ex.ToString();
}
errorMessage += Environment.NewLine + Environment.NewLine + sql + Environment.NewLine + Environment.NewLine;
return(null);
}
private IDataReader ExecuteSQLInternal(string connectionString, string sql, out string errorMessage)
{
try
{
sql = DataUtil.ReplaceTokens(sql);
errorMessage = "";
return(SqlHelper.ExecuteReader(connectionString, CommandType.Text, sql));
}
catch (SqlException sqlException)
{
//error in SQL query
Logger.Error(sqlException);
errorMessage = sqlException.Message + Environment.NewLine + Environment.NewLine + sql + Environment.NewLine + Environment.NewLine;
return(null);
}
catch (Exception ex)
{
Logger.Error(ex);
errorMessage = ex + Environment.NewLine + Environment.NewLine + sql + Environment.NewLine + Environment.NewLine;
return(null);
}
}
