Carrigan.SqlTools.Generators.SqlServer 0.7.0

<a id="carrigan.sqltools.generators.sqlserver"></a>

Carrigan.SqlTools.Generators.SqlServer

Carrigan.SqlTools.Generators.SqlServer is a .NET library that simplifies SQL generation for Microsoft SQL Server while still giving you control when you need it.

It automatically generates SELECT, INSERT, UPDATE, and DELETE statements using reflection. Carrigan.SqlTools.Generators.SqlServer adds a safe, object-oriented API for building more advanced queries.

The companion library Carrigan.SqlTools.Clients.SqlServer extends the generator by wrapping ADO.NET to execute generated queries, map rows to objects, and handle decryption of encrypted properties.

The transitive dependency Carrigan.Core provides interfaces, property attributes for custom property-level encryption, and shared helper functionality.

This package includes Roslyn analyzers bundled with the package to assist with safe usage patterns.

SQL Server is a trademark of Microsoft Corporation. This project is not affiliated with or endorsed by Microsoft.

This library may generate or execute SQL. Review generated SQL before use in production systems. Always test against non-production databases first.

Use caution with schema, migration, and data-modifying operations. The authors are not responsible for data loss, downtime, or unintended database changes.

---

Table of Contents

• Features
• Installation
• Getting Started Examples
  • Select All Rows
  • Select by Id
  • Insert
  • Insert with Auto Id
  • Update by Id
  • Update by Id (selected columns)
  • Delete
  • Delete by Id (multiple keys)
• More Complex Examples
  • Select with Joins and Order By
  • Select with Two Part Order By
  • Delete with Join and Where
  • Select Count With Where
  • Update with Joins and Where
  • Aggregate Expression Examples
• Attribute Examples
  • Table, Column and Key
  • Identifier and Primary Key
  • Procedure and Parameter
• Running Queries (Async & Non-Async)
  • Async: ExecuteNonQueryAsync / ExecuteScalarAsync / ExecuteReaderAsync<T>
  • Non-Async: ExecuteNonQuery / ExecuteScalar / ExecuteReader<T>
• Simple ADO.NET Example With SqlQuery
• Data Type Mappings
  • Default Parameter / Return Types
  • Allowed Override Types
  • Allowed Override Types With Warnings
• ExampleEncryptor (AesGcm-based) and IDecrypters
• License

---

Features

• Automatic CRUD generation
  Build SELECT, INSERT, UPDATE, and DELETE queries with reflection.

• Carrigan.Core integration
  Interfaces and property-level attributes enable property-level encryption and decryption.

• Manual query builder
  Safely construct advanced SQL with JOIN, predicates (e.g., Equal, GreaterThan, IsNull, Like, And, Or, Xor, Not), ORDER BY, and pagination (OFFSET/FETCH NEXT).

• Dictionary → object mapping
  Use the invocation system to populate typed models from database rows.

• Focused on SQL Server
  SQL output targets Microsoft SQL Server.

• Execution helpers
  Async commands (CommandsAsync) and non-async commands (Commands) for running queries and reading results.

Table of Contents

---

Installation

Carrigan.SqlTools.Generators.SqlServer is available as a NuGet package:

dotnet add package Carrigan.SqlTools.Generators.SqlServer

For SQL Server execution helpers:

dotnet add package Carrigan.SqlTools.Clients.SqlServer

Table of Contents

---

Getting Started Examples

We use SqlGenerator<T> to produce a SqlQuery (with QueryText, CommandType, and Parameters).

All examples use the following using statements to keep the code examples focused on SQL generation.

using Carrigan.SqlTools.Attributes;
using Carrigan.SqlTools.Base.Tests.Helpers;
using Carrigan.SqlTools.Base.Tests.TestEntities;
using Carrigan.SqlTools.Sets;
using Carrigan.SqlTools.SqlGenerators;
using Carrigan.SqlTools.SqlServer;
using System.Text;

// Example data models
public class Customer
{
    [PrimaryKey] // Note: PrimaryKey takes precedence over Key for the SQL generator.
    public int Id { get; set; }
    public string Name { get; set; } = "";
    public string Email { get; set; } = "";
    public string Phone { get; set; } = "";
}

