Database

CHill60 wrote:

Personally I would have single transaction table with a transaction type column but each to their own

Quote:

As an invoicing exercise the link between purchase and payment is never designed as 1-1.

ALTER PROCEDURE [dbo].[Usp_Add_LookupRecord] (@LookupTableId int,
    @Type varchar(100), @Description varchar(500), @ForeignKeyId int, @CreatedBy varchar(500), @ModifiedBy varchar(500), @IsValid bit<br />
)
AS
BEGIN
    declare @LookupTableName varchar(100)=(select top 1 LookupTableName FROM [dbo].[ListOfLookupTables] WHERE PkListOfLookupTablesId=@LookupTableId)
    declare @InsertedId int=0, @FKProgramTypeLKPId int = (select top 1 PKProgramTypeLKPId from ProgramTypeLKP where Type='Mental Health')

    IF (@LookupTableName='ProgramTypeLKP')
    BEGIN
        IF NOT EXISTS(SELECT Top 1 1 FROM dbo.ProgramTypeLKP WHERE [Type]=@Type AND ISNULL(IsValid, 1)=1)
        BEGIN
            INSERT INTO dbo.ProgramTypeLKP ([Type], [Description], CreatedDate, CreatedBy, ModifiedDate, ModifiedBy, IsValid)
            VALUES(LEFT(@Type, 15), LEFT(@Description, 500), GETDATE(), LEFT(@CreatedBy, 500), GETDATE(), LEFT(@ModifiedBy, 500), @IsValid)
            SET @InsertedId=SCOPE_IDENTITY()
        END
        ELSE
        BEGIN
            RETURN 0
        END
    END
    ELSE IF (@LookupTableName='AddressTypeLKP')
    BEGIN
        IF NOT EXISTS(SELECT Top 1 1 FROM dbo.AddressTypeLKP WHERE [Description]=@Type AND ISNULL(IsValid, 1)=1)
        BEGIN
            INSERT INTO dbo.AddressTypeLKP ([Description], FKProgramTypeLKPId, CreatedDate, CreatedBy, ModifiedDate, ModifiedBy, IsValid)
            VALUES(LEFT(@Description, 20), @FKProgramTypeLKPId, GETDATE(), LEFT(@CreatedBy, 30), GETDATE(), LEFT(@ModifiedBy, 30), @IsValid)
            SET @InsertedId=SCOPE_IDENTITY()
        END
        ELSE
        BEGIN
            RETURN 0
        END
    END

    RETURN @InsertedId
END

indian143 wrote:

IF NOT EXISTS(SELECT Top 1 1 FROM dbo.ProgramTypeLKP WHERE [Type]=@Type AND ISNULL(IsValid, 1)=1)

indian143 wrote:

declare @LookupTableName varchar(100)=(select top 1 LookupTableName FROM [dbo].[ListOfLookupTables] WHERE PkListOfLookupTablesId=@LookupTableId)
declare @InsertedId int=0, @FKProgramTypeLKPId int = (select top 1 PKProgramTypeLKPId from ProgramTypeLKP where Type='Mental Health')

var returnValue = new SqlParameter() 
{  
    ParameterName = "@ReturnValue",
    SqlDbType = SqlDbType.Int,
    Direction = System.Data.ParameterDirection.Output  
};  

context.Database.ExecuteSqlCommand("exec dbo.Usp_Add_LookupRecord", returnValue, ...other parameters here...);

int result = (int)returnValue.Value;

