JozyKQL.PG 1.1.3

JozyKQL.PG (Dynamic Database Service)

The Dynamic Database Service is a NuGet package that provides a dynamic database service allowing you to perform CRUD (Create, Read, Update, Delete) operations on database tables using generic methods.

This package is for use with Postgres.

Installation

You can install the Dynamic Database Service package via NuGet Package Manager or the dotnet CLI:

dotnet add package JozyKQL.PG

.

Usage:

Configuration:

1. Define your model with the appropriate attributes:

using JozyKQL.PG.Attributes;
public class YourModel
{
    [PrimaryKey]
    public int Id { get; set; }
    
    public string Name { get; set; }
    
    public int Age { get; set; }
    
    [DateNow]
    public DateTime CreatedDate { get; set; }
    
    [IgnoreOnInsert]
    [IgnoreOnUpdate]
    public string IgnoredProperty { get; set; }
    
    [TemporaryDelete]
    public bool IsActive { get; set; }
}

2. Create an instance of DynamicService using the factory method:

using JozyKQL.PG;

// Configure connection
string connectionString = "your_connection_string_here";

// Create an instance of DynamicService
var dynamicService = DynamicServiceFactory.CreateDynamicService(connectionString);

// Perform CRUD operations
// Create a new record
        var example = new YourModel
        {
            Name = "John Doe",
            Age = 30,
            CreatedDate = DateTime.Now,
            IsActive = true
        };
        await _dynamicService.CreateDynamically(example);

        // Retrieve all records
        var allRecords = await _dynamicService.GetAllDynamically<YourModel>();

        // Update a record
        example.Age = 31;
        await _dynamicService.UpdateDynamically(example);

        // Permanently delete a record
        await _dynamicService.DeletePermanentlyDynamically(example);

        // Temporarily delete a record
        await _dynamicService.DeleteTemporarilyDynamically(example);

        // Other Update functions
        await dynamicService.UpdateBySpecificColumnDynamically(example, "Name");

        await dynamicService.UpdateSpecificColumnsDynamically(example, new List<string> { "Name", "Age" });

        await dynamicService.UpdateSpecificColumnsBySpecificColumnDynamically(example, new List<string> { "Name", "Age" }, "Id");

        await dynamicService.UpdateBySpecificColumnsDynamically(example, new List<string> { "Name", "Age" });

        await dynamicService.UpdateSpecificColumnsBySpecificColumnsDynamically(example, new List<string> { "Name", "Age" }, new List<string> { "Id", "IsActive" });

        await dynamicService.UpdateBySpecificWhereClauseDynamically(example, "Id = 1 AND IsActive = 1");

        await dynamicService.UpdateSpecificColumnsBySpecificWhereClauseDynamically(example, new List<string> { "Name", "Age" }, "Id = 1 AND IsActive = 1");

        //Get Functions
        var entity = await dynamicService.GetDynamically(example);

        var entities = await dynamicService.GetAllDynamically(example, 10);

        var entities = await dynamicService.GetBySpecificColumnDynamically(example, "Status", 5);

        var entities = await dynamicService.GetBySpecificColumnsDynamically(example, new List<string> { "Status", "Category" }, 5);

        var entities = await dynamicService.GetBySpecificWhereClauseDynamically(example, "Status = 'Active' AND Category = 'Electronics'", 10);

Available Methods:

CreateBulkDynamically: Creates multiple records in the database.

CreateDynamically: Creates a single record in the database.

DeletePermanentlyBulkDynamically: Deletes multiple records permanently from the database.

DeletePermanentlyDynamically: Deletes a single record permanently from the database.

DeleteTemporarilyBulkDynamically: Deletes multiple records temporarily from the database.

DeleteTemporarilyDynamically: Deletes a single record temporarily from the database.

GetAllDynamically: Retrieves all records from the database.

GetBySpecificColumnDynamically: Retrieves records from the database based on a specific column.

GetBySpecificColumnsDynamically: Retrieves records from the database based on specific columns.

GetBySpecificWhereClauseDynamically: Retrieves records from the database based on a custom WHERE clause.

GetDynamically: Retrieves a single record from the database.

UpdateBulkBySpecificColumnDynamically: Updates multiple records in the database based on a specific column.

UpdateBulkBySpecificColumnsDynamically: Updates multiple records in the database based on specific columns.

UpdateBulkBySpecificWhereClauseDynamically: Updates multiple records in the database based on a custom WHERE clause.

UpdateBulkDynamically: Updates multiple records in the database.

UpdateBulkSpecificColumnsBySpecificColumnDynamically: Updates multiple records in the database based on specific columns and a specific column condition.

UpdateBulkSpecificColumnsBySpecificColumnsDynamically: Updates multiple records in the database based on specific columns and specific column conditions.

UpdateBulkSpecificColumnsBySpecificWhereClauseDynamically: Updates multiple records in the database based on specific columns and a custom WHERE clause.

UpdateBulkSpecificColumnsDynamically: Updates multiple records in the database based on specific columns.

UpdateBySpecificColumnDynamically: Updates a single record in the database based on a specific column.

UpdateBySpecificColumnsDynamically: Updates a single record in the database based on specific columns.