public class Order
{
    [PrimaryKey] // Required attribute for certain SQL generation methods.
    public int Id { get; set; }
    public int CustomerId { get; set; }
    public int PaymentMethodId { get; set; }
    public DateTime OrderDate { get; set; }
    public decimal Total { get; set; }
}

// Generators
public SqlGenerator<Customer> customerGenerator = new();

Table of Contents

Select All Rows

SqlQuery query = customerGenerator.SelectAll();
// SELECT [Customer].* FROM [Customer]

Table of Contents

Select by Id

A key attribute is required. Composite keys are supported by marking multiple key properties.

Customer entity = new() { Id = 42 };
SqlQuery query = customerGenerator.SelectById(entity);
// SELECT [Customer].* 
// FROM [Customer] 
// WHERE ([Customer].[Id] = @Id_1)

Table of Contents

Insert

Customer entity = new()
{
    Id = 42,
    Name = "Hank",
    Email = "Hank@example.com",
    Phone = "+1(555)555-5555"
};
InsertBuilder<Customer> insertBuilder = new()
{
    Records = [entity]
};
SqlQuery query = customerGenerator.Insert(insertBuilder);

// INSERT INTO [Customer] ([Id], [Name], [Email], [Phone])
// VALUES (@Id_1, @Name_2, @Email_3, @Phone_4);

Table of Contents

Insert with Auto Id

A key attribute is required, and identity columns must be generated by the database.

Customer entity = new() 
{ 
    Name = "Hank", 
    Email = "Hank@example.com",
    Phone = "+1(555)555-5555" 
};
SqlQuery query = customerGenerator.InsertAutoId(entity);

// DECLARE @OutputTable TABLE (InsertedId INT NOT NULL);
// INSERT INTO [Customer] ([Name], [Email], [Phone])
// OUTPUT INSERTED.[Id] INTO @OutputTable
// VALUES (@Name_1, @Email_2, @Phone_3);
// SELECT [InsertedId] FROM @OutputTable;

Table of Contents

Update by Id

A key attribute is required. Composite keys are supported by marking multiple key properties.

Customer entity = new() 
{ 
    Id = 42, 
    Name = "Hank", 
    Email = "Hank@example.com",
    Phone = "+1(555)555-5555"
};
SqlQuery query = customerGenerator.UpdateById(entity);

// UPDATE [Customer] SET [Name] = @Name_1, [Email] = @Email_2, [Phone] = @Phone_3
// WHERE [Id] = @Id_4;

Table of Contents

<a id="update-by-id-selected-columns"></a>

Update by Id (selected columns)

A key attribute is required. Composite keys are supported by marking multiple key properties.

ColumnCollection<T> validates property names and throws an error when a property name is not valid.

ColumnCollection<Customer> columns = new(nameof(Customer.Email));
Customer entity = new() { Id = 42, Name = "Hank", Email = "Hank@example.gov" };
SqlQuery query = customerGenerator.UpdateById(entity, columns);
// UPDATE [Customer] 
// SET [Email] = @Email_1 
// WHERE [Id] = @Id_2;

Table of Contents

Delete

A key attribute is required. Composite keys are supported by marking multiple key properties.

Customer entity = new() { Id = 42 };
SqlQuery query = customerGenerator.Delete(entity);
// DELETE FROM [Customer] WHERE ([Customer].[Id] = @Id_1)

Table of Contents

<a id="delete-by-id-multiple-keys"></a>

Delete by Id (multiple keys)

A key attribute is required. Composite keys are supported by marking multiple key properties.

Customer[] entities = [new() { Id = 1 }, new() { Id = 2 }];
SqlQuery query = customerGenerator.DeleteById(entities);
// DELETE FROM [Customer] 
// WHERE (([Customer].[Id] = @Id_1) 
//    OR ([Customer].[Id] = @Id_2))

Table of Contents

---

More Complex Examples

We use SqlGenerator<T> to produce a SqlQuery (with QueryText, CommandType, and Parameters).

All examples use the following using statements to keep the code examples focused on SQL generation.

using Carrigan.SqlTools.Attributes;
using Carrigan.SqlTools.JoinTypes;
using Carrigan.SqlTools.OrderByClause;
using Carrigan.SqlTools.PredicatesLogic;
using Carrigan.SqlTools.Sets;
using Carrigan.SqlTools.SqlGenerators;
using Carrigan.SqlTools.SqlServer;

