Terminalogic.Essentials 1.0.3

Terminalogic.Essentials

A lightweight .NET 8 library providing flexible SQL data access and utility helpers with support for multiple parameter formats.

🚀 Installation

dotnet add package Terminalogic.Essentials

Or via NuGet Package Manager:

Install-Package Terminalogic.Essentials

⚡ Quick Start

1. Configure Services

Add to your Program.cs:

using Terminalogic;

builder.Services.AddSingleton<Data>();
builder.Services.AddSingleton<Helpers>();

2. Configuration Settings

Add to appsettings.json:

{
  "ConnectionStrings": {
    "ConnectionString": "Server=localhost;Database=MyDB;Trusted_Connection=true;"
  }
}

📊 Data Class - Flexible SQL Data Access

The Data class provides a simple, flexible way to interact with SQL Server databases using two parameter formats.

Parameter Formats Supported

1️⃣ Sequential Parameters (?)

Clean and simple for straightforward queries:

data.Read("SELECT * FROM Users WHERE Email = ?", "test@example.com");

2️⃣ Named Parameters (@paramName)

Self-documenting and order-independent for complex queries:

data.Read("SELECT * FROM Users WHERE Email = @email AND Age > @age", 
          "email", "test@example.com", 
          "age", 18);

⚠️ Cannot mix formats - Use either ? OR @paramName in a query, not both.

📖 Read - Query Data (Returns DataTable)

Simple Query:

@inject Data data

// Sequential
var users = data.Read("SELECT * FROM Users WHERE Status = ?", "active");

// Named
var users = data.Read("SELECT * FROM Users WHERE Status = @status", 
                      "status", "active");

Multiple Parameters:

// Sequential - order matters
var orders = data.Read(
    "SELECT * FROM Orders WHERE UserId = ? AND Status = ? AND Total > ?", 
    123, "shipped", 100.50
);

// Named - order doesn't matter
var orders = data.Read(
    "SELECT * FROM Orders WHERE UserId = @userId AND Status = @status AND Total > @total",
    "status", "shipped",      // Any order!
    "total", 100.50,
    "userId", 123
);

Complex Queries:

// Named parameters are more readable for complex queries
var products = data.Read(@"
    SELECT p.*, c.CategoryName 
    FROM Products p
    INNER JOIN Categories c ON p.CategoryId = c.Id
    WHERE p.Price > @minPrice 
      AND p.Stock > @minStock 
      AND c.Name = @category
    ORDER BY p.Price DESC",
    "minPrice", 50.00,
    "minStock", 10,
    "category", "Electronics"
);

Stored Procedures:

// Sequential parameters
var results = data.Read("EXEC GetUsersByAge", 18, "active");

// Or with EXECUTE
var results = data.Read("EXECUTE dbo.SearchProducts", "Electronics", 50.00);

No Parameters:

var allUsers = data.Read("SELECT * FROM Users");

✍️ Write - Insert/Update/Delete (Returns Row Count)

The Write method now supports unlimited parameters and returns the number of rows affected!

Insert with Sequential Parameters:

int rowsInserted = data.Write(
    "INSERT INTO Users (Name, Email, Age, Status) VALUES (?, ?, ?, ?)", 
    "John Doe", "john@example.com", 25, "active"
);
// rowsInserted = 1

Insert with Named Parameters:

int rowsInserted = data.Write(
    "INSERT INTO Users (Name, Email, Age, Status) VALUES (@name, @email, @age, @status)",
    "name", "Jane Smith",
    "email", "jane@example.com",
    "age", 30,
    "status", "active"
);

Update:

// Sequential
int rowsUpdated = data.Write(
    "UPDATE Users SET Status = ?, LastModified = ? WHERE Id = ?", 
    "inactive", DateTime.UtcNow, 123
);

// Named
int rowsUpdated = data.Write(
    "UPDATE Users SET Status = @status, LastModified = @modified WHERE Id = @id",
    "status", "inactive",
    "modified", DateTime.UtcNow,
    "id", 123
);

Delete:

int rowsDeleted = data.Write(
    "DELETE FROM Users WHERE Id = ? AND Status = ?", 
    123, "inactive"
);

Complex Update with Many Parameters (No Limit!):

