/// <summary>
/// Migrate Repositories (Database).
/// </summary>
/// <param name="dbAccess">
/// The Database access.
/// </param>
public void MigrateDatabase(IDbAccess dbAccess)
{
if (!Config.IsDotNetNuke)
{
// Install Membership Scripts
dbAccess.Execute(db => db.Connection.CreateTableIfNotExists <AspNetUsers>());
dbAccess.Execute(db => db.Connection.CreateTableIfNotExists <AspNetRoles>());
dbAccess.Execute(db => db.Connection.CreateTableIfNotExists <AspNetUserClaims>());
dbAccess.Execute(db => db.Connection.CreateTableIfNotExists <AspNetUserLogins>());
dbAccess.Execute(db => db.Connection.CreateTableIfNotExists <AspNetUserRoles>());
}
this.MigrateLegacyUsers(dbAccess);
}
public static void ShrinkDatabase(this IDbAccess dbAccess)
{
CodeContracts.VerifyNotNull(dbAccess, "dbAccess");
dbAccess.Execute(
db => db.Connection.ExecuteSql("DBCC SHRINKDATABASE(N'{0}')".FormatWith(db.Connection.Database)));
}
/// <summary>
/// The re index database.
/// </summary>
/// <param name="dbAccess">
/// The db access.
/// </param>
/// <returns>
/// The <see cref="string"/>.
/// </returns>
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");
return(dbAccess.Execute(
db => db.Connection.Scalar <string>(sb.ToString())));
}
/// <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");
return(dbAccess.Execute(
db => db.Connection.Scalar <int>("SELECT sum(reserved_page_count) * 8.0 / 1024 FROM sys.dm_db_partition_stats")));
}
/// <summary>
/// Gets the dataset.
/// </summary>
/// <param name="dbAccess">The DB access.</param>
/// <param name="cmd">The command.</param>
/// <param name="dbTransaction">The database transaction.</param>
/// <returns>
/// The <see cref="DataSet" /> .
/// </returns>
public static DataSet GetDataset(
[NotNull] this IDbAccess dbAccess,
[NotNull] IDbCommand cmd,
[CanBeNull] IDbTransaction dbTransaction = null)
{
CodeContracts.VerifyNotNull(dbAccess, "dbAccess");
CodeContracts.VerifyNotNull(cmd, "cmd");
return(dbAccess.Execute(
c =>
{
var ds = new DataSet();
IDbDataAdapter dataAdapter = dbAccess.DbProviderFactory.CreateDataAdapter();
if (dataAdapter != null)
{
dataAdapter.SelectCommand = cmd;
dataAdapter.Fill(ds);
}
return ds;
},
cmd,
dbTransaction));
}
/// <summary>
/// The execute non query.
/// </summary>
/// <param name="dbAccess">
/// The db access.
/// </param>
/// <param name="cmd">
/// The cmd.
/// </param>
/// <param name="dbTransaction">
/// The db Transaction.
/// </param>
/// <returns>
/// The <see cref="int"/> .
/// </returns>
public static int ExecuteNonQuery(
[NotNull] this IDbAccess dbAccess, [NotNull] IDbCommand cmd, [CanBeNull] IDbTransaction dbTransaction = null)
{
CodeContracts.VerifyNotNull(dbAccess, "dbAccess");
CodeContracts.VerifyNotNull(cmd, "cmd");
return(dbAccess.Execute((c) => c.ExecuteNonQuery(), cmd, dbTransaction));
}
/// <summary>
/// Executes the scalar.
/// </summary>
/// <param name="dbAccess">The DB access.</param>
/// <param name="cmd">The command.</param>
/// <param name="dbTransaction">The DB Transaction.</param>
/// <returns>
/// Returns the Data
/// </returns>
public static object ExecuteScalar(
[NotNull] this IDbAccess dbAccess, [NotNull] IDbCommand cmd, [CanBeNull] IDbTransaction dbTransaction = null)
{
CodeContracts.VerifyNotNull(dbAccess, "dbAccess");
CodeContracts.VerifyNotNull(cmd, "cmd");
return(dbAccess.Execute(c => c.ExecuteScalar(), cmd, dbTransaction));
}
/// <summary>
/// Gets the reader.
/// </summary>
/// <param name="dbAccess">The DB access.</param>
/// <param name="cmd">The command.</param>
/// <param name="dbTransaction">The DB transaction.</param>
/// <returns>
/// The <see cref="IDataReader" /> .
/// </returns>
public static IDataReader GetReader([NotNull] this IDbAccess dbAccess, [NotNull] IDbCommand cmd, [NotNull] IDbTransaction dbTransaction)
{
CodeContracts.VerifyNotNull(dbAccess, "dbAccess");
CodeContracts.VerifyNotNull(cmd, "cmd");
CodeContracts.VerifyNotNull(dbTransaction, "dbTransaction");
return(dbAccess.Execute(c => c.ExecuteReader(), cmd, dbTransaction));
}
/// <summary>
/// Runs the update command.
/// </summary>
/// <typeparam name="T">
/// The type Parameter
/// </typeparam>
/// <param name="dbAccess">
/// The DB access.
/// </param>
/// <param name="update">
/// The update.
/// </param>
/// <returns>
/// The <see cref="int"/>.
/// </returns>
public static int Update <T>(
[NotNull] this IDbAccess dbAccess,
[NotNull] T update)
where T : IEntity
{
CodeContracts.VerifyNotNull(dbAccess, "dbAccess");
return(dbAccess.Execute(
db => db.Connection.Update(
update)));
}
/// <summary>
/// Migrate Repositories (Database).
/// </summary>
/// <param name="dbAccess">
/// The Database access.
/// </param>
public void MigrateDatabase(IDbAccess dbAccess)
{
dbAccess.Execute(
dbCommand =>
{
this.UpgradeTable(this.GetRepository <TopicTag>(), dbAccess, dbCommand);
///////////////////////////////////////////////////////////
return(true);
});
}
/// <summary>
/// Update only fields in the specified expression that matches the where condition (if any), E.g:
///
/// db.UpdateOnly(() => new Person { FirstName = "JJ" }, where: p => p.LastName == "Hendrix");
/// UPDATE "Person" SET "FirstName" = 'JJ' WHERE ("LastName" = 'Hendrix')
///
/// db.UpdateOnly(() => new Person { FirstName = "JJ" });
/// UPDATE "Person" SET "FirstName" = 'JJ'
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="dbAccess">The database access.</param>
/// <param name="updateFields">The update fields.</param>
/// <param name="where">The where.</param>
/// <param name="commandFilter">The command filter.</param>
/// <returns></returns>
public static int UpdateOnly <T>(
[NotNull] this IDbAccess dbAccess,
Expression <Func <T> > updateFields,
Expression <Func <T, bool> > where = null,
Action <IDbCommand> commandFilter = null)
where T : class, IEntity, new()
{
return(dbAccess.Execute(
db => db.Connection.UpdateOnly(
updateFields,
OrmLiteConfig.DialectProvider.SqlExpression <T>().Where(where),
commandFilter)));
}
/// <summary>
/// Migrate Repositories (Database).
/// </summary>
/// <param name="dbAccess">
/// The Database access.
/// </param>
public void MigrateDatabase(IDbAccess dbAccess)
{
dbAccess.Execute(
dbCommand =>
{
this.UpgradeTable(this.GetRepository <Forum>());
this.UpgradeTable(this.GetRepository <Registry>());
this.UpgradeTable(this.GetRepository <User>());
///////////////////////////////////////////////////////////
return(true);
});
}
/// <summary>
/// Migrate Repositories (Database).
/// </summary>
/// <param name="dbAccess">
/// The Database access.
/// </param>
public void MigrateDatabase(IDbAccess dbAccess)
{
dbAccess.Execute(
dbCommand =>
{
this.UpgradeTable(this.GetRepository <ProfileDefinition>(), dbAccess, dbCommand);
this.UpgradeTable(this.GetRepository <User>(), dbAccess, dbCommand);
///////////////////////////////////////////////////////////
return(true);
});
}
/// <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
{
return(dbAccess.Execute(
db => db.Connection.Scalar <string>("select @@version")));
}
catch
{
return("Unknown");
}
}
/// <summary>
/// Migrate Repositories (Database).
/// </summary>
/// <param name="dbAccess">
/// The Database access.
/// </param>
public void MigrateDatabase(IDbAccess dbAccess)
{
dbAccess.Execute(
dbCommand =>
{
this.UpgradeTable(this.GetRepository <ActiveAccess>(), dbAccess, dbCommand);
this.UpgradeTable(this.GetRepository <Group>(), dbAccess, dbCommand);
///////////////////////////////////////////////////////////
if (dbCommand.Connection.TableExists("FavoriteTopic"))
{
dbCommand.Connection.DropTable("FavoriteTopic");
}
return(true);
});
}
/// <summary>
/// Migrate old Users to Identity.
/// </summary>
/// <param name="dbAccess">The database access.</param>
private void MigrateLegacyUsers(IDbAccess dbAccess)
{
var guests = this.GetRepository <User>().Get(u => (u.Flags & 4) == 4);
if (guests.NullOrEmpty())
{
var expression = OrmLiteConfig.DialectProvider.SqlExpression <User>();
expression.Join <UserGroup>((user, userGroup) => userGroup.UserID == user.ID)
.Join <UserGroup, Group>((userGroup, group) => group.ID == userGroup.GroupID)
.Where <Group>(group => (group.Flags & 2) == 2);
var users = dbAccess.Execute(db => db.Connection.Select(expression));
users.ForEach(
user =>
{
var flags = user.UserFlags;
flags.IsGuest = true;
this.GetRepository <User>().UpdateOnly(
() => new User {
Flags = flags.BitValue
},
u => u.ID == user.ID);
});
}
var boards = this.GetRepository <Board>().GetAll();
boards.ForEach(
board =>
{
// Sync Roles
this.Get <IAspNetRolesHelper>().SyncRoles(board.ID);
var users = this.GetRepository <User>().GetByBoardId(board.ID);
// sync users...
MigrateUsersFromTable(users);
});
}
/// <summary>
/// Executes a non query in a transaction
/// </summary>
/// <param name="dbAccess">The database access.</param>
/// <param name="cmd">The command.</param>
/// <param name="useTransaction">if set to <c>true</c> [use transaction].</param>
/// <param name="isolationLevel">The isolation level.</param>
/// <returns>Returns the Result</returns>
public static int ExecuteNonQuery(
[NotNull] this IDbAccess dbAccess, [NotNull] IDbCommand cmd, bool useTransaction, IsolationLevel isolationLevel = IsolationLevel.ReadUncommitted)
{
CodeContracts.VerifyNotNull(dbAccess, "dbAccess");
CodeContracts.VerifyNotNull(cmd, "cmd");
if (!useTransaction)
{
return(dbAccess.ExecuteNonQuery(cmd));
}
using (var dbTransaction = dbAccess.BeginTransaction(isolationLevel))
{
var result = dbAccess.Execute(c => c.ExecuteNonQuery(), cmd, dbTransaction);
dbTransaction.Commit();
return(result);
}
}
/// <summary>
/// Migrate Repositories (Database).
/// </summary>
/// <param name="dbAccess">
/// The Database access.
/// </param>
public void MigrateDatabase(IDbAccess dbAccess)
{
dbAccess.Execute(
dbCommand =>
{
this.UpgradeTable(this.GetRepository <BBCode>(), dbAccess, dbCommand);
this.UpgradeTable(this.GetRepository <EventLog>(), dbAccess, dbCommand);
this.UpgradeTable(this.GetRepository <Medal>(), dbAccess, dbCommand);
this.UpgradeTable(this.GetRepository <Message>(), dbAccess, dbCommand);
this.UpgradeTable(this.GetRepository <MessageHistory>(), dbAccess, dbCommand);
this.UpgradeTable(this.GetRepository <PMessage>(), dbAccess, dbCommand);
this.UpgradeTable(this.GetRepository <ProfileCustom>(), dbAccess, dbCommand);
this.UpgradeTable(this.GetRepository <User>(), dbAccess, dbCommand);
///////////////////////////////////////////////////////////
return(true);
});
}
/// <summary>
/// The db_recovery_mode.
/// </summary>
/// <param name="dbAccess">
/// The db Access.
/// </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}";
return(dbAccess.Execute(
db => db.Connection.Scalar <string>(recoveryModeSql)));
}
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}");
}
}
public async Task FollowUser(string userName, string userFollowed)
{
string sql = "Insert into dbo.UsersFollowed(UserId, FollowedUserId) values (@UserName, @UserFollowed)";
await _db.Execute <dynamic>(sql, new { UserName = userName, UserFollowed = userFollowed });
}
public async Task IncreaseViewCountById(int id)
{
string sql = "update dbo.ForumThreads set ViewCount=ViewCount+1 where id = @Id";
await _db.Execute <dynamic>(sql, new { Id = id });
}
/// <summary>
/// Returns true if the Query returns any records that match the supplied SqlExpression, E.g:
/// <para>db.Exists(db.From<Person>().Where(x => x.Age < 50))</para>
/// </summary>
public static bool Exists <T>([NotNull] this IDbAccess dbAccess, Expression <Func <T, bool> > where = null)
where T : class, IEntity, new()
{
return(dbAccess.Execute(
db => db.Connection.Exists(OrmLiteConfig.DialectProvider.SqlExpression <T>().Where(where))));
}
/// <summary>
/// System initialize and execute script's.
/// </summary>
/// <param name="dbAccess">
/// The db Access.
/// </param>
/// <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", StringComparison.Ordinal) >= 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{scriptFile}\n\nERROR:\n{x.Message}\n\nSTATEMENT:\n{sql}");
}
}
trans.Commit();
}
}
else
{
// don't use transactions
foreach (var sql in statements.Select(sql0 => sql0.Trim()))
{
try
{
if (sql.ToLower().IndexOf("setuser", StringComparison.Ordinal) >= 0)
{
continue;
}
if (sql.Length <= 0)
{
continue;
}
dbAccess.Execute(db => db.Connection.Scalar <string>(sql.Trim()));
}
catch (Exception x)
{
throw new Exception($"FILE:\n{scriptFile}\n\nERROR:\n{x.Message}\n\nSTATEMENT:\n{sql}");
}
}
}
}
public Task SetUserAvatar(string userName, string url)
{
string sql = "update dbo.AspNetUsers set AvatarUrl = @Url where UserName = @UserName";
return(_db.Execute <dynamic>(sql, new { Url = url, UserName = userName }));
}
public void TestInsertAndSelect()
{
string uniqueValue = Guid.NewGuid().ToString();
string EmailAddress = uniqueValue + "@dbagnostic.test";
//insert a unique value into the databasle
_dbAccess.Execute("INSERT INTO [User] ([FirstName], [LastName], [EmailAddress]) VALUES (@FirstName, @LastName, @EmailAddress)",
new { FirstName = "Test", LastName = uniqueValue, EmailAddress = EmailAddress });
//get the data just inserted
IEnumerable <dynamic> data = _dbAccess.Query <dynamic>("SELECT * FROM [User] WHERE [EmailAddress] = @EmailAddress", new { EmailAddress = EmailAddress });
Assert.IsNotNull(data, "No data found (NULL) with EmailAddress '{0}'", EmailAddress);
Assert.IsTrue(data.Count() > 0, "0 records found with the EmailAddress '{0}'.", EmailAddress);
Assert.IsTrue(data.First().LastName == uniqueValue, "The LastName field for the data retrived does not match the expected value of '{0}'.", uniqueValue);
//clean up
_dbAccess.Execute("DELETE FROM [User] WHERE [EmailAddress] = @EmailAddress", new { EmailAddress = EmailAddress });
}
public async Task DeleteReplyById(int id)
{
string sql = "delete from dbo.ForumReplies where id = @Id";
await _db.Execute <dynamic>(sql, new { Id = id });
}