// Example data models
public class Customer
{
    [PrimaryKey] // Note: PrimaryKey takes precedence over Key for the SQL generator.
    public int Id { get; set; }
    public string Name { get; set; } = "";
    public string Email { get; set; } = "";
    public string Phone { get; set; } = "";
}

public class Order
{
    [PrimaryKey] // Required attribute for certain SQL generation methods.
    public int Id { get; set; }
    public int CustomerId { get; set; }
    public int PaymentMethodId { get; set; }
    public DateTime OrderDate { get; set; }
    public decimal Total { get; set; }
}

// Generators
public SqlGenerator<Customer> customerGenerator = new();
public SqlGenerator<Order> orderGenerator = new();

Select with Joins and Order By

ColumnEqualsColumn<LeftT, RightT> validates property names and throws an error when a property name is not valid.

OrderBy<Order> validates property names and throws an error when a property name is not valid.

ColumnEqualsColumn<Customer, Order> predicate = new(nameof(Customer.Id), nameof(Order.CustomerId));
InnerJoin<Order> join = new(predicate);

OrderBy<Order> orderByOrderDate = new(nameof(Order.OrderDate));

SelectBuilder<Customer> selectBuilder = new()
{
    Joins = join,
    OrderBys = orderByOrderDate
};

SqlQuery query = customerGenerator.Select(selectBuilder);

// SELECT [Customer].* 
// FROM [Customer] 
// INNER JOIN [Order] 
//    ON ([Customer].[Id] = [Order].[CustomerId]) 
// ORDER BY [Order].[OrderDate] ASC

Table of Contents

Select with Two Part Order By

ColumnEqualsColumn<LeftT, RightT> validates property names and throws an error when a property name is not valid.

OrderBy<Order> validates property names and throws an error when a property name is not valid.

ColumnEqualsColumn<Customer, Order> predicate = new(nameof(Customer.Id), nameof(Order.CustomerId));

InnerJoin<Order> join = new(predicate);

OrderBy<Order> orderByOrderDate = new(nameof(Order.OrderDate));
OrderBy<Customer> orderByCustomerId = new(nameof(Customer.Id), SortDirectionEnum.Descending);
OrderBys orderBys = new(orderByCustomerId, orderByOrderDate);

SelectBuilder<Customer> selectBuilder = new()
{
    Joins = join,
    OrderBys = orderBys
};

SqlQuery query = customerGenerator.Select(selectBuilder);

// SELECT [Customer].* 
// FROM [Customer] 
// INNER JOIN [Order] 
//    ON ([Customer].[Id] = [Order].[CustomerId])
// ORDER BY [Customer].[Id] DESC,
//          [Order].[OrderDate] ASC

Table of Contents

Delete with Join and Where

ColumnEqualsColumn<LeftT, RightT> validates property names and throws an error when a property name is not valid.

ColumnValue<T> validates property names and throws an error when a property name is not valid.

ColumnEqualsColumn<Customer, Order> predicate = new(nameof(Customer.Id), nameof(Order.CustomerId));

InnerJoin<Customer> join = new(predicate);

ColumnValue<Customer> customerEmail = new(nameof(Customer.Email), "spam@example.com");

DeleteBuilder<Order> deleteBuilder = new()
{
    Joins = join,
    Where = customerEmail
};

SqlQuery query = orderGenerator.Delete(deleteBuilder);

// DELETE [Order] 
// FROM [Order] 
// INNER JOIN [Customer] 
//    ON ([Customer].[Id] = [Order].[CustomerId]) 
// WHERE ([Customer].[Email] = @Email_1)

Table of Contents

Select Count With Where

Column<T> validates property names and throws an error when a property name is not valid.

Column<Order> totalCol = new(nameof(Order.Total));
Parameter minTotal = new(500m, "Total");
GreaterThan greaterThan = new(totalCol, minTotal);

SqlQuery query = orderGenerator.SelectCount(null, null, null, greaterThan);

// SELECT COUNT([Order].[Id]) 
// FROM [Order] 
// WHERE ([Order].[Total] > @Total_1)

Table of Contents

Update with Joins and Where

ColumnCollection<T> validates property names and throws an error when a property name is not valid.

