nuel_Db 5.3.0

Introduction

Generally speaking, Db aims to minimise the SQL Server boilerplate code. In addition, several methods target front-end needs of an ASP.NET website. Using methods such as Json or Csv, the SQL Server data will not be cast as POCO and will be directly available for JavaScript use, saving development and processing time.

Initialisation

Set the connection string to get started. This should be done only once and preferably at startup.

nuel.Db.ConnectionString = mySqlServerConnectionString;

Simply import the nuel namespace:

using nuel;

All database methods are asynchronous and should be awaited with the await keyword.

Stored Procedures

Most of the methods have an overload to mark a query as a stored procedure.

string result = await Db.Csv("dbo.GetResults", isStoredProc: true);

However, an alternative way of executing a stored procedure is using the SQL exec command:

string result = await Db.Csv("exec dbo.GetResults");

Parameters

Since it is very important to pass user input as parameters in order to prevent SQL injection, most of the methods allow SQL parameters.

string query = "select count(1) from Employees where City=@city";
int count = await Db.Val<int>(query, isStoredProc: false, new SqlParameter("@city", "London"));

Simpler overloads accepting ValueTuple(name, value) parameters can also be used to pass the SQL parameters.

int count = await Db.Val<int>(query, ("@city", "London"));

A shorthand for Nullable string parameters is the nuel.Db.NS function, which replaces empty strings with a null value.

await Db.Execute("update Customers set FullName=@name where Id=@id",
                Db.NS("@name", name), // nullable string parameter
                new SqlParameter("@id", id));

Methods

All the methods are static.

Csv

Converts the query result or an object array to a CSV string, which reduces response size compared with JSON. Date columns keep the # flag and serialize as ISO 8601 strings with second precision (fractional seconds are omitted, not rounded). DateTime preserves the wall-clock value without a timezone suffix, regardless of Kind; DateTimeOffset preserves its explicit offset. No timezone conversion is performed. Consumers must parse these strings instead of Unix timestamps.

For instance, let's retrieve the following data in a SQL Server table named Employees:

The same data may be passed via an array, provided that all the elements have the same properties:

new[] {
    new { 
        Id = 1, 
        FullName = "Loraine Bickerdicke", 
        BirthDate = new DateTime(1994, 8, 22), 
        IsMarried = true
    },
    new { 
        Id = 2, 
        FullName = "Shelley Askem", 
        BirthDate = new DateTime(1992, 12, 7), 
        IsMarried = false
    },
}

The Csv method returns the result of the given query as a CSV string.

string csv = await Db.Csv("select * from Employees");
//!Id~$FullName~#BirthDate~^IsMarried|1~Loraine Bickerdicke~1994-08-22T00:00:00~1|2~Shelley Askem~1992-12-07T00:00:00~0

DbCsvResult supports both Minimal APIs (IResult) and MVC (ActionResult).

In a Minimal API, return it directly from a route handler:

app.MapGet("/employees", (int departmentId) =>
    new DbCsvResult("select * from Employees where DepartmentId = @id", ("id", departmentId)));

In ASP.NET Core MVC, return DbCsvResult directly from a controller action:

public IActionResult Employees(int departmentId)
    => new DbCsvResult("select * from Employees where DepartmentId = @id", ("id", departmentId));

DbCsvResult uses Db.ConnectionString and executes the query when ASP.NET Core processes the result. It streams the first result set as text/csv; charset=utf-8, preserves the custom format below, and returns an empty body when there are no rows. Request cancellation is passed to database operations and response writes. Use isStoredProc: true for stored procedures. The library references the Microsoft.AspNetCore.App shared framework, which must be available in consuming applications.

Please note that the standard comma and new line characters have been replaced by tilde (~) and vertical line (|) respectively in order to avoid conflicts with typical texts.

Moreover, column names have been flagged with the following type markers:

Parse the returned CSV with @nuell/db. Both parseCsv and mapFromCsv convert # fields to JavaScript Date objects; invalid, missing, or null date values become null.

import { parseCsv, mapFromCsv } from '@nuell/db';

interface Employee {
    Id: number;
    FullName: string;
    BirthDate: Date | null;
    IsMarried: boolean;
}

