Advanced library for SQL Server persistence with Entity Framework Core integration. Includes repositories, context management, ADO.NET support, advanced error handling, and enterprise-ready data access patterns for SQL Server databases.
Get Started
$ dotnet add package Acontplus.Persistence.SqlServerReadme
Acontplus.Persistence.SqlServer
SQL Server implementation of the Acontplus persistence layer. Provides optimized Entity Framework Core integration, ADO.NET repositories, and SQL Server-specific features for high-performance data access.
Note: This package implements the abstractions defined in **Acontplus.Persistence.Common **. For general persistence patterns and repository interfaces, see the common package.
🚀 SQL Server-Specific Features
- SQL Server Optimization - OFFSET-FETCH pagination, query hints, and connection pooling
- Advanced Error Translation - SQL Server error code mapping to domain exceptions with retry policies
- Transaction Management - Distributed transactions and savepoints support
- High-Performance ADO.NET - Direct database access with 10,000+ records/sec bulk operations
- Dapper Integration - Lightweight micro-ORM for complex queries with automatic object mapping
- SqlBulkCopy Integration - Optimized bulk inserts with automatic column mapping
- Streaming Queries - Memory-efficient
IAsyncEnumerable<T>for large datasets - SQL Injection Prevention - Regex validation and keyword blacklisting for dynamic queries
- Performance Monitoring - Query execution statistics and performance insights
📦 Installation
NuGet Package Manager
Install-Package Acontplus.Persistence.SqlServer
.NET CLI
dotnet add package Acontplus.Persistence.SqlServer
PackageReference
<ItemGroup>
<PackageReference Include="Acontplus.Persistence.SqlServer" Version="1.5.12" />
<PackageReference Include="Acontplus.Persistence.Common" Version="1.1.13" />
</ItemGroup>
🎯 Quick Start
1. Configure SQL Server Persistence
// Option 1: Full EF Core with UnitOfWork (includes IAdoRepository automatically)
services.AddSqlServerPersistence<MyDbContext>(options =>
options.UseSqlServer(connectionString, sqlOptions =>
{
sqlOptions.EnableRetryOnFailure(3, TimeSpan.FromSeconds(30), null);
sqlOptions.CommandTimeout(60);
}));
// Option 2: Add Dapper alongside EF Core (for complex queries)
services.AddSqlServerPersistence<MyDbContext>(options => options.UseSqlServer(connectionString));
services.AddSqlServerDapperRepository();
// Option 3: Lightweight - Dapper only (no EF Core overhead)
services.AddSqlServerDapperRepository();
// Option 4: Lightweight - ADO.NET only (no EF Core, no Dapper)
services.AddSqlServerAdoRepository();
// Configure resilience options (optional - has sensible defaults)
services.Configure<PersistenceResilienceOptions>(
configuration.GetSection(PersistenceResilienceOptions.SectionName));
2. Entity Framework Repository Pattern
public class UserService
{
private readonly IRepository<User> _userRepository;
public UserService(IRepository<User> userRepository)
{
_userRepository = userRepository;
}
public async Task<Result<User>> GetUserByIdAsync(int id)
{
var user = await _userRepository.GetByIdAsync(id);
return user != null
? Result<User>.Success(user)
: Result<User>.Failure(DomainError.NotFound("USER_NOT_FOUND", $"User {id} not found"));
}
}
3. High-Performance ADO.NET Operations
Scalar Queries
public class OrderService
{
private readonly IAdoRepository _adoRepository;
public OrderService(IAdoRepository adoRepository)
{
_adoRepository = adoRepository;
}
// Get total count
public async Task<int> GetTotalOrdersAsync()
{
return await _adoRepository.CountAsync("SELECT COUNT(*) FROM dbo.Orders WHERE IsDeleted = 0");
}
// Check existence
public async Task<bool> OrderExistsAsync(int orderId)
{
var sql = "SELECT COUNT(*) FROM dbo.Orders WHERE Id = @OrderId AND IsDeleted = 0";
var parameters = new Dictionary<string, object> { ["@OrderId"] = orderId };
return await _adoRepository.ExistsAsync(sql, parameters);
}
// Get single value
public async Task<decimal> GetTotalRevenueAsync()
{
var sql = "SELECT SUM(TotalAmount) FROM dbo.Orders WHERE Status = 'Completed'";
return await _adoRepository.ExecuteScalarAsync<decimal>(sql) ?? 0;
}
}
Pagination with Security
// Using PaginationDto from Acontplus.Core
public async Task<PagedResult<Order>> GetPagedOrdersAsync(PaginationDto pagination)
{
var baseSql = @"
SELECT Id, OrderNumber, CustomerId, TotalAmount, Status, CreatedAt
FROM dbo.Orders
WHERE IsDeleted = 0";
// Automatic OFFSET-FETCH with SQL injection prevention
return await _adoRepository.GetPagedAsync<Order>(baseSql, pagination);
}
// Complex pagination with filters
public async Task<PagedResult<Order>> GetPagedOrdersByStatusAsync(
PaginationDto pagination,
string status)
{
var sql = @"
SELECT o.Id, o.OrderNumber, c.CustomerName, o.TotalAmount, o.Status, o.CreatedAt
FROM dbo.Orders o
INNER JOIN dbo.Customers c ON o.CustomerId = c.Id
WHERE o.Status = @Status AND o.IsDeleted = 0";
var parameters = new Dictionary<string, object> { ["@Status"] = status };
return await _adoRepository.GetPagedAsync<Order>(sql, pagination, parameters);
}
// Stored procedure pagination with OUTPUT parameter
public async Task<PagedResult<User>> GetPagedUsersFromStoredProcAsync(
PaginationDto pagination,
string emailDomain)
{
var parameters = new Dictionary<string, object>
{
["@EmailDomain"] = emailDomain
};
return await _adoRepository.GetPagedFromStoredProcedureAsync<User>(
"dbo.GetPagedUsuarios",
pagination,
parameters);
}
Bulk Operations (10,000+ records/sec)
// SqlBulkCopy with DataTable
public async Task<int> BulkInsertOrdersAsync(List<Order> orders)
{
var dataTable = new DataTable();
dataTable.Columns.Add("OrderNumber", typeof(string));
dataTable.Columns.Add("CustomerId", typeof(int));
dataTable.Columns.Add("TotalAmount", typeof(decimal));
dataTable.Columns.Add("Status", typeof(string));
dataTable.Columns.Add("CreatedAt", typeof(DateTime));
foreach (var order in orders)
{
dataTable.Rows.Add(
order.OrderNumber,
order.CustomerId,
order.TotalAmount,
order.Status,
order.CreatedAt);
}
// Uses SqlBulkCopy internally
return await _adoRepository.BulkInsertAsync(dataTable, "dbo.Orders");
}
// Bulk insert with entity collection
public async Task<int> BulkInsertProductsAsync(IEnumerable<Product> products)
{
var columnMappings = new Dictionary<string, string>
{
["ProductCode"] = "Code",
["ProductName"] = "Name",
["UnitPrice"] = "Price"
};
return await _adoRepository.BulkInsertAsync(
products,
"dbo.Products",
columnMappings,
batchSize: 10000);
}
Streaming Large Datasets
// Memory-efficient CSV export with IAsyncEnumerable
public async Task ExportOrdersToCsvAsync(StreamWriter writer)
{
var sql = "SELECT Id, OrderNumber, TotalAmount, Status FROM dbo.Orders WHERE IsDeleted = 0";
await writer.WriteLineAsync("Id,OrderNumber,TotalAmount,Status");
await foreach (var order in _adoRepository.QueryAsyncEnumerable<Order>(sql))
{
await writer.WriteLineAsync($"{order.Id},{order.OrderNumber},{order.TotalAmount},{order.Status}");
}
}
// Process large datasets in batches
public async Task ProcessLargeOrderBatchAsync()
{
var sql = "SELECT * FROM dbo.Orders WHERE ProcessedDate IS NULL";
var batch = new List<Order>();
const int batchSize = 1000;
await foreach (var order in _adoRepository.QueryAsyncEnumerable<Order>(sql))
{
batch.Add(order);
if (batch.Count >= batchSize)
{
await ProcessOrderBatchAsync(batch);
batch.Clear();
}
}
if (batch.Any())
await ProcessOrderBatchAsync(batch);
}
Batch and Multi-Result Queries
// Execute multiple commands in one transaction
public async Task<int> ExecuteBatchUpdatesAsync(List<int> orderIds)
{
var commands = orderIds.Select(id => (
Sql: "UPDATE dbo.Orders SET Status = @Status WHERE Id = @OrderId",
Parameters: new Dictionary<string, object>
{
["@OrderId"] = id,
["@Status"] = "Processed"
}
));
return await _adoRepository.ExecuteBatchNonQueryAsync(commands);
}
// Get multiple datasets in one round-trip
public async Task<List<List<dynamic>>> GetDashboardDataAsync()
{
var sql = @"
SELECT COUNT(*) AS TotalOrders FROM dbo.Orders;
SELECT SUM(TotalAmount) AS TotalRevenue FROM dbo.Orders WHERE Status = 'Completed';
SELECT TOP 5 * FROM dbo.Orders ORDER BY CreatedAt DESC;";
return await _adoRepository.QueryMultipleAsync<dynamic>(sql);
}
4. Dapper Repository (Lightweight Micro-ORM)
For complex queries where you need automatic object mapping without EF Core overhead:
public class ReportService
{
private readonly IDapperRepository _dapper;
public ReportService(IDapperRepository dapper)
{
_dapper = dapper;
}
// Simple query with automatic mapping
public async Task<IEnumerable<OrderDto>> GetOrdersByStatusAsync(string status)
{
var sql = @"
SELECT o.Id, o.OrderNumber, c.CustomerName, o.TotalAmount, o.Status
FROM dbo.Orders o
INNER JOIN dbo.Customers c ON o.CustomerId = c.Id
WHERE o.Status = @Status";
return await _dapper.QueryAsync<OrderDto>(sql, new { Status = status });
}
// Paginated query with automatic count
public async Task<PagedResult<OrderDto>> GetPagedOrdersAsync(PaginationRequest pagination)
{
var sql = @"
SELECT o.Id, o.OrderNumber, c.CustomerName, o.TotalAmount, o.Status
FROM dbo.Orders o
INNER JOIN dbo.Customers c ON o.CustomerId = c.Id
WHERE o.IsDeleted = 0";
return await _dapper.GetPagedAsync<OrderDto>(sql, pagination);
}
// Multiple result sets in one query
public async Task<(IEnumerable<Order> Orders, IEnumerable<Customer> Customers)> GetDashboardAsync()
{
var sql = @"
SELECT TOP 10 * FROM dbo.Orders ORDER BY CreatedAt DESC;
SELECT TOP 10 * FROM dbo.Customers ORDER BY CreatedAt DESC;";
return await _dapper.QueryMultipleAsync<Order, Customer>(sql);
}
// Execute stored procedure
public async Task<int> ProcessOrderAsync(int orderId)
{
return await _dapper.ExecuteAsync(
"dbo.ProcessOrder",
new { OrderId = orderId },
commandType: CommandType.StoredProcedure);
}
}
5. Advanced EF Core Query Operations
// Complex queries with SQL Server optimizations
public async Task<IReadOnlyList<OrderSummary>> GetOrderSummariesAsync(
DateTime startDate,
CancellationToken ct = default)
{
var queryExpression = (IQueryable<Order> q) => q
.Where(o => o.CreatedAt >= startDate)
.Join(_context.Set<Customer>(),
order => order.CustomerId,
customer => customer.Id,
(order, customer) => new { Order = order, Customer = customer })
.Select(x => new OrderSummary
{
OrderId = x.Order.Id,
CustomerName = $"{x.Customer.FirstName} {x.Customer.LastName}",
TotalAmount = x.Order.TotalAmount,
Status = x.Order.Status
});
return await _orderRepository.ExecuteQueryToListAsync(queryExpression, ct);
}
🔧 SQL Server Configuration
Connection String Best Practices
{
"ConnectionStrings": {
"DefaultConnection": "Server=localhost;Database=MyApp;Trusted_Connection=True;MultipleActiveResultSets=true;Encrypt=true;TrustServerCertificate=false;"
}
}
Performance Tuning
services.AddDbContext<BaseContext>(options =>
{
options.UseSqlServer(connectionString, sqlOptions =>
{
// Connection resilience
sqlOptions.EnableRetryOnFailure(
maxRetryCount: 5,
maxRetryDelay: TimeSpan.FromSeconds(30),
errorNumbersToAdd: null);
// Performance settings
sqlOptions.CommandTimeout(60);
sqlOptions.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery);
});
// Additional performance options
options.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking);
options.EnableSensitiveDataLogging(false);
});
📚 SQL Server API Reference
Entity Framework Repositories
BaseContext- Optimized EF Core context for SQL ServerIRepository<TEntity>- Generic repository pattern with change trackingBaseRepository<TEntity>- EF Core implementation with query optimization
ADO.NET High-Performance Repositories
IAdoRepository- Interface for direct ADO.NET operationsAdoRepository- SQL Server optimized implementation with:- Scalar Queries:
ExecuteScalarAsync<T>,ExistsAsync,CountAsync,LongCountAsync - Pagination:
GetPagedAsync<T>with OFFSET-FETCH optimization - Bulk Operations:
BulkInsertAsyncusing SqlBulkCopy (10,000+ records/sec) - Streaming:
QueryAsyncEnumerable<T>for memory-efficient processing - Batch Operations:
ExecuteBatchNonQueryAsync,QueryMultipleAsync<T> - Stored Procedures:
GetPagedFromStoredProcedureAsync<T>with OUTPUT parameters
- Scalar Queries:
Security & Error Handling
SqlServerExceptionHandler- Maps SQL Server error codes to domain exceptionsValidateAndSanitizeSortColumn- SQL injection prevention (regex^[a-zA-Z0-9_\.]+$+ keyword blacklist)AsyncRetryPolicy- Polly integration with 3 retries and exponential backoff
Utilities
DbDataReaderMapper- Fast mapping from DbDataReader to entities/DTOsCommandParameterBuilder- Type-safe SQL parameter builderPaginationMetadataKeys- Standardized metadata constantsQueryOptimizer- SQL Server query optimization utilities
🔐 Security Features
SQL Injection Prevention
// Automatic validation of sort columns
var pagination = new PaginationDto
{
SortBy = "Username", // Validated with regex ^[a-zA-Z0-9_\.]+$
SortDirection = SortDirection.ASC
};
// Blacklisted keywords: DROP, DELETE, EXEC, ALTER, TRUNCATE, etc.
// Throws SecurityException if invalid
Metadata Exposure Control
// PaginationMetadataOptions controls what metadata is exposed
services.Configure<PaginationMetadataOptions>(options =>
{
options.IncludeQuerySource = false; // Hide internal query details in production
options.IncludeDebugInfo = builder.Environment.IsDevelopment();
});
⚡ Performance Benchmarks
| Operation | EF Core | ADO.NET | Performance Gain |
|---|---|---|---|
| Simple Query (1,000 rows) | 45ms | 12ms | 3.75x faster |
| Pagination (10,000 rows) | 180ms | 35ms | 5.14x faster |
| Bulk Insert (10,000 rows) | 8,500ms | 850ms | 10x faster |
| Bulk Insert (100,000 rows) | 95,000ms | 7,200ms | 13.2x faster |
| Streaming Export (1M rows) | OutOfMemory | 4.5s | Memory efficient |
When to Use Each Approach
Use EF Core (IRepository<T>) when:
- ✅ Complex object graphs with navigation properties
- ✅ Change tracking is needed
- ✅ LINQ query composition
- ✅ Standard CRUD operations
- ✅ Developer productivity is priority
Use ADO.NET (IAdoRepository) when:
- ✅ High-volume bulk operations (10,000+ records)
- ✅ Simple DTOs or read-only queries
- ✅ Custom SQL optimization required
- ✅ Memory-efficient streaming (millions of rows)
- ✅ Maximum performance is critical
- ✅ Stored procedures with complex logic
🤝 Contributing
We welcome contributions! Please see our Contributing Guidelines for details.
Development Setup
git clone https://github.com/acontplus/acontplus-dotnet-libs.git
cd acontplus-dotnet-libs
dotnet restore
dotnet build
📄 License
This project is licensed under the MIT License - see the LICENSE file for details.
🆘 Support
- 📧 Email: proyectos@acontplus.com
- 🐛 Issues: GitHub Issues
- 📖 Documentation: Wiki
👨💻 Author
Ivan Paz - @iferpaz7
🏢 Company
Acontplus - Software solutions
Built with ❤️ for the .NET community