列数据来至其它库后合并

https://doc.rg1008.com/docs/rg_pass_log/rg_pass_log-1e8j7tosbnpv4

E:\0_RG\Code\RG6.PF\rg3-pf\RG3.BO.DB\Services\DbSqlService$ResConnection.cs

E:\0_RG\Code\RG6.PF\rg3-pf\RG3.Web.Host.PaaS\Config\Mapper\basis\msg\mapper_bs-msg-chat_sql__columns.json

配置文件 mapper_bs-msg-chat_sql__columns.json

 "stockInCount": {
      "extendAlias": "input_basis",
      "fieldSort": 9999,
      "tableId": 1,
      "dataType": "varchar",
      "title": "入库记录",
      "isData": false,
      "filter": {
        "isMust": false
      },
      //"sql": "(select count(cis_id) from mall_commodity_in_stock tb_child where tb_child.sku_id=mall_commodity_sku_info.sku_id) stock_in_count",
      // #region 统计字段来源跨库
      "sql": "0 stock_in_count",
      "connectionSql": {
        "mappers": {
          "skuId": "sku_id"
        },
        "dbSql": "select count(cis_id) stock_in_count,sku_id from mall_commodity_in_stock where sku_id in @skuId group by sku_id",
        "rowSql": "sku_id='@skuId'",
        "rowParams": [],
        "connName": "ConnMallOrder"
      },
      // #endregion 统计字段来源跨库
      "selectSql": {
        "oracle": null,
        "sqlserver": null,
        "mysql": null,
        "sqlite": null
      },
      "field": "stockInCount",
      "name": "stock_in_count",
      "alias": "stock_in_count",
      "isDate": false,
      "isPrimary": false,
      "isIdentity": false,
      "maxLength": 200
    },

代码参考

 /// <summary>
        /// 设置  处理列数据来源其它库 
        /// </summary>
        /// <param name="pf"></param>
        /// <param name="sqlConfig"></param>
        /// <param name="dtAll"></param>
        /// <param name="dictionaryUrl"></param>
        public void SetResConnection(PFGlobalParameter pf, SqlConfig sqlConfig, DataTable dtAll, Dictionary<string, object> dictionaryUrl)
        {
            if (dtAll == null) return;
            if (dtAll.Rows == null) return;
            if (dtAll.Rows.Count == 0) return;
            if (sqlConfig == null) return;
            var columns = sqlConfig.Columns.Where(temp => !string.IsNullOrWhiteSpace(temp.Value.ConnectionSql?.DbSql) && dtAll.Columns.Contains(temp.Value.Alias));
            if (columns == null || !columns.Any()) return;

            //Dictionary<string,DataTable> dicDt = new Dictionary<string, DataTable>();
            foreach (var column in columns)
            {
                var connectionSql = column.Value.ConnectionSql;
                var mapper = connectionSql.Mappers;
                var primaryName = mapper.Values.FirstOrDefault();
                var primaryField = mapper.Keys.FirstOrDefault();
                var ids = DataTableUtil.ConverDataTableToList(dtAll, primaryName);
                var dicParams = new Dictionary<string, object>();
                DictionaryUtil.SetConvertDictionary(dicParams, dictionaryUrl);
                dicParams[mapper.Keys.FirstOrDefault()] = ids;

                var result = _db.QueryDataTable(pf, connectionSql.ConnName, connectionSql.DbSql, dicParams);

                foreach (DataRow row in dtAll.Rows)
                {
                    StringBuilder sbRowSql = new StringBuilder(connectionSql.RowSql);
                    //替换来自全局的参数
                    if (connectionSql.RowParams != null)
                    {
                        foreach (var param in connectionSql.RowParams)
                        {
                            sbRowSql.Replace($"@{param}", dicParams[param].ToString());
                        }
                    }
                    sbRowSql.Replace($"@{primaryField}", row[primaryName].ToString());

                    var rowN = result.Select(sbRowSql.ToString()).FirstOrDefault();
                    if (rowN == null) continue;
                    foreach (DataColumn columnN in rowN.Table.Columns)
                    {
                        if (!dtAll.Columns.Contains(columnN.ColumnName)) continue;
                        row[columnN.ColumnName] = rowN[columnN.ColumnName];
                    }
                }
            }
        }