查询数据输出到文件

介绍

1、根据数据库查询语句获取信息
2、文件存储路径
C:\0_RG\Code\RG6.PF\rg3-pf\RG3.Web.Host.PaaS\App_Temp\smdcm\AiCode\db2-data\global.sql
3、代码路径
C:\0_RG\Code\RG6.PF\rg3-pf\RG3.BO.BI.DB\Controller\DbAiCodeController.cs

请求地址（特定表操作）

http://localhost:5900/bo/api/v3/bi/db/aicode/data

http://localhost:5900/bo/api/v3/bi/db/aicode/data?ignore=true&ownerId=smdcm&sysId&mediaType=text&suffix=sql&template=data

mediaType=text

请求地址（输出json数据，带有key,便于查看key参数）

http://localhost:5900/bo/api/v3/bi/db/aicode/data?ignore=true&ownerId=smdcm&sysId&mediaType=kv&suffix=sql&template=data

mediaType=kv

query参数

[
  {
    "key": "ignore",
    "value": "true"
  },
  {
    "key": "ownerId",
    "value": "smdcm"
  },
  {
    "key": "sysId",
    "value": null
  },
  {
    "key": "mediaType",
    "value": "text"
  },
  {
    "key": "suffix",
    "value": "sql"
  }
]

头参数

[
  {
    "description": "项目识别编号（小写）",
    "key": "XownerId",
    "type": "text",
    "value": "smdcm"
  },
  {
    "key": "XsysId",
    "value": "4133033848105605678"
  }
]

body参数

1、根据规则替换对应的字段和内容
https://doc.rg1008.com/docs/rg_dev/rg_dev-1el3dqmqmna8o
2、注意：参数替换规则格式必须正确
3、通常改的字段

    "packageName":"com.rg3.entity",
    "namespaceName":"RG3.MauiAppHostDemo.Entity",
    "connName": "ConnZentao",
    "databaseName": "smdcm_bs_zentao",

tableNames 里面 "tableName": "zt_scripture",

RG3.MauiAppHostDemo

4、文件输入路径控制（如果为空，不输出）

1、tableOutPath 表输出路径
2、outPath 合成内容的文件输出路径

5、完整body案例参数

{
    "connectionString": "Data Source=localhost;Initial Catalog=bt_bs_booking;User Id=root;Password=rg12!;charset=utf8",
    "providerName": "MySql.Data.MySqlClient",
    "querySql":"show databases",
    "templateOut":"drop database `{{database}}`;",
    "templateOutRemark":"对应数据里面key，然后全部小写下划线",
    "body":{},
    "outPath": "~/App_Temp/{{owner_id}}/AiCode/db2-data/global.sql"
}

生成后的结果

接口输出代码

   /// <returns></returns>
        [HttpPost("data")]
        public async Task BuildDbData(string template, [FromBody] DatabasePo body, [FromQuery][Required] string ownerId, [FromQuery] string sysId, [FromQuery] string mediaType = "text", [FromQuery] string suffix = "sql")
        {
            DataKeyValue dkv = await BuildSqlDatas(template, body, ownerId, sysId, suffix);

            StringBuilder sbSql = new StringBuilder(dkv.Value);
            var data = Encoding.UTF8.GetBytes(sbSql.ToString());
            if (mediaType == "html")
            {
                Response.ContentType = "text/html";
                await Response.Body.WriteAsync(data, 0, data.Length);
            }
            else if (mediaType == "text")
            {
                Response.ContentType = "text/plain";
                await Response.Body.WriteAsync(data, 0, data.Length);
            }
            else if (mediaType == "kv")
            {
                Response.ContentType = "text/json";
                data = Encoding.UTF8.GetBytes(JsonUtil.Serialize(dkv, false, false, false));
                await Response.Body.WriteAsync(data, 0, data.Length);
            }
            else
            {
                Response.ContentType = "text/json";
                await Response.Body.WriteAsync(data, 0, data.Length);
            }
        }

