public static string ReIndexDatabase(this IDbAccess dbAccess)
{
CodeContracts.VerifyNotNull(dbAccess, "dbAccess");
var sb = new StringBuilder();
sb.AppendLine("DECLARE @MyTable VARCHAR(255)");
sb.AppendLine("DECLARE myCursor");
sb.AppendLine("CURSOR FOR");
sb.AppendFormat(
"SELECT table_name FROM information_schema.tables WHERE table_type = 'base table' AND table_name LIKE '{0}%'",
Config.DatabaseObjectQualifier);
sb.AppendLine("OPEN myCursor");
sb.AppendLine("FETCH NEXT");
sb.AppendLine("FROM myCursor INTO @MyTable");
sb.AppendLine("WHILE @@FETCH_STATUS = 0");
sb.AppendLine("BEGIN");
sb.AppendLine("PRINT 'Reindexing Table: ' + @MyTable");
sb.AppendLine("DBCC DBREINDEX(@MyTable, '', 80)");
sb.AppendLine("FETCH NEXT");
sb.AppendLine("FROM myCursor INTO @MyTable");
sb.AppendLine("END");
sb.AppendLine("CLOSE myCursor");
sb.AppendLine("DEALLOCATE myCursor");
using (var cmd = dbAccess.GetCommand(sb.ToString(), CommandType.Text))
{
return(dbAccess.ExecuteScalar(cmd).ToType <string>());
}
}
/// <summary>
/// Gets the current SQL Engine Edition.
/// </summary>
/// <param name="dbAccess">The database access.</param>
/// <returns>
/// Returns the current SQL Engine Edition.
/// </returns>
public static string GetSQLEngine(this IDbAccess dbAccess)
{
CodeContracts.VerifyNotNull(dbAccess, "dbAccess");
try
{
using (var cmd = dbAccess.GetCommand("select SERVERPROPERTY('EngineEdition')", CommandType.Text))
{
switch (dbAccess.ExecuteScalar(cmd).ToType <int>())
{
case 1:
return("Personal");
case 2:
return("Standard");
case 3:
return("Enterprise");
case 4:
return("Express");
case 5:
return("Azure");
default:
return("Unknown");
}
}
}
catch
{
return("Unknown");
}
}
/// <summary>
/// The execute scalar.
/// </summary>
/// <param name="dbAccess">
/// The db access.
/// </param>
/// <param name="cmd">
/// The cmd.
/// </param>
/// <returns>
/// The execute scalar.
/// </returns>
public static object ExecuteScalar([NotNull] this IDbAccess dbAccess, [NotNull] IDbCommand cmd)
{
CodeContracts.ArgumentNotNull(dbAccess, "dbAccess");
CodeContracts.ArgumentNotNull(cmd, "cmd");
return(dbAccess.ExecuteScalar(cmd, false));
}
/// <summary>
/// Gets the database size
/// </summary>
/// <param name="dbAccess">The database access.</param>
/// <returns>
/// integer value for database size
/// </returns>
public static int DBSize(this IDbAccess dbAccess)
{
CodeContracts.VerifyNotNull(dbAccess, "dbAccess");
using (var cmd = dbAccess.GetCommand("SELECT sum(reserved_page_count) * 8.0 / 1024 FROM sys.dm_db_partition_stats", CommandType.Text))
{
return(dbAccess.ExecuteScalar(cmd).ToType <int>());
}
}
/// <summary>
/// Gets the database size
/// </summary>
/// <returns>intager value for database size</returns>
public static int DBSize(this IDbAccess dbAccess)
{
CodeContracts.VerifyNotNull(dbAccess, "dbAccess");
using (var cmd = dbAccess.GetCommand("select sum(cast(size as integer))/128 from sysfiles", CommandType.Text))
{
return((int)dbAccess.ExecuteScalar(cmd));
}
}
/// <summary>
/// Determines whether [is full text supported].
/// </summary>
/// <param name="dbAccess">The database access.</param>
/// <returns>
/// Returns if fulltext is supported by the server or not
/// </returns>
public static bool IsFullTextSupported(this IDbAccess dbAccess)
{
CodeContracts.VerifyNotNull(dbAccess, "dbAccess");
try
{
using (var cmd = dbAccess.GetCommand("select SERVERPROPERTY('IsFullTextInstalled')", CommandType.Text))
{
return(dbAccess.ExecuteScalar(cmd).ToType <string>().Equals("1"));
}
}
catch
{
return(false);
}
}
/// <summary>
/// Gets the current SQL Engine Edition.
/// </summary>
/// <param name="dbAccess">The database access.</param>
/// <returns>
/// Returns the current SQL Engine Edition.
/// </returns>
public static string GetSQLVersion(this IDbAccess dbAccess)
{
CodeContracts.VerifyNotNull(dbAccess, "dbAccess");
try
{
using (var cmd = dbAccess.GetCommand("select @@version", CommandType.Text))
{
return(dbAccess.ExecuteScalar(cmd).ToString());
}
}
catch
{
return("Unknown");
}
}
/// <summary>
/// Gets the current SQL Engine Edition.
/// </summary>
/// <param name="dbAccess">The database access.</param>
/// <returns>
/// Returns the current SQL Engine Edition.
/// </returns>
public static string GetSQLEngine(this IDbAccess dbAccess)
{
CodeContracts.VerifyNotNull(dbAccess, "dbAccess");
try
{
using (var cmd = dbAccess.GetCommand("select SERVERPROPERTY('EngineEdition')", CommandType.Text))
{
return(dbAccess.ExecuteScalar(cmd).ToType <int>() switch
{
1 => "Personal",
2 => "Standard",
3 => "Enterprise",
4 => "Express",
5 => "Azure",
_ => "Unknown"
});
}
/// <summary>
/// The db_recovery_mode.
/// </summary>
/// <param name="DBName">
/// The db name.
/// </param>
/// <param name="recoveryMode">
/// The recovery mode.
/// </param>
public static string ChangeRecoveryMode(this IDbAccess dbAccess, [NotNull] string recoveryMode)
{
try
{
var recoveryModeSql =
$"ALTER DATABASE {dbAccess.CreateConnectionOpen().Database} SET RECOVERY {recoveryMode}";
using (var cmd = dbAccess.GetCommand(recoveryModeSql, CommandType.Text))
{
return(dbAccess.ExecuteScalar(cmd).ToString());
}
}
catch (Exception error)
{
var expressDb = string.Empty;
if (error.Message.ToUpperInvariant().Contains("'SET'"))
{
expressDb = "MS SQL Server Express Editions are not supported by the application.";
}
return($"\r\n{error.Message}\r\n{expressDb}");
}
}
private static string GetSequenceName(IDbAccess dataAccess, string tableName, string pkColumnName, bool checkSequence)
{
if (dataAccess == null)
throw new ArgumentNullException(nameof(dataAccess));
if (String.IsNullOrEmpty(tableName))
throw new ArgumentNullException(nameof(tableName));
if (String.IsNullOrEmpty(pkColumnName))
throw new ArgumentNullException(nameof(pkColumnName));
string[] arr = tableName.Split('.');
bool withUser = arr.Length > 1;
string sequenceName = null;
if (withUser)
sequenceName = arr[0] + ".SQE_" + arr[1];
else
sequenceName = "SQE_" + tableName;
if (checkSequence)
{
object temp = null;
decimal ret = 0M, minVal = 1M, curVal = 0M;
SqlQuery query = new SqlQuery();
StringBuilder text = query.Text;
try
{
text.Append("SELECT MAX(");
text.Append(tableName);
text.Append('.');
text.Append(pkColumnName);
text.Append(") FROM ");
text.Append(tableName);
temp = dataAccess.ExecuteScalar(query);
if (temp != null && temp.GetType() != CachedTypes.DBNull)
{
minVal = ((Decimal)temp) + 1M;
}
query.Clear();
text = query.Text;
text.Append(" SELECT COUNT(*) FROM");
if (withUser)
{
text.Append(" ALL_SEQUENCES T WHERE T.SEQUENCE_OWNER = :SEQUENCE_OWNER AND");
query.Parameters.Add("SEQUENCE_OWNER", arr[0]);
query.Parameters.Add("SEQUENCE_NAME", "SQE_" + arr[1]);
}
else
{
text.Append(" USER_SEQUENCES T WHERE");
query.Parameters.Add("SEQUENCE_NAME", sequenceName);
}
text.Append(" T.SEQUENCE_NAME = :SEQUENCE_NAME");
ret = (Decimal)dataAccess.ExecuteScalar(query);
if (ret == 0M)
{
query.Clear();
text = query.Text;
text.Append("CREATE SEQUENCE ");
text.Append(sequenceName);
text.AppendLine();
text.Append("MINVALUE 0");
text.AppendLine();
text.Append("MAXVALUE 9999999999999999999999999");
text.AppendLine();
text.Append("START WITH ");
text.Append(minVal);
text.AppendLine();
text.Append("INCREMENT BY 1");
text.AppendLine();
text.Append("CACHE 20");
text.AppendLine();
dataAccess.ExecuteNonQuery(query);
}
else
{
query.Clear();
text = query.Text;
text.Append("SELECT T.LAST_NUMBER FROM");
if (withUser)
{
text.Append(" ALL_SEQUENCES T WHERE T.SEQUENCE_OWNER = :SEQUENCE_OWNER AND T.SEQUENCE_NAME = :SEQUENCE_NAME");
query.Parameters.Add("SEQUENCE_OWNER", arr[0]);
query.Parameters.Add("SEQUENCE_NAME", "SQE_" + arr[1]);
}
else
{
text.Append(" USER_SEQUENCES T WHERE T.SEQUENCE_NAME = :SEQUENCE_NAME");
query.Parameters.Add("SEQUENCE_NAME", sequenceName);
}
curVal = (Decimal)dataAccess.ExecuteScalar(query);
if (minVal > curVal)
{
query.Clear();
text = query.Text;
text.Append("ALTER SEQUENCE ");
text.Append(sequenceName);
text.Append(" INCREMENT BY :INC;");
query.Parameters.Add("INC", minVal - curVal);
text.AppendLine();
text.Append("SELECT ");
text.Append(sequenceName);
text.Append(".NEXTVAL FROM DUAL;");
text.AppendLine();
text.Append("ALTER SEQUENCE ");
text.Append(sequenceName);
text.Append(" INCREMENT BY 1;");
dataAccess.ExecuteScalar(query);
}
}
}
catch (Exception ex)
{
throw new InvalidOperationException(
$"An error occurred while creating a sequence for '{tableName}' Oracle table. Error detail: '{ex.Message}'");
}
}
return sequenceName;
}
/// <summary>
/// System initialize and execute script's.
/// </summary>
/// <param name="script">The script.</param>
/// <param name="scriptFile">The script file.</param>
/// <param name="useTransactions">The use transactions.</param>
public static void SystemInitializeExecutescripts(
this IDbAccess dbAccess,
[NotNull] string script,
[NotNull] string scriptFile,
bool useTransactions)
{
script = CommandTextHelpers.GetCommandTextReplaced(script);
var statements = Regex.Split(script, "\\sGO\\s", RegexOptions.IgnoreCase).ToList();
// use transactions...
if (useTransactions)
{
using (var trans = dbAccess.CreateConnectionOpen().BeginTransaction())
{
foreach (var sql in statements.Select(sql0 => sql0.Trim()))
{
try
{
if (sql.ToLower().IndexOf("setuser") >= 0)
{
continue;
}
if (sql.Length <= 0)
{
continue;
}
using (var cmd = trans.Connection.CreateCommand())
{
// added so command won't timeout anymore...
cmd.CommandTimeout = int.Parse(Config.SqlCommandTimeout);
cmd.Transaction = trans;
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql.Trim();
cmd.ExecuteNonQuery();
}
}
catch (Exception x)
{
trans.Rollback();
throw new Exception(
"FILE:\n{0}\n\nERROR:\n{2}\n\nSTATEMENT:\n{1}".FormatWith(scriptFile, sql, x.Message));
}
}
trans.Commit();
}
}
else
{
// don't use transactions
foreach (var sql in statements.Select(sql0 => sql0.Trim()))
{
try
{
if (sql.ToLower().IndexOf("setuser") >= 0)
{
continue;
}
if (sql.Length <= 0)
{
continue;
}
using (var cmd = dbAccess.GetCommand(sql.Trim(), CommandType.Text))
{
dbAccess.ExecuteScalar(cmd).ToType <string>();
}
}
catch (Exception x)
{
throw new Exception(
"FILE:\n{0}\n\nERROR:\n{2}\n\nSTATEMENT:\n{1}".FormatWith(scriptFile, sql, x.Message));
}
}
}
}
