【freesql学习】利用freesql生成不同数据库的insert sql语句

利用freesql生成不同数据库的insert sql语句

场景

• 本地研发使用mysql数据库，部署到客户现场使用Oracle数据库，需要对业务系统数据库的基础数据或者叫初始化数据导出insert语句到现场。
• 方便维护，如果数据量大使用人工维护方式容易忘记或者出错
• 没有找到好的管理工具导出不同的insert or update语句

实现方式
利用freesql的tosql方法，tosql方法的Ifreesql对象是不同的数据库实例，而tosql方法不会打开数据库，因此无需知道客户现场的数据库连接。
本地先查询数据出来，再调用insertOrUpdate tosql方法。

实现代码

数据仓储层

public class ExportDataRepository : IExportDataRepository
    {
        private readonly IFreeSql _freeSql;
        public ExportDataRepository(DBContext dbContext)
        {
            _freeSql = dbContext.SjgxjhDB();
        }
        private List<xxxxModel> Get1()
        {
            return _freeSql.Select<xxxxModel>().ToList();
        }
         private List<xxxxModel> Get2()
        {
            return _freeSql.Select<xxxxModel>().ToList();
        }
}

生成sql的方法

public string ExportSql(DataType dataType)
        {
            List<xxxModel> list1 = Get1();
             List<xxModel> list2 = Get2();
            var Freesql = ExportDataHelper.GetIFreeSql(dataType);
            StringBuilder sb = new StringBuilder();
            AppendSql(BuildSql(Freesql, list1), sb);
             AppendSql(BuildSql(Freesql, list2), sb);
            return sb.ToString();
        }

生成sql语句扩展

private string BuildSql<T>(IFreeSql Freesql, List<T> list) where T : class
        {
            return Freesql.InsertOrUpdate<T>()
                              .SetSource(list)
                              .ToSql();
        }

追加sql字符串

 private void AppendSql(string sql, StringBuilder sb)
        {
            if (!string.IsNullOrWhiteSpace(sql))
                sb.Append(sql + ConstDefineGL.SplitChar + ConstDefineGL.NewlineCharacter);
        }

创建IFreeSql 对象扩展方法

public static class ExportDataHelper
    {
        public static IFreeSql GetIFreeSql(DataType dataType) =>
                 new FreeSqlBuilder()
                .UseConnectionString(dataType, "Server=xxxx;User ID=xxxx;Password=xxxx;port=3306;CharSet=utf8;pooling=true;Database=xxx;Max pool size=1;SslMode=None;")
                .UseNoneCommandParameter(true)
                .UseMonitorCommand(cmd => Console.WriteLine(cmd.CommandText))
                .Build();

    }

PS：sql连接字符串必须是指定格式的 包含user database等 不需要连接但格式要正确，否则freesql会报错。

freesql的数据库对象枚举

public enum DataType
    {
        MySql = 0,
        SqlServer = 1,
        PostgreSQL = 2,
        Oracle = 3,
        Sqlite = 4,
        OdbcOracle = 5,
        OdbcSqlServer = 6,
        OdbcMySql = 7,
        OdbcPostgreSQL = 8,
        Odbc = 9,
        OdbcDameng = 10,
        MsAccess = 11,
        Dameng = 12,
        OdbcKingbaseES = 13,
        ShenTong = 14,
        KingbaseES = 15,
        Firebird = 16,
        Custom = 17,
        ClickHouse = 18,
        GBase = 19,
        CustomOracle = 20,
        CustomSqlServer = 21,
        CustomMySql = 22,
        CustomPostgreSQL = 23
    }

常量

public static class ConstDefineGL
    {
        public static string SplitChar => ";";
        public static string NewlineCharacter => "\r\n";
    }

一定要添加需要导出的不同的数据库的nuget包支持
例如：FreeSql.Provider.Dameng
FreeSql.Provider.KingbaseES

效果如下
1.oracle

 MERGE INTO "api_proxy_relation" t1 
USING (SELECT '0000000000000003021' as "proxy_api_code", '0032594976708792320' as "original_api_code", NULL as "api_ip_port" FROM dual 
UNION ALL
 SELECT '0032871846597136384', '0032713550838665216', '' FROM dual ) t2 ON (t1."proxy_api_code" = t2."proxy_api_code" AND t1."original_api_code" = t2."original_api_code") 
WHEN MATCHED THEN 
  update set "api_ip_port" = t2."api_ip_port" 
WHEN NOT MATCHED THEN 
  insert ("proxy_api_code", "original_api_code", "api_ip_port") 
  values (t2."proxy_api_code", t2."original_api_code", t2."api_ip_port");

2.dameng

MERGE INTO "api_proxy_relation" t1 
USING (SELECT '0000000000000003021' as "proxy_api_code", '0032594976708792320' as "original_api_code", NULL as "api_ip_port" FROM dual 
UNION ALL
 SELECT '0032871846597136384', '0032713550838665216', '' FROM dual ) t2 ON (t1."proxy_api_code" = t2."proxy_api_code" AND t1."original_api_code" = t2."original_api_code") 
WHEN MATCHED THEN 
  update set "api_ip_port" = t2."api_ip_port" 
WHEN NOT MATCHED THEN 
  insert ("proxy_api_code", "original_api_code", "api_ip_port") 
  values (t2."proxy_api_code", t2."original_api_code", t2."api_ip_port");

3.kingdb

INSERT INTO "api_proxy_relation"("proxy_api_code", "original_api_code", "api_ip_port") VALUES('0000000000000003021', '0032594976708792320', NULL), ('0032871846597136384', '0032713550838665216', '')
ON CONFLICT("proxy_api_code", "original_api_code") DO UPDATE SET
"api_ip_port" = EXCLUDED."api_ip_port";

4.mysql

INSERT INTO `api_proxy_relation`(`proxy_api_code`, `original_api_code`, `api_ip_port`) VALUES('0000000000000003021', '0032594976708792320', NULL), ('0032871846597136384', '0032713550838665216', '')
ON DUPLICATE KEY UPDATE
`api_ip_port` = VALUES(`api_ip_port`);