Terminalogic.Essentials 1.0.2

Terminalogic.Essentials

A comprehensive .NET 8 library providing flexible SQL data access, identity/authentication management, and email functionality with support for multiple parameter formats.

Installation

dotnet add package Terminalogic.Essentials

Quick Start

1. Configure Services

Add to your Program.cs or Startup.cs:

builder.Services.AddSingleton<Terminalogic.Data>();
builder.Services.AddSingleton<Terminalogic.Helpers>();
builder.Services.AddSingleton<Terminalogic.Id>();
builder.Services.AddSingleton<Terminalogic.Email>();

2. Configure Connection String

Add to appsettings.json:

{
  "ConnectionStrings": {
    "ConnectionString": "Server=localhost;Database=MyDB;Trusted_Connection=true;"
  },
  "Mail": {
    "FromAddress": "no-reply@example.com",
    "FromDisplayName": "My Application"
  },
  "Smtp": {
    "Host": "smtp.example.com",
    "Port": 587,
    "EnableSsl": true,
    "User": "smtp-username",
    "Password": "smtp-password"
  }
}

📊 Data Class - SQL Data Access

Flexible SQL query execution with support for sequential (?) and named (@paramName) parameters.

Read - Query Data

Sequential Parameters:

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

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

Named Parameters:

// More readable for complex queries
var users = data.Read("SELECT * FROM Users WHERE Email = @email AND Age > @minAge", 
                      "email", "test@example.com", 
                      "minAge", 18);

// Order-independent
var products = data.Read("SELECT * FROM Products WHERE Price > @minPrice AND Category = @cat", 
                         "cat", "Electronics",
                         "minPrice", 50.00);

Stored Procedures:

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

Write - Insert/Update/Delete

Sequential Parameters:

int rowsAffected = data.Write("INSERT INTO Users (Name, Email, Age) VALUES (?, ?, ?)", 
                               "John", "john@example.com", 25);

int rowsUpdated = data.Write("UPDATE Users SET Status = ? WHERE Id = ?", "active", 123);

Named Parameters:

int rowsUpdated = data.Write("UPDATE Users SET Name = @name, Email = @email WHERE Id = @id",
                              "name", "Jane", 
                              "email", "jane@example.com", 
                              "id", 123);

ReadOne - Get Single Value

var count = data.ReadOne<int>("SELECT COUNT(*) FROM Users WHERE Active = @active", 
                               null, null, "active", true);

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

Other Methods

// Test connection
if (data.TestConnection(out string? error))
{
    Console.WriteLine("Connected!");
}

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

🔐 Id Class - Identity & Authentication

Secure user management with PBKDF2-SHA256 password hashing and token-based password resets.

Note: All methods require you to provide your own SQL queries to match your database schema.

Get User

var getUserSql = "SELECT Id, BusinessId, Email, FirstName, LastName, PasswordHash, PasswordSalt, PasswordIterations, PasswordAlgorithm, IsActive, CreatedAtUtc, UpdatedAtUtc, LastLoginAtUtc, UserType FROM Users";

var user = id.GetUserByEmail("test@example.com", getUserSql);
var user = id.GetUserById(123, getUserSql);

Validate Credentials

var getUserSql = "SELECT ... FROM Users";
var updateLoginSql = "UPDATE Users SET LastLoginAtUtc = @LastLoginAtUtc WHERE Id = @UserId";

var user = id.ValidateCredentials("test@example.com", "password123", getUserSql, updateLoginSql);
if (user != null)
{
    // User authenticated successfully
}

Create User

var insertSql = "INSERT INTO Users (BusinessId, Email, FirstName, LastName, PasswordHash, PasswordSalt, PasswordIterations, PasswordAlgorithm, IsActive, CreatedAtUtc, UpdatedAtUtc, LastLoginAtUtc, UserType) VALUES (@BusinessId, @Email, @FirstName, @LastName, @PasswordHash, @PasswordSalt, @PasswordIterations, @PasswordAlgorithm, @IsActive, @CreatedAtUtc, NULL, NULL, @UserType)";
var getUserSql = "SELECT ... FROM Users";

var newUser = id.CreateUser(
    businessId: null,  // Auto-generated if null
    email: "new@example.com",
    password: "securePass123",
    firstName: "John",
    lastName: "Doe",
    isActive: true,
    insertSql: insertSql,
    getUserSql: getUserSql,
    userType: "OWNER"
);

Set Password

var updateSql = "UPDATE Users SET PasswordHash = @PasswordHash, PasswordSalt = @PasswordSalt, PasswordIterations = @PasswordIterations, PasswordAlgorithm = @PasswordAlgorithm, UpdatedAtUtc = @UpdatedAtUtc WHERE Id = @UserId";

id.SetPassword(123, "newPassword123", updateSql);

Password Reset Tokens

