Terminalogic.Essentials 1.0.6

Terminalogic.Essentials

A comprehensive .NET 8 library providing flexible SQL data access, email functionality with HTML designer, and utility helpers.

🚀 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>();
builder.Services.AddSingleton<Email>();
builder.Services.AddSingleton<Id>();

2. Configuration Settings

SQL Server (default):

Add to appsettings.json:

{
  "Database": {
    "Provider": "SqlServer"
  },
  "ConnectionStrings": {
    "ConnectionString": "Server=localhost;Database=MyDB;Trusted_Connection=true;"
  },
  "Mail": {
    "FromAddress": "no-reply@myapp.com",
    "FromDisplayName": "My Application"
  },
  "Smtp": {
    "Host": "smtp.gmail.com",
    "Port": 587,
    "EnableSsl": true,
    "User": "your-email@gmail.com",
    "Password": "your-app-password"
  }
}

MySQL:

{
  "Database": {
    "Provider": "MySql"
  },
  "ConnectionStrings": {
    "ConnectionString": "Server=localhost;Database=MyDB;User=root;Password=your-password;"
  },
  "Mail": {
    "FromAddress": "no-reply@myapp.com",
    "FromDisplayName": "My Application"
  },
  "Smtp": {
    "Host": "smtp.gmail.com",
    "Port": 587,
    "EnableSsl": true,
    "User": "your-email@gmail.com",
    "Password": "your-app-password"
  }
}

Provider values: SqlServer, MSSQL, SQL (default if omitted) or MySql, MariaDB.

---

📊 Data Class - Flexible SQL Data Access

The Data class provides a simple, flexible way to interact with SQL Server and MySQL 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 (SQL Server):

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

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

Stored Procedures (MySQL):

