DEV Community: Sean Drew

Writing C# Code Like a Klingon

Sean Drew — Tue, 19 Aug 2025 14:54:40 +0000

Developers aim for clean, maintainable, and readable code, but sometimes readability drifts into verbosity and methods start sounding like bureaucratic procedures instead of executable instructions.

What if, instead, you wrote code like a Klingon warrior? Klingons value strength, honor, and directness and their programming style would be the same: no wasted words, no unnecessary ceremony, and no tolerance for weakness.

The Philosophy of Klingon Code
Klingon code is:
• Aggressive: Methods act as commands, not suggestions.
• Direct: No fluff, no politeness just action.
• Efficient: Do what must be done, nothing more.
• Honorable: Fail loudly, succeed gloriously.
A Klingon programmer does not write "PerformBattleAgainstEnemy", they write "HIv" which is the Klingon word for "attack."

Example: Standard C# vs. Klingon C#
Federation style namby-pamby standard C# error handling. Softly check inputs and wraps dangerous operations in try/catch blocks. Never let the system explode and log issues politely.

// ==========================
// Federation Style: Gentle cautious error handling
// ==========================
public class Warrior
{
  // Private fields for warrior's name and honor points
  private readonly string _name;
  private int _honor;

  // Constructor initializes warrior with a name
  // If the name is null or empty, assigns a safe default
  public Warrior(string name)
  {
    if (string.IsNullOrWhiteSpace(name))
    {
      Console.WriteLine("Warning: Name is empty. Assigning 'Unknown Warrior'.");
      _name = "Unknown Warrior";
    }
    else
    {
      _name = name;
    }

    _honor = 100; // Start honor at 100 points
  }

  // Battle method attempts to fight an enemy
  // Wraps execution in try/catch to prevent crashes
  public void Battle(string enemy)
  {
    try
    {
      // Check for invalid or empty enemy
      if (string.IsNullOrWhiteSpace(enemy))
      {
        Console.WriteLine("Warning: Cannot battle a void enemy.");
        return; // Skip this battle safely
      }

      // Perform the battle and increase honor
      Console.WriteLine($"{_name} battles {enemy}!");
      _honor += 10;
    }
    catch (Exception ex)
    {
      // Polite error reporting
      Console.WriteLine($"An unexpected error occurred during battle: {ex.Message}");
    }
  }

  // ShowHonor method displays current honor points
  public void ShowHonor()
  {
    try
    {
      Console.WriteLine($"{_name} has {_honor} honor points.");
    }
    catch (Exception ex)
    {
      // Polite error reporting
      Console.WriteLine($"Unable to display honor: {ex.Message}");
    }
  }
}

Klingon style direct and decisive error handling. Klingons do not wrap operations in polite try/catch. They confront errors head-on. If something is wrong, the program stops, leaving a clear battle scar (exception) for all to see. The code does not politely show honor. It declares it.

// ==========================
// Klingon Style: Direct, decisive error handling
// ==========================
public class tlhIngan
{
  // Fields for name and honor points
  string name;
  int honor = 100;

  // Constructor requires a valid name; throws exception if missing
  // Klingons do not tolerate cowardice or indecision
  public tlhIngan(string n)
  {
    if (string.IsNullOrWhiteSpace(n))
      throw new ArgumentException("A tlhIngan must have a name! Cowardice is not tolerated.");

    name = n;
  }

  // HIv method performs an attack on a target (jagh)
  // Throws immediately if the target is invalid
  public void HIv(string jagh)
  {
    if (string.IsNullOrWhiteSpace(jagh))
      throw new ArgumentException($"{name} refuses to HIv an empty target. Dishonor is unacceptable!");

    // Perform the attack and increase honor
    Console.WriteLine($"{name} HIv {jagh}!");
    honor += 10;
  }

  // quv method displays current honor
  public void quv()
  {
    Console.WriteLine($"{name} quv {honor}");
  }
}

Federation vs Klingon Coding Conventions

Error Handling • Federation: Gentle—uses warnings and try/catch to avoid crashes. • Klingon: Aggressive—throws immediately; failures are loud and visible.
Input Validation • Federation: Substitutes safe defaults if input is missing or invalid. • Klingon: Requires explicit, valid input; invalid input triggers immediate exceptions.
Method Logic • Federation: Safe, layered, cautious execution. • Klingon: Direct, decisive; actions happen immediately.
Variable and Method Naming • Federation: Descriptive, friendly, easy to read. • Klingon: Action-oriented and battle-themed (e.g., HIv, quv).
Honor / Points Management • Federation: Updated carefully; dishonor is handled politely. • Klingon: Central to all actions; any dishonor halts execution.
Output Messages • Federation: Polite, informative, and explanatory. • Klingon: Bold, concise, dramatic; announces every action clearly.
Try/Catch Usage • Federation: Extensive, for safety and stability. • Klingon: Minimal; only at the top level to observe battle results.
Initialization • Federation: Allows defaults or safe substitutions. • Klingon: Must be explicit; cowardice or missing data is unacceptable.
Philosophy • Federation: Preserve harmony, avoid crashing, be forgiving. • Klingon: Confront errors, act boldly, and leave evidence of every battle.

Klingon Developer’s Guide

Fail Loudly • Principle: A Klingon program never hides dishonor. • Implementation: Throw exceptions immediately on invalid input or actions.

if (string.IsNullOrWhiteSpace(name))
    throw new ArgumentException("A tlhIngan must have a name! Cowardice is not tolerated.");

Direct, Decisive Logic • Principle: Do not dance around problems. Code must act immediately. • Implementation: Perform actions and enforce rules in a single, straightforward method.

public void HIv(string jagh)
{
  if (string.IsNullOrWhiteSpace(jagh))
     throw new ArgumentException($"{name} refuses to HIv an empty target!");
     honor += 10; // Honor increases immediately
}

No Safety Wrappers • Principle: Try/catch is cowardly. Let failure be obvious. • Implementation: Allow exceptions to propagate; they leave evidence of the battle.
Aggressive Naming • Principle: Names reflect the action and the honor system. o HIv → Attack enemy o quv → Display honor o honor → Track warrior honor points
Honor is Central • Principle: Every action affects honor. Dishonor stops execution. • Implementation: Increment or decrement honor in every meaningful method.
Explicit Initialization • Principle: No defaults. Every "tlhIngan" must have a valid name and starting honor.

string name;
int honor = 100;

Clear Concise Output • Principle: Output is bold, descriptive, and battle-themed. Console.WriteLine($"{name} HIv {jagh}!"); // Clearly announces the attack
Optional: Battle Logging • Principle: Keep records of success and failure; each battle is a story. • Implementation can include logging to console or file; nothing is hidden.

Conclusion
Federation code preserves stability, avoids harm, soft on errors. Klingon code confronts dishonor, punishes mistakes, leaves battle scars. Klingon coding is all about clarity, honor, and decisiveness. Fail loudly, act boldly, and let your code reflect the warrior ethos. Writing C# like a Klingon is not about replacing English with Klingon vocabulary it is about embracing the philosophy of strength and clarity. Code should not whisper its intent. It should shout it with honor - Qapla'!

Designing a SOLID, Layered ASP.NET Core Solution for Patient Lookup

Sean Drew — Wed, 30 Jul 2025 12:45:42 +0000

A simple patient lookup might not sound exciting, but it is a perfect chance to show how clean code and solid architecture make all the difference. This example uses ASP.NET Core and stored procedures to do just that. While the use case is straightforward, the architecture adheres to the SOLID principles, maintains separation of concerns, and follows clean code and enterprise-level structuring. This makes the project a practical example of how even simple features benefit from strong software design.

Software development is not only about delivering functionality it is about delivering maintainable, scalable, and testable solutions. Even in small applications or single-feature projects like a patient lookup, poor design decisions can lead to tight coupling, duplication, and fragile code that is difficult to evolve.

The goal is to try and demonstrate how to take a simple use case retrieving a patient by ID from a SQL Server database and wrap it in a SOLID-compliant architecture using ASP.NET Core. The goal is to illustrate how enterprise-quality design is not just for complex systems. It brings clarity, reliability, and extensibility to any codebase, regardless of size.

By isolating responsibilities into Models, Repositories, Services, and Web UI layers, you can ensure the system remains:

Easy to understand and reason about
Simple to test and mock
Safe to extend or refactor
Well-suited to real-world business logic growth

Even if the system starts small, this approach sets a strong foundation that avoids rewrites and future technical debt. It is not about overengineering, but about applying clean architectural principles from the beginning when they are cheapest and most effective to implement.

Goals

Clean separation of concerns using N-Tier Architecture
Full adherence to the SOLID principles
Use of Dependency Injection, async patterns, and stored procedures
A minimal but functional Web API and Razor Page frontend
Centralized configuration and clean routing
A project structure that scales with growth

Recommended Solution Structure
This solution is organized into the following projects:

PatientLookupSolution
├── PatientLookup.Models // Plain old class object
├── PatientLookup.Repositories // SQL logic and ADO.NET (SqlDataClient)
├── PatientLookup.Services // Business logic
└── PatientLookup.Web // API and Razor UI

Each layer only depends on the one below it:

Web depends on Services
Services depends on Repositories
Repositories depends on Models This architecture ensures modularity, testability, and scalability.

SOLID Breakdown
Single Responsibility: Each class serves a single purpose: controller, service, repository, etc.
Open/Closed: You can extend behaviors (e.g., new services) without modifying existing code.
Liskov Substitution: Interfaces are respected; substituting mock services or test repositories is safe.
Interface Segregation: Interfaces are focused (e.g., IPatientService only exposes needed methods).
Dependency Inversion: Higher layers depend on abstractions (IPatientRepository), not implementations.

Code Walkthrough
Patient.cs (Model Layer)

namespace PatientLookup.Models
{
  public class Patient
  {
    public int Id { get; set; }
    public string FirstName { get; set; } = string.Empty;
    public string LastName { get; set; } = string.Empty;
  }
}

Just a normal class, no attributes describing infrastructure concerns or other responsibilities
No data annotations to keep the model agnostic of UI or DB layers.

IPatientRepository
This is the part of the application responsible for talking directly to the database in a controlled and consistent way. It defines the contract for how the rest of the system can ask for patient data without needing to know how that data is actually retrieved.

public interface IPatientRepository
{
  Task<Patient?> GetByIdAsync(int id);
}

Basically it tells the system: "If you give me a patientId, I will return the matching Patient object asynchronously."

But it does not say how it does that. The actual logic of running the stored procedure and mapping the SQL result to a model is handled in the class that implements the IPatientRepository public interface (PatientRepository).

IPatientRepository defines a clear boundary between the application and the data layer. It is a small piece with a big impact when it comes to keeping the code clean, testable, and future-proof.

PatientRepository (Repository Layer)
PatientRepository is the implementation of IPatientRepository. It contains the actual logic for accessing the database by calling a stored procedure to fetch patient data. It focuses only on fetching data. No business logic. No UI code. This keeps the data layer clean, testable, and easy to change without breaking the rest of the system.

public class PatientRepository : IPatientRepository
{
  private readonly string _connectionString;

