JCode.ORMX.DbProvider.SQLServer 1.0.1

JCode.ORMX.DbProvider.SQLServer

SQL Server 数据库提供程序，用于 JCode.ORMX 框架。该提供程序使您能够使用 SQL Server 作为 JCode.ORMX 的后端数据库。

功能特性

• 完整的 SQL Server 支持：支持 SQL Server 的所有基本操作
• 类型安全：使用强类型实体类，避免运行时错误
• LINQ 表达式树：使用 LINQ 表达式进行查询，编译时类型检查
• 表管理：自动创建和管理 SQL Server 表
• 事务支持：支持 SQL Server 事务操作
• 存储过程：支持 SQL Server 存储过程调用
• 批量操作：支持批量插入、更新、删除
• 参数化查询：自动参数化查询，防止 SQL 注入
• 连接池：内置连接池管理，提高性能
• 窗口函数：支持 SQL Server 窗口函数
• 表值参数：支持表值参数进行批量操作
• 空间数据：支持 SQL Server 空间数据类型
• 全文搜索：支持 SQL Server 全文搜索功能

安装

NuGet 包

Install-Package JCode.ORMX.DbProvider.SQLServer

依赖项

• Microsoft.Data.SqlClient (5.x 或更高版本) 或 System.Data.SqlClient (4.8.x 或更高版本)
• JCode.ORMX (1.0.0 或更高版本)

快速开始

1. 定义实体类

[Table("Users")]
public class User
{
    [Column(IsPrimaryKey = true, IsIdentity = true)]
    public int Id { get; set; }

    [Column]
    public string Name { get; set; }

    [Column]
    public string Email { get; set; }

    [Column]
    public DateTime CreatedAt { get; set; }
}

2. 初始化 SQL Server 提供程序

// 使用连接字符串
var provider = new SQLServerProvider("Server=localhost;Database=MyDb;User Id=sa;Password=your_password;");

// 或使用连接字符串构建器
var builder = new SqlConnectionStringBuilder
{
    DataSource = "localhost",
    InitialCatalog = "MyDb",
    UserID = "sa",
    Password = "your_password",
    TrustServerCertificate = true
};
var provider = new SQLServerProvider(builder.ToString());

3. 创建表

var tableManager = provider.GetTableManager();
tableManager.Create(typeof(User));

4. 基本操作

var table = tableManager.Table<User>();

// 插入
var user = new User 
{ 
    Name = "John", 
    Email = "john@example.com", 
    CreatedAt = DateTime.Now 
};
table.Insert(user);

// 查询
var users = table.Find()
    .Where(u => u.Name == "John")
    .OrderBy(u => u.Id)
    .Limit(10)
    .GetList();

// 更新
table.Update()
    .Set("Email", "newemail@example.com")
    .Where(u => u.Id == 1)
    .Execute();

// 删除
table.Delete()
    .Where(u => u.Id == 1)
    .Execute();

高级功能

事务操作

using (var transaction = provider.BeginTransaction())
{
    try
    {
        var table = transaction.Table<User>();
        
        table.Insert(user1);
        table.Insert(user2);
        
        transaction.Commit();
    }
    catch
    {
        transaction.Rollback();
        throw;
    }
}

存储过程调用

// 调用存储过程
var result = tableManager.ExecuteStoredProcedure("sp_GetUsers", 
    new SqlParameter("@Name", "John"));

// 调用带输出参数的存储过程
var outputParam = new SqlParameter("@Count", SqlDbType.Int) { Direction = ParameterDirection.Output };
tableManager.ExecuteStoredProcedure("sp_UpdateUser", 
    new SqlParameter("@Id", 1),
    new SqlParameter("@Name", "John"),
    outputParam);

var count = (int)outputParam.Value;

表值参数

// 使用表值参数进行批量插入
var dataTable = new DataTable();
dataTable.Columns.Add("Name", typeof(string));
dataTable.Columns.Add("Email", typeof(string));

dataTable.Rows.Add("John", "john@example.com");
dataTable.Rows.Add("Jane", "jane@example.com");

var parameter = new SqlParameter("@Users", SqlDbType.Structured)
{
    TypeName = "dbo.UserTableType",
    Value = dataTable
};

tableManager.ExecuteStoredProcedure("sp_InsertUsers", parameter);

窗口函数