int result = context.Database.SqlQuery<int>("exec dbo.Usp_Add_LookupRecord", ... parameters here ...).First();

        public virtual int Usp_Add_LookupRecord(Nullable<int> lookupTableId, string type, string description, Nullable<int> foreignKeyId, string createdBy, string modifiedBy, Nullable<bool> isValid)
        {
            var lookupTableIdParameter = lookupTableId.HasValue ?
                new ObjectParameter("LookupTableId", lookupTableId) :
                new ObjectParameter("LookupTableId", typeof(int));

            var typeParameter = type != null ?
                new ObjectParameter("Type", type) :
                new ObjectParameter("Type", typeof(string));

            var descriptionParameter = description != null ?
                new ObjectParameter("Description", description) :
                new ObjectParameter("Description", typeof(string));

            var foreignKeyIdParameter = foreignKeyId.HasValue ?
                new ObjectParameter("ForeignKeyId", foreignKeyId) :
                new ObjectParameter("ForeignKeyId", typeof(int));

            var createdByParameter = createdBy != null ?
                new ObjectParameter("CreatedBy", createdBy) :
                new ObjectParameter("CreatedBy", typeof(string));

            var modifiedByParameter = modifiedBy != null ?
                new ObjectParameter("ModifiedBy", modifiedBy) :
                new ObjectParameter("ModifiedBy", typeof(string));

            var isValidParameter = isValid.HasValue ?
                new ObjectParameter("IsValid", isValid) :
                new ObjectParameter("IsValid", typeof(bool));

            return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction("Usp_Add_LookupRecord", lookupTableIdParameter, typeParameter, descriptionParameter, foreignKeyIdParameter, createdByParameter, modifiedByParameter, isValidParameter);
        }

ALTER PROCEDURE [dbo].[Usp_Add_LookupRecord] (@LookupTableId int,
    @Type varchar(100), @Description varchar(500), @ForeignKeyId int, @CreatedBy varchar(500), @ModifiedBy varchar(500), @IsValid bit<br />
)
AS
BEGIN
    declare @LookupTableName varchar(100)=(select top 1 LookupTableName FROM [dbo].[ListOfLookupTables] WHERE PkListOfLookupTablesId=@LookupTableId)
    declare @InsertedId int=0, @FKProgramTypeLKPId int = (select top 1 PKProgramTypeLKPId from ProgramTypeLKP where Type='Mental Health')

    IF (@LookupTableName='ProgramTypeLKP')
    BEGIN
        IF NOT EXISTS(SELECT Top 1 1 FROM dbo.ProgramTypeLKP WHERE [Type]=@Type AND ISNULL(IsValid, 1)=1)
        BEGIN
            INSERT INTO dbo.ProgramTypeLKP ([Type], [Description], CreatedDate, CreatedBy, ModifiedDate, ModifiedBy, IsValid)
            VALUES(LEFT(@Type, 15), LEFT(@Description, 500), GETDATE(), LEFT(@CreatedBy, 500), GETDATE(), LEFT(@ModifiedBy, 500), @IsValid)
            SET @InsertedId=SCOPE_IDENTITY()
        END
        ELSE
        BEGIN
            RETURN -3
        END
    END
    ELSE IF (@LookupTableName='AddressTypeLKP')
    BEGIN
        IF NOT EXISTS(SELECT Top 1 1 FROM dbo.AddressTypeLKP WHERE [Description]=@Type AND ISNULL(IsValid, 1)=1)
        BEGIN
            INSERT INTO dbo.AddressTypeLKP ([Description], FKProgramTypeLKPId, CreatedDate, CreatedBy, ModifiedDate, ModifiedBy, IsValid)
            VALUES(LEFT(@Description, 20), @FKProgramTypeLKPId, GETDATE(), LEFT(@CreatedBy, 30), GETDATE(), LEFT(@ModifiedBy, 30), @IsValid)
            SET @InsertedId=SCOPE_IDENTITY()
        END
        ELSE
        BEGIN
            RETURN -3
        END
    END

    RETURN @InsertedId
END