  public PatientRepository(IConfiguration config)
  {
    _connectionString = config.GetConnectionString("DefaultConnection")
      ?? throw new InvalidOperationException("Missing DefaultConnection string.");
  }

  public async Task<Patient?> GetByIdAsync(int id)
  {
    using var conn = new SqlConnection(_connectionString);
    using var cmd = new SqlCommand("cp_GetPatientById", conn)
    {
      CommandType = CommandType.StoredProcedure
    };
    cmd.Parameters.AddWithValue("@patid", id);
    await conn.OpenAsync();

    using var reader = await cmd.ExecuteReaderAsync();
    if (await reader.ReadAsync())
    {
      return new Patient
      {
        Id = reader.GetInt32(reader.GetOrdinal("Id")),
        FirstName = reader.GetString(reader.GetOrdinal("FirstName")),
        LastName = reader.GetString(reader.GetOrdinal("LastName"))
      };
    }

    return null;
  }
}

Uses ADO.NET for performance and transparency.
Clean separation from business logic.
Uses a stored procedure for optimized and secure DB access.

IPatientService
IPatientService defines the business-facing contract for how the application can retrieve patient information. It sits above the repository and lets higher layers (like the controller) request patient data without knowing anything about the data source. This abstraction makes the service layer testable, swappable, and cleanly separated from the database layer.

public interface IPatientService
{
  Task<Patient?> GetPatientByIdAsync(int id);
}

Acts as the middleman between the controller and the data layer, handling patient-related business logic.
Offers a simple async method "GetPatientByIdAsync" to fetch a patient by their ID.

PatientService (Service Layer)
PatientService is the implementation of IPatientService. It calls the repository to fetch data and can apply business rules, validation, logging, or transformations before returning the result. Basically, it acts as a bridge between the controller and the repository cleanly separating concerns and centralizing any logic beyond raw data access.

public class PatientService : IPatientService
{
  private readonly IPatientRepository _repository;

  public PatientService(IPatientRepository repository)
  {
    _repository = repository;
  }

  public Task<Patient?> GetPatientByIdAsync(int id)
  {
    return _repository.GetByIdAsync(id);
  }
}

PatientController (Web API Layer)
PatientController is the entry point of the application. It handles incoming HTTP requests, delegates work to the service layer, and returns appropriate responses (usually JSON or a view). In this case, it returns the response to a view.

[Route("api/[controller]")]
[ApiController]
public class PatientController : ControllerBase
{
  private readonly IPatientService _service;

  public PatientController(IPatientService service)
  {
    _service = service;
  }

  [HttpGet("{id}")]
  public async Task<IActionResult> GetPatient(int id)
  {
    var patient = await _service.GetPatientByIdAsync(id);
    if (patient == null)
      return NotFound();

    return Ok(patient);
  }
}

Validates incoming requests at a high level (e.g., route-level).
Delegates the actual logic to the service layer.
Returns a clean HTTP response (200 OK, 404 Not Found, etc.).
It does not contain business or data logic.
Minimal and clean controller.
Relies only on the service interface.

Razor Page: PatientView.cshtml

@page
@model PatientLookup.Web.Pages.PatientViewModel
@{
  ViewData["Title"] = "Patient Details";
}
<h2>Patient Details</h2>
@if (Model.Patient != null)
{
  <div>
    <strong>ID:</strong> @Model.Patient.Id<br />
    <strong>Name:</strong> @Model.Patient.FirstName @Model.Patient.LastName
        </div>
}
else
{
  <div>Patient not found.</div>
}

Code-Behind: PatientView.cshtml

public class PatientViewModel : PageModel
{
  private readonly IPatientService _service;

  public PatientViewModel(IPatientService service)
  {
    _service = service;
  }

  [BindProperty(SupportsGet = true)]
  public int Id { get; set; }

  public Patient? Patient { get; set; }

  public async Task<IActionResult> OnGetAsync()
  {
    Patient = await _service.GetPatientByIdAsync(Id);
    return Patient == null ? NotFound() : Page();
  }
}

Clean and simple frontend view.
Demonstrates reuse of business logic via dependency injection in Razor Pages.

Program.cs
Program.cs is the glue that connects and configures all the architectural layers at startup. It is the application entry point in ASP.NET Core and configures the web host, registers services for dependency injection, defines how the application will run, etc.

using Microsoft.AspNetCore.Builder;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using PatientLookup.Repositories;
using PatientLookup.Services;
using System.Data;
using System.Data.SqlClient;

var builder = WebApplication.CreateBuilder(args);

// Load configuration
builder.Configuration.AddJsonFile("appsettings.json", optional: false, reloadOnChange: true);

// Register controllers and framework services
builder.Services.AddControllers();
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();

// Register custom services and repositories
builder.Services.AddScoped<IPatientRepository, PatientRepository>();
builder.Services.AddScoped<IPatientService, PatientService>();

// Register SQL connection for dependency injection
builder.Services.AddScoped<IDbConnection>(sp =>
  new SqlConnection(builder.Configuration.GetConnectionString("DefaultConnection")));

var app = builder.Build();

app.UseHttpsRedirection();
app.UseAuthorization();

app.MapControllers();
app.Run();

appsettings.json (environment based configuration)

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=SQLServer;Database=YourDb;Trusted_Connection=True;"
  }
}

Best Practice Highlights

Use Stored Procedures: More secure, better for performance.
Separation of Concerns: Data, business, and presentation logic are isolated.
Thin Controllers: Controllers delegate logic to services.
Interface-Driven Design: Easily testable and extensible.
Async Everywhere: Improves scalability.

How all the Layers Work Together
This architecture separates responsibilities across four key layers, each with a clear purpose:

Controller Layer
Responsibility: Handles HTTP requests and returns responses.
Example: PatientController
Accepts GET /api/patient/123
Calls IPatientService.GetPatientAsync(123)
Returns 200 OK with the patient object or 404 Not Found
Service Layer
Responsibility: Contains business logic and orchestrates data access.
Example: PatientService
Validates input (e.g., patientId > 0)
Calls repository: IPatientRepository.GetPatientByIdAsync()
Applies any business rules before returning data
Repository Layer
Responsibility: Communicates with the database (via stored procedures or queries).
Example: PatientRepository
Executes cp_APIGetPatientById
Maps SqlDataReader result to a Patient model
Returns the data to the service layer
Model Layer
Responsibility: Defines the shape of your data objects.
Example: Patient class
Represents patient data across layers
Keeps your code strongly typed and structured

Separation of Concerns: Each layer does one thing and does it well
Testability: You can mock or stub any layer during unit testing
Maintainability: Changes in one layer (e.g., database schema) do not break the rest
Scalability: Easily extend logic (e.g., add caching or logging) without disrupting the design

Conclusion
This example illustrates how clean, scalable architecture is not limited to large enterprise projects. By applying the SOLID principles, structuring with layered boundaries, and embracing clean code practices, you create software that is:

Easy to maintain
Easy to extend
Easier to onboard new developers
Ready for scaling into production

Refactoring Repetitive Model Validation in ASP.NET Core

Sean Drew — Wed, 16 Jul 2025 14:55:23 +0000

In one of my ASP.NET Core APIs, I was working with [FromBody] models that had a number of required fields, some were nullable integers, others were strings that needed to be non-empty. Initially, I handled validation directly in the controller action with a series of repetitive if statements like this:

if (!model.patienId!.HasValue || (int)model.patienId! == 0)
{ return new ContentResult() { Content = "patienId is a required parameter", StatusCode = 400 }; }

if (string.IsNullOrEmpty(model.ndcnumber))
{ return new ContentResult() { Content = "ndcnumber is a required parameter", StatusCode = 400 }; }

if (!model.qty!.HasValue || (int)model.qty! <= 0) 
{ return new ContentResult() { Content = "qty is a required parameter", StatusCode = 400 }; }

// ... and so on for every field

This approach worked well for what I was doing at the time, but it became messy very quickly, especially as I added more fields and repeated the same pattern in multiple actions and across different controllers. It was hard to read, error-prone to update, and encouraged copy/paste over clean reuse.

To clean this up, I decided to refactor the common validation checks into a dedicated "ValidationHelper" class. This utility would let me validate nullable value types and strings in a reusable and centralized way.

In this post, I show a simple refactor I made to clean up these types of repetitive validation "if" statements in my controller actions by introducing a lightweight ValidationHelper. The result is clearer code, consistent error handling, and much better maintainability.

Refactoring with a Validation Helper
Basically, a helper class is a fancy way to describe a separate CS file where I put reusable code like small utility functions that I can call from my controllers (or anywhere else). It keeps things clean and avoids repeating the same logic in every method. So, instead of cluttering my controllers with repetitive checks (like verifying required fields), I just put those checks in a helper class and call them as needed. It makes the code easier to read and maintain.

This helper lives in its own file under the "/Helpers/ValidationHelper.cs" folder, and I keep it in the shared "MyAPI.Helpers" namespace.

// simple helper method for validating required model fields in ASP.NET Core controllers.
public static class ValidationHelper
{
  // Validate a nullable value type is not null and not its default value (e.g., 0 for int, false for bool).
  // <typeparam name="T">A struct type (e.g., int, bool, DateTime).</typeparam>
  // <param name="value">The value to validate.</param>
  // <param name="name">The name of the parameter (used in the error message).</param>
  // Returns ContentResult with a 400 status if the value is missing or default; otherwise, null.
  public static ContentResult? Required<T>(T? value, string name) where T : struct
  {
    // Check if the value is null or equals the default value for its type (e.g., 0 for int).
    if (!value.HasValue || EqualityComparer<T>.Default.Equals(value.Value, default))
    {
      return new ContentResult
      {
        Content = $"{name} is a required parameter",
        StatusCode = 400
      };
    }

    // If valid, return null to indicate no validation error.
    return null;
  }

  // Validate a string is not null, empty, or whitespace.
  // <param name="value">The string value to validate.</param>
  // <param name="name">The name of the parameter (used in the error message).</param>
  // Returns ContentResult with a 400 status if the string is null or empty; otherwise, null.
  public static ContentResult? Required(string? value, string name)
  {
    // Check if the string is null, empty, or whitespace-only.
    if (string.IsNullOrWhiteSpace(value))
    {
      return new ContentResult
      {
        Content = $"{name} is a required parameter",
        StatusCode = 400
      };
    }

    // If valid, return null to indicate no validation error.
    return null;
  }
}

A Sample of My Model

public class MySampleModel
{
  [Required] public int? patientId { get; set; }
  [Required] public string? ndcnumber { get; set; } = string.Empty;
  [Required] public int? qty { get; set; }
}

Sample Usage in my Controller
Now, instead of repeating lots of "if" validation logic in every action I simply do something like this:

[ApiController]
[Route("[controller]")]
public class MyController : ControllerBase
{
  private ContentResult? _validationResult;

