Starb.DataLibrary 2.0.0.26263

Starb.DataLibrary

Provider-agnostic ADO.NET data access for .NET Framework and .NET, plus the reflection, logging and resource helpers the rest of the Starbook libraries build on.

One API works against SQL Server, OLE DB, ODBC, Oracle and Firebird: the configured provider decides parameter syntax (@Name, :Name or ?), identifier delimiters and the provider type enum, so call sites do not change when the database does.

Target frameworks: net461, net8.0-windows · Root namespace: DataLibraryStarb

Install

dotnet add package Starb.DataLibrary

Configure

The provider comes from a named connection string, ConnectionString by default:

<connectionStrings>
  <add name="ConnectionString"
       connectionString="Data Source=(local);Initial Catalog=Sales;Integrated Security=True"
       providerName="System.Data.SqlClient" />
</connectionStrings>

DataBase.ConnectionStringToRead = "ConnectionString";   // pick another entry
DataBase.ConnectionString = new ConnectionStringSettings(...);  // or set one directly
DataBase.CommandTimeOut = 60;
DataBase.ReuseConnection = true;                        // keep one connection per session

{APP_PATH} in a connection string is expanded to the application directory, which is convenient for file-based databases.

Query and update

// Scalars, readers and DataSets; {0}, {1}, … become real parameters, never string concatenation.
var total = DbConvert.ToInt32(ScalarUtility.GetScalar("SELECT COUNT(*) FROM Customer"));

using var reader = DataReaderUtility.GetDataReader(
    "SELECT Id, Name FROM Customer WHERE CountryId = {0}", countryId);

var orders = DataSetUtility.GetDataSet("SELECT * FROM Orders WHERE Date >= {0}", since);

var affected = SetUtility.SetData("DELETE FROM Cart WHERE SessionId = {0}", sessionId);

DataBase.BeginTransaction();
try
{
    SetUtility.SetData("UPDATE Account SET Balance = Balance - {0} WHERE Id = {1}", amount, from);
    SetUtility.SetData("UPDATE Account SET Balance = Balance + {0} WHERE Id = {1}", amount, to);
    DataBase.CommitTransaction();
}
catch
{
    DataBase.RollBackTransaction();
    throw;
}

Build statements from row state

CommandBuilder turns "old values / new values" into parameterised INSERT, UPDATE, DELETE and SELECT statements — the shape a grid or a data-bound form already has. It honours optimistic locking, identity columns, per-column save expressions and value generators.

string? sql = null;
DbParameter?[]? parameters = null;

CommandBuilder.GetSqlRowUpdate(
    "Customer", newValues, oldValues,
    useOptimisticLocking: false,
    ref sql, ref parameters,
    primaryKeys: new[] { "Id" },
    visualizations: new Dictionary<string, bool> { ["Id"] = true, ["Name"] = true },
    decodes: new Dictionary<string, string?>(),
    isCounterField: new Dictionary<string, bool> { ["Id"] = true, ["Name"] = false });

// UPDATE Customer SET Name = @NewName WHERE Id = @Id

TableUtil builds the paging predicates that go with it — MakeWhereFw, MakeWhereBw, MakeWhereStand walk a composite key forwards, backwards and from the current row.

Schema

string?[]? fields = null; Type?[]? types = null; string?[]? keys = null;
int[]? sizes = null; string?[]? unique = null;

SchemaUtility.GetSchema("Customer", ref fields, ref types, ref keys, ref sizes, ref unique);

SchemaUtility.CheckTableExistance("Customer");
SchemaUtility.CheckPrimaryKeyExistance("Customer");

var create = DatabaseTypes.BuildCreateStatement("Customer", new List<TableField>
{
    new() { FieldName = "Id",   FieldType = DatabaseTypes.IntType },
    new() { FieldName = "Name", FieldType = DatabaseTypes.StringType, StringSize = 50, AllowNull = true }
});

Schema lookups are cached in SchemaUtility.DbCache; clear it after a DDL change.

What else is in the box

Running inside a web application

The library never references ASP.NET. HttpContextHelper.Current takes any implementation of IHttpContext; set it once at start-up and connections, transactions, session values and DataSet caches move from static fields to per-request state:

HttpContextHelper.Current = new MyAspNetHttpContext(HttpContext.Current);

Leave it null in desktop and console applications.

Notes

• Configuration lives in static state, so it is process-wide: set the provider once at start-up.
• DbConvert returns a default (0, false, DateTime.MinValue) instead of throwing, which suits reading loosely typed database values.
• DataBase.DbError holds the last provider exception after a failed call.

License

Copyright (c) Piero Viano. All rights reserved. — Librerie Starbook