生活小妙招

小白的求学之路

已于 2024-10-12 11:36:29 修改

阅读量341

点赞数 3

文章标签： c#

于 2024-08-14 16:23:57 首次发布

本文链接：https://blog.csdn.net/weixin_51111241/article/details/141194286

版权

0.NET 辅助类库

一个全面且高效的C#帮助类库

1.SQL通过逗号或者分号相连存入的ID类型字段，想转为中文名称场景：存的员工编号

效果为：原：YG001,YG002,YG003 执行完后：张三,李四,王二
select PW.List_NO,PW.Create_Time,WorkDoc_NO,Process_Name,PWD.Material_Number
,Tab.Counts,
STUFF((
SELECT DISTINCT ',' + U.Employee_Name
FROM PD_WorkList T
CROSS APPLY STRING_SPLIT(T.Sign_User, ';') S
JOIN dbo.HR_Employee U ON S.value = U.Employee_ID
WHERE T.List_NO = PW.List_NO
FOR XML PATH('')), 1, 1, '') AS Produce_User,
STUFF((
SELECT DISTINCT ',' + U.Machine_Name
FROM PD_WorkListDtl T
CROSS APPLY STRING_SPLIT(T.Machine_ID, ',') S
JOIN dbo.EM_Machine U ON S.value = U.Machine_ID
WHERE T.ID = PWL.ID
FOR XML PATH('')), 1, 1, '') AS Produce_User
from PD_WorkList PW
LEFT JOIN PD_WorkListDtl PWL ON PW.List_NO=PWL.List_NO
LEFT JOIN PD_WorkDoc PWD ON PWD.List_NO=PWL.WorkDoc_NO
LEFT JOIN (SELECT COUNT(1) Counts ,Source_NO from IV_BarcodeDtl group by Source_NO) Tab on Tab.Source_NO=PWL.List_NO

2.分页：

result.OrderBy(x => x.Barcode).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList()

两个List集合混合分组效果：A(1,2,3,4) B(3,4,5,6) ---> C(1,2,3,4,5,6)

var Bads = pdd.Concat(fdd)
.GroupBy(p => p.Required_Standards)
.Select(g => new BadDistribution
{
Required_Standards = g.Key,
Bad_Qty = g.Sum(p => p.Bad_Qty)
})
.OrderByDescending(x => x.Bad_Qty)
.ToList();

3.查找范围内的数据

WHERE File_Code IN('{string.Join("','", entity.File_Code.Split(','))}')

4.Redis 拿到缓存的数据

string MouldData = RedisHelper.GetString(key);
if (MouldData == null)
 {
      List<Barcode> Barcodes = new List<Barcode>();
 }

5.应用场景：删除仓库时去检索库位表是否已占用了该仓库如果占用不可删除并提示

var ids = keys.Select(s => s.GetInt()).ToList();
var data = repository.FindAsIQueryable(x => ids.Contains(x.ID)).Select(s => s.Customer_ID).ToList();

// 如果不提示具体的仓库下面注释的这句就已经满足需求
//bool flag = repository.DbContext.Set<BD_Location>().Any(x => data.Contains(x.Stock_ID));
List<BD_Package> PackageList = repository.DbContext.Set<BD_Package>().Where(x => data.Any(c => c == x.Customer_ID)).ToList();
if (PackageList != null && PackageList.Count > 0)
{
    var lst = PackageList.GroupBy(x => x.Customer_ID)
        .Select(x => x.Key)
        .ToList();
    return WebResponseContent.Instance.Error($"选择行中存在客户已被使用：{string.Join(",", lst)}");
}

6.查数据库锁死

select 'Kill' as aaa , request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName
from sys.dm_tran_locks where resource_type='OBJECT'
-- 以下为查询到的锁死任务直接执行就好
Kill   57
Kill   55
Kill   64
Kill   73
Kill   56

7.数据库以某个字段类型分组并按照时间排序拿到最新的那条数据 WITH (NOLOCK) 防锁

