UDataset.SQLServer 0.13.0

UDataset.SQLServer

UDataset.SQLServer is SQL Server implementation for UDataset cross-database data access framework. It provides SQL Server-specific implementations of core UDataset interfaces, enabling seamless data operations with SQL Server databases.

Features

• High-Performance Bulk Operations: Utilizes SqlBulkCopy for optimized bulk insert operations
• Full SQL Server Support: Complete implementation of all UDataset features for SQL Server
• Optimistic Locking: Supports ROWVERSION (timestamp) data type for efficient concurrency control
• Database Management: Full support for database creation, modification, and renaming operations
• Advanced SQL Features: Leverages SQL Server-specific features like computed columns, triggers, and stored procedures
• Transaction Support: Full ACID transaction support with nested transaction capabilities
• Connection Pooling: Efficient connection pooling with ADO.NET's built-in pooling
• Temporal Tables: Support for system-versioned temporal tables (SQL Server 2016+)
• JSON Support: Native JSON data type support (SQL Server 2016+)

Installation

dotnet add package UDataset.Core
dotnet add package UDataset.SQLServer

Quick Start

1. Register Provider

using UDataset.Core;
using UDataset.SQLServer;

// Register SQL Server provider at application startup
SqlServerBootstrapper.Register();

2. Create Connection

// Basic connection string (Windows Authentication)
string connectionString = "Server=localhost;Database=MyDatabase;Integrated Security=true;";

// With SQL Server Authentication
string authConnectionString = "Server=localhost;Database=MyDatabase;User Id=myuser;Password=mypass;";

// With connection pooling and encryption
string pooledConnectionString = "Server=localhost;Database=MyDatabase;User Id=myuser;Password=mypass;Pooling=true;Min Pool Size=5;Max Pool Size=100;Encrypt=true;TrustServerCertificate=true;";

IConnection connection = ProviderFactory.CreateConnection("SqlServer", connectionString);

3. Basic Operations

// Create table
var schemaManager = ProviderFactory.CreateSchemaManager("SqlServer", connectionString);
var userTable = new Table("Users")
    .WithGuidPK("UserId") // SQL Server supports native GUID with NEWID()
    .WithVersionControl("RowVersion"); // Uses ROWVERSION for optimistic locking
userTable.Attributes.Add(new Column("Username", DataType.String) { Properties = { [ColumnProperty.Length] = 100, [ColumnProperty.Unique] = true } });
userTable.Attributes.Add(new Column("Email", DataType.String) { Properties = { [ColumnProperty.Length] = 255 } });
userTable.Attributes.Add(new Column("Age", DataType.Int));
userTable.Attributes.Add(new Column("IsActive", DataType.Boolean));
userTable.Attributes.Add(new Column("Metadata", DataType.Json)); // JSON type (SQL Server 2016+)
schemaManager.Create(userTable);

// Insert data
var user = new Row("Users")
{
    ["Username"] = "john_doe",
    ["Email"] = "john@example.com",
    ["Age"] = 30,
    ["IsActive"] = true,
    ["Metadata"] = "{ \"department\": \"engineering\" }"
};
var createdUser = await connection.Create(user);

// Update with optimistic locking
var userToUpdate = new Row("Users", createdUser.Id);
userToUpdate.Version = createdUser.Version; // Required for optimistic locking
userToUpdate["Age"] = 31;
var updatedUser = await connection.Update(userToUpdate);

// Query data
var query = new QueryExpression("Users");
query.Filter = "IsActive = @isActive AND Age > @minAge";
query.Parameters["isActive"] = true;
query.Parameters["minAge"] = 25;
query.AddOrderBy("Username", SortDirection.Ascending);
var activeUsers = await connection.Query(query);

SQL Server-Specific Features

ROWVERSION (Timestamp) for Optimistic Locking

SQL Server's ROWVERSION automatically changes on updates:

// UDataset automatically uses ROWVERSION for version control
var table = new Table("Products")
    .WithAutoIncrementPK("ProductId")
    .WithVersionControl("RowVersion"); // Creates ROWVERSION column
schemaManager.Create(table);

// Updates automatically check ROWVERSION
var product = await connection.Retrieve("Products", productId);
product["Price"] = 99.99m;
await connection.Update(product); // Automatically uses ROWVERSION for concurrency check

GUID Primary Keys

SQL Server has native GUID support with NEWID() or NEWSEQUENTIALID():

// UDataset uses NEWID() for GUID generation
var table = new Table("Orders")
    .WithGuidPK("OrderId"); // GUIDs generated by NEWID()
schemaManager.Create(table);

// Bulk inserts work seamlessly - database generates GUIDs
var bulkOrders = new List<Row>();
for (int i = 0; i < 100; i++)
{
    bulkOrders.Add(new Row("Orders"));
}
await connection.Create(bulkOrders); // All GUIDs generated by database