  [HttpPost("DoSomethingEndpoint")]
  public IActionResult DoSomethingEndpoint([FromBody] MySampleModel model)
  {
    // Use ValidationHelper to check required fields
    if ((_validationResult = ValidationHelper.Required(model.patientId, "patientId")) != null) return _validationResult;
    if ((_validationResult = ValidationHelper.Required(model.ndcnumber, "ndcnumber")) != null) return _validationResult;
    if ((_validationResult = ValidationHelper.Required(model.qty, "qty")) != null) return _validationResult;

    // if all fields are valid, proceed with processing
    return Ok("Request accepted");
  }
}

Controller-Wide Result Field
To avoid declaring the same "ContentResult? result;" variable in every action, I made it a private field on the controller, like this:
private ContentResult? _validationResult;

Then I reused it across all of my methods.
if ((_validationResult = ValidationHelper.Required(model.qty, "qty")) != null) return _validationResult;
Because ASP.NET Core creates a new instance of the controller per request, this pattern is safe and avoids unnecessary duplication while keeping the logic tight and readable.

Why This Is Better
Switching to a helper-based approach gave me several benefits:
• Cleaner, reusable validation logic. the core logic lives in one place and is reused everywhere
• Consistent error formatting, validation lines are short, clear, and consistent
• Easier maintenance. Changes to the validation rules only require updates in one spot. Easy to extend and apply to other models.
• Cleaner controller actions. No external validation libraries required

Conclusion
This refactor started as a way to clean up clutter in a single action, but it quickly became a general pattern I now apply across my API projects.

Also, for more complex validation rules, like conditional logic (e.g., field B is required if field A is X) I can simply extend this pattern or supplement it with FluentValidation. If you're writing the same validation statements over and over, give a helper like this a try. It might be all you need to make your codebase more elegant and maintainable.

Transitioning to JSON: Supporting Both Body and Query Parameters in ASP.NET Core

Sean Drew — Mon, 14 Jul 2025 12:41:59 +0000

How to Support Both JSON Body and Query Parameters in ASP.NET Core APIs Without the Headaches

Recently, I was tasked with updating older POST endpoints to support [FromBody] input instead of the traditional [FromQuery] model. As part of this transition, I needed to support both [FromQuery] and [FromBody] inputs for backward compatibility until all clients can be updated. This approach allows existing clients to continue functioning without interruption, while new clients benefit from cleaner, structured JSON.

While this pattern is helpful for POST or PUT endpoints where the client is sending data, it is not intended for GET requests. In GET requests the input should remain as route or query parameters and not the request body.

Sounds simple, right? But in practice, ASP.NET Core’s model binding and validation pipeline is not designed to easily accept both [FromBody] and [FromQuery] inputs for the same model. You typically need to choose one source of input (not both).

In this write-up I go through a (relatively) reusable way to support both input models without introducing unnecessary complexity or breaking existing client functionality.

Objective
Allow my ASP.NET Web API POST/PUT endpoints to accept parameters either through:
• JSON body (using [FromBody])
• Query string (using [FromQuery])
The logic is:
• If the body exists and is valid then use it instead of query parameters.
• If the body is null or invalid then look at query parameters.
• If the query parameters are null or invalid then re-check body as fallback.
• If neither exist or are invalid then return a validation error.

The Problem
• If I decorate my action parameters with just [FromBody], then clients must send JSON. If they do not, they will see errors like “Unsupported Media Type” or model validation failures because the body is missing. I am doing this to help with migration so existing clients do not have to change right away and to support a smooth transition period.
• Mixing [FromBody] and [FromQuery] on the same action parameters does not work particularly well and could throw unexpected validation failures.
• I need to move to a JSON body input but fallback gracefully to query parameters if the body is missing or invalid, especially for existing clients.
• I want validation to “just work” with either input method.

My Solution is an Extension Method That Takes Care of Both Input Types
Here is how I approached it:

• I wrote a helper method that first tries to read and validate my model from the JSON body. A helper method is a fancy way of saying "code" (method) that I have in a in a class file. This helps to simplify and centralize logic that I might otherwise duplicate across multiple places.

• If that does not work (maybe because the client sent no body or the JSON is invalid) then it then tries to build and validate the model from query parameters instead.

• The plan is to end up with one valid model object but it is also possible that neither input works.

• I call this helper from my controller action. If it returns a valid model, preferably from the JSON body (which is the goal here) then I continue with my business logic. If it doesn’t then I return a validation error to the caller.

What Does That Look Like?
I added a new class file named HttpRequestExtensions.cs to my project.

The file contains an extension method for my ASP.NET controllers that helps seamlessly retrieve and validate a model from either the JSON request body ([FromBody]) or the URL query parameters ([FromQuery]). In a nut shell:
• Read and parse the JSON body first, validating the model if present.
• If the body is missing or invalid, it then attempts to build and validate the model from the query string parameters.
• It returns the first valid model it finds, or null if neither source is valid.

This simplifies my controller code by centralizing the logic for supporting both input styles, making migration smoother and keeping the endpoint implementation clean and consistent.

Here is what the HttpRequestExtensions.cs file extension method looks like.

// This static class provides an extension method for Controller to support input binding
// from either JSON body ([FromBody]) or query string ([FromQuery]).
public static class HttpRequestExtensions
{
  // Generic method to try binding a model of type T from the body or query.
  // T must be a class with a parameterless constructor.
  public static async Task<T?> TryGetModelFromBodyOrQueryAsync<T>(this Controller controller) where T : class, new()
  {
    var request = controller.HttpContext.Request;

    // --- STEP 1: Try to bind from JSON body if content exists and is of type application/json ---
    if (request.ContentLength > 0 && 
        request.ContentType?.Contains("application/json", StringComparison.OrdinalIgnoreCase) == true)
    {
      try
      {
        // Attempt to deserialize the JSON body into the model
        var bodyModel = await request.ReadFromJsonAsync<T>();

        // If deserialization is successful and model passes validation, return it
        if (bodyModel != null && controller.TryValidateModel(bodyModel)) 
          return bodyModel;
      }
      catch
      {
        // Ignore JSON parsing errors and fall through to try query string
      }
    }

    // --- STEP 2: Fallback to building the model from query string parameters ---
    var queryModel = new T(); // Create a new instance of T
    var props = typeof(T).GetProperties(); // Reflectively get all public properties

    foreach (var prop in props)
    {
      var key = prop.Name;

      // Skip if the query string does not contain the key
      if (!request.Query.ContainsKey(key)) continue;

      var value = request.Query[key].ToString();

      // Skip if value is empty or whitespace
      if (string.IsNullOrWhiteSpace(value)) continue;

      try
      {
        // Determine the correct type, handling nullable types
        var targetType = Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType;

        // Convert the string query value to the correct property type
        var convertedValue = Convert.ChangeType(value, targetType, CultureInfo.InvariantCulture);

        // Assign the converted value to the property
        prop.SetValue(queryModel, convertedValue);
      }
      catch
      {
        // Ignore conversion errors (e.g., invalid format); leave default value
      }
    }

    // Return the query-bound model if it passes validation; otherwise return null
    return controller.TryValidateModel(queryModel) ? queryModel : null;
  }
}

How I use it in my controller
I replaced the existing model-binding code in my controller with this for each of the actions:

var model = await this.TryGetModelFromBodyOrQueryAsync<MyModelType>();
if (model == null)
    return ValidationProblem(ModelState);

// Proceed with my normal business logic using the valid model

Why Is This Awesome?
• Clean & simple controller actions with no messy manual parameter parsing.
• Supports both old clients (query string) and new clients (JSON body).
• Graceful fallback logic. JSON body is preferred but will fall back to query parameters (or error out).
• It is reusable and generic. It works with any of my public property model classes that are defined in my Model.

Is This Method a Good Approach?
It depends. Supporting both [FromBody] and [FromQuery] is not ideal for long-term API design, but it serves as a practical and effective short-term solution when transitioning existing endpoints from query parameters to JSON body input without breaking existing clients
• Have clients relying on query parameters.
• Want a clean and reusable way to handle dual input methods (JSON and parameters).
• Want validation to work seamlessly for either input method (JSON and parameters).
• Want to avoid complex conditional code scattered in my controllers.

Pros of This Approach
• Backwards compatibility: Existing clients keep working while new clients use JSON.
• Future-proof: Existing clients can switch to JSON body without changing server logic.
• Fewer Breaking Changes: Avoids forcing clients to rewrite request logic.
• Clean controller code: Single line to get your model, no manual parameter parsing.
• Generic and reusable: Can be used with any defined model class.
• Graceful fallback: Prefers JSON body but falls back to query parameters if body is missing or invalid.

Cons of This Approach
• Increased complexity: The API endpoint is no longer truly RESTful in a pure sense because it supports two very different input mechanisms simultaneously.
• Not Idiomatic: ASP.NET Core expects one clear model-binding path (e.g., [FromBody] or [FromQuery]). Supporting both circumvents the framework’s intended design.
• Potential Maintenance Burden: basically supporting two versions of input in one endpoint.
• Validation Complexity: Now have to validate two sources and resolve conflicts cleanly.
• Performance overhead: Minor overhead in parsing and validation twice per request.

Is There a Better Way?
Of course, there always is.
• Separate endpoints: Keep old endpoints accepting query parameters for legacy, and new ones accepting JSON body, rather than mixing both in one action. Create one endpoint that supports query parameters (/v1/endpoint) and another that only accepts a JSON body (/v2/endpoint). This is clean, versioned, and explicit.
• Custom model binders: Implement a custom model binder that can elegantly handle both query and body input, but this requires more advanced ASP.NET Core expertise.
• API Gateway or Middleware: Use an API gateway or middleware that transforms query parameters into JSON bodies before the request reaches your controller.
• Deprecate Gradually: Add support for JSON body now, log usage, and phase out query parameter support over time and eventually simplify the codebase to expect only one model-binding source (JSON).

Is Supporting Both JSON Body and Query Parameters a NoNo?
• Although not a strict “NoNo,” it is generally discouraged for clarity and maintainability reasons.
• APIs should ideally be explicit and consistent in how they accept input, mixing sources could potentially confuse both clients and maintainers.
• During transitions or migrations supporting both input styles is a reasonable and often necessary compromise.
• Keep the logic simple and well documented if supporting both input methods.
• Ultimately, the best practice is to have a clear contract (either query parameters or JSON body).

Why I Chose to Do This

Transition Period During API Modernization I am modernizing older API endpoints that originally only accepted query parameters, but now need to support more expressive JSON payloads. Instead of requiring all clients to switch immediately, I am allowing both input methods temporarily to maintain backward compatibility. This approach ensures that existing clients keep working without forcing major changes on them.
Convenience for Testing and Prototyping When I am testing endpoints in Postman it is easier to pass a few parameters through the URL for quick tests. This is especially helpful during development when the payload is small or simple.

When to Avoid
• Building a new API from scratch.
• For clean consistent POST/PUT API contracts.
• Prioritize long-term maintainability over short-term flexibility.

Summary