var result = table.Find()
    .WindowFunction()
    .RowNumber("RowNum", u => u.Salary)
    .Rank("SalaryRank", u => u.Salary)
    .DenseRank("DenseRank", u => u.Salary)
    .Ntile("SalaryQuartile", 4, u => u.Salary)
    .Lag("PrevSalary", u => u.Salary, 1)
    .Lead("NextSalary", u => u.Salary, 1)
    .OrderByDesc(u => u.Salary)
    .GetList();

聚合查询

var result = table.Aggregate()
    .GroupBy("Department")
    .Count("EmployeeCount", "Id")
    .Avg("AvgSalary", "Salary")
    .Sum("TotalSalary", "Salary")
    .Max("MaxSalary", "Salary")
    .Min("MinSalary", "Salary")
    .GetList();

连接查询

var result = table.Find()
    .InnerJoin<Order>((u, o) => u.Id == o.UserId)
    .LeftJoin<OrderDetail>((u, o, od) => o.Id == od.OrderId)
    .Where(u => u.Name == "John")
    .GetList();

批量操作

// 批量插入
var users = new List<User> { /* ... */ };
table.InsertAll(users);

// 批量更新
table.UpdateAll(users);

// 批量删除
table.DeleteAll(users);

全文搜索

// 创建全文索引
tableManager.ExecuteSql("CREATE FULLTEXT INDEX ON Users(Name, Email) KEY INDEX PK_Users");

// 全文搜索
var results = table.Find()
    .Where("CONTAINS(Name, '\"John*\"')")
    .GetList();

空间数据

[Table("Locations")]
public class Location
{
    [Column(IsPrimaryKey = true)]
    public int Id { get; set; }

    [Column]
    public string Name { get; set; }

    [Column]
    public string Coordinates { get; set; }  // GEOMETRY 或 GEOGRAPHY 类型
}

// 空间查询
var results = table.Find()
    .Where("Coordinates.STDistance(geography::Point(40.7128, -74.0060, 4326)) < 1000")
    .GetList();

配置选项

连接字符串选项

var connectionString = "Server=localhost;Database=MyDb;User Id=sa;Password=your_password;" +
    "TrustServerCertificate=True;" +           // 信任服务器证书
    "Connect Timeout=30;" +                    // 连接超时（秒）
    "Command Timeout=30;" +                    // 命令超时（秒）
    "MultipleActiveResultSets=True;" +         // 启用 MARS
    "Pooling=true;" +                          // 启用连接池
    "Min Pool Size=5;" +                       // 最小连接池大小
    "Max Pool Size=100;" +                     // 最大连接池大小
    "Connection Lifetime=300;" +               // 连接生命周期（秒）
    "Encrypt=False;" +                         // 加密连接
    "Integrated Security=False;" +             // Windows 身份验证
    "Persist Security Info=False;" +           // 持久化安全信息
    "Application Name=MyApp;" +                // 应用程序名称
    "Workstation Id=MyWorkstation;" +          // 工作站 ID
    "Packet Size=8000;" +                      // 网络包大小
    "Asynchronous Processing=True;" +           // 异步处理

var provider = new SQLServerProvider(connectionString);

类型映射

SQL Server 提供程序支持以下 .NET 类型到 SQL Server 类型的映射：

性能建议

1. 使用索引：为常用查询字段创建索引
2. 批量操作：使用 InsertAll、UpdateAll、DeleteAll 进行批量操作
3. 查询优化：只查询需要的字段，使用 TOP 限制结果集
4. 连接池：合理配置连接池大小
5. 事务范围：尽量缩小事务范围，减少锁持有时间
6. 避免 N+1 查询：使用连接查询代替多次查询
7. 使用 MARS：启用 MultipleActiveResultSets 提高并发性能
8. 使用表值参数：对于大批量数据使用表值参数

故障排除

常见问题

Q: 连接超时 A: 增加 ConnectTimeout 和 CommandTimeout 参数的值

Q: SSL 连接错误 A: 设置 TrustServerCertificate=True 或配置正确的 SSL 证书

Q: 查询性能慢 A: 使用执行计划分析查询，添加适当的索引

Q: 批量操作失败 A: 检查 max text repl size 配置，增加其值以支持大批量操作

Q: 内存使用过高 A: 调整连接池大小，使用 TOP 限制结果集