Database

Nathan Minier wrote:

Now I'm not going to really disagree with Eddy, Mongo IS intended to store BLOBs, but the BLOBs in question are these BSON objects, generated from your classes/structures and squirreled away; not necessarily just images or videos.

Nathan Minier wrote:

but is absolutely not appropriate for all applications

Nathan Minier wrote:

IMO it's a great tool for the box.

Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.

Eddy Vluggen wrote:

A NoSQL db will not be optimized to run (complex) queries.

Eddy Vluggen wrote:

There's little in terms of constraints to keep the data consistent or correct.

Nathan Minier wrote:

Since you will not be using an object from different languages, i.e. an object in a store will need to be derived from the same library or have an adapter to be read in the first place, pushing constraints into the database is not appropriate. Your applications (or shared class library) should be handling that. I would argue that relying on constraints for consistency in a SQL system is exceptionally bad practice as well; your application should have some understanding of what an acceptable value is.

Nathan Minier wrote:

The real difference to me is that SQL is great when you don't know what will be producing or consuming the data, or how they will be doing it. It is very powerful in that regard, and when you're a DBA and have no idea what crazy app dev will be using your data, all these controls are appropriate and necessary.

Nathan Minier wrote:

If, however, you're an app dev making a purpose-built system that you do not intend to expose data for across several platforms, NoSQL brings a hell of a lot to the table. For one: you don't need to play these SQL translation games, and your DAL doesn't need to be written in a completely different language.

Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.

Eddy Vluggen wrote:

Unless it is a local data-cache, your db is going to be serving multiple UI's. Without a decent db, one of those UI's will f*** the complete db, as Murphy's law predicts.

Nathan Minier wrote:

I just don't assume the technology that mucks with the data will be a UI

Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.

Member 10283191 wrote:

I want to get this resutl:

Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.

Table_provider
 ID_Provider
 Name_Provider

 Table_purchase
 ID_Purchase
 Date_Purchase
 ID_Provider
 Amount

 Table_payment
 ID_payment
 Date_payment
 ID_Provider
 Amount

SELECT Provider.Name_Provider. Sum(purchase.Amount), sum(Payment.Amount)

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;
}