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