How easy is it to implement? • This approach takes a bit of setup since it uses a helper method. • Other options might be trickier, like writing a custom model binder.
How good is it for backwards compatibility? • This is one of the biggest strengths of this method—it lets old clients keep working without changes. • Other approaches might need separate versioned endpoints.
How clear and maintainable is the code? • Mixing query and body input adds a little complexity. • Alternatives that use one clear input model are easier to read and maintain.
Does validation still work well? • Yep. FluentValidation works fine with this method. • Same goes for most alternatives.
Is this how ASP.NET Core is “supposed” to be used? • Not exactly. It bends the usual model binding rules a bit. • Alternatives tend to stick closer to how the framework was designed to work.

Final Thoughts
This approach helps me keep the API backwards compatible without cluttering up the controller logic. It is a practical middle ground while I migrate endpoints, especially since I still need to support some legacy clients for now.

Once I am ready to drop query parameters entirely, I can just update the action signatures and remove the fallback helper with no major refactoring needed.

Bottom Line
• In the short term, I am okay supporting both [FromQuery] and [FromBody]as I am doing this intentionally with clear fallback rules and proper validation.
• For the long term, the plan is to eventually fully migrate to [FromBody] because it is cleaner and aligns better with modern, RESTful API design. Eventually I will deprecate [FromQuery] for POST and PUT requests.
• As a transition strategy my goal is to move each existing endpoint to JSON body input when the time is right.

SQL Server Stored Procedure Design for Flexible Record Lookup

Sean Drew — Thu, 10 Jul 2025 20:49:15 +0000

Using CTEs and JSON to Support Multiple Matching Strategies

When working with data I often run into situations where the available information varies. Sometimes I get a clean patientid, which makes the lookup easy. Other times, all I have is a date of birth and ZIP code or even just an invoice number from billing.

Instead of creating a separate stored procedure for each case or writing messy dynamic SQL, I prefer a cleaner approach. I design one flexible stored procedure that can handle all of these lookup paths while keeping my logic organized which makes the code easier to maintain and helps my system adapt to whatever input it receives.

What This Procedure Supports

Lookups by @patientid
Lookups by @dob and @zip
Lookups by @invoice_no
Optional parameters with fallback matching
Clean modular structure using Common Table Expressions (CTEs)
Output in structured JSON, ideal for API responses

Why Flexible Lookup Matters
I rarely get the same kind of data twice. One part of the system might send me a patientid while another might only have a date of birth and ZIP code or just an invoice number.

Here is how it usually breaks down:

Scenario: API call with internal ID
Inputs I Get: @patientid
Scenario: Registration
Inputs I Get: @dob, @zip
Scenario: Billing or payment inquiry
Inputs I Get: @invoice_no

That kind of variety means I need a lookup approach that can adapt. By building in flexible matching logic, I avoid writing separate stored procedures for each case which helps to keep things consistent, avoid duplication, and help me handle more situations with less code with only one stored procedure instead of multiple.

How It Works
The stored procedure follows a clear order of evaluation:

Scenario: @patientid provided
What Happens: Direct match
Scenario: @dob + @zip provided
What Happens: Match via demographics
Scenario: @invoice_no provided
What Happens: Match via invoice number
Scenario: Multiple match types provided
What Happens: Uses first match in priority order
Scenario: No match
What Happens: Returns empty JSON
Scenario: Any error
What Happens: Returns structured JSON error

Why Use CTEs?
Common Table Expressions (CTEs) help keep the matching logic clean, modular, and easy to extend. Each match strategy goes into its own named block:

Each strategy is easy to isolate and debug
Logic is easier to read and debug
Maintenance is simpler when business rules change
Match types can be reordered or extended without affecting the others

Stored Procedure Walkthrough
This is a simplified version of the procedure that covers all three match types:

use [thedb]
go

drop procedure if exists GetPatientSumInfo
go

set ansi_nulls on
go

set quoted_identifier on
go

create procedure GetPatientSumInfo
  @patientid int = null,
  @dob date = null,
  @zip varchar(20) = null,
  @invnum varchar(50) = null
as
begin
  set nocount on;

  begin try

    ;with patidmatch as (
      select patientid
      from patinfo
      where patientid = @patientid
    ),
    demographicmatch as (
      select p.patientid
      from patinfo as p
      join patinfo_addr as pa on p.patientid = pa.patientid
      where
        @patientid is null and
        @dob is not null and
        @zip is not null and
        p.dob = @dob and
        pa.zip = @zip
    ),
    invoicematch as (
      select distinct p.patientid
      from orderinfo as o
      join patinfo as p on o.patientid = p.patientid
      where
        @patientid is null and
        @invnum is not null and
        o.invnum = @invnum
    ),
    allmatches as (
      select patientid from patidmatch
      union
      select patientid from demographicmatch
      union
      select patientid from invoicematch
    ),
    matchedpatient as (
      select top 1 patientid
      from allmatches
      order by patientid
    )
    select
      p.patientid,
      p.first_name,
      p.last_name,
      p.dob,
      (
        select
          pa.addr_id,
          pa.street,
          pa.city,
          pa.state,
          pa.zip
        from patinfo_addr as pa
        where pa.patientid = p.patientid
        for json path
      ) as address
    from matchedpatient as m
    join patinfo p on p.patientid = m.patientid
    for json path, without_array_wrapper;

  end try
  begin catch
    select
      error_number() as error_number,
      error_message() as error_message,
      error_line() as error_line,
      error_procedure() as error_procedure
    for json path, without_array_wrapper;
  end catch
end

go

Sample Usage
exec GetPatientSumInfo @dob = '1980-05-01', @zip = '12345'

Sample Output

{
  "patientid": 123,
  "first_name": "John",
  "last_name": "Doe",
  "dob": "1980-05-01",
  "address": [
    {
      "addr_id": 789,
      "street": "123 Main St",
      "city": "Springfield",
      "state": "IL",
      "zip": "12345"
    }
  ]
}

Error Handling
If any unexpected error happens (e.g., bad join, null issue, query failure, etc.), the CATCH block will return a JSON response like this:

{
  "error_number": 208,
  "error_message": "Invalid object name 'patinfo'.",
  "error_line": 7,
  "error_procedure": "GetPatientSumInfo"
}

This is great for logging or for passing through to an API that displays a friendly error message to the caller.

Performance Comparison
I like using the CTEs + UNION approach because it keeps the code clean and easy to maintain, but it is not always the fastest option, especially in systems with a lot of traffic or big datasets.

Depending on the situation, I sometimes consider other approaches. Here is a quick comparison I use when deciding what fits best:

CTEs + UNION

Performance: Medium
Maintainability: Excellent
Best Use: Clean modular logic with de-duplication
Notes: Extra cost for de-dupe

CTEs + UNION ALL

Performance: Good
Maintainability: Excellent
Best Use: Clean logic when overlap is impossible
Notes: Slightly faster if safe to use

Single SELECT + OR conditions

Performance: Fastest
Maintainability: Moderate
Best Use: High-performance environments
Notes: Harder to maintain and scale

Temp table / Table variable

Performance: Good
Maintainability: Moderate
Best Use: Multi-step logic or complex joins
Notes: Good for control, indexing flexibility

Indexed view

Performance: Very Fast
Maintainability: Hard to manage
Best Use: High-volume repeat queries
Notes: Overhead on inserts, complex setup

Final Thoughts
This kind of flexible stored procedure has worked really well for me in situations where I need to match records using different types of information. By combining optional parameters, CTEs, and JSON output, I get something that is clean, modern, and easy to connect to an API.

If performance ever becomes a concern, there are some good ways to optimize without throwing out the whole design. I can switch from UNION to UNION ALL if I know the match paths are mutually exclusive. I can also cache commonly used reference data in the application layer, or preload smaller datasets into temp tables inside the procedure. And in heavier systems, I might use indexed views to speed up the matching process by querying a flattened, pre-joined structure.

But for most systems I have worked on, starting with this kind of clean, modular, CTE-based design provides a solid foundation. It is easy to read, easy to extend, and flexible enough to support all kinds of input combinations.

Dynamic API Dispatching in C#

Sean Drew — Tue, 24 Jun 2025 16:06:56 +0000

A flexible way to handle changing JSON inputs without messy "if" or "switch" statements

When building an API, one of the first things you need to figure out is how to route incoming requests to the right piece of code. This is usually done by checking which fields are present in the request and deciding which method to run. For simple APIs, this is often handled with a few if or switch statements. But once your inputs start to vary and grow more complex, this approach becomes hard to manage.

In my case, I needed to build an API endpoint that could handle multiple types of patient-related data. Sometimes a request had a PatientIdNum, other times it had just a date of birth and zip code, or maybe an invoice number. I wanted a way to let the API decide which method to call based on what was sent, without having to write a long list of conditions in code. So I went with a rule-based approach using a feature in C# called reflection.

What is Dispatching?
"Dispatching" simply means sending something to the right place. In APIs, this usually means figuring out which method should handle a request. A "dispatcher" is just a piece of code that makes this decision.

In my case, I wrote a dispatcher that checks the incoming JSON request, matches it to a rule in a JSON file, and then calls the matching method by name. This keeps the routing logic separate from the rest of the business logic and makes it easier to change.

The Problem with Hardcoded Logic
If you've ever written code like this, you know how quickly it can grow:

if (request.PatientIdNum != null)
  return HandleByPatientIdNum(request);
else if (request.PatInfo?.Dob != null && request.PatInfo?.Zip != null)
  return HandleByDemographics(request);
else if (request.OmInfo?.FirstOrDefault()?.InvoiceNo != null)
  return HandleByInvoice(request);
else
  return BadRequest("Insufficient identifiers");

This kind of logic is fine at first, but over time it:
• Gets harder to read and maintain
• Tangles routing and business logic together
• Requires code changes every time the JSON input and rules change

A Simpler Way: Rule-Based Routing
With rule-based routing, you define your routing rules outside of your code, usually in a file. For me, that file is "rules.json". Each rule lists the fields required to trigger a handler method. Here's an example:

[
  {
    "name": "HandleByPatientIdNum",
    "match": ["PatientIdNum"]
  },
  {
    "name": "HandleByDemographics",
    "match": ["patinfo.dob", "patinfo.zip"]
  },
  {
    "name": "HandleByInvoice",
    "match": ["ominvoice.invoice_no"]
  }
]

Basically, each rule says: If these fields are present and not null, then run the method with this "name".

How Reflection Helps
Reflection is a .NET feature that lets your code look at itself while it's running. That means you can find a method by name and call it even if you didn't hardcode it directly. So, if the rule says to call "HandleByPatientIdNum", and that method exists in your controller, you can call it just by using its name (Reflection).

This makes the system very flexible. To add new functionality, you just write a new method and add a new rule to the JSON file (rules.json).

Reflection Scope Note:
For safety and clarity, I restrict the reflection lookup to non-public instance methods only using BindingFlags.NonPublic | BindingFlags.Instance. This avoids exposing public framework or utility methods unintentionally and keeps the dynamic invocation limited to the intended handler methods.

How It Works
The dispatcher loads the rules from the JSON file.

Rule Validation:
Before evaluating incoming requests, I run a simple preflight validator to check for common mistakes in the "rules.json" file. This helps catch issues early and avoid runtime surprises. The validator:
• Confirms all name entries refer to actual methods (using GetMethod(...))
• Ensures match arrays are not empty
• Optionally checks for duplicate or overly generic rules (e.g., rules that match any input)

