/// <summary>
/// Add security roles to the database objects that are used to serve up ids
/// </summary>
/// <param name="connection">Connection to the database containing the object to secure</param>
/// <param name="roleNames">The names of security roles that should have permissions to generate ids</param>
public static void AddDbPermissionsForGeneratingId(string connection, params string[] roleNames)
{
foreach (var roleName in roleNames)
{
string sql = string.Format("GRANT EXECUTE ON OBJECT::[dbo].[{0}] TO [{1}]", GetNextIdProcName, roleName);
SimpleDataAccess.ExecuteSql(sql, connection);
}
}
public void DropTable(string tableName)
{
string sql =
String.Format("IF EXISTS ({0}) DROP TABLE {1};",
ObjectExistsSql(tableName, DbObjectType.Table),
tableName);
SimpleDataAccess.ExecuteSql(sql, ConnectionString);
}
public void DropStoredProc(string procName)
{
string sql =
string.Format("IF EXISTS ({0}) DROP PROC {1};",
ObjectExistsSql(procName, DbObjectType.StoredProc),
procName);
SimpleDataAccess.ExecuteSql(sql, ConnectionString);
}
public static void DropUserConnections(string serverName, string databaseName)
{
var connectionInfo = new DbConnectionInfo {
DatabaseName = "master", ServerName = serverName
};
string sql = String.Format("ALTER DATABASE [{0}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE", databaseName);
SimpleDataAccess.ExecuteSql(sql, DbConnectionInfo.GetMsSqlServerConnectionString(connectionInfo));
}
/// <summary>
/// Inserts the initial seed record
/// </summary>
/// <param name="connection">The connection to the database</param>
/// <param name="objectType">The name to associate with the seed</param>
/// <param name="seed">The initial value for id to be generated</param>
public static void InsertSeed(string connection, string objectType, int seed)
{
string sql = string.Format("EXEC dbo.{0} {1}, {2}",
InsertSeedProcName,
WhereStatement.FormatSqlValue(objectType),
WhereStatement.FormatSqlValue(seed));
SimpleDataAccess.ExecuteSql(sql, connection);
}
private static void CreateSeedTable(SqlAdminQueries.DbInstance db)
{
const string createTableSql =
@"CREATE TABLE [dbo].[tbl_Ids_Identities](
[ObjectType] [varchar](255) NOT NULL CONSTRAINT [PK_tbl_Ids_Identities] PRIMARY KEY CLUSTERED,
[CurrentId] [int] NOT NULL,
)";
SimpleDataAccess.ExecuteSql(createTableSql, db.ConnectionString);
}
public static void CreateDatabase(string serverName, string databaseName, bool drop)
{
if (drop)
{
DropDatabaseIfExists(serverName, databaseName);
}
string createDatabaseScript = "IF (SELECT DB_ID('" + databaseName + "')) IS NULL "
+ " CREATE DATABASE " + databaseName;
string connectionString = DbConnectionInfo.GetMsSqlServerConnectionString(serverName, "master");
SimpleDataAccess.ExecuteSql(createDatabaseScript, connectionString);
}
public static void DropDatabase(string serverName, string databaseName)
{
DropUserConnections(serverName, databaseName);
var connectionInfo = new DbConnectionInfo {
DatabaseName = "master", ServerName = serverName
};
string sql = String.Format("DROP DATABASE {0}", databaseName);
SimpleDataAccess.ExecuteSql(sql, DbConnectionInfo.GetMsSqlServerConnectionString(connectionInfo));
//Necessary so as to avoid a connection to the database that just been dropped being being reused from the pool
//Not clearing the pool, leads to your app throwing an exception when it tried to access the dropped database
//from a pooled connection that was no longer open
SqlConnection.ClearAllPools();
}
private static void CreateSeedInsertStoredProc(SqlAdminQueries.DbInstance db)
{
string createInsertSeedProcSql =
string.Format(
@"CREATE PROC [dbo].[{0}]
(
@objectType AS VARCHAR(255),
@seed INT = 0
)
AS
INSERT INTO dbo.tbl_Ids_Identities ( ObjectType, CurrentId )
VALUES ( @objectType, @seed )",
InsertSeedProcName);
SimpleDataAccess.ExecuteSql(createInsertSeedProcSql, db.ConnectionString);
}
private static void CreateSeedUpdateStoredProc(SqlAdminQueries.DbInstance db)
{
string createUpdateSeedProcSql =
string.Format(
@"CREATE PROC [dbo].[{0}]
(
@objectType AS VARCHAR(255),
@seed INT
)
AS
UPDATE dbo.tbl_Ids_Identities SET CurrentId = @seed WHERE ObjectType = @objectType
IF @@ROWCOUNT = 0 BEGIN
RAISERROR('No rows were updated.', 16, 1)
END",
UpdateSeedProcName);
SimpleDataAccess.ExecuteSql(createUpdateSeedProcSql, db.ConnectionString);
}
private static void CreateGetNextIdStoredProc(SqlAdminQueries.DbInstance db)
{
string createGetNextIdProcSql =
string.Format(
@"CREATE PROCEDURE [dbo].[{0}]
(
@objectType AS VARCHAR(255),
@blockSize AS INT = 1
)
AS
SET NOCOUNT ON
-- ensures even if command times out or batch is cancelled tx will rollback and locks released
SET XACT_ABORT ON
DECLARE @isOwnerOfTransaction INT
DECLARE @errmsg nvarchar(2048),
@severity tinyint,
@state TINYINT
DECLARE @resultId INT
BEGIN TRY
IF @@TRANCOUNT = 0
BEGIN
BEGIN TRAN
SET @isOwnerOfTransaction = 1
END
-- sanitize bad inputs
IF ( ISNULL(@blockSize, 0) = 0 )
BEGIN
SET @blockSize = 1
END
SELECT @resultId = CurrentId + @blockSize
FROM dbo.tbl_Ids_Identities WITH (UPDLOCK)
WHERE ObjectType = @objectType
UPDATE dbo.tbl_Ids_Identities
SET CurrentId = @resultId
WHERE ObjectType = @objectType
SELECT @resultId AS NextId
ExitLabel:
IF @isOwnerOfTransaction = 1
AND @@TRANCOUNT > 0
BEGIN
COMMIT TRAN
END
END TRY
BEGIN CATCH
IF @isOwnerOfTransaction = 1
AND @@TRANCOUNT > 0
BEGIN
ROLLBACK TRAN
END
SELECT @errmsg = error_message(),
@severity = error_severity(),
@state = error_state()
RAISERROR ( @errmsg, @severity, @state )
RETURN -1
END CATCH",
GetNextIdProcName);
SimpleDataAccess.ExecuteSql(createGetNextIdProcSql, db.ConnectionString);
}