表数据同步

表数据字段同步

需求：实现新旧数据库表字段及类型的同步
设计思路:

• 1.如表：a，查询新旧数据库此表的字段及类型及长度；
• 2.比对新旧两个表的差集字段；
• 3.向新表中插入字段；

---

关键sql脚本

查询所有表名称
select t.name as tableName from sys.tables t left join sys.schemas s on t.schema_id=s.schema_id order by tableName asc

获取指定表所有字段名、数据类型、长度
SELECT syscolumns.name AS 字段名,systypes.name AS 数据类型,syscolumns.length AS 长度 FROM syscolumns INNER JOIN systypes ON systypes.xtype=syscolumns.xtype WHERE id=(SELECT id FROM sysobjects WHERE name='{0}')

数据库表添加字段属性
alter table NewDB add property1 varchar(30), property2 varchar(30);

控制台实例代码

点击查看代码

using DemoSqlServer.Helpers;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;

namespace DemoSqlServer
{
    internal class Program
    {
        private static void Main(string[] args)
        {
            ConnForSQL connForSQL = new ConnForSQL(".", "AJDB", "sa", "sa123");
            ConnForSQL connForSQLNew = new ConnForSQL(".", "AJDBNew", "sa", "sa123");

            DataTable tableNameTable = new DataTable();

            //string getAllTableNameSql = "select concat(s.name ,concat('.',t.name)) as tableName from sys.tables t left join sys.schemas s on t.schema_id=s.schema_id order by tableName asc;";

            //获取数据库所有表名称
            string getAllTableNameSql = "select t.name as tableName from sys.tables t left join sys.schemas s on t.schema_id=s.schema_id order by tableName asc;";

            var dataSet = connForSQL.ReturnDataSet(getAllTableNameSql);
            var dataSetNew = connForSQLNew.ReturnDataSet(getAllTableNameSql);

            List<string> tableNameList = new List<string>();
            List<string> tableNameListNew = new List<string>();

            //表名集合
            for (int i = 0; i < dataSet.Tables[0].Rows.Count; i++)
            {
                tableNameList.Add(dataSet.Tables[0].Rows[i][0].ToString());
            }

            for (int i = 0; i < dataSetNew.Tables[0].Rows.Count; i++)
            {
                tableNameListNew.Add(dataSetNew.Tables[0].Rows[i][0].ToString());
            }

            //获取字段信息
            string getFieldInfoSql = "SELECT syscolumns.name AS 字段名,systypes.name AS 数据类型,syscolumns.length AS 长度 FROM syscolumns INNER JOIN systypes ON systypes.xtype=syscolumns.xtype WHERE id=(SELECT id FROM sysobjects WHERE  name='{0}')";

            List<string> targetFieldList = new List<string>();

            for (int i = 0; i < tableNameListNew.Count; i++)
            {
                var t = tableNameListNew[i];
                string sql = string.Format(getFieldInfoSql, tableNameListNew[i]);
                var fieldInfoTable = connForSQLNew.ReturnDataSet(sql).Tables[0];

                targetFieldList.Clear();

                for (int j = 0; j < fieldInfoTable.Rows.Count; j++)
                {
                    var a = fieldInfoTable.Rows[j][0];//TestId
                    var b = fieldInfoTable.Rows[j][1];//varchar
                    var c = fieldInfoTable.Rows[j][2];//50

                    targetFieldList.Add(a + "@" + b + "(" + c + ")");
                }
            }

            List<string> sourceFieldList = new List<string>();

            for (int i = 0; i < tableNameList.Count; i++)
            {
                var t = tableNameList[i];

                if (t.Equals("Dic_Employee"))
                {
                    string sql = string.Format(getFieldInfoSql, tableNameList[i]);
                    var fieldInfoTable = connForSQL.ReturnDataSet(sql).Tables[0];

                    sourceFieldList.Clear();

                    for (int j = 0; j < fieldInfoTable.Rows.Count; j++)
                    {
                        var a = fieldInfoTable.Rows[j][0];
                        var b = fieldInfoTable.Rows[j][1];
                        var c = fieldInfoTable.Rows[j][2];
                        sourceFieldList.Add(a + "@" + b + "(" + c + ")");
                    }
                }
            }

            List<string> differenceSetList = new List<string>();

            differenceSetList = sourceFieldList.Except(targetFieldList).ToList();
            /*******************************
           alter table NewVehicleInformation add
kssj_NQ varchar(30),
jssj_NQ varchar(30);
             * *********************/
            if (differenceSetList.Count > 0)
            {
                string sql = " alter table Dic_Employee add ";

                for (int i = 0; i < differenceSetList.Count; i++)
                {
                    var fieldArr = differenceSetList[i].Split('@');
                    var a = fieldArr[0];
                    var b = fieldArr[1];
                    if (i.Equals(differenceSetList.Count - 1))
                    {
                        sql += a + " " + b + ";";
                    }
                    else
                    {
                        sql += a + " " + b + ",";
                    }
                }

                int nSuccessLine = connForSQLNew.ExecuteSQL(sql);
                Console.WriteLine("执行成功行数：" + nSuccessLine);
            }
            else
            {
                Console.WriteLine("数据表无差异!!!");
            }

            Console.ReadKey();
        }
    }
}