int rows = data.Write(@"
    UPDATE UserProfile 
    SET FirstName = @fname, 
        LastName = @lname, 
        Email = @email, 
        Phone = @phone, 
        Address = @addr, 
        City = @city, 
        State = @state, 
        Zip = @zip,
        Country = @country,
        UpdatedAt = @updated
    WHERE UserId = @id",
    "fname", "John",
    "lname", "Doe",
    "email", "john@example.com",
    "phone", "555-1234",
    "addr", "123 Main St",
    "city", "Springfield",
    "state", "IL",
    "zip", "62701",
    "country", "USA",
    "updated", DateTime.UtcNow,
    "id", 123
);
// 11 parameters - no problem! 🚀

Stored Procedures:

int rows = data.Write("EXEC UpdateUserStatus", 123, "active", DateTime.UtcNow);

🔢 ReadOne<T> - Get Single Value

Read a single scalar value with type safety:

// Count
var userCount = data.ReadOne<int>(
    "SELECT COUNT(*) FROM Users WHERE Status = @status", 
    null, null, 
    "status", "active"
);

// Max/Min
var maxPrice = data.ReadOne<decimal>(
    "SELECT MAX(Price) FROM Products WHERE Category = @cat", 
    null, null, 
    "cat", "Electronics"
);

// Single value
var userName = data.ReadOne<string>(
    "SELECT Name FROM Users WHERE Id = @id", 
    null, null, 
    "id", 123
);

// Scalar function
var generatedKey = data.ReadOne<string>(
    "SELECT dbo.GenerateKey(@length)", 
    null, null, 
    "length", 12
);

📝 Note: ReadOne currently only supports named parameters. Pass null, null for connection and command to auto-create.

🔌 Other Methods

Test Connection:

if (data.TestConnection(out string? error, timeoutSeconds: 5))
{
    Console.WriteLine("✅ Connected to database!");
}
else
{
    Console.WriteLine($"❌ Connection failed: {error}");
}

Get Raw Connection/Command (Advanced):

var connection = data.GetConnection();
var command = data.GetCommand(connection);
// Use for advanced scenarios

🔧 Helpers Class - Utility Methods

Generate Business ID

@inject Helpers helpers

string businessId = helpers.GenerateBusinessId();
// Returns: "A1B2C3D4E5F6" (12-character uppercase alphanumeric)

Generate Random Key

string randomKey = helpers.GenerateRandomKey(16);
// Returns: "X9K2M7P4Q1L8N3R5" (16-character uppercase alphanumeric)

string apiKey = helpers.GenerateRandomKey(32);
// Returns: 32-character random key

🛡️ Security Notes

• Data Class: Always use parameterized queries (either ? or @paramName format) to prevent SQL injection.
• Connection Strings: Store connection strings securely (e.g., Azure Key Vault, user secrets).
• Validation: Always validate and sanitize user input before passing to database methods.

📦 Publishing to NuGet

To build and publish this package:

# Build in Release mode
dotnet build -c Release

# Pack the project (creates .nupkg in bin/Release)
dotnet pack -c Release

# Push to NuGet.org (replace YOUR_API_KEY and version)
dotnet nuget push bin/Release/Terminalogic.Essentials.1.0.3.nupkg --api-key YOUR_API_KEY --source https://api.nuget.org/v3/index.json

📄 License

MIT License - See LICENSE file for details

🤝 Contributing

Contributions are welcome! Please feel free to submit issues and pull requests. // Returns: 32-character random key

---

## 📋 Models

### UserRecord