// Create token
var getUserSql = "SELECT ... FROM Users";
var insertTokenSql = "INSERT INTO PasswordResetTokens (Id, UserId, TokenHash, ExpiresAtUtc, CreatedAtUtc) VALUES (@Id, @UserId, @TokenHash, @ExpiresAtUtc, @CreatedAtUtc)";

var tokenContext = id.CreatePasswordResetToken("user@example.com", TimeSpan.FromHours(24), getUserSql, insertTokenSql);
if (tokenContext != null)
{
    string resetLink = $"https://myapp.com/reset?token={tokenContext.RawToken}";
    // Send resetLink via email
}

// Validate token
var getTokenSql = "SELECT t.Id, t.UserId, t.TokenHash, t.ExpiresAtUtc, t.CreatedAtUtc AS TokenCreatedAtUtc, t.RedeemedAtUtc, u.Id AS User_Id, u.BusinessId AS User_BusinessId, u.Email, u.FirstName, u.LastName, u.PasswordHash, u.PasswordSalt, u.PasswordIterations, u.PasswordAlgorithm, u.IsActive, u.CreatedAtUtc AS UserCreatedAtUtc, u.UpdatedAtUtc AS UserUpdatedAtUtc, u.LastLoginAtUtc AS UserLastLoginAtUtc, u.UserType AS AccessType FROM PasswordResetTokens t INNER JOIN Users u ON u.Id = t.UserId ORDER BY t.CreatedAtUtc DESC";

var context = id.GetValidPasswordResetToken(token, getTokenSql);
if (context != null)
{
    var updatePasswordSql = "UPDATE Users SET PasswordHash = @PasswordHash, PasswordSalt = @PasswordSalt, PasswordIterations = @PasswordIterations, PasswordAlgorithm = @PasswordAlgorithm, UpdatedAtUtc = @UpdatedAtUtc WHERE Id = @UserId";
    id.SetPassword(context.User.Id, "newPassword", updatePasswordSql);

    var redeemSql = "UPDATE PasswordResetTokens SET RedeemedAtUtc = @RedeemedAtUtc WHERE Id = @Id AND RedeemedAtUtc IS NULL";
    id.RedeemPasswordResetToken(context.Token.Id, redeemSql);
}

Utility Methods (Public Static)

// Hash password manually
var hashResult = Id.HashPassword("myPassword");
byte[] hash = hashResult.Hash;
byte[] salt = hashResult.Salt;
int iterations = hashResult.Iterations;

// Verify password
bool isValid = Id.VerifyPassword("myPassword", salt, iterations, hash);

// Generate tokens
string secureToken = Id.GenerateTokenString();
byte[] tokenHash = Id.HashToken(secureToken);

📧 Email Class - Email Service

Send emails via SMTP with HTML and plain text support.

Send Password Reset Email

if (email.TrySendPasswordResetEmail("user@example.com", resetLink, out string? error))
{
    Console.WriteLine("Email sent successfully!");
}
else
{
    Console.WriteLine($"Failed to send email: {error}");
}

🔧 Helpers Class - Utility Methods

Generate Business ID

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

Generate Random Key

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

📋 Models

UserRecord

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; }
}

🔒 Security Features

• ✅ Parameterized queries - SQL injection protection
• ✅ PBKDF2-SHA256 - Industry-standard password hashing
• ✅ 100,000 iterations - Strong key derivation
• ✅ Timing-attack protection - Constant-time comparison
• ✅ Secure token generation - Cryptographically random
• ✅ SHA-256 token hashing - Secure token storage

📝 Database Schema Requirements

For the Id class to work, you need tables with these columns:

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),
    PasswordSalt VARBINARY(256),
    PasswordIterations INT,
    PasswordAlgorithm NVARCHAR(50),
    IsActive BIT,
    CreatedAtUtc DATETIME2,
    UpdatedAtUtc DATETIME2,
    LastLoginAtUtc DATETIME2,
    UserType NVARCHAR(50)
);

PasswordResetTokens Table

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

Note: You can customize table/column names by providing your own SQL queries to each method.

⚠️ Important Notes

Parameter Formats

• Cannot mix ? and @ in the same query
• Sequential (?) - Simple, order-dependent
• Named (@paramName) - Readable, order-independent

Id Class Flexibility

The Id class requires you to provide SQL queries for all operations, giving you complete control over your database schema.

🎯 Best Practices

1. Use named parameters for complex queries (3+ parameters)
2. Use sequential parameters for simple queries (1-2 parameters)
3. Provide custom SQL to the Id class to match your schema
4. Store configuration in appsettings.json, not in code
5. Use dependency injection to manage service lifetimes
6. Test your queries before deploying

📦 What's Included

• ✅ Data - Flexible SQL data access
• ✅ Id - User authentication & password management
• ✅ Email - SMTP email sending
• ✅ Helpers - Utility methods
• ✅ Models: UserRecord, PasswordResetTokenRecord

📄 License

MIT

🤝 Contributing

Issues and pull requests are welcome!

📞 Support

For issues, visit: https://github.com/terminalogic/terminalogic

Made with ❤️ by Dustin Hall