UpdateBySpecificWhereClauseDynamically: Updates a single record in the database based on a custom WHERE clause.

UpdateDynamically: Updates a single record in the database.

UpdateSpecificColumnsBySpecificColumnDynamically: Updates a single record in the database based on specific columns and a specific column condition.

UpdateSpecificColumnsBySpecificColumnsDynamically: Updates a single record in the database based on specific columns and specific column conditions.

UpdateSpecificColumnsBySpecificWhereClauseDynamically: Updates a single record in the database based on specific columns and a custom WHERE clause.

UpdateSpecificColumnsDynamically: Updates a single record in the database based on specific columns.

Attribute Definitions:

PrimaryKey

Indicates the primary key property for the model.

[AttributeUsage(AttributeTargets.Property)]
public class PrimaryKeyAttribute : Attribute { }

IgnoreOnInsert

Indicates that the property should be ignored during insert operations.

[AttributeUsage(AttributeTargets.Property)]
public class IgnoreOnInsertAttribute : Attribute { }

IgnoreOnUpdate

Indicates that the property should be ignored during update operations.

[AttributeUsage(AttributeTargets.Property)]
public class IgnoreOnUpdateAttribute : Attribute { }

DateNow

Indicates that the current date should be used for this property during insert or update operations.

[AttributeUsage(AttributeTargets.Property)]
public class DateNowAttribute : Attribute { }

TemporaryDelete

Indicates the property used for temporary delete operations.

[AttributeUsage(AttributeTargets.Property)]
public class TemporaryDeleteAttribute : Attribute { }

OrderBy

Indicates the property should be used for ordering in select operations.

[AttributeUsage(AttributeTargets.Property)]
public class OrderByAttribute : Attribute 
{
    public int Rank { get; set; }
    public bool OrderByDesc { get; set; }

    public OrderByAttribute(int rank, bool orderByDesc = false)
    {
        Rank = rank;
        OrderByDesc = orderByDesc;
    }
}

The OrderBy attribute has two properties:

Rank: An integer indicating the order of sorting precedence. Lower numbers have higher precedence.
OrderByDesc: A boolean indicating whether the sorting should be descending. By default, it is set to false for ascending order.

Usage in a Model:

public class Product
{
    [PrimaryKey]
    public int Id { get; set; }
    
    public string Name { get; set; }
    
    public string Category { get; set; }
    
    [OrderBy(1)]
    public decimal Price { get; set; }
    
    [OrderBy(2, OrderByDesc = true)]
    public DateTime CreatedDate { get; set; }
}

Stored Procedure Methods:

The following methods are designed to interact with stored procedures in a dynamic and flexible way. They allow you to pass any type of input model and receive output from stored procedures.

Important Note:

The input model type's name must match the name of the stored procedure you intend to call. This is how the methods dynamically identify which stored procedure to execute.

/* 
InputOutputStoredProcedureDynamic<TInput, TOutput>
Executes a stored procedure with input parameters and expects a single output result.
*/
var inputModel = new MyInputModel { Parameter1 = "value1", Parameter2 = 123 };
IEnumerable<MyOutputModel> outputModels = new List<MyOutputModel>();
outputModels = await dynamicService.InputOutputStoredProcedureDynamic(inputModel, outputModels);

/* 
InputOutputStoredProcedureDynamic<TInput, TOutput>
Executes a stored procedure with input parameters and expects multiple output results.
*/
var inputModel = new MyInputModel { Parameter1 = "value1", Parameter2 = 123 };
IEnumerable<MyOutputModel> outputModels = new List<MyOutputModel>();
outputModels = await dynamicService.InputOutputStoredProcedureDynamic(inputModel, outputModels);


/* 
IntegerStoredProcedureDynamic<TInput>
Executes a stored procedure with input parameters and returns an integer result. This is often used for procedures that perform an action and return a status or affected rows count.
*/
var inputModel = new MyInputModel { Parameter1 = "value1", Parameter2 = 123 };
int result = await dynamicService.IntegerStoredProcedureDynamic(inputModel);


/* 
StoredProcedureDynamic<TInput>
Executes a stored procedure with input parameters but does not expect any output. This is typically used for procedures that perform actions such as updates or inserts.
*/
var inputModel = new MyInputModel { Parameter1 = "value1", Parameter2 = 123 };
await dynamicService.StoredProcedureDynamic(inputModel);

Explanation:

• Dynamic Naming: These methods derive the stored procedure name from the Name property of the input model type. Ensure the input model type's name matches the stored procedure name.
• Command Type: All methods specify CommandType.StoredProcedure to ensure the command is executed as a stored procedure.
• Error Handling: Each method contains a try-catch block to handle and rethrow exceptions.

Usage Scenarios:

• Single Result: Use InputOutputStoredProcedureDynamic when you expect a single row or a single result from the stored procedure.
• Multiple Results: Use InputOutputStoredProcedureDynamic with an IEnumerable<TOutput> for multiple rows or multiple results.
• Integer Result: Use IntegerStoredProcedureDynamic for procedures that return a status code or the number of affected rows.
• No Result: Use StoredProcedureDynamic for procedures that only perform actions and do not return any result.