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