ColumnEqualsColumn<LeftT, RightT> validates property names and throws an error when a property name is not valid.

ColumnValue<T> validates property names and throws an error when a property name is not valid.

Order entity = new() { Id = 10, Total = 123.45m };

ColumnCollection<Order> columnCollection = new(nameof(Order.Total));

ColumnEqualsColumn<Order, Customer> predicate = new(nameof(Order.CustomerId), nameof(Customer.Id));

InnerJoin<Customer> join = new(predicate);

ColumnValue<Customer> customerEmailEquals = new(nameof(Customer.Email), "spam@example.com");

UpdateBuilder<Order> updateBuilder = new()
{
    Values = entity,
    UpdateColumns = columnCollection,
    Joins = join,
    Where = customerEmailEquals
};

SqlQuery query = orderGenerator.Update(updateBuilder);

// UPDATE [Order] 
// SET [Order].[Total] = @Total_1 
// FROM [Order] 
// INNER JOIN [Customer] 
//    ON ([Order].[CustomerId] = [Customer].[Id]) 
// WHERE ([Customer].[Email] = @Email_2)

Table of Contents

Aggregate Expression Examples

Column<Grades> gradePoint = new(nameof(Grades.GradePoint));

SelectBuilder<Grades> selectBuilder = new()
{
    Selects = new SelectTags
    (
        SelectTagGenerator.Get<Grades>(nameof(Grades.StudentId)),
        SelectTagGenerator.Get<Grades>(nameof(Grades.CourseCode)),
        new SelectTag(new Average(gradePoint), "AverageGradePoint"),
        new SelectTag(new Sum(gradePoint), "TotalGradePoints"),
        new SelectTag(new Min(gradePoint), "MinimumGradePoint"),
        new SelectTag(new Max(gradePoint), "MaximumGradePoint"),
        new SelectTag(new Count(gradePoint), "GradePointCount")
    ),
    GroupBys = GroupBys
        .New<Grades>(nameof(Grades.StudentId))
        .Append<Grades>(nameof(Grades.CourseCode))
};

SqlQuery query = selectBuilder.AsSqlQuery();

//  SELECT 
//      [Grades].[StudentId], 
//      [Grades].[CourseCode], 
//      AVG([Grades].[GradePoint]) AS [AverageGradePoint], 
//      SUM([Grades].[GradePoint]) AS [TotalGradePoints],
//      MIN([Grades].[GradePoint]) AS [MinimumGradePoint], 
//      MAX([Grades].[GradePoint]) AS [MaximumGradePoint], 
//      COUNT([Grades].[GradePoint]) AS [GradePointCount] 
//  FROM [Grades] 
//  GROUP BY 
//      [Grades].[StudentId],
//      [Grades].[CourseCode]

---

Attribute Examples

We use SqlGenerator<T> to produce a SqlQuery (with QueryText, CommandType, and Parameters).

You can use the [Table] attribute from System.ComponentModel.DataAnnotations.Schema to override the table name, otherwise the table name is assumed to be the same as the class. You can also specify the schema name or not.

All examples use using statements to keep the code examples focused on SQL generation.

using Carrigan.SqlTools.Attributes;
using Carrigan.SqlTools.Base.Tests.Helpers;
using Carrigan.SqlTools.Base.Tests.TestEntities;
using Carrigan.SqlTools.SqlGenerators;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using Carrigan.SqlTools.SqlServer;

// Generators
public SqlGenerator<PhoneModel> phoneGenerator = new();
public SqlGenerator<EmailModel> emailGenerator = new();
public SqlGenerator<ProcedureExec> procedureExecGenerator = new();

<a id="table-column-and-key"></a>

Table, Column and Key

[Table("Phone", Schema="schema")]
internal class PhoneModel
{
    [Key]
    public int Id { get; set; }
    public int CustomerId { get; set; }
    [Column("Phone")]
    public string? PhoneNumber { get; set; }
}

PhoneModel phone = new()
{
    Id = 2718,
    CustomerId = 3141,
    PhoneNumber = "07700 900461"
};
SqlQuery query = phoneGenerator.UpdateById(phone);

// UPDATE [schema].[Phone] 
// SET [CustomerId] = @CustomerId_1, [Phone] = @Phone_2
// WHERE [Id] = @Id_3;

