DBcs - C# Database helper for PostgreSQL and other DBs.

Basic usage

For class

public class DBCategory
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public bool IsDeleted { get; set; }
}

with coresponding table in PostgreSQL

CREATE TABLE IF NOT EXISTS public.categories
(
    id         integer NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    name       text    NOT NULL,
    is_deleted boolean NOT NULL,
    CONSTRAINT pk_categories PRIMARY KEY (id)
);

we can load items into List<DBCategory> with

var h = new DBcs.DBcs(connString);
var categories =  await h.RunQueryAsync<DBCategory>("select * from categories;");

Matching column and property names

Matching Column names and properties is always done case-insensitive with removed underscore (_) to ensure matching between snake_case (pgsql) and CamelCase (C# properties).

Parameters

All methods have optional parameterObject which is used to fill parameters. Parameters in query text are marked with prefix @.

For example:

var h = new DBcs.DBcs(connString);
// load existing category
var oldCategory = await h.RunQuerySingleOrDefaultAsync<DBCategory>(
    "select * from categories where id=@id;",  new {id=1});
//change object
oldCategory.Name = "This is changed";
// save object to db, and return new object from db
var newCategory = await h.RunQuerySingleOrDefaultAsync<DBCategory>(
    "update public.categories set name=@name, is_deleted=@is_deleted where id=@id returning *;",
     oldCategory);

Large data sets

For large data sets use RunQueryWithCallBackAsync method, witch calls your Action for every row and then forgets the data. It uses IDataReader and process data row-by-row, avoiding loading whole data set into memory.

var h = new DBcs.DBcs(connString);
await dBHelp.RunQueryWithCallBackAsync<DBCategory>(
    "select * from categories;", (c =>
        {
            //process row fast 
            Console.WriteLine($"Soon will be forgotten: {c.Name}");
        }));

Simplicity

Methods:

• RunNonQueryAsync - returns number of affected rows.
• RunScalarAsync - returns first column of the first row, the rest is discarded.
• RunQuerySingleOrDefaultAsync - object from the first row, rest is discarded.
• RunQueryAsync - Loads rows from table into List<T>.
• RunQueryWithCallBackAsync - Raises callback for every row without storing data.

all use simple, predictable name matching and they do not check from wich table data comes from. No decoration of classes is needed. Just matching column and property names.

Advanced usage

Class code generation

At run time you can call GetClassCodeString to recive text of classes for tables specified in query:

var h = new DBcs.DBcs(connString);
var classes = await dBHelp.GetClassCodeString(
    // Class represents one row, while List of classes
    // represents table. Thats why is good practice to
    // name classes in singular, and tables in plural
    //
    // It is recommended to add prefix to class names
    // that represents items from db.
    //  -makes you aware that this class represents data
    //   row in db
    //  -avoids name conflicts - in our quiz example, if 
    //   we name "Question" item from table questions,
    //   it will be in conflict with property "Question",
    //   Which represents text of the question.
    //
    new[]
    {
        "DBQuestion",
        "DBCategory",
        "DBAuthor",
        "DBQuiz",
        "DBQuizQuestion",
    },
    @"
        select * from questions;
        select * from categories;
        select * from authors;
        select * from quizzes;
        select * from quizzes_questions;

    ");
Console.WriteLine(classes);

returned code may look like

[Table("questions")]
public class DBQuestion
{
        [Key]
        public int Id {get; set;}
        public int Difficulty {get; set;}
        [ForeignKey("DBCategory")]
        public int CategoryId {get; set;}
        // property for refernced object
        public DBCategory DBCategory {get; set;}
        [ForeignKey("DBAuthor")]        
        public int? AuthorId {get; set;}
        // property for refernced object
        public DBAuthor? DBAuthor {get; set;}
...

You can now copy/paste classes to your code and use it.

Fill reference types (objects)

With generated classes you can now use RunAndFillReferenceTypesAsync and coresponding RunAndFillReferenceTypesWithCallbackAsync which fills object and Lists.

• first we select main object and then "inner join" referenced and referncing tables
• we need to sort dataset by main object, and then by other objects

If we, for exmple want to load all Authors from table and corresponding Questions and Quizzes we first select main object (DBAuthor), then DBQuestions and DBQuizzes

var h = new DBcs.DBcs(connString);
var authors = await h.RunAndFillReferenceTypesAsync<DBAuthor>(
    @"
        select
	        *
        from 
	        authors a
        inner join 
	        quizzes qz on a.id = qz.author_id
        inner join 
	        questions q on a.id = q.author_id
        order by
	        a.id, qz.id, q.id;
");
//we get list of objects with it's collections filled  
foreach (var author in authors)
{
    Console.WriteLine($"Author : {author.Name}");

    Console.WriteLine($"   Questions:");
    foreach (var question in author.Questions)
    {
        Console.WriteLine($"      {question.Question} - Answer is: {question.CorrectAnswer}");
    }

    Console.WriteLine($"   Quizzes:");
    foreach (var quiz in author.Quizzes)
    {
        Console.WriteLine($"      {quiz.Name}");
    }
}

SQLServer, Oracle, ...

To use DBHelp with other RDBMSs just override CreateDataSource method:

   internal class SQLDBcs:DBcs.DBcs
   {
       public override DbDataSource CreateDataSource()
       {
           return SqlClientFactory.Instance.CreateDataSource("<connection string>"); 
       }
   }

now use SQLDBcs class to create DBcs object. Do the same for other databases.