public virtual int Usp_Add_LookupRecord(int? lookupTableId, string type, string description, int? foreignKeyId, string createdBy, string modifiedBy, bool? isValid)
{
    var returnValue = new SqlParameter
    {  
        ParameterName = "@ReturnValue",
        SqlDbType = SqlDbType.Int,
        Direction = System.Data.ParameterDirection.Output  
    };
    
    var lookupTableIdParameter = new SqlParameter("LookupTableId", SqlDbType.Int) 
    {
        Value = (object)lookupTableId ?? DBNull.Value
    };

    var typeParameter = new SqlParameter("Type", SqlDbType.VarChar, 100)
    {
        Value = (object)type ?? DBNull.Value
    };

    var descriptionParameter = new SqlParameter("Description", SqlDbType.VarChar, 500)
    {
        Value = (object)description ?? DBNull.Value
    };

    var foreignKeyIdParameter = new SqlParameter("ForeignKeyId", SqlDbType.Int)
    {
        Value = (object)foreignKeyId ?? DBNull.Value
    };

    var createdByParameter = new SqlParameter("CreatedBy", SqlDbType.VarChar, 500)
    {
        Value = (object)createdBy ?? DBNull.Value
    };

    var modifiedByParameter = new SqlParameter("ModifiedBy", SqlDbType.VarChar, 500)
    {
        Value = (object)modifiedBy ?? DBNull.Value
    };

    var isValidParameter = new SqlParameter("IsValid", SqlDbType.Bit)
    {
        Value = (object)isValid ?? DBNull.Value
    };
    
    Database.ExecuteSqlCommand("exec dbo.Usp_Add_LookupRecord", 
        returnValue, 
        lookupTableIdParameter, 
        typeParameter, 
        descriptionParameter, 
        foreignKeyIdParameter, 
        createdByParameter, 
        modifiedByParameter, 
        isValidParameter);

    return (int)returnValue.Value;
}

Quote:

Cannot drop schema 'InsertYourSchemaName' because it is being referenced by object

-- The schema we are trying to drop
declare @schemaName varchar(255) = 'Test'
-- a temporary table for the list of things in the schema
declare @thingsToDrop table (TABLE_NAME varchar(255), TABLE_TYPE varchar(255))
insert into @thingsToDrop (TABLE_NAME, TABLE_TYPE) 
SELECT TABLE_NAME, TABLE_TYPE
FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = @schemaName

-- Declare the variables for the cursor
DECLARE @name varchar(255)
DECLARE @type varchar(30)

-- Set up the cursor
IF CURSOR_STATUS('global','myCursor')>=-1 DEALLOCATE tb_cursor 
DECLARE tb_cursor CURSOR FOR 
	SELECT TABLE_NAME, TABLE_TYPE FROM @thingsToDrop

OPEN tb_cursor  
FETCH NEXT FROM tb_cursor INTO @name, @type

-- step through the list of things to dispose of 
DECLARE @sql nvarchar(max)
WHILE @@FETCH_STATUS = 0  
BEGIN  
	DECLARE @thing varchar(10)
	SET @thing = (SELECT CASE	WHEN @type = 'BASE TABLE' THEN 'table'
								WHEN @type = 'VIEW' THEN 'view' -- etc as required
								END)
	SET @sql = CONCAT('DROP ',@thing, ' ',@schemaName, '.', @name)
	--If you have SQL Server 2017 you could use CONCAT_WS instead

	EXEC sp_executesql @sql

	FETCH NEXT FROM tb_cursor INTO @name, @type
END 

CLOSE tb_cursor  
DEALLOCATE tb_cursor

set @sql = 'DROP SCHEMA ' + quotename(@SchemaName)
exec sp_executesql @sql

-- test 1
;CREATE SCHEMA Test;
GO
create table Test.test(id int identity(1,1))
create table Test.test1(id int identity(1,1))
GO

;CREATE VIEW Test.ViewTest AS
	SELECT * FROM Test.test UNION ALL SELECT * FROM Test.test1
GO

-- test 2
;CREATE SCHEMA Test;
GO
;CREATE VIEW Test.ViewTest AS
	INFORMATION_SCHEMA.TABLES
GO

-- test 3
;CREATE SCHEMA Test;
GO
create table Test.test(id int identity(1,1))
create table Test.test1(id int identity(1,1))
GO

-- test 4
;CREATE SCHEMA Test;
GO