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);
}
/// <summary>
/// Deletes the database objects that are created to manage id's
/// </summary>
/// <param name="connection">The database containing the objects</param>
/// <param name="deleteExistingTables">
/// Supply true if you want to delete an existing table created to store ids
/// </param>
/// <remarks>
/// This is safe to call even when if the database does not contain the objects
/// </remarks>
public static void DeleteDbObjects(string connection, bool deleteExistingTables)
{
var db = new SqlAdminQueries.DbInstance {
ConnectionString = connection
};
db.DropStoredProc(GetNextIdProcName);
db.DropStoredProc(InsertSeedProcName);
db.DropStoredProc(UpdateSeedProcName);
db.DropTable("tbl_Ids_Identities");
}
/// <summary>
/// Create the database objects required to generate identities
/// </summary>
/// <param name="connection">The database to create the objects in</param>
/// <param name="deleteExistingTables">
/// Supply true if you want to delete an existing table created to store ids
/// </param>
/// <remarks>
/// If the table for storing identities already exists in the database AND <paramref name="deleteExistingTables"/>
/// is <c>false</c>, then an exception will be thrown, and the existing table will not be deleted
/// </remarks>
public static void CreateDbObjects(string connection, bool deleteExistingTables)
{
var db = new SqlAdminQueries.DbInstance {
ConnectionString = connection
};
DeleteDbObjects(connection, deleteExistingTables);
CreateSeedTable(db);
CreateSeedInsertStoredProc(db);
CreateSeedUpdateStoredProc(db);
CreateGetNextIdStoredProc(db);
}
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);
}