var results = data.Read("CALL GetUsersByAge", 18, "active");
var results = data.Read("CALL 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 (SQL Server):

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

Stored Procedures (MySQL):

int rows = data.Write("CALL 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 (SQL Server)
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. Use EXEC/EXECUTE on SQL Server or CALL on MySQL for stored procedures.

---

🔌 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):

using System.Data;

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

Check Active Provider:

using Terminalogic.DataAccess;

if (data.Provider == DatabaseProvider.MySql)
{
    // MySQL-specific logic in your app if needed
}

---

📧 Email Class - Flexible Email Sending

The Email class provides a powerful, reusable way to send emails with HTML and plain text versions.

Send Simple Email

@inject Email email

var html = "<h1>Hello!</h1><p>Welcome to our service.</p>";
var plainText = "Hello!\nWelcome to our service.";

if (email.Send("user@example.com", "Welcome!", html, out var error, plainText))
{
    Console.WriteLine("✅ Email sent!");
}
else
{
    Console.WriteLine($"❌ Failed: {error}");
}

Send to Multiple Recipients

var recipients = new[] { "user1@example.com", "user2@example.com", "user3@example.com" };

email.SendToMultiple(
    recipients,
    "Newsletter - March 2026",
    htmlContent,
    out var error,
    plainTextContent);

Send with Attachments

using var attachment = new Attachment("invoice.pdf");

email.SendWithAttachments(
    "user@example.com",
    "Your Invoice",
    htmlContent,
    new[] { attachment },
    out var error);

Override From Address

// Override default from address for specific email
email.Send(
    "user@example.com",
    "Custom Sender Email",
    htmlContent,
    out var error,
    plainTextContent,
    fromEmail: "custom@example.com",
    fromName: "Custom Sender Name");

---

🎨 EmailDesigner - HTML Email Builder

The EmailDesigner class makes it easy to create beautiful, responsive HTML emails using a fluent API.

Basic Usage

var designer = new EmailDesigner()
    .WithLogo("https://myapp.com/logo.png")
    .WithHeaderColor("#3b82f6")
    .AddHeading("Welcome to MyApp!")
    .AddParagraph("Thank you for signing up. We're excited to have you on board.")
    .AddButton("Get Started", "https://myapp.com/start")
    .AddDivider()
    .AddParagraph("If you have any questions, feel free to reach out.")
    .WithFooter("© 2026 MyApp. All rights reserved.");

var html = designer.Build();
var plainText = designer.BuildPlainText();

email.Send("user@example.com", "Welcome!", html, out _, plainText);

Pre-Built Templates

Password Reset:

var designer = EmailDesigner.PasswordResetTemplate(
    resetLink: "https://myapp.com/reset?token=abc123",
    appName: "MyApp");

designer
    .WithLogo("https://myapp.com/logo.png")
    .WithHeaderColor("#6366f1");

email.Send(userEmail, "Reset Your Password", designer.Build(), out _);

Verification Code:

var designer = EmailDesigner.VerificationCodeTemplate(
    code: "123456",
    appName: "MyApp",
    expiryMinutes: 10);

email.Send(userEmail, "Your Verification Code", designer.Build(), out _);

Welcome Email:

var designer = EmailDesigner.WelcomeTemplate(
    userName: "John",
    actionUrl: "https://myapp.com/start",
    appName: "MyApp");

email.Send(userEmail, "Welcome to MyApp!", designer.Build(), out _);

EmailDesigner Components

Headings & Text

designer
    .AddHeading("Main Title")           // H1
    .AddHeading("Subtitle", 2)          // H2
    .AddParagraph("This is a paragraph.");

Buttons & Links

designer
    .AddButton("Primary Action", "https://example.com")
    .AddButton("Custom Color", "https://example.com", "#ef4444");

Info Boxes

designer
    .AddInfoBox("ℹ️ This is informational.")
    .AddWarningBox("⚠️ Please be careful!")
    .AddSuccessBox("✅ Action completed!");

Code Blocks

designer.AddCodeBlock("ABC123");  // Perfect for verification codes

Lists

designer.AddList(new[] { "Item 1", "Item 2", "Item 3" });
designer.AddList(new[] { "Step 1", "Step 2", "Step 3" }, ordered: true);

Tables

designer.AddTable(
    new[] { "Product", "Quantity", "Price" },
    new[]
    {
        new[] { "Widget A", "2", "$29.99" },
        new[] { "Widget B", "1", "$49.99" }
    });

Variables

designer
    .WithVariable("userName", "John Doe")
    .WithVariable("orderNumber", "12345")
    .AddParagraph("Hello {{userName}}, your order {{orderNumber}} is ready!");

Real-World Example: Order Confirmation

var designer = new EmailDesigner()
    .WithLogo("https://myapp.com/logo.png")
    .WithVariable("orderNumber", "ORD-12345")
    .AddHeading("Order Confirmed! 📦")
    .AddParagraph("Thank you for your order {{orderNumber}}!")
    .AddSuccessBox("Your order will ship within 2-3 business days.")
    .AddTable(
        new[] { "Item", "Qty", "Price" },
        new[]
        {
            new[] { "Product A", "2", "$59.99" },
            new[] { "Product B", "1", "$89.99" }
        })
    .AddDivider()
    .AddHeading("Total: $149.98", 2)
    .AddButton("Track Order", "https://myapp.com/track/ORD-12345")
    .WithFooter("© 2026 MyApp. Questions? support@myapp.com");

var html = designer.Build();
email.Send("customer@example.com", "Order Confirmation", html, out _);

---

🔧 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.
• Email Class: Store SMTP credentials securely (e.g., Azure Key Vault, user secrets). Never commit passwords to source control.
• Connection Strings: Store connection strings securely (e.g., Azure Key Vault, user secrets).
• Validation: Always validate and sanitize user input before passing to database or email methods.

---

📚 What's Included

• ✅ Data - Flexible SQL data access with sequential (?) and named (@paramName) parameter support
• ✅ Email - Send emails with HTML/plain text, multiple recipients, and attachments
• ✅ EmailDesigner - Build beautiful, responsive HTML emails with a fluent API
• ✅ Helpers - Business ID and random key generation utilities
• 📖 Complete Documentation - See EMAIL_GUIDE.md for detailed email examples

---

🎯 Common Use Cases

Password Reset Flow

// 1. Generate reset link
var resetToken = helpers.GenerateRandomKey(32);
var resetLink = $"https://myapp.com/reset?token={resetToken}";

// 2. Send reset email
var designer = EmailDesigner.PasswordResetTemplate(resetLink, "MyApp");
email.Send(userEmail, "Reset Your Password", designer.Build(), out _);

User Verification

// 1. Generate code
var code = helpers.GenerateRandomKey(6);

// 2. Store in database
data.Write("INSERT INTO VerificationCodes (UserId, Code, ExpiresAt) VALUES (@userId, @code, @expires)",
    "userId", userId,
    "code", code,
    "expires", DateTime.UtcNow.AddMinutes(10));

// 3. Send verification email
var designer = EmailDesigner.VerificationCodeTemplate(code, "MyApp", 10);
email.Send(userEmail, "Verification Code", designer.Build(), out _);

Newsletter with Data

// 1. Query subscriber data
var subscribers = data.Read("SELECT Email, FirstName FROM Subscribers WHERE IsActive = @active", 
    "active", true);

// 2. Build newsletter
var designer = new EmailDesigner()
    .WithLogo("https://myapp.com/logo.png")
    .AddHeading("Monthly Update")
    .AddParagraph("Check out what's new this month!")
    .AddList(new[] { "Feature A", "Feature B", "Feature C" });

// 3. Send to all subscribers
var emails = subscribers.AsEnumerable().Select(r => r.Field<string>("Email"));
email.SendToMultiple(emails, "Newsletter - March 2026", designer.Build(), out _);

📖 Additional Resources

• EMAIL_GUIDE.md - Comprehensive email documentation with 10+ real-world examples
• Examples/EmailExamples.cs - Code examples for common email scenarios
• Supports Gmail, SendGrid, Mailgun, and any SMTP provider

---

📄 License

MIT License - See LICENSE file for details

---

🤝 Contributing

Contributions are welcome! Please feel free to submit issues and pull requests.

---

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

---

🗄️ 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.6

• 🐛 Fixed ? parameter binding when SQL contains @ in string literals (e.g. LIKE '%@%')
• ✨ Improved named-parameter detection in ParameterBinding

v1.0.5

• ✨ Added MySQL support via Database:Provider configuration
• ✨ Refactored data access behind provider abstraction (SqlServer, MySql)
• ✨ GetConnection() / GetCommand() now return DbConnection / DbCommand
• ✨ Re-enabled Id class with provider-neutral duplicate key detection

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