Table of Contents

Identifier and Primary Key

[Identifier("Email", "schema")]
internal class EmailModel
{
    [PrimaryKey]
    public int Id { get; set; }
    public int CustomerId { get; set; }
    [Identifier("Email")]
    public string? EmailAddress { get; set; }
}

EmailModel email = new()
{
    Id = 10,
    CustomerId = 313,
    EmailAddress = "Exterminate@GenericTinCanLand.gov"
};
SqlQuery query = emailGenerator.UpdateById(email);

// UPDATE [schema].[Email] 
// SET [CustomerId] = @CustomerId_1, [Email] = @Email_2 
// WHERE [Id] = @Id_3;

Table of Contents

Procedure and Parameter

[Identifier("UpdateThing", "schema")]
internal class ProcedureExec
{
    [Parameter("SomeValue")]
    public string? ValueColumn { get; set; }
}

ProcedureExec procedureExec = new()
{
    ValueColumn = "DangIt"
};
SqlQuery query = procedureExecGenerator.Procedure(procedureExec);

// [schema].[UpdateThing]

Table of Contents

---

<a id="running-queries-async--non-async"></a>

Running Queries (Async & Non-Async)

The SQL Server helpers live in Carrigan.SqlTools.Clients.SqlServer.
These examples use using statements to keep the examples focused on command execution.

using System.Collections.Generic;
using System.Data.Common;
using Carrigan.Core.Interfaces;
using Carrigan.SqlTools.SqlGenerators;
using Carrigan.SqlTools.SqlServer;
using Microsoft.Data.SqlClient;

<a id="async-executenonqueryasync--executescalarasync--executereaderasynct"></a>

Async: ExecuteNonQueryAsync / ExecuteScalarAsync / ExecuteReaderAsync<T>

// Build a query (example: SelectAll)
SqlQuery query = customerGenerator.SelectAll();

SqlConnection connection = new("<your connection string>");
DbTransaction? transaction = null;

// Implement IDecrypters (see ExampleEncryptor section)
IDecrypters decrypters = new MyDecrypters();

// Execute (async)
IEnumerable<Customer> rows =
    await CommandsAsync.ExecuteReaderAsync<Customer>(query, transaction, connection, decrypters);

// Write/update (async)
int affected =
    await CommandsAsync.ExecuteNonQueryAsync(query, transaction, connection);

// Single scalar (async)
object result =
    await CommandsAsync.ExecuteScalarAsync(query, transaction, connection);

Example Encryptor (AesGcm-based) and IDecrypters

Table of Contents

<a id="non-async-executenonquery--executescalar--executereadert"></a>

Non-Async: ExecuteNonQuery / ExecuteScalar / ExecuteReader<T>

Customer[] toDelete = [new() { Id = 7 }];
SqlQuery query = customerGenerator.DeleteById(toDelete);

SqlConnection connection = new SqlConnection("Server=.;Database=AppDb;Integrated Security=true;");
DbTransaction? transaction = null;

// IDecrypters (same interface as async)
IDecrypters decrypters = new MyDecrypters();

// Execute (sync)
int affected = Commands.ExecuteNonQuery(query, transaction, connection);
object scalar = Commands.ExecuteScalar(query, transaction, connection);
IEnumerable<Customer> customers =
    Commands.ExecuteReader<Customer>(query, transaction, connection, decrypters);

Table of Contents

---

<a id="simple-ado.net-example-with-sqlquery"></a>

Simple ADO.NET Example With SqlQuery

If you prefer raw ADO.NET, you can still use SqlQuery for the SQL text, command type, and parameters:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using Carrigan.SqlTools.SqlGenerators;
using Microsoft.Data.SqlClient;

// 1) Generate the query
SqlQuery query = customerGenerator.SelectById(new Customer { Id = 42 });

// 2) Use ADO.NET directly
SqlConnection connection = new SqlConnection("Server=.;Database=AppDb;Integrated Security=true;");
connection.Open();

DbCommand command = connection.CreateCommand();
command.CommandText = query.QueryText;   // ← from SqlQuery
command.CommandType = query.CommandType; // ← from SqlQuery

// Add parameters
foreach (KeyValuePair<string, object?> p in query.Parameters)
{
    command.Parameters.Add(new SqlParameter(p.Key, p.Value ?? DBNull.Value));
}