实现代码

    /// <summary>
        /// 生成sql等数据代码
        /// </summary>
        /// <param name="template"></param>
        /// <param name="conn"></param>
        /// <param name="ownerId"></param>
        /// <param name="sysId"></param>
        /// <param name="suffix"></param>
        /// <returns></returns>
        /// <exception cref="BizException"></exception>
        private async Task<DataKeyValue> BuildSqlDatas(string template, DatabasePo conn, string ownerId, string sysId, string suffix)
        {
            DataKeyValue dkv = new DataKeyValue();
            StringBuilder sbSql = new StringBuilder();
            string tableVirF = $"~/App_Data/Templates/AiCode/{template}/tables.{suffix}";
            if (string.IsNullOrEmpty(conn.TemplateOut) && !FileUtil.ExistsFilesByVirPath(tableVirF))
            {
                throw new BizException(ErrorCodeConst.FILE_17001.ErrorCode, $"文件未存在【{tableVirF}】且TemplateOut没传递。");
            }
            ValidateUtil.ExistsRegexXssSql(conn.QuerySql);
            string tableSqlF = null;
            var encoding = System.Text.Encoding.UTF8;
            if (FileUtil.ExistsFilesByVirPath(tableVirF))
            {
                encoding = FileUtil.GetContentEncodingType(tableVirF);
                tableSqlF = await FileUtil.ReadFileAndContentByVirUrlAsync(tableVirF, encoding);
            }

            string columnVirF = $"~/App_Data/Templates/AiCode/{template}/columns.{suffix}";
            string columnSqlF = conn.TemplateOut;
            if (string.IsNullOrEmpty(conn.TemplateOut) && FileUtil.ExistsFilesByVirPath(columnVirF))
            {
                columnSqlF = await FileUtil.ReadFileAndContentByVirUrlAsync(columnVirF, encoding);
            }

            PFGlobalParameter pf = new PFGlobalParameter
            {
                ConnName = conn.ConnName,
                OwnerId = ownerId,
                SysId = sysId
            };
            var connection = _dbConnection.GetConnection(pf, conn.ConnectionString, conn.ProviderName);
            var dbData = _db.QueryDictionary(pf, connection, conn.QuerySql, conn.Body, PF.Abstractions.Enums.ColumnNameCase.Lower);

            foreach (var data in dbData)
            {
                StringBuilder sbDbLine = new StringBuilder(columnSqlF);
                foreach (var key in data.Keys)
                {
                    sbDbLine.Replace("{{" + key + "}}", (data[key] ?? "null").ToString());
                }
                sbSql.AppendLine(sbDbLine.ToString());
            }
            #region 输出代码到对应文件


            StringBuilder sbDbSql = new StringBuilder();
            if (tableSqlF == null)
            {
                sbDbSql.AppendLine(sbSql.ToString());
            }
            else
            {
                sbDbSql.AppendLine(tableSqlF).Replace("{{columns}}", sbSql.ToString());
            }


            if (!string.IsNullOrEmpty(conn.OutPath))
            {
                StringBuilder sbOutPath = new StringBuilder(conn.OutPath);
                sbOutPath.Replace("{{global_owner_id}}", ownerId);
                sbOutPath.Replace("{{global_sys_id}}", sysId);
                sbOutPath.Replace("{{global_conn_name}}", conn.ConnName);
                sbOutPath.Replace("{{table_conn_name}}", conn.ConnName);// 数据库连接串
                sbOutPath.Replace("{{owner_id}}", ownerId); //项目所有者
                dkv.Key = sbOutPath.ToString();
                FileUtil.CreateFileAndContentAsync(sbOutPath.ToString(), sbDbSql.ToString(), encoding);
            }
            #endregion 输出代码到对应文件
            dkv.Value = sbSql.ToString();
            return dkv;
        }