private static void Main(string[] args) { var compareResult = new DataTable(); compareResult.Columns.Add(new DataColumn("表英文名", Type.GetType("System.String"))); compareResult.Columns.Add(new DataColumn("表中文名", Type.GetType("System.String"))); compareResult.Columns.Add(new DataColumn("比较类型", Type.GetType("System.String"))); compareResult.Columns.Add(new DataColumn("高版本属性", Type.GetType("System.String"))); compareResult.Columns.Add(new DataColumn("高版本值", Type.GetType("System.String"))); //删除0/增加1 compareResult.Columns.Add(new DataColumn("低版本属性", Type.GetType("System.String"))); compareResult.Columns.Add(new DataColumn("低版本值", Type.GetType("System.String"))); compareResult.Columns.Add(new DataColumn("属性名称", Type.GetType("System.String"))); var sqlHigh = new SqlExecuteHelper(ConfigurationManager.AppSettings["highSQLConnect"]); var sqlLow = new SqlExecuteHelper(ConfigurationManager.AppSettings["lowSQLConnect"]); string tableName = ConfigurationManager.AppSettings["CompareTable"]; string[] tableArrary = tableName.Split(','); var dtHight = new DataTable(); var dtLow = new DataTable(); foreach (string currentTable in tableArrary) { dtHight = sqlHigh.GetDataTable(string.Format( "select * from information_schema.columns where table_name='{0}'", currentTable)); dtLow = sqlLow.GetDataTable(string.Format( "select * from information_schema.columns where table_name='{0}'", currentTable)); string currentTableCn = sqlHigh.GetSacalarString( string.Format("SELECT TOP 1 table_name_c FROM dbo.data_dict WHERE table_name='{0}'", currentTable)); bool isDiff = true; //如果两边都存在 if (dtHight.Rows.Count != 0 && dtLow.Rows.Count != 0) { Console.ForegroundColor = ConsoleColor.Yellow; Console.WriteLine("两边都存在表{0}", currentTable); Console.ForegroundColor = ConsoleColor.White; //todo //高版本的字段 for (int i = 0; i < dtHight.Rows.Count; i++) { string currentProp = dtHight.Rows[i]["COLUMN_NAME"].ToString(); string sqlProp = string.Format( "select * from information_schema.columns where table_name='{0}' AND COLUMN_NAME='{1}'", currentTable, currentProp); string currentPropCn = sqlHigh.GetSacalarString( string.Format( "SELECT TOP 1 field_name_c FROM dbo.data_dict WHERE table_name='{0}' AND field_name='{1}'", currentTable, currentProp)); DataTable tempHighTable = sqlHigh.GetDataTable(sqlProp); DataTable tempLowTable = sqlLow.GetDataTable(sqlProp); //如果低版本没有则不比较 if (tempLowTable.Rows.Count < 1) { Console.WriteLine("低版本没有字段{0}", currentProp); //compareResult.Add(new CompareEntity(currentTable, currentTableCn, "低版本没有字段", "", "", "", "", "")); compareResult.Rows.Add(currentTable, currentTableCn, "低版本没有字段", currentProp, tempHighTable.Rows[0]["DATA_TYPE"], "", "", currentPropCn); Console.ForegroundColor = ConsoleColor.White; isDiff = false; } else { //默认值 if (tempHighTable.Rows[0]["COLUMN_DEFAULT"].ToString() != tempLowTable.Rows[0]["COLUMN_DEFAULT"].ToString()) { Console.WriteLine("字段{0}高版本默认值为{1},低版本//默认值为{2}", currentProp, tempHighTable.Rows[0]["COLUMN_DEFAULT"], tempLowTable.Rows[0]["COLUMN_DEFAULT"]); compareResult.Rows.Add(currentTable, currentTableCn, "默认值", currentProp, tempHighTable.Rows[0]["COLUMN_DEFAULT"].ToString(), currentProp, tempLowTable.Rows[0]["COLUMN_DEFAULT"].ToString(), currentPropCn); isDiff = false; } //类型 if (tempHighTable.Rows[0]["DATA_TYPE"].ToString() != tempLowTable.Rows[0]["DATA_TYPE"].ToString()) { Console.WriteLine("字段{0}高版本类型为{1},低版本类型为{2}", currentProp, tempHighTable.Rows[0]["DATA_TYPE"], tempLowTable.Rows[0]["DATA_TYPE"]); compareResult.Rows.Add(currentTable, currentTableCn, "类型", currentProp, tempHighTable.Rows[0]["DATA_TYPE"].ToString(), currentProp, tempLowTable.Rows[0]["DATA_TYPE"].ToString(), currentPropCn); isDiff = false; } //长度 if (tempHighTable.Rows[0]["CHARACTER_MAXIMUM_LENGTH"].ToString() != tempLowTable.Rows[0]["CHARACTER_MAXIMUM_LENGTH"].ToString()) { Console.WriteLine("字段{0}高版本类型为{1},低版本类型为{2}", currentProp, tempHighTable.Rows[0]["CHARACTER_MAXIMUM_LENGTH"], tempLowTable.Rows[0]["CHARACTER_MAXIMUM_LENGTH"]); compareResult.Rows.Add(currentTable, currentTableCn, "长度", currentProp, tempHighTable.Rows[0]["CHARACTER_MAXIMUM_LENGTH"].ToString(), currentProp, tempLowTable.Rows[0]["CHARACTER_MAXIMUM_LENGTH"].ToString(), currentPropCn); isDiff = false; } } } if (isDiff) { compareResult.Rows.Add(currentTable, currentTableCn, "两边完全相同的表", "", "", "", "", ""); } //todo //低版本的字段 for (int i = 0; i < dtLow.Rows.Count; i++) { string currentProp = dtLow.Rows[i]["COLUMN_NAME"].ToString(); string sqlProp = string.Format( "select * from information_schema.columns where table_name='{0}' AND COLUMN_NAME='{1}'", currentTable, currentProp); string currentPropCn = sqlLow.GetSacalarString( string.Format( "SELECT TOP 1 field_name_c FROM dbo.data_dict WHERE table_name='{0}' AND field_name='{1}'", currentTable, currentProp)); DataTable tempHighTable = sqlHigh.GetDataTable(sqlProp); DataTable tempLowTable = sqlLow.GetDataTable(sqlProp); //如果高版本没有则不比较 if (tempHighTable.Rows.Count < 1) { Console.WriteLine("高本没有字段{0}", currentProp); compareResult.Rows.Add(currentTable, currentTableCn, "高版本没有字段", "", "", currentProp, tempLowTable.Rows[0]["DATA_TYPE"], currentPropCn); } } } //如果高版本存在,低版本不存在 else if (dtHight.Rows.Count > 0 && dtLow.Rows.Count == 0) { Console.ForegroundColor = ConsoleColor.Yellow; Console.WriteLine("低版本不存在表{0}", currentTable); compareResult.Rows.Add(currentTable, currentTableCn, "低版本不存在表", "", "", "", "", ""); Console.ForegroundColor = ConsoleColor.White; } else if (dtHight.Rows.Count == 0 && dtLow.Rows.Count > 0) { Console.ForegroundColor = ConsoleColor.Yellow; Console.WriteLine("高版本不存在表{0}", currentTable); compareResult.Rows.Add(currentTable, currentTableCn, "高版本不存在表", "", "", "", "", ""); Console.ForegroundColor = ConsoleColor.White; } else if (dtHight.Rows.Count == 0 && dtLow.Rows.Count == 0) { Console.ForegroundColor = ConsoleColor.Yellow; Console.WriteLine("两边都不存在表{0}", currentTable); compareResult.Rows.Add(currentTable, currentTableCn, "两边都不存在表", "", "", "", "", ""); Console.ForegroundColor = ConsoleColor.White; } } NPOIExcelRender.RenderToExcel(compareResult, string.Format(@"{0}.xls", "导出数据")); Console.ForegroundColor = ConsoleColor.Green; Console.WriteLine("数据差异分析完成,已成功导出Excel"); }
/// <summary> /// 此处执行业务操作逻辑,数据库可以直接使用小平台连接ESB数据库,进行读写操作。 /// 如果非得要自己取sql连接,请去config.ApplicationName的值(注册表键)。自己获取。 /// </summary> private void DoWork() { //此处实现代码... //MyTrace.Write(TraceCategory.AsyncService, TraceLevel.Error, // "自定义作业操作:作业测试: " + DateTime.Now.ToString()); WriteMsg("调度开始"); try { //获取ERP业务对象 ERPObjectBusiness erpObjectBusiness = new ERPObjectBusiness(); var erpObjectList = erpObjectBusiness.GetERPObjectList(); //初始化配置数据 //ERPConfigBusiness erpConfigBusiness = new ERPConfigBusiness(); //var _erpConfig = erpConfigBusiness.ErpConfigs; string item = ConfigurationManager.AppSettings["ApplicationName"]; if (item == null) { WriteMsg("ApplicationName为空"); } var strConnection = (new MyDbConnection(item)).GetConnectionString(); //if (!string.IsNullOrEmpty(item)) //{ // Initializer.UnSafeInit(strConnection); //} string str = "SELECT ProviderID,ProviderName,DisplayName,Description,ProviderType, IsMainSite AS Protocol,DbServer,DBUserName,DbPassword,DbName FROM esb_Provider WHERE (1=1)"; var _erpConfig = CPQuery.From(str).ToList <EsbProvider>(); ////获取主系统数据 var mainConfig = _erpConfig.SingleOrDefault(t => t.Protocol == "1"); strConnection = string.Format("server={0};uid={1};pwd={2};database={3}", mainConfig.DbServer, mainConfig.DBUserName, mainConfig.DbPassword, mainConfig.DbName); SqlExecuteHelper sqlHelper = new SqlExecuteHelper(strConnection); //获取子系统数据 var otherConfig = _erpConfig.Where(t => t.Protocol == "0" && t.DbName != mainConfig.DbName); otherConfig = DataTableHelper.DistinctBy(otherConfig, p => p.DbName); string SelectSQL = string.Empty; //业务系统 DataSet _dsResult = new DataSet(); foreach (var erpObject in erpObjectList) { foreach (var erpTable in erpObject.ERPTables) { //得到主库数据 SelectSQL = string.Format("SELECT {0},'' as CheckAction FROM {1}", "*", erpTable.TableName); DataTable mainTable = new DataTable(); mainTable = sqlHelper.GetDataTable(SelectSQL, erpTable.TableName, erpObject.ObjectName); DataTable itemTable = new DataTable(); foreach (var itemConfig in otherConfig) { DataTable diffTable = new DataTable(); //得到子库数据 strConnection = string.Format("server={0};uid={1};pwd={2};database={3}", itemConfig.DbServer, itemConfig.DBUserName, itemConfig.DbPassword, itemConfig.DbName); SqlExecuteHelper itemSqlHelper = new SqlExecuteHelper(strConnection); itemTable = itemSqlHelper.GetDataTable(SelectSQL, erpTable.TableName, erpObject.ObjectName); //现默认两个DataTable需要对比的字段是一样的 DataTableHelper.CompareTable(mainTable, itemTable, out diffTable, erpTable.TableKey, erpTable.TableProps); //写入日志 var filePath = string.Format(@"{0}\{1}\{2}\{3}\{4}", System.AppDomain.CurrentDomain.BaseDirectory, "InspectionLog", itemConfig.DisplayName, DateTime.Now.ToString("yyyyMMdd"), erpObject.ObjectName); if (!Directory.Exists(filePath)) { Directory.CreateDirectory(filePath); } diffTable.WriteXml(string.Format(@"{0}\{1}\{2}\{3}\{4}\{5}.xml", System.AppDomain.CurrentDomain.BaseDirectory, "InspectionLog", itemConfig.DisplayName, DateTime.Now.ToString("yyyyMMdd"), erpObject.ObjectName, erpTable.TableName)); } } } } catch (Exception err) { WriteMsg(err.ToString()); } WriteMsg("调度结束"); }