CPSysUDBCore 1.0.0

CPSysUDBCore

CPSysUDBCore is a database abstraction and SQL query builder library for .NET.

The library provides a unified API for working with different database providers, allowing applications to perform database operations without depending directly on provider-specific implementations.

Features

• Database abstraction layer
• SQL Server support
• MySQL support
• Firebird support
• SQLite support
• CRUD operations
• SQL query builder
• Conditional queries
• BETWEEN queries
• SQL functions
• GROUP BY
• HAVING
• ORDER BY
• JOIN
• Subqueries
• UNION
• Pagination
• Database metadata and schema operations
• Transactions
• Database management utilities
• Strongly typed C# API

Supported Framework

• .NET 10

Supported Databases

Installation

Install the package using the .NET CLI:

dotnet add package CPSysUDBCore

Or using the NuGet Package Manager:

Install-Package CPSysUDBCore

The package is also available through NuGet:

https://www.nuget.org/packages/CPSysUDBCore/

Basic Usage

Create a ConnectionData object containing the database connection information and initialize the database manager.

Example:

using CPSysUDBCore;

var connection = new ConnectionData
{
    Server = "localhost",
    Database = "MyDatabase",
    User = "sa",
    Password = "password"
};

The exact configuration depends on the database provider being used.

Database Providers

CPSysUDBCore provides implementations for different database engines.

The library allows application code to work with a common database abstraction while the provider-specific implementation handles the communication with the database.

Conceptually:

Application
    |
    v
CPSysUDBCore
    |
    +-- SQL Server
    +-- MySQL
    +-- Firebird
    +-- SQLite

This makes it possible to structure applications independently from a specific database provider.

CRUD Operations

The library supports the main database operations:

• Insert
• Select
• Update
• Delete

Queries can be constructed using the library's query-building API instead of manually concatenating SQL strings.

Query Builder

CPSysUDBCore provides functionality for building SQL queries programmatically.

Supported operations include:

SELECT
WHERE
BETWEEN
ORDER BY
GROUP BY
HAVING
JOIN
SUBQUERY
UNION
PAGINATION

This allows complex queries to be constructed while keeping the database access code organized.

Example Query Structure

A query can be constructed using the library's query objects and methods:

var query = new Table("users");

query
    .Select("*")
    .Where("active", "=", 1)
    .OrderBy("name");

The query builder is designed to provide reusable SQL construction functionality across supported database providers.

Database Management

CPSysUDBCore also provides database management functionality for operations such as:

• Creating databases
• Checking database existence
• Creating tables
• Working with table metadata
• Managing database structures
• Executing database commands

The available operations depend on the database provider.

SQL Functions

The query builder supports SQL functions that can be used when constructing queries.

Examples include operations such as:

COUNT
SUM
AVG
MIN
MAX

Functions can be combined with filtering, grouping and ordering operations.

Joins

Queries can use different types of SQL joins, allowing data to be retrieved from multiple tables.

Typical join types include:

INNER JOIN
LEFT JOIN
RIGHT JOIN

The exact availability depends on the database engine.

Subqueries

CPSysUDBCore supports subqueries as part of more complex SQL statements.

This allows queries such as:

SELECT *
FROM customers
WHERE id IN (
    SELECT customer_id
    FROM orders
);

to be represented through the query-building API.

UNION

Multiple queries can be combined using SQL UNION operations.

Example:

SELECT id, name FROM customers
UNION
SELECT id, name FROM suppliers;

Pagination

The library provides functionality for building paginated queries, which can be useful when working with large datasets and APIs.

Pagination allows applications to retrieve only a specific portion of the result set instead of loading all records at once.

Transactions

Database operations can be executed using transactions when atomicity between multiple operations is required.

Typical transaction flow:

Begin Transaction
       |
       v
 Execute Operations
       |
       +---- Success ----> Commit
       |
       +---- Error ------> Rollback

Architecture

The library is organized around database abstraction.

The main concepts include:

CPSysUDBCore
|
+-- DataBaseManager
|
+-- IDatabase
|
+-- ConnectionData
|
+-- SQL Server implementation
+-- MySQL implementation
+-- Firebird implementation
+-- SQLite implementation
|
+-- Query Builder
    |
    +-- Select
    +-- Where
    +-- Between
    +-- Functions
    +-- Group
    +-- Having
    +-- Order
    +-- Join
    +-- Subquery
    +-- Union
    +-- Pagination

Design Goals

CPSysUDBCore was created with the following goals:

• Simplify database access in C# applications
• Reduce provider-specific code
• Provide a common API for different database engines
• Simplify SQL query construction
• Improve code organization
• Provide reusable database components
• Support applications that may work with different database providers

Dependencies

CPSysUDBCore uses the following database providers:

FirebirdSql.Data.FirebirdClient
Microsoft.Data.SqlClient
Microsoft.Data.Sqlite.Core
MySql.Data
SQLitePCLRaw.bundle_e_sqlite3

These dependencies are automatically installed when the NuGet package is added to a project.

Example Project

A typical application using CPSysUDBCore can be structured as:

MyApplication
|
+-- Application
|
+-- Services
|
+-- Models
|
+-- Data
|   |
|   +-- CPSysUDBCore
|
+-- Program.cs

The application can keep its database logic separated from the rest of the business logic while CPSysUDBCore handles database communication and SQL construction.

License

CPSysUDBCore is distributed under the MIT License.

See the LICENSE file included with the package for the complete license text.

Author

Rafael Pinal

CPSysUDBCore is part of the CPSysUDB project family, developed to provide reusable database abstraction and SQL construction components for .NET applications.