This step is run at app startup and helps ensure all routing rules are both valid and intentional.

Then:

The request comes in as nested JSON
The JSON is flattened into a dictionary with keys like patinfo.dob or ominvoice.invoice_no
The dispatcher loads the validated rules from rules.json
It checks each rule to see if all required fields are present and not null
It picks the most specific matching rule
It uses reflection to call the method named in the rule ("name")

Flattening the JSON input
You cannot easily check for field presence like "patinfo.dob" or "ominvoice.invoice_no" with a simple dictionary lookup because those values are nested inside JSON objects.

Flattening turns nested structures into a flat dictionary where the keys represent the full JSON path using dot notation, which makes things much easier.

Here is an example of a nested JSON request:

{
  "patinfo": {
    "dob": "1980-05-01",
    "address": {
      "zip": "12345"
    }
  },
  "ominvoice": {
    "invoice_no": "INV123"
  }
}

And the dictionary (flattened) representation of the JSON input.

{
  "patinfo.dob": "1980-05-01",
  "patinfo.address.zip": "12345",
  "ominvoice.invoice_no": "INV123"
}

Each key is a string path pointing to the original value in the nested JSON which is easier to check instead of writing complex null checks or traversal code.

Example Dispatcher Code
This sample code uses the flattened dictionary to check against the rules in "rules.json" and then calls the right method.

// Find the first rule that matches ALL of its required fields
var matchedRule = rules
  // Keep only the rules where every required field in the rule's `Match` array:
  //   - Exists in the flattened input dictionary
  //   - Is not null
  .Where(r => r.Match.All(field =>
      flattenedInput.ContainsKey(field) && flattenedInput[field] != null))

  // If multiple rules match, prefer the one with the most required fields
  // (i.e., the most specific match)
  .OrderByDescending(r => r.Match.Count)

  // Return the first matching rule, or null if none match
  .FirstOrDefault();

// If no rule matched, return a 400 Bad Request
if (matchedRule == null)
  return BadRequest("No matching handler found");

// Use reflection to locate a method on this controller with the same name
// as the rule's `name` field. Assume all handlers are private instance methods.
var method = this.GetType().GetMethod(
    matchedRule.Name,
    BindingFlags.NonPublic | BindingFlags.Instance
);

// If the method doesn't exist (e.g., typo in rules.json), return error
if (method == null)
  return BadRequest($"Handler method '{matchedRule.Name}' not found");

// Dynamically invoke the matched handler method and pass the `request` object
// This assumes all handler methods follow the signature: 
//   Task<IActionResult> MethodName(PatientRequest request)
var result = await (Task<IActionResult>)method.Invoke(
    this,
    new object[] { request }
);

// Return the handler’s result
return result;

And the matching handler methods look like this:

private async Task<IActionResult> HandleByPatientIdNum(PatientRequest request)
{
  // code here
}

private async Task<IActionResult> HandleByDemographics(PatientRequest request)
{
  // code here
}

private async Task<IActionResult> HandleByInvoice(PatientRequest request)
{
  // code here
}

Why This Is Useful
• You can change the rules without touching the code.
• Helps keep routing logic out of the controller methods.
• Avoids giant if-else/switch blocks.
• Easy to scale by just adding new methods and rules.

Final Thoughts
This rule-based dispatch system has made it much easier for me to handle flexible JSON inputs without burying myself in if-then/switch conditions. By flattening the input to a dictionary and matching fields to rules, I let my code decide what to do based on the data. It's clean, adaptable, and easy to extend. If your API needs to handle many different input patterns, this could be a great way to go.

Note:
While externalizing (rules.json) routing rules offers flexibility, the rules themselves must be managed carefully to avoid becoming a new source of complexity.

Identifying Stored Procedures Created or Modified Within a Date Range in SQL Server

Sean Drew — Wed, 18 Jun 2025 20:39:07 +0000

As part of rewriting the documentation for my custom API, I needed to test each endpoint to ensure it behaved as expected, including validating both input and output. During this process, I discovered that several existing stored procedures required modification. Unfortunately, I did not keep track of which procedures were changed. Once the documentation was complete, I had to go back and identify the stored procedures that had been altered.

This experience led me to think more seriously about how to track changes to stored procedures; something that would be valuable for auditing, deployment reviews, and debugging. Having visibility into when a stored procedure was created or last modified can help both developers and database administrators identify recent stored procedure changes.

To address this, I took a look at SQL Server's system catalog views. I decided on the sys.procedures view which contain metadata such as creation and modification dates for stored procedures. This brief write-up outlines how to use T-SQL to retrieve a list of stored procedures that were either created or modified within a specified date range, including the script I wrote to accomplish this task.

The TSQL Query

use [yourdatabase]
go

-- select SPs that were created or modified within a specific date range
select
  schema_name(schema_id) as schema_name, -- retrieve schema name (e.g., 'dbo') for the procedure
  name as procedure_name, -- the name of the stored procedure
  create_date, -- the date the procedure was originally created
  modify_date -- the date the procedure was last altered
from sys.procedures -- contains metadata for user-defined procedures
where create_date between '2025-06-01' and '2025-06-18' or modify_date between '2025-06-01' and '2025-06-18'
order by modify_date desc

What This Query Does
It retrieves a list of stored procedures from your selected SQL database that meet either of the following criteria:

• Created or altered between June 06 2025 and June 18 2025
• Each row in the result set includes:
• The schema name (such as dbo)
• The procedure name
• The creation date
• The last modification date

The results are sorted in descending order by modify_date (most recent appear at the top).

Additional Notes
Each database has their own "sys.procedures" and "sys.objects".

If you want to find other objects besides stored procedures you can use "sys.objects" instead of "sys.procedures". Example: Specify type = 'P' with sys.objects to limit the results to stored procedures.

Here is a query to find other object types such as triggers, user tables, etc.

select
distinct
type,
type_desc
from sys.objects

type type_desc
TT TYPE_TABLE
FN SQL_SCALAR_FUNCTION
UQ UNIQUE_CONSTRAINT
SQ SERVICE_QUEUE
F FOREIGN_KEY_CONSTRAINT
U USER_TABLE
D DEFAULT_CONSTRAINT
PK PRIMARY_KEY_CONSTRAINT
V VIEW
S SYSTEM_TABLE
IT INTERNAL_TABLE
P SQL_STORED_PROCEDURE
TR SQL_TRIGGER

Conclusion
SQL Server makes it easy to find out which stored procedures have been added or changed recently. Using the built-in system views, you can quickly get a list of procedures based on when they were created or last modified. This is helpful when trying to audit recent changes, review what went out in a deployment, or troubleshoot something that suddenly stopped working. Pretty good with keeping track of changes to help with things like documentation.

Customizing Output Caching in ASP.NET Web Forms and C# APIs

Sean Drew — Fri, 30 May 2025 13:55:51 +0000

Efficient performance is essential for responsive, scalable web applications and one way to achieve that and boost performance is through caching. By avoiding repetitive data processing and rendering, caching allows applications to serve content more efficiently. In ASP.NET Web Forms and C# API-based applications, output caching and data caching can help improve load times and reduce server resource usage when applied appropriately.

Good Fit for Caching

Data is read often (rarely written)
Content is shared across users
Calls are slow or expensive
Data freshness is not business-critical

Not a Good Fit for Caching

Data is updated frequently or in real-time
Data is personalized or user-specific
Data is already fast or trivial to retrieve
Data freshness is business-critical

Some Benefits of Using Caching

Reduce server load. By serving cached responses, the server uses fewer CPU, disk, and memory resources.
Improve User Experience. Serving cached responses will make response time faster, smoother, and more responsive.
Scales Better Under Load. Serving cached responses will help the system handle high volumes of traffic without performing full processing for each incoming request.

Caching is a valuable performance tool, but it is not the only option. Other options such as asynchronous processing, batching, and queuing can also help improve responsiveness and reduce system load, especially when caching is not appropriate for situations where frequently updated data or tasks require the most current data.

Why Use Caching to Boot Performance?
Caching reduces the need to repeatedly perform expensive operations for things like database queries, page rendering, data serialization, and network and disk I/O.

Querying a database, especially with complex joins or large datasets can be time-consuming. Caching the result set means subsequent requests can bypass the database entirely and deliver the same data as a cached response.
Calling an external API or service (weather, stock prices, third-party data over HTTP).
Database access over the network (querying a SQL database hosted on another machine or cloud service).
Accessing a microservice (sending requests between services in a distributed system).
Retrieving remote files (documents, images, PDFs, etc.)

Computation or Business Logic
If a response depends on heavy processing (aggregating reports, business rules, etc.) caching can help avoid repeating that work for each request.

Rendering
Is typically for ASP.NET Web Form applications and is the process of generating HTML from server controls (or other content) that will be sent to the browser. In the context of ASP.NET Web Forms, this involves taking server-side controls (like GridView, Label, etc.) and converting them into HTML markup.

Serialization
Is typically for APIs and can be things like serializing return data into JSON or XML. Caching the final JSON or XML result (output caching) is pretty much skipping the rendering/serialization step for repeated (the same) inputs/outputs.

External Resource Access
Fetching data from third-party services or external APIs (weather, stock info, etc.) could introduce latency and maybe even rate limits. Caching these results reduces dependency on the third-party service and external API.

Network and Disk I/O
Reading static files or loading configuration from disk or across the network can also add delays. Caching them in memory will allow for quicker access to that information.

Output Caching
Output Caching in ASP.NET Web Forms
Output caching stores the final rendered HTML of a page and reuses it for subsequent requests. This is particularly effective for pages with static or content that rarely changes and can be represented as more static content.

To implement output caching in a Web Forms application, you can use the OutputCache directive:
<%@ OutputCache Duration="60" VaryByParam="none" %>

Duration defines how long (in seconds) the page is cached.
VaryByParam allows different versions of the cache based on query string or form parameters.

For example, to cache different versions of a product page by productId for 120 seconds
<%@ OutputCache Duration="120" VaryByParam="productId" %>

This ensures that each unique productId results in a distinct cached page.

You can also configure output caching programmatically in the page code-behind which provides more caching behavior control.

    Response.Cache.SetExpires(DateTime.UtcNow.AddMinutes(2));
    Response.Cache.SetCacheability(HttpCacheability.Public);
    Response.Cache.VaryByParams["productId"] = true;

Output Caching in C# APIs
ASP.NET Web API does not include built-in output caching like Web Forms does but basic HTTP response caching can be simulated by using a custom action filter. This filter sets caching headers on the response, allowing browsers or intermediary proxies to cache the result for a specified duration.

Here is a simple example defining a custom action filter:

public class OutputCacheAttribute : ActionFilterAttribute
{
  private readonly int _durationSeconds;

  public OutputCacheAttribute(int durationSeconds)
  {
      _durationSeconds = durationSeconds;
  }

  public override void OnActionExecuted(HttpActionExecutedContext context)
  {
    var response = context.Response;
    if (response != null)
    {
      response.Headers.CacheControl = new CacheControlHeaderValue
      {
        Public = true,
        MaxAge = TimeSpan.FromSeconds(_durationSeconds)
      };
    }
  }
}

