public static void DropTable(string tableName) { try { using (var connection = new SqlConnection(ConnectionString)) { connection.Open(); if (!connection.GetTableNames().Contains(tableName, StringComparer.InvariantCultureIgnoreCase)) return; Console.WriteLine("Dropping table {0}", tableName); try { using (var command = connection.CreateCommand()) { command.CommandText = string.Format("DROP TABLE [{0}]", tableName); command.ExecuteNonQuery(); } } catch (SqlException exception) { if (exception.Number == SqlServerMagic.ObjectDoesNotExistOrNoPermission) return; throw; } } } catch (Exception exception) { DumpWho(); throw new ApplicationException(string.Format("Could not drop table '{0}'", tableName), exception); } }
/// <summary> /// Creates the necessary timeout storage table if it hasn't already been created. If a table already exists /// with a name that matches the desired table name, no action is performed (i.e. it is assumed that /// the table already exists). /// </summary> public SqlServerTimeoutStorage EnsureTableIsCreated() { using (var connection = new SqlConnection(connectionString)) { connection.Open(); var tableNames = connection.GetTableNames(); if (tableNames.Contains(timeoutsTableName, StringComparer.OrdinalIgnoreCase)) { return this; } log.Info("Table '{0}' does not exist - it will be created now", timeoutsTableName); using (var command = connection.CreateCommand()) { command.CommandText = string.Format(@" CREATE TABLE [dbo].[{0}]( [id] [bigint] IDENTITY(1,1) NOT NULL, [time_to_return] [datetime2](7) NOT NULL, [correlation_id] [nvarchar](200) NOT NULL, [saga_id] [uniqueidentifier] NOT NULL, [reply_to] [nvarchar](200) NOT NULL, [custom_data] [nvarchar](MAX) NULL CONSTRAINT [PK_{0}] PRIMARY KEY NONCLUSTERED ( [id] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ) ", timeoutsTableName); command.ExecuteNonQuery(); } using (var command = connection.CreateCommand()) { command.CommandText = string.Format(@" CREATE CLUSTERED INDEX [IX_{0}_TimeToReturn] ON [dbo].[{0}] ( [time_to_return] ASC ) ", timeoutsTableName); command.ExecuteNonQuery(); } } return this; }