Sphere10.Framework.Data 3.0.2

There is a newer version of this package available.
See the version list below for details.
dotnet add package Sphere10.Framework.Data --version 3.0.2
                    
NuGet\Install-Package Sphere10.Framework.Data -Version 3.0.2
                    
This command is intended to be used within the Package Manager Console in Visual Studio, as it uses the NuGet module's version of Install-Package.
<PackageReference Include="Sphere10.Framework.Data" Version="3.0.2" />
                    
For projects that support PackageReference, copy this XML node into the project file to reference the package.
<PackageVersion Include="Sphere10.Framework.Data" Version="3.0.2" />
                    
Directory.Packages.props
<PackageReference Include="Sphere10.Framework.Data" />
                    
Project file
For projects that support Central Package Management (CPM), copy this XML node into the solution Directory.Packages.props file to version the package.
paket add Sphere10.Framework.Data --version 3.0.2
                    
#r "nuget: Sphere10.Framework.Data, 3.0.2"
                    
#r directive can be used in F# Interactive and Polyglot Notebooks. Copy this into the interactive tool or source code of the script to reference the package.
#:package Sphere10.Framework.Data@3.0.2
                    
#:package directive can be used in C# file-based apps starting in .NET 10 preview 4. Copy this into a .cs file before any lines of code to reference the package.
#addin nuget:?package=Sphere10.Framework.Data&version=3.0.2
                    
Install as a Cake Addin
#tool nuget:?package=Sphere10.Framework.Data&version=3.0.2
                    
Install as a Cake Tool

💾 Sphere10.Framework.Data

Universal data access abstraction layer providing ADO.NET enhancements, schema management, transaction scopes, and database-agnostic persistence.

Sphere10.Framework.Data enables vendor-independent database access through a unified API while supporting SQLite, SQL Server, Firebird, and NHibernate. Core abstractions handle connection pooling, transactions, type mapping, and bulk operations.

⚡ 10-Second Example

using Sphere10.Framework.Data;

// SQLite in-memory database
var dac = Tools.Sqlite.Open(":memory:");

// Insert records
dac.Insert("Users", new[] {
    new ColumnValue("ID", 1),
    new ColumnValue("Name", "Alice")
});

// Query with parameters (safe from injection)
dac.ExecuteScalar<int>("SELECT COUNT(*) FROM Users WHERE Name = @name",
    new ColumnValue("@name", "Alice"));  // Returns 1

// Transactional scope
using (var scope = dac.BeginTransactionScope()) {
    dac.Update("Users", new[] { new ColumnValue("Name", "Alice Smith") }, "WHERE ID = 1");
    scope.Commit();  // Atomic persist
}

🏗️ Core Concepts

Data Access Context (DAC): Wrapper around IDbConnection providing transaction scoping, parameterized queries, and connection reuse.

Connection Pooling: Connections are automatically managed and reused within a scope for efficiency.

Column Values: Named parameter approach using ColumnValue objects prevents SQL injection.

Transaction Scopes: Atomic boundaries for ACID operations; auto-rollback on exception.

🔧 Core Examples

Connection & Basic Operations

using Sphere10.Framework.Data;

// Open SQLite database
var dac = Tools.Sqlite.Open("mydata.db");

// Create and open connection
using (var conn = dac.CreateOpenConnection()) {
    var state = conn.State;  // ConnectionState.Open
}

// Create unopened connection (for lazy initialization)
using (var conn = dac.CreateConnection()) {
    var state = conn.State;  // ConnectionState.Closed
}

Insert & Retrieve

using Sphere10.Framework.Data;

var dac = Tools.Sqlite.Open(":memory:");

// Insert with named columns
dac.Insert("BasicTable", new[] {
    new ColumnValue("ID", 1),
    new ColumnValue("Text", "Hello World")
});

// Read all records
var results = dac.ExecuteQuery("SELECT * FROM BasicTable");

// Count records with parameterized safety
int count = dac.ExecuteScalar<int>(
    "SELECT COUNT(*) FROM BasicTable WHERE Text = @text",
    new ColumnValue("@text", "Hello World")
);  // Returns 1

Transactions with Scopes

var dac = Tools.Sqlite.Open(":memory:");

// Create table
dac.ExecuteNonQuery(@"CREATE TABLE Accounts (
    ID INTEGER PRIMARY KEY,
    Name TEXT,
    Balance REAL
)");

// Transactional scope ensures atomicity
using (var scope = dac.BeginTransactionScope()) {
    dac.Insert("Accounts", new[] {
        new ColumnValue("ID", 1),
        new ColumnValue("Name", "Alice"),
        new ColumnValue("Balance", 1000.0)
    });
    
    dac.Insert("Accounts", new[] {
        new ColumnValue("ID", 2),
        new ColumnValue("Name", "Bob"),
        new ColumnValue("Balance", 500.0)
    });
    
    // Transfer: withdraw from Alice
    dac.Update("Accounts",
        new[] { new ColumnValue("Balance", 900.0) },
        "WHERE ID = 1");
    
    // Transfer: deposit to Bob
    dac.Update("Accounts",
        new[] { new ColumnValue("Balance", 600.0) },
        "WHERE ID = 2");
    
    // Commit atomically (rollback auto-occurs on exception)
    scope.Commit();
}