You would then use the custom action filter in a controller action doing something like this. This caches the response for 120 seconds and reduces the load on the backend for repeated requests with the same parameters.

[OutputCache(120)]
public IHttpActionResult GetProduct(int id)
{
  var product = _productService.GetProductById(id);
  return Ok(product);
}

Data Caching
In addition to output caching, data caching offers greater flexibility and can be used effectively in both Web Forms and APIs. Data caching is the storing of data temporarily in memory so that it can be retrieved faster the next time it is needed. This reduces expensive operations such as repeated database calls.

Data Caching for ASP.NET Web Forms
In an ASP.NET Web Form application, data caching is implemented using the "System.Web.Caching.Cache" class. Example of getting a list of products and cache it for 5 minutes and then reuse it to avoid repeated "database" access.

ProductRepository.cs

public class ProductRepository
{
  public List<string> GetProducts()
  {
    return new List<string> { "Apple", "Banana", "Cherry" };
  }
}

Default.aspx.cs

public partial class _Default : System.Web.UI.Page
{
  protected void Page_Load(object sender, EventArgs e)
  {
    if (!IsPostBack)
    {
      var products = GetProductsFromCache();
      ListBox1.DataSource = products;
      ListBox1.DataBind();
    }
  }

  private List<string> GetProductsFromCache()
  {
    var products = Cache["ProductList"] as List<string>;
    if (products == null)
    {
      var repo = new ProductRepository();
      products = repo.GetProducts();
      Cache.Insert("ProductList", products, null, DateTime.Now.AddMinutes(5), System.Web.Caching.Cache.NoSlidingExpiration);
    }
    return products;
  }
}

Default.aspx
<asp:ListBox ID="ListBox1" runat="server" Width="200"></asp:ListBox>

Data Caching in an ASP.NET Core API
In ASP.NET Core APIs, data caching is commonly implemented using the built-in IMemoryCache service. This allows you to store frequently requested data (such as database query results) in memory to reduce redundant database calls and improve performance. Example of getting a list of products and cache it for 5 minutes and then reuse it to avoid repeated "database" access.

ProductRepository.cs

public class ProductRepository
{
  public List<string> GetProducts()
  {
    return new List<string> { "Apple", "Banana", "Cherry" };
  }
}

ProductController.cs

using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Caching.Memory;

[ApiController]
[Route("products")]
public class ProductsController : ControllerBase
{
  private readonly IMemoryCache _cache;
  private readonly ProductRepository _repo = new ProductRepository();

  public ProductsController(IMemoryCache cache)
  {
    _cache = cache;
  }

  [HttpGet]
  public List<string> Get()
  {
    if (!_cache.TryGetValue("ProductList", out List<string> products))
    {
      products = _repo.GetProducts();
      _cache.Set("ProductList", products, TimeSpan.FromMinutes(5));
    }

    return products;
  }
}

Program.cs

var builder = WebApplication.CreateBuilder(args);
builder.Services.AddMemoryCache();
builder.Services.AddControllers();

var app = builder.Build();
app.MapControllers();
app.Run();

Conclusion
Caching is a good way to boost ASP.NET Web Forms and C# API application performance.

Output caching is a good way to speed up an entire page or response and is ideal for quickly serving complete pages or responses.
Data Caching is a good way to improve business logic or data access performance and supports reusability and responsiveness at the business logic layer.

Also, it is handy that you can control how long things stay in the cache and even change what gets cached based on things like query strings or user roles. Basically, you can cache what makes sense when it makes sense and also refresh the cache when required.

Filtering and Exporting SQL Stored Procedures with PowerShell

Sean Drew — Tue, 25 Mar 2025 13:50:07 +0000

When managing SQL Server stored procedures, there are times when I need to quickly generate scripts for them, usually for backups, migrations, a specific client or functionality, or documentation. In this document, I show how I use T-SQL and PowerShell to filter stored procedures based on naming patterns and export them into individual .sql files. Each file contains the full DROP IF EXISTS and CREATE script which allows for the easy recreation of the stored procedures.

Running the script. The user account executing this script will require permissions to connect to the target database and read from the "sys.procedures" and "sys.sql_modules" system tables. Additionally, the account must have write permissions for the designated output file directory.

The Script

# Define the SQL Server connection string
$connectionString = "Server=SQLServerName;Database=DBName;Integrated Security=True"

 # Define the output folder for stored procedure scripts
$outputFolder = "C:\API_SPs" # Can also be a network UNC
$logFile = "$outputFolder\ErrorLog.txt" # Log file to store errors
$errorsOccurred = $false  # Flag to track if any errors occurred
$scriptsWritten = $false  # Flag to track if any scripts were actually written

# Ensure output folder exists, create it if necessary
if (!(Test-Path $outputFolder))
{
  New-Item -ItemType Directory -Path $outputFolder -Force | Out-Null
}

# Define SQL query to retrieve stored procedure definitions
$query = @"
SELECT 
 p.name AS ProcedureName,
 s.name AS SchemaName,
 'DROP PROCEDURE IF EXISTS [' + s.name + '].[' + p.name + '];' + CHAR(13) + CHAR(10) +
 'GO' + CHAR(13) + CHAR(10) +
 sm.definition + CHAR(13) + CHAR(10) +
 'GO' AS ScriptContent
FROM sys.procedures p
JOIN sys.schemas s ON p.schema_id = s.schema_id
JOIN sys.sql_modules sm ON p.object_id = sm.object_id
WHERE 
(
 p.name LIKE 'XXX_YYY%' 
 AND p.name NOT IN ('%BACKUP%', '%OLD%')
)
"@

# Function to log errors to a file and display in the console
function Log-Error
{
  param ([string]$message)
  $timestamp = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
  $logEntry = "$timestamp - ERROR: $message"
  Add-Content -Path $logFile -Value $logEntry  # Append error to log file

  # Print error directly to console
  Write-Host "ERROR: $message" -ForegroundColor Red  

  # Mark that an error has occurred
  $global:errorsOccurred = $true
}

# Initialize SQL connection and execute query
$sqlConnection = $null  # Variable to hold the connection object
$procedures = @{}  # Dictionary to store procedure names and their scripts

try
{
  # Create and open SQL connection
  $sqlConnection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
  $sqlConnection.Open()

  # Check if connection is successful
  if ($sqlConnection.State -ne 'Open')
  {
    throw "Failed to open SQL connection."
  }

  # Execute the query
  $sqlCommand = $sqlConnection.CreateCommand()
  $sqlCommand.CommandText = $query
  $reader = $sqlCommand.ExecuteReader()

  # Read query results
  while ($reader.Read())
  {
    # Ensure valid file names by replacing invalid characters
    $procedureName = $reader["ProcedureName"] -replace '[\\\/:*?"<>|]', '_' # replace \\\/:*?"<>| with underscore
    $scriptContent = $reader["ScriptContent"]
    $procedures[$procedureName] = $scriptContent  # Store in PS hashtable/dictionary
  }

  $reader.Close()  # Close the SQL data reader
}
catch
{
  Log-Error "Database error: $_"
}
finally
{
  # Ensure SQL connection is closed to avoid resource leaks
  if ($sqlConnection -ne $null -and $sqlConnection.State -eq 'Open')
  {
    $sqlConnection.Close()
  }
}

# Write stored procedure scripts to files only if there are procedures to save
if ($procedures.Count -gt 0)
{
  foreach ($procedureName in $procedures.Keys) # $procedures is a PS hashtable/dictionary and .Keys retrieves all keys from the hashtable
  {
    try
    {
      $filePath = "$outputFolder\$procedureName.sql"
      # Write the script content to the file using UTF-8 encoding
      [System.IO.File]::WriteAllText($filePath, $procedures[$procedureName], [System.Text.Encoding]::UTF8)
      $scriptsWritten = $true  # Mark that at least one script was successfully written
    }
    catch
    {
      Log-Error "Failed to write file: $filePath - $_"
    }
  }
}

# Final status message
if ($errorsOccurred -and -not $scriptsWritten)
{
    Write-Host "No stored procedure scripts were saved. Errors occurred. See $logFile for details." -ForegroundColor Yellow
}
elseif ($errorsOccurred -and $scriptsWritten)
{
    Write-Host "Stored procedure scripts have been saved to: $outputFolder, but some errors occurred. See $logFile for details." -ForegroundColor Yellow
}
elseif ($scriptsWritten)
{
    Write-Host "Stored procedure scripts have been successfully saved to: $outputFolder" -ForegroundColor Green
}
else
{
    Write-Host "No stored procedures matched the criteria. No scripts were generated." -ForegroundColor Cyan
}

Steps Performed by the Script

Connect to SQL Server: The script establishes a connection to the SQL Server using the provided credentials.
Query the Database: It executes a SQL query to retrieve a list of stored procedures that match the specified criteria (e.g., names starting with "CP_API").
Generate Drop and Create Scripts: For each stored procedure, the script constructs a SQL script containing the DROP PROCEDURE IF EXISTS and CREATE PROCEDURE statements, including the procedure's full definition.
Save to Files: The script generates an individual .sql file for each stored procedure and saves it to the designated directory (e.g., C:\SQLScripts).
Handle Exceptions: Any issues encountered during the execution (such as permission issues, missing procedures, cannot connect to SQL server) are handled by the script. Error messages are written to ErrorLog.txt.

Conclusion
This PowerShell script provides an automated and efficient way to mass generate DROP and CREATE scripts for stored procedures in SQL Server, saving them as individual files for easy deployment or backup. By customizing the SQL query criteria, you can target specific stored procedures, such as those beginning with a particular prefix or excluding certain procedures from the output.

Using this script, database administrators or developers can quickly export the definitions of stored procedures for version control, migrations, or disaster recovery planning. This solution simplifies the process of managing stored procedure scripts, making it a valuable tool for SQL Server environments.

Some Random SQL Error Handling Techniques for Reliability and Transactional Integrity

Sean Drew — Mon, 24 Feb 2025 20:21:16 +0000

Error handling is an important part of writing SQL code. Whether working with stored procedures, triggers, or transactions, proper error handling ensures that database operations remain reliable, maintainable, and capable of handling unexpected situations. In this write-up, I put together some very basic samples of SQL error handling, including TRY...CATCH blocks, logging, transaction management, as well as SAVEPOINT, XACT_ABORT, and stored procedure return codes.

For simplicity, the T-SQL code snippets in this write-up are provided for illustrative purposes only and do not necessarily reflect best coding practices.

1. Fundamentals of SQL Error Handling
TRY...CATCH Block
The TRY...CATCH block is the primary mechanism for handling errors in SQL Server. It allows capturing exceptions and responding appropriately. If an error occurs inside the TRY block, the CATCH block executes, returning error details in a select statement. If you need execution to stop, then you will need to throw the error instead on simply doing a select statement.

begin try
  -- your main sql code here
  -- your main sql code here
end try
begin catch
  select
  error_message() as ErrorMessage,
  error_number() as ErrorNumber,
  error_severity() as ErrorSeverity,
  error_state() as ErrorState
