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