目标: 在数据库表结构未知的情况下,用sqlsugar同步远程数据库的一张表
1.同步表结构
新建一个类TableField,存放表的每一字段的相关参数
public class TableField
{
//字段名
public string Column_name { get; set; }
//字段类型
public string Column_type { get; set; }
//字段注释
public string Column_comment { get; set; }
}
同步数据库db下A表的表结构(本地数据库需提前创建同名数据库和同名表)
//表结构同步
List<TableField> dynamicStructure = sqlSugarClientRemote.SqlQueryable<TableField>(
"select column_name,column_type,column_key,column_comment " +
"from information_schema.COLUMNS " +
"where table_schema = 'db' and table_name ='A'").ToList();
List<TableField> localStructure = sqlSugarClientLocal.SqlQueryable<TableField>(
"select column_name,column_type,column_key,column_comment " +
"from information_schema.COLUMNS " +
"where table_schema = 'db' and table_name ='A'").ToList();
foreach (TableField tf in dynamicStructure )
if (localStructure .Where(s => s.Column_name == tf.Column_name).Count() == 0)
sqlSugarClientLocal.Ado.ExecuteCommand($"alter table A add column {tf.Column_name} {tf.Column_type} comment '{tf.Column_comment}';");
2.同步数据(由于表的字段未知,故采用dynamic类型储存数据)
List<dynamic> queryable = sqlSugarClientRemote.SqlQueryable<dynamic>(
"select * from A").ToList();
//将每行数据的ID都改为0,插入本地数据库时就会按照ID自增顺序来修改ID
queryable .ForEach(p => { p.id = 0; });
//使用 StringBuilder生成插入字符串,并执行插入操作,返回被影响的行数
int count = 0;
if (queryable .Count > 0)
{
StringBuilder sql = new StringBuilder();
sql.Append($"insert into A({string.Join(",", ((IDictionary<string, object>)queryable[0]).Keys)}) values");
foreach (dynamic p in queryable )
{
sql.Append("(");
foreach (object v in ((IDictionary<string, object>)p).Values)
sql.Append(v == null ? "NULL," : $"'{Convert.ToString(v).Replace("'", "''")}',");
sql.Remove(sql.Length - 1, 1);
if (queryable .IndexOf(p) == queryable .Count - 1)
sql.Append($");");
else
sql.Append($"),");
}
count = sqlSugarClientLocal.Ado.ExecuteCommand(sql.ToString());
}
return count;