end catch

THROW and RAISEERROR
RAISEERROR allows you to generate custom error messages with specified severity levels.

THROW is used to re-throw captured errors and halt execution.

The point of this code is to demonstrate conditional logic before raising an error in SQL Server using RAISERROR with TRY...CATCH error handling.

-- example of conditional logic before raising an error
declare @somecondition int = 1 -- this will cause the error to be thrown

begin try
  -- conditionally raise an error
  if @somecondition = 1 -- if @somecondition <> 1 then error would not be thrown
  begin
    raiserror('my custom error occurred due to some condition.', 16, 1) -- raise error
  end

  -- your main sql code here
  select 'hello there'
  select 'no error occurred'
end try
begin catch
  -- capture the error and re-throw it
  throw  -- re-throw the error
end catch

Breaking Down the Concept of "Rethrow"

An error occurs in the TRY block.
If @somecondition = 1, the THROW statement inside TRY raises an error.
Execution immediately jumps to the CATCH block.
The CATCH block captures the error.
SQL Server automatically provides access to error details (ERROR_MESSAGE(), ERROR_NUMBER(), etc.).
Just handling the error in CATCH does not automatically stop execution as it needs to be re-raised if necessary.
Using THROW in CATCH (Re-throwing the error)
Instead of logging the error or handling it silently, THROW raises the exact same error again.
This allows the error to propagate up to the caller (e.g., another stored procedure or application).
No need to pass error details manually because SQL Server remembers the error context.

Why "Re-throw" Instead of Just "Throw"?

The term "throw" generally refers to raising a new error.
The term "rethrow" is used when you catch an existing error and raise it again.

When to Use Rethrow?

When you don’t want to change the original error.
When you want the error to propagate up the call stack as if the TRY...CATCH was not there.
When handling errors in nested stored procedures or transaction rollback scenarios.

An alternative version of the SQL code using just THROW without RAISERROR

declare @somecondition int = 1 -- this will cause the error to be thrown

begin try
  -- conditionally throw an error
  if @somecondition = 1 -- if @somecondition <> 1 then the error would not be thrown
  begin
    throw 50001, 'my custom error occurred due to some condition.', 1
  end

  -- your main sql code here
  select 'hello there'
  select 'no error occurred'
end try
begin catch
  -- capture the error and re-throw it
  throw  -- re-throw the error
end catch

Key Differences Between THROW and RAISERROR

Simpler Syntax:
THROW 50001, 'My custom error occurred due to some condition.', 1
No need to specify severity (16) like in RAISERROR.
Built-in THROW for Re-Raising Errors:
THROW inside CATCH automatically re-throws the caught error without needing parameters.
RAISERROR requires capturing and re-raising the original error manually.
Always Uses Severity 16+:
With THROW, the severity must be 50000 or higher, which is the custom error range for SQL server.
RAISERROR allows you to specify lower severity levels.

When to Use THROW Instead of RAISERROR?

When you need simpler and cleaner error handling.
When you don't need advanced formatting. RAISERROR supports message substitution using placeholders.
When using TRY...CATCH blocks, THROW is the preferred way to re-raise exceptions.

2. Transaction Management and Atomicity
Using TRY...CATCH with Transactions
Transactions in SQL Server guarantee atomicity, meaning that all operations within a transaction either succeed as a group or fail as a group. If any part of the process fails, the system rolls back to its original state, preventing partial updates and ensuring database integrity.

Using transactions is crucial when executing multiple SQL statements that must either fully complete or not execute at all to maintain consistency.

Basic Transaction with Error Handling
This sample code shows how a transaction ensures atomicity. If a specific condition is met, an error is thrown which causes the transaction to roll back:

declare @somecondition int = 1 -- this will cause the error to be thrown

begin transaction
begin try
  if @somecondition = 1 -- if @somecondition <> 1 then the error would not be thrown
  begin
    throw 50001, 'my custom error occurred due to some condition.', 1
  end

  -- sql operations (insert, update, delete, etc.)
  commit transaction
end try
begin catch
  rollback transaction
  throw  -- re-throw the error
end catch

If any SQL statement inside the TRY block fails, SQL Server immediately jumps to the CATCH block. The CATCH block rolls back the transaction, undoing any changes made since BEGIN TRANSACTION, thereby preventing partial updates.

Logging Errors in Transactions
Adding error logging to the CATCH block can help track failures and diagnose issues effectively.

begin transaction
begin try
  -- some sql operations (insert, update, delete, etc.)
  commit transaction
end try
begin catch
    rollback transaction

    -- log error details to a custom audit table
    insert into errorlog (errormessage, errorprocedure, errorline, errortime)
    values (ERROR_MESSAGE(), ERROR_PROCEDURE(), ERROR_LINE(), GETDATE())

    select ERROR_MESSAGE() as errormessage
end catch

Transactions are essential for atomicity and when performing multiple dependent SQL operations. ROLLBACK ensures data consistency by reverting changes in case of failure and the TRY…CATCH block allows for logging errors and prevents partial updates. This is important in all operations that interact with SQL and where data integrity is crucial.

Transactions ensure atomicity, which means that when performing multiple dependent SQL operations, they all must succeed together or fail together. ROLLBACK maintains consistency by reverting changes in case of failure and the TRY…CATCH block handles errors effectively while allowing for error logging and preventing partial updates. Logging errors provides valuable debugging information, ensuring better system reliability.

Transactions are essential in any SQL operation where data integrity is critical, ensuring that changes to the database remain consistent and reliable even in the event of failures.

Using SAVEPOINT for Partial Transaction Rollbacks
SAVEPOINT allows rolling back specific parts of a transaction while keeping others intact. In SQL Server, SAVEPOINT provides finer control over transactions by allowing partial rollbacks. Unlike a full ROLLBACK TRANSACTION, which undoes all changes since BEGIN TRANSACTION, SAVEPOINT enables rolling back only a portion of the transaction while keeping the rest intact. This is useful when handling multiple operations where only certain parts should be undone in case of an error.

SAVEPOINT can help preserve successful operations so if an error occurs, you can roll back to the last SAVEPOINT rather than discarding the entire transaction. This can allow for finer control when handling failures within a large transaction which can be helpful when dealing with dependent operations where certain steps should remain committed even if a later step fails and is rolled back.

begin transaction
begin try
  -- insert new record into the orders table
  insert into orders (orderid, customername, orderdate)
  values (101, 'John Doe', getdate())

  -- define savepoint before inserting a new
  -- record into the orderdetails table
  save transaction OrderDetailsSavepoint

  -- insert new record into the orderdetails table
  insert into orderdetails (orderid, productid, quantity)
  values (101, 1, 5)

  -- simulate an error
  throw 50001, 'simulated error occurred after OrderDetails insertion.', 1

  commit transaction
end try
begin catch
  select 'error. rolling back to OrderDetailsSavepoint savepoint.'

  -- rollback only the changes made after the savepoint
  rollback transaction OrderDetailsSavepoint

  select 'continuing with remaining transaction...'

  -- you can still commit other successful operations
  commit transaction
end catch

If a transaction is fully rolled back (full ROLLBACK TRANSACTION), all SAVEPOINTS within it are also discarded. Using SAVEPOINT does not release locks until the full transaction is committed or fully rolled back.

Using XACT_ABORT for Automatic Rollbacks
XACT_ABORT is a session-level (entire transaction) setting in SQL Server that automatically rolls back the entire transaction if a runtime error occurs. This eliminates the need for explicit error handling with ROLLBACK TRANSACTION which can help ensure data integrity with minimal code. It is very useful for bulk inserts, bulk updates and other "batch" type operations as it prevents partial updates in large batch operations.

XACT_ABORT ensures full rollback on failure. If any statement inside a transaction fails, the entire transaction is automatically rolled back. You do not need explicit BEGIN CATCH and ROLLBACK TRANSACTION with every transaction (reduces manual error handling).

set xact_abort on -- turn it on
-- do not need to turn off xact_abort
-- because it applies to the current session or batch
-- after the session ends or a new connection is made
-- xact_abort resets to its default state (off).

begin transaction
begin try
  -- insert new record into the orders table
  insert into orders (orderid, customername, orderdate)
  values (102, 'Jane Doe', getdate())

  -- simulate an error
    insert into orderdetails (orderid, productid, quantity)
    values (102, NULL, 5)  -- NULL is not allowed for ProductID

    commit transaction  -- this will not run if an error occurs
end try
begin catch
    select 'error. xact_abort automatically rolled back the transaction.'
end catch

3. Error Logging and Reporting
Logging Errors to a Table
Storing error details in a log table helps with troubleshooting and debugging.

begin catch
  insert into errorlog (errormessage, errornumber, errorseverity, errorstate)
  values (ERROR_MESSAGE(), ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE())
end catch

It couldn't hurt to index the errorlog table for better query performance and to add other logging information such as SESSION_USER, APP_NAME(), HOST_NAME(), etc. to help make debugging easier and to add more context. If the error occurs in a stored procedure, then adding ERROR_PROCEDURE() and ERROR_LINE() for good measure might also be helpful.

Using Output Parameters for Error Reporting
Stored Procedures can return error messages via an output parameter.

drop procedure if exists dbo.MyProcedure
go

create procedure dbo.MyProcedure @ErrorMessage nvarchar(4000) output
as
begin
  begin try
    -- sql logic
  end try
  begin catch
    set @errormessage = error_message() -- return the error message to the calling code
  end catch
end
go

Conclusion
Using SQL error handling techniques ensures database reliability and maintainability. Using TRY...CATCH, transaction management, and logging to a table can help improve system resilience, handle unexpected situations effectively and maintain system integrity.

Centralizing SQL Connection Handling with a DatabaseHelper Class

Sean Drew — Tue, 11 Feb 2025 20:18:01 +0000

For applications that rely on SQL Server connectivity for real-time data processing and high-volume data operations, transient connection failures can occur due to network issues, server load, timeouts, etc. Implementing a custom SQL connection retry mechanism ensures that these temporary disruptions do not compromise application stability.

In this write-up, I look at a custom GetResilientConnection method in ADO.NET when connecting to SQL Server using SqlClient to ensure my application handles connection disruptions gracefully by implementing automated custom retry logic that allow my applications to attempt reconnections with controlled delays and limits.

Transient Connection Failures
These faults are temporary connectivity issues, often due to network instability or database server connectivity issues. While these errors are typically resolved once the network or server recovers, they can disrupt an application's flow unless handled properly. Some general transient connection examples can include:

• Network-related errors (e.g., error code 40613)
• SQL Server unavailability (e.g., error code 10928)

Implementing Custom Retry Logic in ADO.NET
A simple yet effective way to enhance resilience is by implementing retry logic. When a transient error occurs, the application should wait for a brief period before attempting to reconnect. This code defines a class named DatabaseHelper, which provides a method called GetResilientConnection. This method is responsible for handling transient database connection failures by implementing a retry mechanism.

Sample Retry Pattern Code Example

using System;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Net.Sockets;
using System.Threading;