Bulk Operations with SqlBulkCopy

SQL Server's SqlBulkCopy for high-performance bulk inserts:

// UDataset automatically uses SqlBulkCopy for bulk operations
var largeDataset = new List<Row>();
// ... populate with thousands of records
await connection.Create(largeDataset); // Uses SqlBulkCopy internally

JSON Support (SQL Server 2016+)

Native JSON data type with OPENJSON, JSON_VALUE, etc.:

var table = new Table("Configurations");
table.Attributes.Add(new Column("Settings", DataType.Json));
schemaManager.Create(table);

// Query JSON data
var query = new QueryExpression("Configurations");
// UDataset handles JSON queries through SQL Server's JSON functions

Temporal Tables (SQL Server 2016+)

System-versioned temporal tables for automatic history tracking:

// Create temporal table
// UDataset supports temporal table schema management
var table = new Table("AuditLog")
    .WithAutoIncrementPK("LogId");
schemaManager.Create(table);
// Then add temporal table features manually if needed

Use Cases

SQL Server is ideal for:

• Enterprise Applications: Large-scale enterprise applications with complex business logic
• Windows Ecosystem: Organizations with Microsoft technology stack
• High-Concurrency Systems: Applications with many concurrent users
• Business Intelligence: Integration with SSRS, SSIS, and Power BI
• Financial Systems: Applications requiring strong ACID compliance
• Cloud Applications: Azure SQL Database, Azure SQL Managed Instance

Compatibility

• SQL Server 2016 and later
• Azure SQL Database
• Azure SQL Managed Instance
• .NET 8.0
• Entity Framework Core compatibility (when used alongside)

Performance Characteristics

• Bulk Inserts: Highly optimized SqlBulkCopy for massive data loads
• Query Optimization: Sophisticated query optimizer with statistics
• Index Types: Clustered, non-clustered, columnstore, filtered, full-text indexes
• Connection Pooling: Efficient connection pooling with ADO.NET
• In-Memory OLTP: Memory-optimized tables and natively compiled stored procedures (SQL Server 2014+)

Advanced Features

Columnstore Indexes

// Columnstore indexes for analytical workloads
// UDataset supports columnstore index creation through SchemaManager

Computed Columns

// Computed columns with expressions
var table = new Table("Products");
table.Attributes.Add(new Column("TotalPrice", DataType.Decimal)
{
    // Computed columns can be added manually after table creation
});
schemaManager.Create(table);

Full-Text Search

// Full-text search catalogs and indexes
// UDataset supports full-text search through QueryExpression
var searchQuery = new QueryExpression("Documents");
searchQuery.Filter = "CONTAINS(Content, @searchTerm)";
searchQuery.Parameters["searchTerm"] = "\"database\" AND \"performance\"";
var results = await connection.Query(searchQuery);

Limitations

• Column Size: SQL Server has size limits on column types (e.g., VARCHAR(MAX) 2GB)
• Parameter Limits: 2100 parameter limit per query
• GUID Fragmentation: Random GUIDs can cause index fragmentation
• ROWVERSION Size: Each ROWVERSION consumes 8 bytes

Requirements

• .NET 8.0
• Microsoft.Data.SqlClient 6.1.4

Dependencies

• UDataset.Core
• Microsoft.Data.SqlClient 6.1.4
• Dapper 2.1.66
• System.Data.Common

SQL Server SQL Syntax

UDataset uses native SQL Server syntax:

Connection String Examples

// Windows Authentication
"Server=localhost;Database=MyDatabase;Integrated Security=true;"

// SQL Server Authentication
"Server=localhost;Database=MyDatabase;User Id=myuser;Password=mypass;"

// With connection pooling
"Server=localhost;Database=MyDatabase;User Id=myuser;Password=mypass;Pooling=true;Min Pool Size=5;Max Pool Size=100;"

// With encryption
"Server=localhost;Database=MyDatabase;User Id=myuser;Password=mypass;Encrypt=true;TrustServerCertificate=true;"

// Azure SQL Database
"Server=myserver.database.windows.net;Database=MyDatabase;User Id=myuser;Password=mypass;Encrypt=true;"

// With timeout
"Server=localhost;Database=MyDatabase;User Id=myuser;Password=mypass;Connect Timeout=30;Command Timeout=60;"

Azure SQL Database Considerations

When using Azure SQL Database:

• Connection Resiliency: Consider implementing connection retry logic
• Elastic Pool: Use elastic pools for multiple databases
• Service Tiers: Choose appropriate service tier (DTU/vCore)
• Tempdb: Shared tempdb, consider tempdb size limits

For general usage examples and advanced features, please refer to UDataset.Core documentation and architecture guide.

Version History

0.11.0 (2026-04-16)

• Added DM (Dameng) and KingbaseES database support to UDataset framework
• Note: KingbaseES support is experimental and not recommended for production use