const employees = parseCsv<Employee>(csv);
const employeesById = mapFromCsv<Employee>(csv, 'Id');

parseCsv(csv) returns an array. mapFromCsv(csv, keyColumn?) returns a map keyed by the first column unless a column name or index is supplied. Neither function accepts parsing options or a row callback; there is no Unix timestamp mode.

Offset-free date/time strings are interpreted in the JavaScript runtime's local timezone. Explicit offsets identify an instant, but JavaScript Date does not retain the original offset and preserves only millisecond precision.

Json

Converts the query result to JSON. Db.Json and DbJsonResult serialize dates as ISO 8601 strings, matching CSV: DateTime has no timezone suffix, and DateTimeOffset retains its explicit offset. Both omit fractional seconds without rounding or timezone conversion. Browser JSON parsing leaves these values as strings. Calling new Date(value) interprets offset-free date/time strings in the browser's local timezone; keep them as strings to preserve wall-clock semantics.

string json = await Db.Json($"select * from Customers where Id={id}");
//{"Id":1,"FullName":"Loraine Bickerdicke","BirthDate":"1994-08-22T00:00:00","IsMarried":true}

The default result is a JSON object. However, using an optional parameter you may require a JSON array result.

string json = await Db.Json($"select Id, Age from Customers", JsonValueType.Array);
//[{"Id":1,"Age":24},{"Id":2,"Age":36},{"Id":3,"Age":31}]

DbJsonResult supports both Minimal APIs (IResult) and MVC (ActionResult).

In a Minimal API, return it directly from a route handler:

app.MapGet("/customers", () =>
    new DbJsonResult("select Id, Age from Customers", JsonValueType.Array));

In ASP.NET Core MVC, return DbJsonResult directly from a controller action:

public IActionResult Customers()
    => new DbJsonResult("select Id, Age from Customers", JsonValueType.Array);

DbJsonResult uses Db.ConnectionString and executes the query when ASP.NET Core processes the result. It streams the result set as application/json; charset=utf-8 directly to the response body, honoring request cancellation.

JsonObject

Converts one data row to System.Text.Json.Nodes.JsonObject.

JsonObject jobj = await Db.JsonObject($"select * from Customers where Id={id}");

Table

Converts the query result to System.Data.DataTable. For example:

DataTable employees = await Db.Table("select * from Employees");

List<T>

Converts a one-field query result to System.Collections.Generic.List<T>. For example:

List<int> idList = await Db.List<int>("select Id from Employees");

StrList

Converts a one-field query result to List<string>. For example:

List<string> names = await Db.StrList("select FullName from Employees");

Object<T>

Converts a one-row query result to an object of the class T. For example:

Employee employee = await Db.Object<Employee>($"select * from Employees where Id={id}");

The names and types of the class properties must match the query fields. Query field name matching is case-sensitive.

ObjList<T>

Converts the query result to a System.Collections.Generic.List<T>, where T is a class. For example:

List<Employee> employeeList = await Db.ObjList<Employee>("select * from Employees");

The names and types of the class properties must match the query fields. Query field name matching is case-sensitive.

Dictionary<K, V>

Converts a two-field query to a System.Collections.Generic.Dictionary<K, V>. For example:

Dictionary<int, string> cities = await Db.Dictionary<int, string>("select ZipCode, City from Addresses");

Str

Returns one string value. For example:

string s = await Db.Str($"select FullName from Employees where Id={id}");

Val<T>

Returns one value of the primitive type T.

int c = await Db.Val<int>("select count(1) from Employees");

Values

Returns all the fields of all the rows as a boxed System.Object array. For example:

object[] values = await Db.Values($"select Id, BirthDate from Employees where Id={id}; select count(1) from Customers");

int id = (int)values[0];
DateTime birth = (DateTime)values[1];
int count = (int)values[2];

Json with multiple result sets

For multiple result sets, pass an array of (string Name, JsonValueType ResultType) tuples as the result parameter of Db.Json. A single JsonValueType.Object or JsonValueType.Array keeps the single-result behavior.

It receives a tuple array that specifies the label and type of each result and returns a JSON object. For example:

string query = "select count(1) from Employees;"
    + "select * from Employees where Id=1;"
    + "select * from Employees;"
    + "select * from Customers";

var resultTypes = new [] {
    ("employeeCount", JsonValueType.Value),
    ("oneEmployee", JsonValueType.Object),
    ("employeeCsv", JsonValueType.Csv),
    ("customersArray", JsonValueType.Array),
};

string json = await Db.Json(query, result: resultTypes);
//{"employeeCount":1200,"oneEmployee":{...},"employeeCsv":"...","customersArray":[...]}

The returned JSON object in the above example includes 4 properties, mapped to result sets in tuple-array order. Empty result sets become null, including those marked Array; a single-result JsonValueType.Array returns [] when empty.

In a Minimal API, return it directly from a route handler:

app.MapGet("/report", () =>
    new DbJsonResult(query, result: resultTypes));

In ASP.NET Core MVC, return DbJsonResult directly from a controller action:

public IActionResult Report()
    => new DbJsonResult(query, result: resultTypes);

DbJsonResult uses Db.ConnectionString and executes the query when ASP.NET Core processes the result. It streams the combined result sets as application/json; charset=utf-8 directly to the response body, honoring request cancellation.

Execute

Executes a query and returns the number of affected rows.

int rows = await Db.Execute("update Customers set Balance=0 where Balance>0");

Delete

Deletes a record with the specified Id field from the given table and returns a boolean value to report the success of the operation.

bool success = await Db.Delete(5, "Customers");

Transaction

Executes queries within a database transaction. A single query is executed as an intact batch without text splitting, and multiple commands can be passed as explicit collections for per-command affected row counts and parameter support.

Execute an intact batch with parameters:

int[] rows = await Db.Transaction("delete from Orders where CustomerId=@id; delete from Customers where Id=@id;", ("@id", id));

Execute explicit command collections with parameters:

int[] rows = await Db.Transaction(
    ("delete from Orders where CustomerId=@id", [("@id", id)]),
    ("delete from Customers where Id=@id", [("@id", id)])
);

Or execute an enumerable of queries:

int[] rows = await Db.Transaction([query1, query2]);

Save

Saves a JsonElement, JsonObject, JsonNode, or an object of type <T> to the specified table and returns the identity of the saved record.

The JsonElement, JsonObject, JsonNode, or <T> object data must include an identity property specified in the case-sensitive idProp parameter (default is "Id"), and the target table must contain an identity primary key with the same name.

If the value of the identity property is zero, it will be ignored and the rest of the properties will be inserted into the table, returning the newly created identity. Otherwise, the record with the specified identity will be updated, returning the record's identity if a matching record was updated, or 0 if no matching record was found.

All the properties must match the table fields.

var employee = JsonNode.Parse("{ \"Id\": 0, \"FullName\": \"Shelley Askem\", \"Age\": 34, \"Balance\": 1520 }");
int id = await Save(employee, "Employees");

var employee = new Employee { Id = 0, FullName = "Shelley Askem", Age = 34, Balance = 1520 };
int id = await Save(employee, "Employees");

Insert

Inserts JsonElement, JsonObject, or JsonNode data into the specified table. All the properties must match the table fields.

var employee = JsonNode.Parse("{\"FullName\":\"Shelley Askem\",\"Age\":34, \"Balance\":1520}");
await Insert(employee, "Employees");

Update

Updates a row in the spcified table with JsonElement, JsonObject, or JsonNode data. The primary key field must be passed. All the properties must match the table fields.

var employee = JsonNode.Parse("{\"Id\":3,\"FullName\":\"Shelley Askem\",\"Age\":34, \"Balance\":1520}");
await Update(employee, "Employees", "Id");

NewItem

Returns a JSON value containing a new record from the specified table.

Default values of the table fields will be respected. If a table field has no default value, the values for nullable, numeric, boolean, and string fields will be null, 0, false, and empty string, respectively.

string json = await Db.NewItem("Employees");
//returns e.g. { "Id": 0, "FullName": "", "Married": false, "Address": null }

The returned value may be used to initialise a reactive front-end form.