public FileResult ExportSql(string categoryFullID, string startDate) { var key = FormulaHelper.CreateGuid(); StringBuilder sb = new StringBuilder(); sb.AppendFormat(@" --本SQL使用方法: --1、冲突确认,即执行后返回的列表信息即冲突信息; --2、在系统中处理冲突; --3、再次执行本sql; "); SQLHelper sqlHelper = SQLHelper.CreateSqlHelper(ConnEnum.Base); SQLHelper flowSqlHelper = SQLHelper.CreateSqlHelper(ConnEnum.WorkFlow); string preDBName = sqlHelper.DbName.Split('_').First(); sb.AppendFormat("USE [{0}]", sqlHelper.DbName); sb.AppendLine(); sb.AppendFormat(@" --SQL只能执行一次 if exists (select ID from S_UI_ModifyLogLog where ID='{0}' and IOType='导入') begin select '本SQL已经执行过,不能再次执行!' return end ", key); sb.AppendLine(); string sql = "select * from S_M_Category where FullID like '" + categoryFullID + "%'"; var dtCategory = sqlHelper.ExecuteDataTable(sql); //模块名称 string categoryName = dtCategory.AsEnumerable().SingleOrDefault(c => c["FullID"].ToString() == categoryFullID)["Name"].ToString(); if (categoryFullID != "0") { var parentCategory = sqlHelper.ExecuteDataTable("select * from S_M_Category where ID='" + dtCategory.AsEnumerable().SingleOrDefault(c => c["FullID"].ToString() == categoryFullID)["ParentID"].ToString() + "'"); sb.AppendFormat(@" --模块不存在,不能导入 if not exists (select ID from S_M_Category where ID='{0}') begin select '模块“{1}”不存在,不能导入!' return end ", parentCategory.Rows[0]["ID"], parentCategory.Rows[0]["Name"]); } sb.AppendLine(); sb.Append("--更新模块开始"); sb.AppendFormat(SQLHelper.CreateUpdateSql("S_M_Category", dtCategory)); sb.Append("--更新模块结束"); sb.AppendLine(); sb.AppendLine(); sql = string.Format("select * from {2} where CategoryID in('{0}') and ModifyTime>'{1}'", string.Join("','", dtCategory.AsEnumerable().Select(c => c["ID"].ToString()).ToArray()), startDate, "{0}"); var dtForm = sqlHelper.ExecuteDataTable(string.Format(sql, "S_UI_Form")); var dtList = sqlHelper.ExecuteDataTable(string.Format(sql, "S_UI_List")); var dtPivot = sqlHelper.ExecuteDataTable(string.Format(sql, "S_UI_Pivot")); var dtSelector = sqlHelper.ExecuteDataTable(string.Format(sql, "S_UI_Selector")); var dtWord = sqlHelper.ExecuteDataTable(string.Format(sql, "S_UI_Word")); var dtExcelPrint = sqlHelper.ExecuteDataTable(string.Format(sql, "S_UI_ExcelPrint")); var dtReport = sqlHelper.ExecuteDataTable(string.Format(sql, "S_R_Define")); var dtEnum = sqlHelper.ExecuteDataTable(string.Format(sql, "S_M_EnumDef")); var dtWorkflow = sqlHelper.ExecuteDataTable(string.Format(sql, flowSqlHelper.DbName + ".dbo.S_WF_DefFlow")); sb.AppendLine(); sb.AppendFormat("update S_UI_Form set Collision='Collision' where (Collision is null or Collision not in('{2}','keep','cover')) and ID in('{0}') and ModifyTime > '{1}'", string.Join("','", dtForm.AsEnumerable().Select(c => c["ID"]).ToArray()), startDate, key); sb.AppendLine(); sb.AppendFormat("update S_UI_List set Collision='Collision' where (Collision is null or Collision not in('{2}','keep','cover')) and ID in('{0}') and ModifyTime > '{1}'", string.Join("','", dtList.AsEnumerable().Select(c => c["ID"]).ToArray()), startDate, key); sb.AppendLine(); sb.AppendFormat("update S_UI_Pivot set Collision='Collision' where (Collision is null or Collision not in('{2}','keep','cover')) and ID in('{0}') and ModifyTime > '{1}'", string.Join("','", dtPivot.AsEnumerable().Select(c => c["ID"]).ToArray()), startDate, key); sb.AppendLine(); sb.AppendFormat("update S_UI_Selector set Collision='Collision' where (Collision is null or Collision not in('{2}','keep','cover')) and ID in('{0}') and ModifyTime > '{1}'", string.Join("','", dtSelector.AsEnumerable().Select(c => c["ID"]).ToArray()), startDate, key); sb.AppendLine(); sb.AppendFormat("update S_UI_Word set Collision='Collision' where (Collision is null or Collision not in('{2}','keep','cover')) and ID in('{0}') and ModifyTime > '{1}'", string.Join("','", dtWord.AsEnumerable().Select(c => c["ID"]).ToArray()), startDate, key); sb.AppendLine(); sb.AppendFormat("update S_UI_ExcelPrint set Collision='Collision' where (Collision is null or Collision not in('{2}','keep','cover')) and ID in('{0}') and ModifyTime > '{1}'", string.Join("','", dtExcelPrint.AsEnumerable().Select(c => c["ID"]).ToArray()), startDate, key); sb.AppendLine(); sb.AppendFormat("update S_R_Define set Collision='Collision' where (Collision is null or Collision not in('{2}','keep','cover')) and ID in('{0}') and ModifyTime > '{1}'", string.Join("','", dtReport.AsEnumerable().Select(c => c["ID"]).ToArray()), startDate, key); sb.AppendLine(); sb.AppendFormat("update S_M_EnumDef set Collision='Collision' where (Collision is null or Collision not in('{2}','keep','cover')) and ID in('{0}') and ModifyTime > '{1}'", string.Join("','", dtEnum.AsEnumerable().Select(c => c["ID"]).ToArray()), startDate, key); sb.AppendLine(); sb.AppendFormat("update {3}.dbo.S_WF_DefFlow set Collision='Collision' where (Collision is null or Collision not in('{2}','keep','cover')) and ID in('{0}') and ModifyTime > '{1}'", string.Join("','", dtWorkflow.AsEnumerable().Select(c => c["ID"]).ToArray()), startDate, key, flowSqlHelper.DbName); sb.AppendLine(); //如果存在日志修改记录,则停止执行sql sb.AppendLine(); sb.AppendFormat(@" --如果存在冲突则终止执行sql if exists (select ID from S_UI_Form where Collision = 'Collision' union select ID from S_UI_List where Collision = 'Collision' union select ID from S_UI_Pivot where Collision = 'Collision' union select ID from S_UI_Selector where Collision = 'Collision' union select ID from S_UI_Word where Collision = 'Collision' union select ID from S_UI_ExcelPrint where Collision = 'Collision' union select ID from S_R_Define where Collision = 'Collision' union select ID from S_M_EnumDef where Collision = 'Collision' union select ID from {0}.dbo.S_WF_DefFlow where Collision = 'Collision') begin select '请先处理冲突!' return end ", flowSqlHelper.DbName); //表单 UIFO uiFO = new UIFO(); foreach (DataRow row in dtForm.Rows) { sb.AppendFormat(@" if not exists (select * from S_UI_Form where Collision in('keep','{0}') and ID ='{1}') begin delete S_UI_Form where ID='{1}' {2} {3} {4} end ", key , row["ID"] , SQLHelper.CreateInsertSql("S_UI_Form", row) , string.Format("USE [{0}_{1}]", preDBName, row["ConnName"]) , uiFO.CreateReleaseFormSql(row["ID"].ToString()) ); sb.AppendFormat("USE [{0}_Base]", preDBName); sb.AppendLine(); } //列表 foreach (DataRow row in dtList.Rows) { sb.AppendFormat(@" if not exists (select * from S_UI_List where Collision in('keep','{0}') and ID ='{1}') begin delete S_UI_List where ID='{1}' {2} end ", key, row["ID"], SQLHelper.CreateInsertSql("S_UI_List", row)); } //Word foreach (DataRow row in dtWord.Rows) { sb.AppendFormat(@" if not exists (select * from S_UI_Word where Collision in('keep','{0}') and ID ='{1}') begin delete S_UI_Word where ID='{1}' {2} end ", key, row["ID"], SQLHelper.CreateInsertSql("S_UI_Word", row)); } //ExcelPrint foreach (DataRow row in dtExcelPrint.Rows) { sb.AppendFormat(@" if not exists (select * from S_UI_ExcelPrint where Collision in('keep','{0}') and ID ='{1}') begin delete S_UI_ExcelPrint where ID='{1}' {2} end ", key, row["ID"], SQLHelper.CreateInsertSql("S_UI_ExcelPrint", row)); } //选择器 foreach (DataRow row in dtSelector.Rows) { sb.AppendFormat(@" if not exists (select * from S_UI_Selector where Collision in('keep','{0}') and ID ='{1}') begin delete S_UI_Selector where ID='{1}' {2} end ", key, row["ID"], SQLHelper.CreateInsertSql("S_UI_Selector", row)); } //透视表 foreach (DataRow row in dtPivot.Rows) { sb.AppendFormat(@" if not exists (select * from S_UI_Pivot where Collision in('keep','{0}') and ID ='{1}') begin delete S_UI_Pivot where ID='{1}' {2} end ", key, row["ID"], SQLHelper.CreateInsertSql("S_UI_Pivot", row)); } //报表 foreach (DataRow row in dtReport.Rows) { var dtReportSub = sqlHelper.ExecuteDataTable(string.Format("select * from S_R_DataSet where DefineID in('{0}')", row["ID"])); var dtReportSubSub = sqlHelper.ExecuteDataTable(string.Format("select * from S_R_Field where DataSetID in('{0}')", string.Join("','", dtReportSub.AsEnumerable().Select(c => c["ID"].ToString())))); sb.AppendFormat(@" if not exists (select * from S_R_Define where Collision in('keep','{0}') and ID ='{1}') begin delete S_R_Define where ID='{1}' {2} {3} {4} end ", key , row["ID"] , SQLHelper.CreateInsertSql("S_R_Define", row) , SQLHelper.CreateInsertSql("S_R_DataSet", dtReportSub) , SQLHelper.CreateInsertSql("S_R_Field", dtReportSubSub) ); } //枚举 foreach (DataRow row in dtEnum.Rows) { var dtEnumSub = sqlHelper.ExecuteDataTable(string.Format("select * from S_M_EnumItem where EnumDefID in('{0}')", row["ID"])); sb.AppendFormat(@" if not exists (select * from S_M_EnumDef where Collision in('keep','{0}') and ID ='{1}') begin delete S_M_EnumDef where ID='{1}' {2} {3} end ", key , row["ID"] , SQLHelper.CreateInsertSql("S_M_EnumDef", row) , SQLHelper.CreateInsertSql("S_M_EnumItem", dtEnumSub)); } //流程 sb.AppendLine(); sb.AppendFormat("USE [{0}]", flowSqlHelper.DbName); sb.AppendLine(); foreach (DataRow row in dtWorkflow.Rows) { sql = string.Format("select * from S_WF_DefStep where DefFlowID='{0}'", row["ID"]); DataTable dtDefStep = flowSqlHelper.ExecuteDataTable(sql); sql = string.Format("select * from S_WF_DefRouting where DefFlowID='{0}'", row["ID"]); DataTable dtDefRouting = flowSqlHelper.ExecuteDataTable(sql); sql = string.Format("select * from S_WF_DefSubForm where DefFlowID='{0}'", row["ID"]); DataTable dtDefSubForm = flowSqlHelper.ExecuteDataTable(sql); sb.AppendFormat(@" if not exists (select * from S_WF_DefFlow where Collision in('keep','{0}') and ID ='{1}') begin delete S_WF_DefFlow where ID='{1}' {2} {3} {4} {5} end ", key , row["ID"] , SQLHelper.CreateInsertSql("S_WF_DefFlow", row) , SQLHelper.CreateInsertSql("S_WF_DefStep", dtDefStep) , SQLHelper.CreateInsertSql("S_WF_DefRouting", dtDefRouting) , SQLHelper.CreateInsertSql("S_WF_DefSubForm", dtDefSubForm) ); } sb.AppendFormat("USE [{0}]", sqlHelper.DbName); //记录更新 StringBuilder sb1 = new StringBuilder(); sb1.AppendFormat("【表单名称】{0}\n", dtForm.Rows.Count == 0 ? "无" : string.Join(",", dtForm.AsEnumerable().Select(c => c["Name"].ToString()).ToArray())); sb1.AppendFormat("【列表名称】{0}\n", dtList.Rows.Count == 0 ? "无" : string.Join(",", dtList.AsEnumerable().Select(c => c["Name"].ToString()).ToArray())); sb1.AppendFormat("【流程名称】{0}\n", dtWorkflow.Rows.Count == 0 ? "无" : string.Join(",", dtWorkflow.AsEnumerable().Select(c => c["Name"].ToString()).ToArray())); sb1.AppendFormat("【报表名称】{0}\n", dtReport.Rows.Count == 0 ? "无" : string.Join(",", dtReport.AsEnumerable().Select(c => c["Name"].ToString()).ToArray())); sb1.AppendFormat("【选择器名称】{0}\n", dtSelector.Rows.Count == 0 ? "无" : string.Join(",", dtSelector.AsEnumerable().Select(c => c["Name"].ToString()).ToArray())); sb1.AppendFormat("【透视表名称】{0}\n", dtPivot.Rows.Count == 0 ? "无" : string.Join(",", dtPivot.AsEnumerable().Select(c => c["Name"].ToString()).ToArray())); sb1.AppendFormat("【Word导出名称】{0}\n", dtWord.Rows.Count == 0 ? "无" : string.Join(",", dtWord.AsEnumerable().Select(c => c["Name"].ToString()).ToArray())); sb1.AppendFormat("【枚举名称】{0}\n", dtEnum.Rows.Count == 0 ? "无" : string.Join(",", dtEnum.AsEnumerable().Select(c => c["Name"].ToString()).ToArray())); S_UI_ModifyLogLog log = new S_UI_ModifyLogLog(); log.ID = key; log.IOTime = DateTime.Now; log.IOType = "导出"; log.CategoryName = categoryName; log.RelateData = sb1.ToString(); entities.Set <S_UI_ModifyLogLog>().Add(log); entities.SaveChanges(); sb.AppendLine(); sb.AppendFormat(@" delete S_UI_ModifyLogLog where ID='{0}' insert into S_UI_ModifyLogLog ([ID],[IOTime],[IOType],[RelateData],[CategoryName]) values ('{0}','{1}','导入','{2}','{3}') ", key, DateTime.Now, sb1.ToString(), categoryName); //更新冲突状态为完成 sb.AppendFormat("USE [{0}]", sqlHelper.DbName); sb.AppendLine(); sb.AppendFormat("update S_UI_Form set Collision='{1}' where ID in('{0}')", string.Join("','", dtForm.AsEnumerable().Select(c => c["ID"].ToString())), key); sb.AppendLine(); sb.AppendFormat("update S_UI_List set Collision='{1}' where ID in('{0}')", string.Join("','", dtList.AsEnumerable().Select(c => c["ID"].ToString())), key); sb.AppendLine(); sb.AppendFormat("update S_UI_Pivot set Collision='{1}' where ID in('{0}')", string.Join("','", dtPivot.AsEnumerable().Select(c => c["ID"].ToString())), key); sb.AppendLine(); sb.AppendFormat("update S_UI_Selector set Collision='{1}' where ID in('{0}')", string.Join("','", dtSelector.AsEnumerable().Select(c => c["ID"].ToString())), key); sb.AppendLine(); sb.AppendFormat("update S_UI_Word set Collision='{1}' where ID in('{0}')", string.Join("','", dtWord.AsEnumerable().Select(c => c["ID"].ToString())), key); sb.AppendLine(); sb.AppendFormat("update S_R_Define set Collision='{1}' where ID in('{0}')", string.Join("','", dtReport.AsEnumerable().Select(c => c["ID"].ToString())), key); sb.AppendLine(); sb.AppendFormat("update S_M_EnumDef set Collision='{1}' where ID in('{0}')", string.Join("','", dtEnum.AsEnumerable().Select(c => c["ID"].ToString())), key); sb.AppendLine(); sb.AppendFormat("update {2}.dbo.S_WF_DefFlow set Collision='{1}' where ID in('{0}')", string.Join("','", dtWorkflow.AsEnumerable().Select(c => c["ID"].ToString())), key, flowSqlHelper.DbName); MemoryStream ms = new MemoryStream(System.Text.Encoding.Default.GetBytes(sb.ToString())); ms.Position = 0; return(File(ms, "application/octet-stream ; Charset=UTF8", "base" + startDate + "更新脚本.sql")); }
public FileResult SqlFile(string defID, string fileCode, string tableName) { string sql = string.Format("select * from {1} where ID in('{0}')", defID.Replace(",", "','"), tableName); SQLHelper sqlHelper = SQLHelper.CreateSqlHelper(ConnEnum.Base); DataTable dt = sqlHelper.ExecuteDataTable(sql); //表单定义和word模板同时导出历史版本 if (tableName.ToUpper() == "S_UI_FORM" || tableName.ToUpper() == "S_UI_WORD") { var codes = string.Join("','", dt.AsEnumerable().Select(c => c["Code"].ToString()).ToArray()); sql = string.Format("select * from {1} where Code in('{0}')", codes, tableName); dt = sqlHelper.ExecuteDataTable(sql); } StringBuilder result = new StringBuilder();; if (Config.Constant.IsOracleDb) { //声明变量 result.Append("DECLARE"); result.AppendLine(); foreach (DataColumn col in dt.Columns) { if (col.DataType == typeof(string)) { result.AppendFormat("{0} nclob;", col.ColumnName); result.AppendLine(); } } result.AppendFormat("begin"); //begin result.AppendFormat(SQLHelper.CreateUpdateSql(tableName, dt)); result.AppendFormat("end"); } else { #region SqlServer版 result.AppendLine(String.Format("USE [{0}] \n", sqlHelper.DbName)); result.AppendLine(SQLHelper.CreateUpdateSql(tableName, dt)); //如果导出的是表单,则增加生成表的Sql if (tableName == "S_UI_Form") { UIFO uiFO = new UIFO(); StringBuilder sb = new StringBuilder("\n\n"); foreach (DataRow row in dt.Rows) { sb.AppendLine(String.Format("USE [{0}_{1}]", sqlHelper.DbName.Split('_').First(), row["ConnName"])); sb.AppendLine(); sb.AppendLine(uiFO.CreateReleaseFormSql(row["ID"].ToString())); } result.AppendLine(sb.ToString()); } #endregion } MemoryStream ms = new MemoryStream(System.Text.Encoding.Default.GetBytes(result.ToString())); ms.Position = 0; return(File(ms, "application/octet-stream ; Charset=UTF8", fileCode + ".sql")); }