public static void Initialize(DatabaseManager dbManager)
{
Table table = new Table("_SysUris");
table.Columns.Add(new Column("Name", System.Data.SqlDbType.NVarChar, 128, false));
table.Columns.Add(new Column("Number", System.Data.SqlDbType.Int, nullable: false));
table.Columns.Add(new Column("Year", System.Data.SqlDbType.Int, nullable: false));
table.Constraints.Add(new UniqueConstraint(table.Name, "Name", "Number", "Year"));
var sp = new StoredProcedure("_SysUris_GetNext", @"
CREATE PROCEDURE [_SysUris_GetNext]
@sequence nvarchar(128),
@year int,
@next int output
AS
BEGIN
DECLARE @t TABLE([NUMBER] int);
merge [_SysUris] T
USING (select @sequence as [Name], @year as [Year]) S
ON T.[Name] = S.[Name] AND T.[Year] = S.[Year]
WHEN MATCHED THEN
UPDATE SET [NUMBER] = [NUMBER]+1
WHEN NOT MATCHED THEN
INSERT ([Name], [Number], [Year]) VALUES (S.[Name], 1, S.[Year])
OUTPUT inserted.[Number] into @t;
select @next = [Number] from @t;
END");
dbManager.Merge(new List<Table>() { table }, new List<StoredProcedure>() { sp });
}
private void MergeProcedure(StoredProcedure existing, StoredProcedure proc)
{
if (!existing.Definition.Equals(proc.Definition, StringComparison.InvariantCultureIgnoreCase))
{
var dropCmd = conn.CreateCommand();
dropCmd.CommandText = string.Format("DROP PROCEDURE {0}", proc.Name);
var createCmd = conn.CreateCommand();
createCmd.CommandText = proc.Definition;
dropCmd.ExecuteNonQuery();
createCmd.ExecuteNonQuery();
}
}
private void CreateProcedure(StoredProcedure proc)
{
var createCmd = conn.CreateCommand();
createCmd.CommandText = proc.Definition;
createCmd.ExecuteNonQuery();
}