// Execute
DbDataReader reader = command.ExecuteReader();
// ... map rows (or use invoker) ...
reader.Close();
connection.Close();

Table of Contents

---

Data Type Mappings

<a id="default-parameter-return-types"></a>

Default Parameter / Return Types

C# CLR Type	Default SqlDbType
Guid	UniqueIdentifier
string	NVarChar
char	NChar
byte[]	VarBinary
bool	Bit
byte	TinyInt
sbyte	SmallInt
short	SmallInt
int	Int
long	BigInt
float	Real
double	Float
decimal	Decimal
DateTime	DateTime2
DateOnly	Date
TimeOnly	Time
DateTimeOffset	DateTimeOffset
XDocument	XML
XmlDocument	XML

Table of Contents

Allowed Override Types

C# CLR Type	Allowed SqlTypeAttribute-derived attributes	SqlDbType values allowed by those attributes
byte[]	SqlBinaryAttribute<br>SqlVarBinaryMaxAttribute	SqlDbType.Binary<br>SqlDbType.VarBinary
char	SqlCharAttribute	SqlDbType.Char<br>SqlDbType.NChar<br>SqlDbType.NVarChar<br>SqlDbType.VarChar
string	SqlCharAttribute<br>SqlVarCharMaxAttribute	SqlDbType.Char<br>SqlDbType.NChar<br>SqlDbType.NVarChar<br>SqlDbType.VarChar
DateTime	SqlDateTime2Attribute	SqlDbType.DateTime2
DateOnly	SqlDateAttribute	SqlDbType.Date
TimeOnly	SqlTimeAttribute	SqlDbType.Time
DateTimeOffset	SqlDateTimeOffsetAttribute	SqlDbType.DateTimeOffset
double	SqlFloatAttribute	SqlDbType.Float
decimal	SqlDecimalAttribute	SqlDbType.Decimal

Table of Contents

Allowed Override Types With Warnings

C# CLR Type	Allowed SqlTypeAttribute-derived attributes	SqlDbType values allowed by those attributes
byte[]	SqlImageAttribute	SqlDbType.Image
char	SqlVarCharMaxAttribute<br>SqlTextAttribute	SqlDbType.VarChar<br>SqlDbType.NVarChar<br>SqlDbType.Text<br>SqlDbType.NText
string	SqlTextAttribute	SqlDbType.Text<br>SqlDbType.NText
DateTime	SqlDateTimeAttribute<br>SqlDateAttribute<br>SqlTimeAttribute	SqlDbType.DateTime<br>SqlDbType.SmallDateTime<br>SqlDbType.Date<br>SqlDbType.Time
DateOnly	SqlDateTimeAttribute<br>SqlDateTime2Attribute	SqlDbType.DateTime<br>SqlDbType.SmallDateTime<br>SqlDbType.DateTime2
TimeOnly	SqlDateTimeAttribute<br>SqlDateTime2Attribute	SqlDbType.DateTime<br>SqlDbType.SmallDateTime<br>SqlDbType.DateTime2
float	SqlFloatAttribute<br>SqlDecimalAttribute<br>SqlMoneyAttribute	SqlDbType.Float<br>SqlDbType.Decimal<br>SqlDbType.Money<br>SqlDbType.SmallMoney
double	SqlDecimalAttribute<br>SqlMoneyAttribute	SqlDbType.Decimal<br>SqlDbType.Money<br>SqlDbType.SmallMoney
decimal	SqlFloatAttribute<br>SqlMoneyAttribute	SqlDbType.Float<br>SqlDbType.Money<br>SqlDbType.SmallMoney

Table of Contents

---

<a id="exampleencryptor-aesgcm-based-and-idecrypters"></a>

ExampleEncryptor (AesGcm-based) and IDecrypters

Below is an example encryptor that uses an AesGcm-style API. Replace key handling with your own secure storage.

//THIS IS JUST A SIMPLE EXAMPLE OF A ENCRYPTION CLASS
//I AM NOT A CRYPTOGRAPHIC EXPERT, DO NOT USE THIS EXAMPLE IN A REAL SYSTEM.

using System;
using System.Text;
using System.Security.Cryptography;
using Carrigan.Core.Interfaces;

