# docs

**Repository Path**: soul-au/docs

## Basic Information

- **Project Name**: docs
- **Description**: fffffffffffffffffaaaaaaaa
- **Primary Language**: Unknown
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 0
- **Created**: 2023-05-06
- **Last Updated**: 2023-05-24

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

## SqlBuilder

安装依赖

```powershell
dotnet add package Dapper.SqlBuilder --version 2.0.78
```

这是一个需要分页的示列

```C#
public class ReqModel
{
    public List<int> Ids { get; set; }
    public string Keyworkds { get; set; }
    public int PageIndex { get; set; }
    public int PageSize { get; set; }

    /// <summary>
    /// 把pageindex转换成下标
    /// </summary>
    /// <returns></returns>
    public int RowIndex => PageIndex > 1 ? (PageIndex - 1) * PageSize : 0;   
}

```

```C#
var request = new ReqModel();
var sb = new SqlBuilder();
if (!string.IsNullOrEmpty(request.Keyworkds))
{
    //处理模糊查询需要添加%%
    request.Keyworkds = $"%{request.Keyworkds}%";
    sb.Where("column1 like @Keyworkds");
}

if (request.Ids != null)
{
    if (!request.Ids.Any())
    {
        request.Ids.Add(0);
    }
    sb.Where("column2 in @Ids");
}

IDbConnection connection = default;
var sql = $@"
select
    column1,
    column2,
    column3
from
    t_table_name limit {request.RowIndex},{request.PageSize}";
sb.OrderBy("column1 desc,column2 asc");
sb.GroupBy("column1 desc,column2 asc");
querySql = sb.AddTemplate($"{sql} /**where**/ /**groupby**/ /**orderby**/").RawSql;
//多表
countSql = sb.AddTemplate("SELECT COUNT(*) FROM ({sql} /**where**/ /**groupby**/) AS t").RawSql;
var list = connection.Query(querySql, request);
var count = connection.ExecuteScalar<int>(countSql, request);
/**
注意sql的基本规则
	1. 计数查询：分组会影响分页计数，排序不会，因此排序不需要orderby
	2. 执行顺序：where->groupby->having->orderby->select	
*/
```

参数问题

```C#
//使用匿名对象
var sql = "select * from student id = @Id and age = @Age";
IDbConnection connection = default;
var list = connection.Query(sql, new 
{
    Id = 1,
    Age = 20
});
//使用DynamicParameters
var sql = "select * from student id = @Id and age = @Age";
var obj = new 
{
    Id = 1,
};
//把obj对象的参数id添加进来
var param = new DynamicParameters(obj);
//把age添加进来
param.Add("Age", 20);
IDbConnection connection = default;
var list = connection.Query(sql, param);
```



## 字符串拼接处理

老的写法

```C#
List<int> idsList = new List<int>()
{
    1,5,6,9,10
};
var EnPer = string.Empty;
foreach (int iditem in idsList)
{
    if (!string.IsNullOrEmpty(EnPer))
        EnPer += "," + iditem.ToString();
    else
    {
        EnPer += iditem.ToString();
    }
}
```

规范写法

```C#
List<int> idsList = new List<int>()
{
    1,5,6,9,10
};
var EnPer = string.Join(",",idsList);
```

## Linq

- 注意所有实现了IEnumable接口的派生类都可以使用Linq，因熟练的使用Linq方法来处理集合问题。
- Linq是函数式编程风格，编写的代码通俗易懂，简洁高效

### Select：投影

select可以根据一个集合生成一个新的集合

```C#
//示列
int[] auIdlist = Array.ConvertAll(enper.Split(','), int.Parse);
//推荐
enper.Split(',').Select(s => int.Parse(s));

```

这个式列代码，从数据库加载出来SysAdminuser集合，但是由于这个表存在很多大数据列，而这个业务中只用到了AuDdId一列。使用投影可以明显提升性能

```C#
//不推荐
var sysAdminuserList = DbContext.From<SysAdminuser>().Where(a => Operator.In(a.Id, auIdlist))
    .Select();
//优化,使用匿名对象来返回部分列，生成的sql只会去查询AuDdId这一列
var sysAdminuserList = DbContext.From<SysAdminuser>().Where(a => Operator.In(a.Id, auIdlist))
    .Select(s=>new 
    {
		s.AuDdId   
    });
//如果只需要一列可以直接这样
var AuDdIdList = DbContext.From<SysAdminuser>().Where(a => Operator.In(a.Id, auIdlist))
    .Select(s=>s.AuDdId);
string auddid = string.Empty;
foreach (var sys in sysAdminuserList)
{
    if (!string.IsNullOrEmpty(auddid))
        auddid += "," + sys.AuDdId;
    else auddid = sys.AuDdId;
}
```