select CreateTime, Machine_ID,ROW_NUMBER() OVER (PARTITION BY Machine_ID ORDER BY CreateTime DESC) AS RowNum
FROM DC_PulverizerInfo WITH (NOLOCK)

8.数据库误删

1.数据操作日志还原到某个时间点测试用的2016版本的确实还原了在正式库试过试过2次成功过一次会造成一直显示正在还原中....

具体操作：右键要还原的数据库->任务->还原->数据库->时间线选择想要还原到的时间点

2.如果是 2014 或低于2014版本的可以直接使用ApexSQLLog2014 小工具选择时间点生成脚本还原

右击数据库-任务-脱机
ALTER DATABASE Test SET OFFLINE WITH ROLLBACK IMMEDIATE
右击数据库-任务-联机
ALTER DATABASE Test SET ONLINE

终止操作
restore database 数据库名称 with recovery

9.SQL查询优化（加索引与数据库执行计划）
视频地址：SQLServer技巧--查询分析器和图形化执行计划_哔哩哔哩_bilibili

执行语句后查看执行计划执行后会提供建议优化点如缺少索引直接右键点击缺少索引详细信息

如提示INCLUDE 某列只需要在此索引里包含该列即可

10.简单日志记录

public bool LogMessage(string message)
{
    bool result = false;
    string logFilePath = $"C:\\DZ\\Log\\DZCloud\\XiHeLog\\{DateTime.Now.ToString("yyyy-MM-dd")}.txt"; // 日志文件路径
    try
    {
        // 判断日志文件路径是否存在
        if (!Directory.Exists(Path.GetDirectoryName(logFilePath)))
        {
            // 如果路径不存在，则新建路径
            Directory.CreateDirectory(Path.GetDirectoryName(logFilePath));
        }
        using (StreamWriter writer = new StreamWriter(logFilePath, true))
        {
            writer.WriteLine($"{DateTime.Now}: {message}");
        }
        result = true;
    }
    catch (Exception ex)
    {
        Console.WriteLine($"Error writing to log file: {ex.Message}");
        result = false;
    }
    return result;
}

11 .NET6 上传文件大小，地址栏长度限制

//上传文件限制
Program 文件加：
builder.Services.Configure<FormOptions>(x =>
{
    x.MultipartBodyLengthLimit = int.MaxValue; // In case of multipart
});

IIS 环境 Web.config <system.webServer> 下加

12. 异步查询/报表用需要统计数据量太多

Controller层

[HttpGet, Route("GetTopDetailQty")]
        public async Task<ActionResult> GetTopDetailQty()
        {
            List<Inventory> result = await _service.GetTopDetailQty();
            return JsonNormal(result);
        }

IService层

public Task<List<Inventory>> GetTopDetailQty();

Service层这里用的SqlSugar

public async Task<List<Inventory>> GetTopDetailQty()
        {
            List<Inventory> result = new List<Inventory>();
            try
            {
                var tran = await repository.SqlSugarClient.Ado.UseTranAsync(async () =>
                {
                    var poDtl = await _pooniceDtlRepository.SqlSugarClient.Queryable<PONoticeDtl>()
                                 .LeftJoin<PONotice>((p, pn) => p.Order_ID == pn.Order_ID)
                                 .Where((p, pn) => pn.AuditStatus == 0)
                                 .GroupBy((p, pn) => p.Order_ID)
                                 .Select((p, pn) => new Inventory
                                 {
                                     Dtl_Qty = SqlFunc.AggregateSum(p.Delivery_Qty),
                                     ID = Convert.ToInt32(p.Order_ID)
                                 })
                                 .ToListAsync();
 
                    result.Add(new Inventory
                    {
                        Order_Qty = poDtl != null && poDtl.Count > 0 ? poDtl.Count : 0,
                        Dtl_Qty = poDtl != null && poDtl.Count > 0 ? poDtl.Sum(x => x.Dtl_Qty) : 0,
                    });
 
                });
            }
            catch (Exception ex)
            {
 
            }
 
            return result;
        }