public sealed class ExampleNonceGenerator : INonceGenerator
{
    public byte[] GenerateNonce()
    {
        byte[] nonce = new byte[12]; // 96-bit nonce
        RandomNumberGenerator.Fill(nonce);
        return nonce;
    }
}

public sealed class ExampleEncryptor : IEncryption
{
    private readonly byte[] _key;
    private const int TagSize = 16;

    public int? Version => 1;

    public byte[] KeyBytes => _key;

    public ExampleEncryptor(byte[] key)
    {
        if (key == null || key.Length != 32) throw new ArgumentException("Key must be 32 bytes.");
        _key = key;
    }

    public string? Encrypt(string? plainText)
    {
        if (plainText == null) return null;

        byte[] pt = Encoding.UTF8.GetBytes(plainText);
        byte[] ct = new byte[pt.Length];
        byte[] nonce = new byte[12];
        byte[] tag = new byte[TagSize];
        RandomNumberGenerator.Fill(nonce);

        using (AesGcm gcm = new AesGcm(_key, TagSize))
        {
            gcm.Encrypt(nonce, pt, ct, tag);
        }

        byte[] combined = new byte[nonce.Length + ct.Length + tag.Length];
        Buffer.BlockCopy(nonce, 0, combined, 0, nonce.Length);
        Buffer.BlockCopy(ct, 0, combined, nonce.Length, ct.Length);
        Buffer.BlockCopy(tag, 0, combined, nonce.Length + ct.Length, tag.Length);

        return Convert.ToBase64String(combined);
    }

    public string? Decrypt(string? cipherText)
    {
        if (cipherText == null) return null;

        byte[] combined = Convert.FromBase64String(cipherText);
        byte[] nonce = new byte[12];
        byte[] tag = new byte[TagSize];

        int cipherLen = combined.Length - nonce.Length - tag.Length;
        byte[] ct = new byte[cipherLen];

        Buffer.BlockCopy(combined, 0, nonce, 0, nonce.Length);
        Buffer.BlockCopy(combined, nonce.Length, ct, 0, ct.Length);
        Buffer.BlockCopy(combined, nonce.Length + ct.Length, tag, 0, tag.Length);

        byte[] pt = new byte[ct.Length];

        using (AesGcm gcm = new AesGcm(_key, TagSize))
        {
            gcm.Decrypt(nonce, ct, tag, pt);
        }

        return Encoding.UTF8.GetString(pt);
    }
}

// Minimal IDecrypters implementation mapping versions to encryptors.

//THIS IS JUST A SIMPLE EXAMPLE OF A ENCRYPTION CLASS
//I AM NOT A CRYPTOGRAPHIC EXPERT, DO NOT USE THIS EXAMPLE IN A REAL SYSTEM.


public sealed class MyDecrypters : IDecrypters
{
    private readonly System.Collections.Generic.Dictionary<int, IEncryption> _map =
        new System.Collections.Generic.Dictionary<int, IEncryption>()
        {
            { 1, new ExampleEncryptor(RandomNumberGenerator.GetBytes(32)) }
        };

    public System.Collections.Generic.IEnumerable<int> Keys => _map.Keys;

    public IEncryption? Decryptor(int version)
    {
        return _map.TryGetValue(version, out IEncryption enc) ? enc : null;
    }
}


//THIS IS JUST A SIMPLE EXAMPLE OF A ENCRYPTION CLASS
//I AM NOT A CRYPTOGRAPHIC EXPERT, DO NOT USE THIS EXAMPLE IN A REAL SYSTEM.

Plug MyDecrypters into ExecuteReaderAsync<T> / ExecuteReader<T> to transparently decrypt properties annotated in your models.

Table of Contents

---

License

Carrigan.SqlTools
Copyright © 2025-2026 Carrigan Software Solutions LLC

Licensed under the Apache License, Version 2.0: http://www.apache.org/licenses/LICENSE-2.0

Table of Contents

---

Disclaimers

SQL Server is a trademark of Microsoft Corporation. This project is not affiliated with or endorsed by Microsoft.

This library may generate or execute SQL. Review generated SQL before use in production systems. Always test against non-production databases first.

Use caution with schema, migration, and data-modifying operations. The authors are not responsible for data loss, downtime, or unintended database changes.