public void CreateObjects()
{
Server.Execute($@"DECLARE @IsBroker int
DECLARE @SQL nvarchar(4000)
SELECT @IsBroker = is_broker_enabled FROM sys.databases WHERE [Name] = db_name()
IF (@IsBroker = 0)
BEGIN
SET @SQL = 'ALTER DATABASE ' + db_name() + ' SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE'
EXEC sp_executesql @SQL
END
IF NOT EXISTS(SELECT * FROM sys.service_message_types WHERE [Name] = '{Parameters.MessageType}')
BEGIN
CREATE MESSAGE TYPE {Parameters.MessageType} VALIDATION = WELL_FORMED_XML
END
IF NOT EXISTS(SELECT * FROM sys.service_contracts WHERE [Name] = '{Parameters.Contract}')
BEGIN
CREATE CONTRACT {Parameters.Contract}({Parameters.MessageType} SENT BY ANY)
END
IF NOT EXISTS(SELECT * FROM sys.service_queues WHERE [Name] = '{Parameters.QueueOrigin}')
BEGIN
CREATE QUEUE {Parameters.QueueOrigin}
END
IF NOT EXISTS(SELECT * FROM sys.service_queues WHERE [Name] = '{Parameters.QueueDestination}')
BEGIN
CREATE QUEUE {Parameters.QueueDestination}
END
IF NOT EXISTS(SELECT * FROM sys.services WHERE [Name] ='{Parameters.ServiceOrigin}')
BEGIN
CREATE SERVICE {Parameters.ServiceOrigin} ON QUEUE {Parameters.QueueOrigin}({Parameters.Contract})
END
IF NOT EXISTS(SELECT * FROM sys.services WHERE [Name] ='{Parameters.ServiceDestination}')
BEGIN
CREATE SERVICE {Parameters.ServiceDestination} ON QUEUE {Parameters.QueueDestination}({Parameters.Contract})
END
IF OBJECT_ID('{Parameters.BaggageTable}') IS NULL
BEGIN
CREATE TABLE [{Parameters.BaggageTable}]([Uri] varchar(50), [Data] varbinary(MAX))
END");
}
public void CreateObjects()
{
var typeName = string.Format(Parameters.TypeFormat, Parameters.TableName);
var spName = string.Format(Parameters.StoredProcedureFormat, Parameters.TableName);
var columnsNames = string.Join(",", Parameters.ColumnsName);
var sql = $@"IF TYPE_ID('{typeName}') IS NULL
BEGIN
DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(@Names + ',', '') + COLUMN_NAME + ' ' + DATA_TYPE +
CASE WHEN DATA_TYPE IN ('char', 'varchar','nchar','nvarchar') THEN '('+
CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1 THEN 'MAX'
ELSE CONVERT(VARCHAR(4),
CASE WHEN DATA_TYPE IN ('nchar','nvarchar')
THEN CHARACTER_MAXIMUM_LENGTH/2 ELSE CHARACTER_MAXIMUM_LENGTH END )
END +')'
WHEN DATA_TYPE IN ('decimal','numeric')
THEN '('+ CONVERT(VARCHAR(4),NUMERIC_PRECISION)+','
+ CONVERT(VARCHAR(4),NUMERIC_SCALE)+')'
ELSE '' END
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'{Parameters.TableName}' AND COLUMN_NAME IN ({string.Join(",", Parameters.ColumnsName.Select(x => $"'{x}'"))})
SET @Names = 'CREATE TYPE [{typeName}] AS TABLE(' + @Names + ')'
EXEC(@Names)
END
IF OBJECT_ID('{spName}') IS NULL
BEGIN
EXEC('CREATE PROCEDURE [{spName}](@datasource {typeName} READONLY)
AS
MERGE [{Parameters.TableName}] as [Target]
USING(SELECT {columnsNames} from @datasource) as [Source]
({columnsNames}) on
{string.Join(" AND ", Parameters.ColumnsName.Select(x => $"[Target].{x} = [Source].{x}"))}
WHEN NOT MATCHED THEN
INSERT({columnsNames})
VALUES({string.Join(",", Parameters.ColumnsName.Select(x => $"[Source].{x}"))});')
END";
Server.Execute(sql);
}