// Note: modified for 1.3, Aug 2016 - added setting READ_COMMITTED_SNAPSHOT public void EnableSnapshotIsolation(IEntitySession session) { var dbConn = session.GetDirectDbConnector(admin: true); dbConn.OpenConnection(); // First get current DB name var cmdGetDb = dbConn.DbConnection.CreateCommand(); cmdGetDb.CommandText = "SELECT DB_NAME()"; var dbName = dbConn.ExecuteScalar <string>(cmdGetDb); var cmd = dbConn.DbConnection.CreateCommand(); cmd.CommandText = string.Format("ALTER DATABASE {0} SET ALLOW_SNAPSHOT_ISOLATION ON;", dbName); dbConn.ExecuteNonQuery(cmd); //The following command enables special option that requires single-user mode - // otherwise it can hang forever cmd = dbConn.DbConnection.CreateCommand(); cmd.CommandText = string.Format(@" ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE; ALTER DATABASE {0} SET READ_COMMITTED_SNAPSHOT ON; ALTER DATABASE {0} SET MULTI_USER; ", dbName); dbConn.ExecuteNonQuery(cmd); dbConn.CloseConnection(); }
public void EnableSnapshotIsolation(IEntitySession session) { var dbConn = session.GetDirectDbConnector(admin: true); dbConn.OpenConnection(); // First get current DB name var cmdGetDb = dbConn.DbConnection.CreateCommand(); cmdGetDb.CommandText = "SELECT DB_NAME()"; var dbName = (string) dbConn.ExecuteDbCommand(cmdGetDb, Data.DbExecutionType.Scalar); var cmd = dbConn.DbConnection.CreateCommand(); cmd.CommandText = string.Format("ALTER DATABASE {0} SET ALLOW_SNAPSHOT_ISOLATION ON;", dbName); dbConn.ExecuteDbCommand(cmd, Data.DbExecutionType.NonQuery); dbConn.CloseConnection(); }
public static void ExecuteNonQuery(this IEntitySession session, string sql, params object[] args) { var sqlStmt = string.Format(sql, args); var dbConn = session.GetDirectDbConnector().DbConnection; var cmd = dbConn.CreateCommand(); cmd.CommandText = sqlStmt; var isClosed = dbConn.State != System.Data.ConnectionState.Open; if (isClosed) { dbConn.Open(); } cmd.ExecuteNonQuery(); if (isClosed) { dbConn.Close(); } }
}//method private static void EnableSnapshotIsolation(IEntitySession session) { var dbConn = session.GetDirectDbConnector(admin: true); dbConn.OpenConnection(); // First get current DB name var cmdGetDb = dbConn.DbConnection.CreateCommand(); cmdGetDb.CommandText = "SELECT DB_NAME()"; var dbName = dbConn.ExecuteScalar <string>(cmdGetDb); var cmd = dbConn.DbConnection.CreateCommand(); //Note: for real-wold databases, when you enable these options, you need to switch to single-user mode // temporarily; otherwise the statement might hang trying to lock database cmd.CommandText = string.Format(@" ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE; ALTER DATABASE {0} SET ALLOW_SNAPSHOT_ISOLATION ON; ALTER DATABASE {0} SET READ_COMMITTED_SNAPSHOT OFF; ALTER DATABASE {0} SET MULTI_USER; ", dbName); dbConn.ExecuteNonQuery(cmd); dbConn.CloseConnection(); }