```csharp
public class UserRecord
{
    public int Id { get; set; }
    public string? BusinessId { get; set; }
    public string Email { get; set; }
    public string? FirstName { get; set; }
    public string? LastName { get; set; }
    public byte[]? PasswordHash { get; set; }
    public byte[]? PasswordSalt { get; set; }
    public int PasswordIterations { get; set; }
    public string? PasswordAlgorithm { get; set; }
    public bool IsActive { get; set; }
    public DateTime CreatedAtUtc { get; set; }
    public DateTime? UpdatedAtUtc { get; set; }
    public DateTime? LastLoginAtUtc { get; set; }
    public string? AccessType { get; set; }
}

PasswordResetTokenRecord

public class PasswordResetTokenRecord
{
    public Guid Id { get; set; }
    public int UserId { get; set; }
    public byte[] TokenHash { get; set; }
    public DateTime ExpiresAtUtc { get; set; }
    public DateTime CreatedAtUtc { get; set; }
    public DateTime? RedeemedAtUtc { get; set; }
}

🗄️ Database Schema Requirements

The Id class expects these tables (customize column names via your SQL queries):

Users Table

CREATE TABLE Users (
    Id INT PRIMARY KEY IDENTITY(1,1),
    BusinessId NVARCHAR(50),
    Email NVARCHAR(255) UNIQUE NOT NULL,
    FirstName NVARCHAR(100),
    LastName NVARCHAR(100),
    PasswordHash VARBINARY(256) NOT NULL,
    PasswordSalt VARBINARY(256) NOT NULL,
    PasswordIterations INT NOT NULL,
    PasswordAlgorithm NVARCHAR(50) NOT NULL,
    IsActive BIT NOT NULL,
    CreatedAtUtc DATETIME2 NOT NULL,
    UpdatedAtUtc DATETIME2,
    LastLoginAtUtc DATETIME2,
    UserType NVARCHAR(50)
);

PasswordResetTokens Table

CREATE TABLE PasswordResetTokens (
    Id UNIQUEIDENTIFIER PRIMARY KEY,
    UserId INT NOT NULL,
    TokenHash VARBINARY(256) NOT NULL,
    ExpiresAtUtc DATETIME2 NOT NULL,
    CreatedAtUtc DATETIME2 NOT NULL,
    RedeemedAtUtc DATETIME2,
    CONSTRAINT FK_PasswordResetTokens_Users 
        FOREIGN KEY (UserId) REFERENCES Users(Id)
);

💡 Tip: You can use different table/column names! Just modify the SQL queries you pass to each method.

🔒 Security Features

• ✅ SQL Injection Protection - All queries use parameterized commands
• ✅ PBKDF2-SHA256 - Industry-standard password hashing
• ✅ 100,000 Iterations - Strong key derivation (adjustable)
• ✅ Timing-Attack Protection - Constant-time password comparison
• ✅ Secure Token Generation - Cryptographically random tokens
• ✅ SHA-256 Token Hashing - Tokens stored as hashes, not plain text
• ✅ Token Expiration - Configurable token lifetimes
• ✅ One-Time Tokens - Tokens can only be redeemed once

⚠️ Important Notes

Parameter Format Rules

1. Cannot mix formats - Use ? OR @paramName, not both in one query
2. Sequential (?) - Simple, clean, order-dependent
3. Named (@paramName) - Self-documenting, order-independent, reusable
4. Validation - Automatic mismatch detection with clear error messages

When to Use Each Format

Use Sequential (?) for:

• Simple queries with 1-3 parameters
• Quick prototypes
• When parameter order is obvious

Use Named (@paramName) for:

• Complex queries with 3+ parameters
• Queries where parameters are reused
• Production code (better maintainability)
• Team projects (self-documenting)

💡 Best Practices

1. ✅ Use named parameters for production code
2. ✅ Store SQL queries in constants or resource files
3. ✅ Use dependency injection for service management
4. ✅ Store configuration in appsettings.json
5. ✅ Test connection before running queries
6. ✅ Handle exceptions appropriately
7. ✅ Use transactions for multi-statement operations
8. ✅ Validate input before passing to queries

📦 What's Included

• ✅ Data - Flexible SQL data access with unlimited parameters
• ✅ Id - Secure authentication & password management
• ✅ Email - SMTP email with HTML templates
• ✅ Helpers - ID and key generation utilities
• ✅ Models - UserRecord, PasswordResetTokenRecord

🔄 Version History

v1.0.2

• ✨ Updated Write method with unlimited parameters
• ✨ Added support for both ? and @paramName parameter formats
• ✨ Enhanced Id class with custom SQL query support
• ✨ Improved documentation and examples
• ✨ Added comprehensive README

v1.0.1

• Updated placeholder queries in Id class

v1.0.0

• Initial release

📄 License

MIT License - Free for personal and commercial use

🤝 Contributing

Issues and pull requests welcome at: https://github.com/terminalogic/terminalogic

📞 Support

For questions and support, please open an issue on GitHub.

Made with ❤️ by Dustin Hall
Terminalogic.net