/// <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");
}
}
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 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}");
}
}
/// <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));
}
}
}
}