// Verify final state
var aliceBalance = dac.ExecuteScalar<double>(
    "SELECT Balance FROM Accounts WHERE ID = 1");
Console.WriteLine($"Alice: {aliceBalance}");  // 900.0

Multi-Database Support

// SQLite (embedded, in-memory)
var sqliteDac = Tools.Sqlite.Open(":memory:");

// SQL Server
var sqlServerDac = Tools.MSSQL.Open(
    "Server=localhost;Database=mydb;Trusted_Connection=true");

// Firebird
var firebirdDac = Tools.Firebird.Open(
    "ServerType=1;DataSource=localhost;Database=mydb.fdb;User=sysdba;Password=masterkey");

// All use same DAC interface
sqliteDac.ExecuteScalar("SELECT COUNT(*) FROM Users");
sqlServerDac.ExecuteScalar("SELECT COUNT(*) FROM Users");
firebirdDac.ExecuteScalar("SELECT COUNT(*) FROM Users");

Type Mapping & GUIDs

var dac = Tools.Sqlite.Open(":memory:");

// Define table with GUID column
dac.ExecuteNonQuery(@"CREATE TABLE [Items] (
    ID INTEGER PRIMARY KEY,
    UniqueKey UNIQUEIDENTIFIER NOT NULL
)");

// Write GUID
var guid = Guid.NewGuid();
dac.Insert("Items", new[] {
    new ColumnValue("ID", 1),
    new ColumnValue("UniqueKey", guid)
});

// Read GUID back (automatic type conversion)
var retrieved = dac.ExecuteScalar<Guid>(
    "SELECT UniqueKey FROM Items WHERE ID = 1");

Console.WriteLine($"Written: {guid}");
Console.WriteLine($"Retrieved: {retrieved}");
Console.WriteLine($"Match: {guid == retrieved}");  // true

Parameterized Queries (SQL Injection Safe)

var dac = Tools.Sqlite.Open(":memory:");

dac.ExecuteNonQuery(@"CREATE TABLE [Users] (
    ID INTEGER PRIMARY KEY,
    Name TEXT,
    Email TEXT
)");

dac.Insert("Users", new[] {
    new ColumnValue("ID", 1),
    new ColumnValue("Name", "Alice"),
    new ColumnValue("Email", "alice@example.com")
});

// Safe parameterized query - prevents SQL injection
string userInput = "Alice"; // Could be malicious
var results = dac.ExecuteQuery(
    "SELECT * FROM Users WHERE Name = @name",
    new ColumnValue("@name", userInput));

// Multiple parameters
results = dac.ExecuteQuery(
    "SELECT * FROM Users WHERE Name = @name AND Email = @email",
    new ColumnValue("@name", "Alice"),
    new ColumnValue("@email", "alice@example.com"));

📊 Database-Specific Implementations

Sphere10.Framework.Data provides database-specific projects with optimized implementations:

Corresponding WinForms projects available for GUI applications:

🔧 Usage

📦 Dependencies

  • Sphere10 Framework: Core framework
  • System.Data: ADO.NET abstraction (.NET built-in)
  • Database-specific drivers: Installed by platform-specific packages (SQLite, SQL Server, Firebird, NHibernate)

⚠️ Best Practices

  • Always use using statements for scopes to ensure proper connection cleanup and transaction handling
  • Use ColumnValue for all parameters to prevent SQL injection
  • Keep transaction scopes small - only operations that must be atomic
  • Parameterized queries only - never concatenate user input into SQL strings
  • Handle exceptions properly - transaction scopes auto-rollback on exception; explicit Commit() required for success
  • Test with multiple databases - Sphere10.Framework.Data is database-agnostic, but specific DBMS edge cases may exist

🧬 Architecture Layers

  • ADO.NET Extensions: IDbConnection, IDbCommand, IDbReader helpers
  • DAC (Data Access Context): Transaction and connection management (IDataAccessContext)
  • Schema Support: Database introspection and DDL operations
  • Type Mapping: CLR ↔ Database type conversion
  • Bulk Operations: Efficient batch insert/update patterns
  • CSV/XML Support: Data import/export utilities

🛠️ Tools.* Namespace

This project extends the global Tools namespace with database-specific utilities:

Tools.Data

Generic database operations across all providers.

var connection = Tools.Data.CreateConnection(connectionString);
var adapter = Tools.Data.CreateDataAdapter(connection);

Tools.Sqlite (via Sphere10.Framework.Data.Sqlite)

SQLite-specific utilities.

var dac = Tools.Sqlite.Open(":memory:");
var dac = Tools.Sqlite.Create(filename, pageSize: 4096);
Tools.Sqlite.Drop(Tools.Sqlite.GetFilePathFromConnectionString(connStr));
bool exists = Tools.Sqlite.Exists(connectionString);

