예제 #1
0
        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 });
        }
예제 #2
0
        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();
            }
        }
예제 #3
0
        private void CreateProcedure(StoredProcedure proc)
        {
            var createCmd = conn.CreateCommand();
            createCmd.CommandText = proc.Definition;

            createCmd.ExecuteNonQuery();
        }