public class DatabaseHelper
{
  public static SqlConnection GetResilientConnection(string connectionString)
  {
    int maxRetries = 3;  // maximum retry attempts
    int delay = 2000;    // initial delay (2 seconds)

    for (int i = 0; i < maxRetries; i++)
    {
      try
      {
        // attempt to establish a SQL connection
        var connection = new SqlConnection(connectionString);
        connection.Open(); // open the connection
        return connection; // successfully connected, return the connection
      }
      catch (Exception ex) when (IsTransientError(ex)) // catch broader transient errors
      {
        Console.WriteLine($"Transient error encountered: {ex.Message}");
        Console.WriteLine($"Retrying in {delay / 1000} seconds...");

        Thread.Sleep(delay); // wait before retrying (exponential backoff)
        delay *= 2; // double the delay for the next retry
      }
    }

    // if all retries fail, throw an exception indicating failure
    throw new Exception("Max retry attempts exceeded.");
  }

  private static bool IsTransientError(Exception ex)
  {
    // some common SQL server transient error codes
    int[] transientErrors = { 40613, 10928, 40197, 40501, 49918, 1205, 233, -2 };

    if (ex is SqlException sqlEx)
    {
      // check if any of the SQL error numbers match the transientErrors errors array
      if (sqlEx.Errors.Cast<SqlError>().Any(e => transientErrors.Contains(e.Number)))
      {
        return true;
      }
    }

    // none of the erros codes in the transientErrors errors array were found
    // so look at these other types 
    // additional transient errors based on exception types
    return ex is TimeoutException  // SQL command timeout
      || ex is IOException       // general network failure
      || ex is InvalidOperationException // connection unexpectedly closed
      || ex is SocketException; // low-level socket failure
  }
}

This GetResilientConnection example method does the following:
• Attempts to establish a SQL Server connection using the provided connection string.
• Retries up to 3 times if a transient failure occurs, with an increasing wait time between attempts - waits (2s → 4s → 8s) before retrying which can improve application server recovery chances. Retries up to 3 times before throwing an exception.
• Uses IsTransientError to detect common transient errors, including SQL timeouts, deadlocks, and network issues. Detects timeouts, network failures, and connection drops.
• Logs the failure and waits before retrying, using exponential backoff (doubling the delay each time).
• Throws an exception if all retry attempts fail.
• Encapsulated in a reusable helper class and can be called from multiple parts of the application for consistent connection handling.

Example of GetResilientConnection Usage
Instead of rewriting SQL connectivity retry logic everywhere in my application where I need to make a SQL connection, I can simply call DatabaseHelper.GetResilientConnection. When another part of my application references DatabaseHelper, it is using this class as a utility to establish SQL connections with built-in resilience.

using (var connection = DatabaseHelper.GetResilientConnection(connectionString))
{
  Console.WriteLine("Connected successfully!");

  // use the connection here
  // and do your data processing stuff
  // in the context of the connection
}

This approach promotes code maintainability and follows the Single Responsibility Principle (SRP) and ensures consistent handling of connection failures across my application.

The Encapsulation Part
I created the custom DatabaseHelper class to centralize database logic and avoid repeating connection code throughout my application. By keeping (encapsulating) this logic in one place, I improve code maintainability and make it easier to update connection handling in the future (only one place if I want to add functionality or fix bugs).

Doing this also ensures that database connection logic remains separate from the rest of my application logic, which helps with making the codebase cleaner and more modular.

Within this class, the IsTransientError method is marked private, meaning transient error detection is only accessible inside DatabaseHelper, which ensures that SQL connectivity error handling is consistent and can only be leveraged by referencing the DatabaseHelper class.

Basically - since DatabaseHelper.GetResilientConnection(connectionString) is public and static, any part of my application that needs a resilient SQL connection can simply call it. This prevents duplicate code and ensures consistent retry logic across the application.

Why Use It
• Improves database connection reliability in case of temporary issues.
• Ensures automatic retry instead of failing immediately.
• Centralized connection handling reduces redundant code across my application.

Conclusion
By creating and using GetResilientConnection, my application can gracefully handle transient failures and improve reliability without requiring manual intervention and ensures that application database connectivity remains stable(ish). Proper logging and exception handling within the retry mechanism further help diagnose persistent issues, making it a valuable strategy for robust database interactions.

Launching Executables and Switching Focus in Delphi

Sean Drew — Tue, 21 Jan 2025 16:39:14 +0000

I am currently using Delphi to provide support, implement enhancements, and resolve bugs in legacy monolithic Windows applications. One key enhancement involved creating a launch pad to manage multiple pre-existing external executables, requiring seamless interaction with external programs. Whether it’s launching secondary tools like an image importer or bringing an already running application to the foreground, Delphi offers a powerful suite of tools to facilitate these tasks. By leveraging the Windows API and Delphi’s advanced process management capabilities, I can ensure smooth and efficient integration with external applications.

In this write up, I show how to achieve this functionality using Windows API and Delphi code.

Key Steps
Check if the Process is Running
Use the "IsProcessRunning" function to determine if the target application is already running. This function scans the active processes and matches their executable names with the desired process name.

Find the Window Associated with the Process
If the process is running, use the "FindWindowByProcess" function to locate the window handle of the application.

Bring the Application to the Foreground
Once the window handle is retrieved, the "SetForegroundWindow" API is used to bring the application window to the front. If minimized, the "ShowWindow" API restores it.

Launch the Application if it is Not Running
If the application is not running, the "ShellExecuteEx" API launches it. After launching, the program waits for the application window to appear and then brings it to the foreground.

Example Code
The following code implements the steps for a button click event in my Delphi application. This serves as the core logic and relies on two supporting functions.

procedure TForm1.btnMyButtonClientEvent(Sender: TObject);
var
  ExecInfo: TShellExecuteInfo; // structure to define execution parameters
  Handle: HWND;               // handle to the window of the external application
begin
  // check if the process "TheExternalProgram.exe" is already running
  if IsProcessRunning('TheExternalProgram.exe') then
  begin
    // ff the process is running, find its window by its title
    Handle := FindWindow(nil, 'Extenal Program Window Title');
    if Handle <> 0 then
    begin
      // ff the window is found, restore it if minimized
      ShowWindow(Handle, SW_RESTORE);
      // bring the application window to the foreground
      SetForegroundWindow(Handle);
      end;
  end
  else
  begin
    // if the external executable is not running then prepare to launch it
    ZeroMemory(@ExecInfo, SizeOf(ExecInfo)); // clear the structure
    ExecInfo.cbSize := SizeOf(ExecInfo);  // set the size of the structure
    ExecInfo.fMask := SEE_MASK_NOCLOSEPROCESS; // ensure process handle remains open after launch
    ExecInfo.lpFile := 'c:\thepath\TheExternalProgram.exe'; // path to the executable
    ExecInfo.nShow := SW_SHOWNORMAL; // show the application normally

    // attempt to launch the executable
    if ShellExecuteEx(@ExecInfo) then
    begin
      // wait for the external exe main window to appear
      repeat
        Handle := FindWindow(nil, 'Extenal Program Window Title');
        Sleep(100); // pause for 100ms to allow the external exe to initialize
      until (Handle <> 0); // exit loop once the window handle is found

      // restore the external exe window if minimized and bring it to the foreground
      ShowWindow(Handle, SW_RESTORE);
      SetForegroundWindow(Handle);
    end
    else
    begin
      // show an error message ff launching the external exe fails
      MessageDlg('Failed to launch TheExternalProgram.exe', mtInformation, [mbOk], 0);
    end;
  end;
end;

Supporting Functions
1. Checking if the Process is Running
This supporting code is for the "IsProcessRunning" function. This function checks whether a specific process, identified by its executable name, is currently running on the system. It takes a process name as input, takes a snapshot of all running processes, and compares each process name to the target. If it finds a match, the function returns True, indicating the process is running; otherwise, it returns False.

function IsProcessRunning(const AProcessName: string): Boolean;
var
  SnapShot: THandle; // handle to the process snapshot
  ProcessEntry: TProcessEntry32; // structure to store process information
begin
      Result := False; // default to process not running
  SnapShot := CreateToolhelp32Snapshot(TH32CS_SNAPPROCESS, 0); // take a snapshot of all processes
  if SnapShot = INVALID_HANDLE_VALUE then Exit;

  ProcessEntry.dwSize := SizeOf(TProcessEntry32); // initialize the structure size

  if Process32First(SnapShot, ProcessEntry) then // iterate through the processes in the snapshot
  begin
    repeat
      // compare each process name with the target process name (case-insensitive)
      if SameText(ProcessEntry.szExeFile, AProcessName) then
      begin
        Result := True; // process is running
        Break;
      end;
    until not Process32Next(SnapShot, ProcessEntry); // move to the next process
  end;

  CloseHandle(SnapShot); // release the snapshot handle
end;

2. Finding the Window Associated with a Process
This supporting code is for the "FindWindowByProcess" function. This function searches for a window that is associated with a running process by matching the process name. It iterates through all processes and their windows, comparing the process ID of each window to the target process. If a match is found, it returns the handle of the corresponding window.

function FindWindowByProcess(const ProcessName: string): HWND;
var
  Snapshot: THandle; // handle to the process snapshot
  ProcessEntry: TProcessEntry32; // Structure to store process information
  Handle: HWND; // handle to the window
begin
  Result := 0; // default to no window found
  Snapshot := CreateToolhelp32Snapshot(TH32CS_SNAPPROCESS, 0); // take a snapshot of all processes
  if Snapshot = INVALID_HANDLE_VALUE then Exit;

  ProcessEntry.dwSize := SizeOf(ProcessEntry); // initialize the structure size
  // iterate through the processes in the snapshot
  if Process32First(Snapshot, ProcessEntry) then
  begin
    repeat
      // check if the process name matches the target name (case-insensitive)
      if AnsiCompareText(ProcessEntry.szExeFile, ProcessName) = 0 then
      begin
        // iterate through all top-level windows
        Handle := FindWindow(nil, nil);
        while Handle <> 0 do
        begin
          // check if the window belongs to the target process
          if GetWindowThreadProcessId(Handle, nil) = ProcessEntry.th32ProcessID then
          begin
            Result := Handle; // found the window handle
            Break;
          end;
          Handle := GetWindow(Handle, GW_HWNDNEXT); // move to the next window
        end;
        Break;
      end;
    until not Process32Next(Snapshot, ProcessEntry); // move to the next process
  end;

  CloseHandle(Snapshot); // release the snapshot handle
end;

A Brief Note About the Order of Things
In Delphi, the "IsProcessRunning" and "FindWindowByProcess" functions must be defined before the "btnMyButtonClick" event handler in the code. This order is essential because the button click event needs to reference these functions and they must be available at the time the event is triggered. By defining these functions earlier in my code, I ensure that the button click handler can successfully call them to check if the process is running and to find the associated window.

Conclusion
Incorporating external executables and managing their interactions within a Delphi application is essential when working with legacy systems. By leveraging Delphi’s integration with the Windows API and utilizing key API functions such as "ShellExecuteEx", "ShowWindow", and "SetForegroundWindow", I can easily check if the external program is running, bring its window to the foreground if necessary, and launch it if it’s not already active.