Tools.MSSql (via Sphere10.Framework.Data.MSSQL)

SQL Server-specific utilities.

var adapter = Tools.MSSql.CreateAdapter(connectionString);
var connection = Tools.MSSql.CreateConnection(connectionString);

Tools.Json & Tools.Xml

Data format conversion utilities.

string json = Tools.Json.Serialize(data);
var deserialized = Tools.Json.Deserialize<T>(json);

string xml = Tools.Xml.Serialize(data);
var deserialized = Tools.Xml.Deserialize<T>(xml);

For complete Tools reference, see docs/Tools-Reference.md

🔌 Extensibility

Implement IDataAccessContext to create custom DAC implementations for unsupported databases or specialized persistence needs. All database-specific projects (SQLite, MSSQL, Firebird) extend the abstract DataAccessContext base class.

⚡ Performance Considerations

  • Connection Pooling: Reuse connections within transaction scopes for efficiency
  • Batch Operations: Use bulk insert patterns for large datasets
  • Parameterized Queries: Cached query plans improve execution speed
  • Type Mapping: Constant-size types (int, long, GUID) faster than variable-size (string, byte[])
  • Database Selection: SQLite optimal for single-process/embedded; MSSQL/Firebird for multi-user/distributed

✅ Status & Compatibility

  • Maturity: Production-tested, core abstraction stable
  • .NET Target: .NET 8.0+ (primary), .NET Standard 2.0 compatibility for some components
  • Thread Safety: Each DAC instance should be used single-threaded; create separate instances for concurrent access
  • Backward Compatibility: Database schemas may require migration between Sphere10 Framework versions

⚖️ License

Distributed under the MIT NON-AI License.

See the LICENSE file for full details. More information: Sphere10 NON-AI-MIT License

👤 Author

Herman Schoenfeld - Software Engineer

Product Compatible and additional computed target framework versions.
.NET net8.0 is compatible.  net8.0-android was computed.  net8.0-browser was computed.  net8.0-ios was computed.  net8.0-maccatalyst was computed.  net8.0-macos was computed.  net8.0-tvos was computed.  net8.0-windows was computed.  net9.0 was computed.  net9.0-android was computed.  net9.0-browser was computed.  net9.0-ios was computed.  net9.0-maccatalyst was computed.  net9.0-macos was computed.  net9.0-tvos was computed.  net9.0-windows was computed.  net10.0 was computed.  net10.0-android was computed.  net10.0-browser was computed.  net10.0-ios was computed.  net10.0-maccatalyst was computed.  net10.0-macos was computed.  net10.0-tvos was computed.  net10.0-windows was computed. 
Compatible target framework(s)
Included target framework(s) (in package)
Learn more about Target Frameworks and .NET Standard.

NuGet packages (10)

Showing the top 5 NuGet packages that depend on Sphere10.Framework.Data:

Package Downloads
Sphere10.Framework.Application

Application framework and lifecycle management for Sphere10 Framework-based apps. Provides dependency injection integration, settings persistence, command-line argument parsing, product/version metadata, and builder-based startup/shutdown hooks.

Sphere10.Framework.Windows.Forms

Windows Forms UI framework and component library for building desktop applications with Sphere10 Framework. Provides reusable WinForms controls, dialogs, layout/helpers, and common UI utilities intended to integrate cleanly with the framework's application and data layers.

Sphere10.Framework.Data.MSSQL

Microsoft SQL Server provider for Sphere10.Framework.Data enabling vendor-independent access through the shared DAC abstractions. Includes SQL Server-specific helpers and Tools.MSSQL utilities for opening connections and working with the provider.

Sphere10.Framework.Data.Firebird

Firebird provider for Sphere10.Framework.Data enabling vendor-independent access through the shared DAC abstractions. Provides FirebirdDAC and the GUID-correcting FirebirdCorrectingReader, with SQL text from the FirebirdSQLBuilder in Sphere10.Framework.Data.

Sphere10.Framework.Data.Sqlite

SQLite provider for Sphere10.Framework.Data enabling embedded and in-memory databases through the shared DAC abstractions. Includes SQLite-specific helpers and Tools.Sqlite utilities for opening connections and working with the provider.

GitHub repositories

This package is not used by any popular GitHub repositories.

Version Downloads Last Updated
3.2.0-ci.163.43664782937.1 62 11/15/2026
3.2.0-ci.160.43660265392.1 63 11/15/2026
3.2.0-ci.159.43537748063.1 73 11/14/2026
3.2.0-ci.158.43536665157.1 75 11/14/2026
3.2.0-ci.157.43350632870.1 90 11/13/2026
3.2.0-ci.156.43233095581.1 89 11/12/2026
3.1.2 337 9/9/2026
3.1.1 256 9/8/2026
3.1.0 232 9/7/2026
3.0.3 322 1/6/2026
3.0.2 261 1/2/2026
3.0.1 267 1/2/2026
3.0.0 259 1/2/2026

Initial public release