public static IResponsitryBase <T> CreateDAL <T>(DBType pp) where T : class, new() { IResponsitryBase <T> dal; switch (pp) { case (DBType.Sql): dataAccess = new BaseDAL <T>(); break; case (DBType.Sql2005): dataAccess = new SQL2005Dal <T>(); break; case (DBType.Oracle): dataAccess = new OracleDal <T>(); break; case (DBType.MySql): dataAccess = new MySqlDal <T>(); break; default: dataAccess = new BaseDAL <T>(); break; } }
private void btn0InitFirst_Click(object sender, EventArgs e) { if (_InitFirst.Equals("1")) { if (MessageBox.Show("Notice", "Are you Sure to Update First,That will use some time. Please Wait.", MessageBoxButtons.YesNo) == DialogResult.Yes) { // var tmpcom = " CONTRACT='" + _CONTRACT + "'"; try { _scheduler.Shutdown(); //1table INVENTORY_PART_TAB var tmpsql_INVENTORY_PART_TAB = "select * from INVENTORY_PART_TAB where " + tmpcom; var allCount = OracleDal.GetCount(AutoUpdateData._DBOracle11DBname, "INVENTORY_PART_TAB", tmpsql_INVENTORY_PART_TAB); logger.DebugFormat("*************initFirst INVENTORY_PART_TAB:总有 {0} 条.", allCount); var tmpds_INVENTORY_PART_TAB = OracleDal.Query(tmpsql_INVENTORY_PART_TAB); tmpds_INVENTORY_PART_TAB.DataSetName = "INVENTORY_PART_TAB"; //**************************同步 INVENTORY_PART_TAB OracleDal.StartToMSSQL(true, false, tmpds_INVENTORY_PART_TAB, ""); logger.DebugFormat("*************initFirst INVENTORY_PART_TAB 成功."); // } catch (Exception ex) { logger.Error("**************更新失败:" + ex.Message); } //2 } } }
void initfrm() { this.StartPosition = FormStartPosition.CenterScreen; this.MaximizeBox = false; this.notifyIcon1.ContextMenuStrip = contextMenuStrip1; this.btn0InitFirst.Visible = false; this.btn0InitFirst.Enabled = false; this.TopMost = true; this.btn0Save.Enabled = false; this.groupBox1.Enabled = false; this.lbl0msg.ReadOnly = true; this.lbl0msg.Multiline = true; lbl0msg.WordWrap = true; lbl0msg.ScrollBars = ScrollBars.Both; _tableList = new Dictionary <string, int>(); _tableKeyList = new Dictionary <string, string>(); _dsList = new List <DataSet>(); _isUploading = false; try { _txt0Rtime = Convert.ToInt32(System.Configuration.ConfigurationManager.AppSettings["Common.retime"]); _txt1batchNum = Convert.ToInt32(System.Configuration.ConfigurationManager.AppSettings["Common.batchNum"]); _updatemode = System.Configuration.ConfigurationManager.AppSettings["Common.updateWay"]; _CONTRACT = System.Configuration.ConfigurationManager.AppSettings["CONTRACT"].ToString(); _DBOracle11DBname = System.Configuration.ConfigurationManager.AppSettings["DBOracle11DBname"].ToString(); //_PRIME_COMMODITY = System.Configuration.ConfigurationManager.AppSettings["PRIME_COMMODITY"].ToString(); _N_OBL_PART_TYPE = System.Configuration.ConfigurationManager.AppSettings["N_OBL_PART_TYPE"].ToString(); _TESTFKG = System.Configuration.ConfigurationManager.AppSettings["TESTFKG"].ToString(); _ORG_START_DATE = System.Configuration.ConfigurationManager.AppSettings["ORG_START_DATE"].ToString(); if (string.IsNullOrEmpty(_CONTRACT)) { _CONTRACT = "no CONTRACT,please set,than Run again."; } tInitIni(false); tInitIniToday(DateTime.Now.ToString("yyyyMMdd")); lbl0msg.Text = ""; _tmpFlagMsg = lbl0msg; _ipAddMac = OracleDal.getIp(true); this.Text = "C:[" + _CONTRACT + "], P_C:" + _N_OBL_PART_TYPE + " -->AutoUpdate:" + _ipAddMac; } catch (Exception ex) { logger.Error(ex); } }
internal bool OracleBulkCopyInsert() { CheckGUIDAndDateTime(DataBaseType.Oracle); string conn = ConnBean.Create(_Conn).ConnString; Assembly ass = OracleDal.GetAssembly(); object sbc = ass.CreateInstance("Oracle.DataAccess.Client.OracleBulkCopy", false, BindingFlags.CreateInstance, null, new object[] { conn }, null, null); Type sbcType = sbc.GetType(); try { sbcType.GetProperty("BatchSize").SetValue(sbc, 100000, null); sbcType.GetProperty("BulkCopyTimeout").SetValue(sbc, AppConfig.DB.CommandTimeout, null); sbcType.GetProperty("DestinationTableName").SetValue(sbc, SqlFormat.Keyword(mdt.TableName, DataBaseType.Oracle), null); PropertyInfo cInfo = sbcType.GetProperty("ColumnMappings"); object cObj = cInfo.GetValue(sbc, null); MethodInfo addMethod = cInfo.PropertyType.GetMethods()[4]; foreach (MCellStruct column in mdt.Columns) { addMethod.Invoke(cObj, new object[] { column.ColumnName, column.ColumnName }); } sbcType.GetMethods()[4].Invoke(sbc, new object[] { mdt }); return(true); } catch (Exception err) { if (err.InnerException != null) { err = err.InnerException; } sourceTable.DynamicData = err; Log.Write(err, LogType.DataBase); return(false); } finally { sbcType.GetMethod("Dispose").Invoke(sbc, null); } //using (Oracle.DataAccess.Client.OracleBulkCopy sbc = new OracleBulkCopy(conn, OracleBulkCopyOptions.Default)) //{ // sbc.BatchSize = 100000; // sbc.DestinationTableName = mdt.TableName; // foreach (MCellStruct column in mdt.Columns) // { // sbc.ColumnMappings.Add(column.ColumnName, column.ColumnName); // } // sbc.WriteToServer(mdt); //} //return true; }
/// <summary> /// 获得oracle数据库的所有表结构 /// </summary> /// <param name="postParam"></param> /// <returns></returns> public static List <TableModel> Generation(PostParamModel postParam) { //获取连接字符串 string connectionString = OracleDal.GetConnectionString(postParam); //获取连接 OracleConnection connection = OracleDal.GetConnect(connectionString); //查询sql StringBuilder sql = new StringBuilder("SELECT "); sql.Append("t.table_name AS \"TableName\",f.comments AS \"TableComment\" "); sql.Append("FROM user_tables t "); sql.Append("INNER JOIN user_tab_comments f ON t.table_name = f.table_name "); sql.Append("WHERE t.table_name IN (" + postParam.Tb + ")"); OracleParameter[] tabParams = { new OracleParameter("@table_schema", postParam.Dbname) }; connection.Open(); List <TableModel> tabList = OracleDal.GetQueryData <TableModel>(connection, sql.ToString(), tabParams, typeof(TableModel)); //表的描述 StringBuilder colField = new StringBuilder(); colField.Append("col.column_name AS \"ColumnName\","); colField.Append("col.data_type AS \"ColumnType\","); colField.Append("col.data_default AS \"ColumnDefault\","); colField.Append("col.nullable AS \"IsNullable\","); colField.Append("cns.constraint_type AS \"ColumnKey\","); colField.Append("ucc.comments AS \"ColumnComment\" "); //表的连接 StringBuilder joinTab = new StringBuilder(); joinTab.Append("LEFT JOIN user_col_comments ucc ON ucc.table_name=col.table_name "); joinTab.Append("AND ucc.column_name=col.column_name "); joinTab.Append("LEFT JOIN user_cons_columns ccs ON ccs.table_name=col.table_name "); joinTab.Append("AND ccs.column_name=col.column_name AND ccs.position=col.column_id "); joinTab.Append("LEFT JOIN user_constraints cns ON col.table_name=cns.table_name "); joinTab.Append("AND cns.constraint_type='P' AND ccs.constraint_name=cns.constraint_name "); //表名 string tab = " user_tab_columns col "; string order = " ORDER BY col.column_id ASC "; for (int i = 0; i < tabList.Count; i++) { TableModel table = tabList[i]; //查询sql string sqlCol = string.Format("SELECT {0} FROM {1} {2} WHERE col.table_name='{3}' {4}", colField.ToString(), tab, joinTab.ToString(), table.TableName, order); List <ColumnModel> colList = OracleDal.GetQueryData <ColumnModel>(connection, sqlCol, null, typeof(ColumnModel)); table.Colums = colList; } connection.Close(); return(tabList); }
/// <summary> /// 测试oracle数据库连接 /// </summary> /// <param name="postParam"></param> /// <returns></returns> public static bool TestConnect(PostParamModel postParam) { //获取连接字符串 string connectionString = OracleDal.GetConnectionString(postParam); //获取连接 OracleConnection connection = OracleDal.GetConnect(connectionString); //查询sql string sql = "select count(1) from dual"; connection.Open(); int count = Convert.ToInt32(OracleDal.GetSingle(connection, sql)); connection.Close(); return(count > 0); }
private BaseDal GetDal(string connString) { BaseDal dal = null; if (this.rbtMysql.Checked) { //dal = new MysqlDal(connString); } if (this.rbtOracle.Checked) { dal = new OracleDal(connString); } if (this.rbtSqlServer.Checked) { dal = new SqlServerDal(connString); } return(dal); }
/// <summary> /// 获取oracle所有表名 /// </summary> /// <param name="postParam"></param> /// <returns></returns> public static List <TableModel> GetTables(PostParamModel postParam) { //获取连接字符串 string connectionString = OracleDal.GetConnectionString(postParam); //获取连接 OracleConnection connection = OracleDal.GetConnect(connectionString); //查询sql StringBuilder sql = new StringBuilder("SELECT "); sql.Append("t.table_name AS \"TableName\",f.comments AS \"TableComment\" "); sql.Append("FROM user_tables t "); sql.Append("INNER JOIN user_tab_comments f ON t.table_name = f.table_name "); connection.Open(); List <TableModel> list = OracleDal.GetQueryData <TableModel>(connection, sql.ToString(), null, typeof(TableModel)); connection.Close(); return(list); }
public void Execute(Quartz.IJobExecutionContext context) { if (AutoUpdateData._isUploading) { logger.DebugFormat("***************************Previous job is In Upload. Please wait。。。 {0}", context.PreviousFireTimeUtc.Value.DateTime); AutoUpdateData.jobflag("P Please wait。。。revious job is In Upload:" + context.PreviousFireTimeUtc.Value.DateTime); return; } DataTable tblPicked; BssLineStock bssLineStock = new BssLineStock(); AutoUpdateData._isUploading = true; //get sql update mode //1-删除后再追加 2-直接更新 if (AutoUpdateData._updatemode.StartsWith("1")) { _is1 = true; } else if (AutoUpdateData._updatemode.StartsWith("2")) { _is1 = false; } else { _is1 = true; } logger.DebugFormat("执行更新任务!!!!!!!!!!!!!!!"); AutoUpdateData.jobflag("Is Runing, Next Exec Job Time:" + context.NextFireTimeUtc.Value.DateTime); try { var _tmpOracleDBname = AutoUpdateData._DBOracle11DBname; //init even tInitIniToday AutoUpdateData.tInitIniToday(DateTime.Now.ToString("yyyyMMdd")); //get if (AutoUpdateData._tableList.Count > 0) { logger.Debug("执行数据获取任务!!!!!!!!!!!!!!!"); var tmpBatch = AutoUpdateData._txt1batchNum; AutoUpdateData._dsList.Clear(); var tmpwhereFirst = getInSql(AutoUpdateData._CONTRACT, "CONTRACT", false); if (string.IsNullOrEmpty(tmpwhereFirst)) { return; } var tmpwhere = tmpwhereFirst; foreach (var item in AutoUpdateData._tableList) { try { //init attr _typeOfTable = "0"; _time_start = DateTime.Now.ToString(); _time_done = DateTime.Now.ToString(); _sql = "select * from "; tmpwhere = tmpwhereFirst; if (item.Key.Contains('|')) { var td = item.Key.Split('|'); var tmpTableTakeDataNum = ""; int preNum = 0; var allCount = 0; var tmpKeyname = td[0].Trim() + "_KEY"; var tmpKeyLast = td[0].Trim() + "." + td[1].Trim(); var tmpds = new DataSet(); //father and son var isSon = false; var tmpdsForFatherSon = new DataSet(); tmpds.DataSetName = td[0].Trim(); //test //if (item.Value != 3) //{ // continue; //} switch (item.Value) { case 1: //key: 0 table | where 1 | order by 2 | type 3 var tmpInsql = getInSql(AutoUpdateData._N_OBL_PART_TYPE, td[1].Trim(), true); if (string.IsNullOrEmpty(tmpInsql)) { continue; } else { tmpwhere += tmpInsql; } //pre update number tmpTableTakeDataNum = AutoUpdateData._iniToday.IniReadValue("TableTakeDataNum", td[0].Trim()); if (!int.TryParse(tmpTableTakeDataNum, out preNum)) { preNum = 0; } //get all count form oracle db allCount = OracleDal.GetCount(_tmpOracleDBname, td[0].Trim(), tmpwhere); logger.DebugFormat("*********Table: {0},已上传:{1} ,Oracle 现在有数据:{2}.当日:{3}", td[0], preNum, allCount, DateTime.Now.ToString("yyyyMMdd")); if (preNum >= allCount) { logger.DebugFormat("*********(已上传数) {0} >= {1} (Oracle 现在有数据),无需更新.", preNum, allCount); } else { var tmptoUpdate = (allCount - preNum); logger.DebugFormat("*********需更新数:{0}.", tmptoUpdate); var tmporderby = td[2].Trim(); if (td[2].Trim().ToLower().Equals("no")) { tmporderby = ""; } tmpds = OracleDal.GetData(_tmpOracleDBname, td[0].Trim(), tmpwhere, tmporderby, preNum, tmptoUpdate); } tmpds.DataSetName = td[0].Trim(); _typeOfTable = td[3].Trim(); break; case 2: //key: 0 table | add Id 1 | order by 2 | datefrom 3 | type 4 //get the last ID //get from SQL by id; //var tmpTRANSACTION_ID = AutoUpdateData._iniToday.IniReadValue("TableKeyLastValue", tmpKeyLast); var tmpTRANSACTION_ID = DbHelperSQL.GetDMaxID(td[1], td[0]); // get last where tmpwhere += " and " + td[1].Trim() + ">='" + tmpTRANSACTION_ID + "' "; //pre update number tmpTableTakeDataNum = AutoUpdateData._iniToday.IniReadValue("TableTakeDataNum", td[0].Trim()); if (!int.TryParse(tmpTableTakeDataNum, out preNum)) { preNum = 0; } //get all count form oracle db OracleParameter[] parameters2 = { new OracleParameter(":gxsj", OracleDbType.Varchar2, 10) }; parameters2[0].Value = DateTime.Now.AddDays(-3).ToString("yyyy-MM-dd") + " 00:00:00"; // HH if (tmpTRANSACTION_ID == 1) { logger.DebugFormat("******************************{0} 初始更新,加限时间-3天。", td[0]); _time_start = DateTime.Now.AddDays(-3).ToString("yyyy-MM-dd") + " 00:00:00"; tmpwhere += " and " + td[3] + ">=to_date(:gxsj,'yyyy-MM-dd HH24:mi:ss')"; allCount = OracleDal.GetCount(_tmpOracleDBname, td[0].Trim(), tmpwhere, parameters2); } else { allCount = OracleDal.GetCount(_tmpOracleDBname, td[0].Trim(), tmpwhere); } logger.DebugFormat("*********Table: {0},已上传:{1} ,Oracle 现在有数据:{2}.当日:{3}", td[0], preNum, allCount, DateTime.Now.ToString("yyyyMMdd")); if (preNum >= allCount) { logger.DebugFormat("*********(已上传数) {0} >= {1} (Oracle 现在有数据),无需更新.", preNum, allCount); } else { var tmptoUpdate = (allCount - preNum); logger.DebugFormat("*********需更新数:{0}.", tmptoUpdate); var tmporderby = td[2].Trim(); if (td[2].Trim().ToLower().Equals("no")) { tmporderby = ""; } if (tmpTRANSACTION_ID == 1) { tmpds = OracleDal.GetData(_tmpOracleDBname, td[0].Trim(), tmpwhere, tmporderby, preNum, tmptoUpdate, parameters2); } else { tmpds = OracleDal.GetData(_tmpOracleDBname, td[0].Trim(), tmpwhere, tmporderby, preNum, tmptoUpdate); } } tmpds.DataSetName = td[0].Trim(); _typeOfTable = td[4].Trim(); _time_done = OracleDal.getMaxCol(tmpds, td[3]).ToString(); break; case 3: //key: 0 table|1 where|2 order by | type 3 //get per last datetime var tmpLastWhere = AutoUpdateData._iniToday.IniReadValue("TableKeyLastValue", tmpKeyLast); var tmpLastWhereDateTime = DateTime.Now; if (!DateTime.TryParse(tmpLastWhere, out tmpLastWhereDateTime)) { tmpLastWhereDateTime = DateTime.Now; AutoUpdateData._iniToday.IniWriteValue("TableKeyLastValue", tmpKeyLast, tmpLastWhereDateTime.ToString()); } tmpwhere += " and to_char(" + td[1] + ", 'yyyymmddHH24miss') >= :gxsj"; OracleParameter[] parameters3 = { new OracleParameter(":gxsj", OracleDbType.Varchar2, 10) }; //no time parameters3[0].Value = DateTime.Now.ToString("yyyyMMdd000000"); //for N_AIS_SHOP_LIST_PICKED_ACT_TAB var tmpnewDateTime = DateTime.Now.ToString("yyyyMMdd000000"); if (td[0].Trim().ToUpper().Equals("N_AIS_SHOP_LIST_PICKED_ACT_TAB".ToUpper())) { tmpnewDateTime = DbHelperSQL.GetTableFieldDateTime("AISPICK_UPD_DATE", "M_CONTOROL", " [KEY-ID]='01'"); parameters3[0].Value = tmpnewDateTime; logger.DebugFormat("**************{0},{1} change to new value:{2}.", td[0], td[1], tmpnewDateTime); } //pre update number tmpTableTakeDataNum = AutoUpdateData._iniToday.IniReadValue("TableTakeDataNum", td[0].Trim()); if (!int.TryParse(tmpTableTakeDataNum, out preNum)) { preNum = 0; } allCount = OracleDal.GetCount(_tmpOracleDBname, td[0].Trim(), tmpwhere, parameters3); logger.DebugFormat("*********Table: {0},已上传:{1} ,Oracle 现在有数据:{2}.当日:{3}", td[0], preNum, allCount, DateTime.Now.ToString("yyyyMMdd")); if (preNum >= allCount) { logger.DebugFormat("*********(已上传数) {0} >= {1} (Oracle 现在有数据),无需更新.", preNum, allCount); } else { var tmptoUpdate = (allCount - preNum); logger.DebugFormat("*********需更新数:{0}.", tmptoUpdate); var tmporderby = td[2].Trim(); if (td[2].Trim().ToLower().Equals("no")) { tmporderby = ""; } tmpds = OracleDal.GetData(_tmpOracleDBname, td[0].Trim(), tmpwhere, tmporderby, preNum, tmptoUpdate, parameters3); } tmpds.DataSetName = td[0].Trim(); _typeOfTable = td[3].Trim(); _time_done = OracleDal.getMaxCol(tmpds, td[1]).ToString(); #region new update 2015-12-02 if (tmpds.Tables.Count > 0 && td[0].Trim().ToUpper().Equals("N_AIS_SHOP_LIST_PICKED_ACT_TAB".ToUpper())) { logger.DebugFormat("*******开始更新{0}的相关表[M_PARTS_STOCK,M_LINE_PARTS_STOCK]", td[0].Trim()); //AIS配膳リストピッキング実績-> AIS配膳リストピッキング実績 tblPicked = tmpds.Tables[0]; // bssLineStock.GetPickedActData(); //更新ライン在庫マス数据 if (bssLineStock.SetLinePartsStockData(tblPicked) <= 0) { //TODO: 更新失败 logger.ErrorFormat("******* {0} 的相关表[M_PARTS_STOCK,M_LINE_PARTS_STOCK] 更新失败", td[0].Trim()); } else { logger.DebugFormat("******* {0} 的相关表[M_PARTS_STOCK,M_LINE_PARTS_STOCK] 更新Success", td[0].Trim()); var tmpExist = DbHelperSQL.GetCount("M_CONTOROL", "[KEY-ID]='01'"); var tmpsql = ""; if (tmpExist > 0) { tmpsql = string.Format("UPDATE M_CONTOROL set [AISPICK_UPD_DATE]='{0}' where [KEY-ID]='01'", _time_done); } else { tmpsql = string.Format("INSERT INTO M_CONTOROL([KEY-ID],[AISPICK_UPD_DATE]) VALUES('{0}','{1}')", "01", _time_done); } var tmpdd = DbHelperSQL.ExecuteSql(tmpsql); if (tmpdd > 0) { logger.DebugFormat("*******Success M_CONTOROL 更新时间 AISPICK_UPD_DATE success", td[0].Trim()); } else { logger.DebugFormat("*******Error M_CONTOROL 更新时间 AISPICK_UPD_DATE Error", td[0].Trim()); } } } #endregion break; case 4: //key: P 0|where 1| order by 2 | C 3 | type 4 //get P:父,C: 子 根据P的Key得到C.的数据。 //get the last ID // set tmpwhere tmpwhere += " and " + td[1] + ">=to_date(:gxsj,'yyyy-MM-dd HH24:mi:ss')"; OracleParameter[] parameters4 = { new OracleParameter(":gxsj", OracleDbType.Varchar2, 10) }; //set time parameters4[0].Value = DateTime.Now.ToString("yyyy-MM-dd") + " 00:00:00"; // HH logger.DebugFormat("****TESTFKG:{0},ORG_START_DATE:{1} 【0:の場合 PCの日付を抽出条件にする。1:の場合 環境ファイル内の日付(yyyy-mm-dd)を設定し、その日付を抽出条件にする】", AutoUpdateData._TESTFKG, AutoUpdateData._ORG_START_DATE); if (AutoUpdateData._TESTFKG.Equals("1")) { logger.DebugFormat("****使用配置文件中的日期。{0}", AutoUpdateData._ORG_START_DATE); parameters4[0].Value = AutoUpdateData._ORG_START_DATE + " 00:00:00"; // HH } var trytmpDD = DateTime.Now; var chedkdate = DateTime.TryParse(parameters4[0].Value.ToString(), out trytmpDD); if (!chedkdate) { logger.DebugFormat("**配置文件提供ORG_START_DATE的值不符合要求(yyyy-MM-dd),value:{0}.则使用当天日期。", AutoUpdateData._ORG_START_DATE, DateTime.Now.ToString("yyyy-MM-dd")); logger.ErrorFormat("**配置文件提供ORG_START_DATE的值不符合要求(yyyy-MM-dd),value:{0}.则使用当天日期。", AutoUpdateData._ORG_START_DATE, DateTime.Now.ToString("yyyy-MM-dd")); parameters4[0].Value = DateTime.Now.ToString("yyyy-MM-dd") + " 00:00:00"; // HH } // parameters4[0].Value = tmpORG_START_DATE.ToString("yyyy-MM-dd") + " 00:00:00";// HH //pre update number tmpTableTakeDataNum = AutoUpdateData._iniToday.IniReadValue("TableTakeDataNum", td[0].Trim()); if (!int.TryParse(tmpTableTakeDataNum, out preNum)) { preNum = 0; } //get all count form oracle db allCount = OracleDal.GetCount(_tmpOracleDBname, td[0].Trim(), tmpwhere, parameters4); logger.DebugFormat("*********Table: {0},已上传:{1} ,Oracle 现在有数据:{2}.>=条件日期:{3}", td[0], preNum, allCount, trytmpDD.ToString("yyyy-MM-dd")); if (preNum >= allCount) { logger.DebugFormat("*********(已上传数) {0} >= {1} (Oracle 现在有数据),无需更新.", preNum, allCount); } else { var tmptoUpdate = (allCount - preNum); logger.DebugFormat("*********需更新数:{0}.", tmptoUpdate); var tmporderby = td[2].Trim(); if (td[2].Trim().ToLower().Equals("no")) { tmporderby = ""; } tmpds = OracleDal.GetData(_tmpOracleDBname, td[0].Trim(), tmpwhere, tmporderby, preNum, tmptoUpdate, parameters4); } tmpds.DataSetName = td[0].Trim(); //to Get C if (!AutoUpdateData._tableKeyList.ContainsKey(tmpKeyname)) { isSon = false; logger.DebugFormat("*************************表:{0} 没有设置主键,无法更新子表:{1}。", td[0], td[3]); } else { //var tmpkeyValue = AutoUpdateData._tableKeyList[tmpKeyname]; //var tmpkeys = tmpkeyValue.Split(','); //logger.DebugFormat("*************************表:{0} 的主键:{1}。", td[0], tmpkeyValue); isSon = true; } tmpds.DataSetName = td[0].Trim(); _typeOfTable = td[4].Trim(); _time_done = OracleDal.getMaxCol(tmpds, td[1]).ToString(); break; default: tmpds.DataSetName = td[0].Trim(); if (td[0].IndexOf('.') > 0) { tmpds.DataSetName = td[0].Trim().Split('.')[1]; } break; } _sql += td[0].Trim() + " where " + tmpwhere; //**************************同步表 OracleDal.StartToMSSQL(_is1, false, tmpds, tmpKeyLast); //for father and son if (isSon) { var tmpallSonCount = 0; if (tmpds.Tables.Count > 0) { var tmpkeyValue = AutoUpdateData._tableKeyList[tmpKeyname]; var tmpkeys = tmpkeyValue.Split(','); if (tmpkeys.Count() <= 0) { logger.DebugFormat("*************************表:{0} 的主键 为空:{1}。", td[0], tmpkeyValue); } else { logger.DebugFormat("*************************表:{0} 的主键:{1}。", td[0], tmpkeyValue); logger.DebugFormat("*************************开始更新表:{0},子表:{1}。", td[0], td[3]); string sonTmpsonwhereAll = ""; foreach (DataRow p in tmpds.Tables[0].Rows) { string sonTmpsonwhere = ""; try { var tmpsonwhere = ""; tmpsonwhere = "SELECT * FROM " + td[3].Trim() + " where "; if (!string.IsNullOrEmpty(_tmpOracleDBname)) { tmpsonwhere = "SELECT * FROM " + _tmpOracleDBname + "." + td[3].Trim() + " where "; } if (tmpkeys.Count() > 0) { if (tmpkeys.Count() == 1) { tmpsonwhere += tmpkeys[0] + "='" + p[tmpkeys[0]].ToString().Trim() + "'"; } else { for (int i = 0; i < tmpkeys.Count(); i++) { if (i == 0) { tmpsonwhere += tmpkeys[0] + "='" + p[tmpkeys[0]].ToString().Trim() + "'"; } else { tmpsonwhere += " and " + tmpkeys[i] + "='" + p[tmpkeys[i]].ToString().Trim() + "'"; } } } } // sonTmpsonwhere = tmpsonwhere; sonTmpsonwhereAll += sonTmpsonwhere + "\n"; //get data var tmpSon = OracleDal.Query(tmpsonwhere); tmpSon.DataSetName = td[3].Trim(); //**************************同步子表 tmpallSonCount += OracleDal.StartToMSSQL(_is1, true, tmpSon, ""); } catch (Exception ex) { logger.ErrorFormat("****************************更新主表:{0},记录:{1} -->的子表失败。{2}", td[0], (p[0].ToString() + "," + p[1].ToString() + "," + p[2].ToString()), ex.Message); //OracleDal.ilog(td[3].Trim(), allCount, AutoUpdateData._CONTRACT + ",Fail," + AutoUpdateData._updatemode, "AutoUpdateOracleMSSQL: SQL:" + sonTmpsonwhere + " Fail. Error:" + ex.Message, AutoUpdateData._ipAddMac); OracleDal.ilog("error", updateJob._typeOfTable, updateJob._time_start, updateJob._time_done, sonTmpsonwhere, allCount, AutoUpdateData._ipAddMac, AutoUpdateData._CONTRACT + "|" + AutoUpdateData._updatemode); continue; } } //OracleDal.ilog(td[3].Trim(), tmpallSonCount, AutoUpdateData._CONTRACT + ",Success," + AutoUpdateData._updatemode, "AutoUpdateOracleMSSQL:Update Count:" + tmpallSonCount + " Success.", AutoUpdateData._ipAddMac); OracleDal.ilog("success", updateJob._typeOfTable, updateJob._time_start, updateJob._time_done, sonTmpsonwhereAll, tmpallSonCount, AutoUpdateData._ipAddMac, AutoUpdateData._CONTRACT + "|" + AutoUpdateData._updatemode); } } else { logger.DebugFormat("*************************表:{0} 的数据 为空。", td[0]); } } } else { var tmpTableTakeDataNum = AutoUpdateData._iniToday.IniReadValue("TableTakeDataNum", item.Key.Trim()); int preNum = 0; if (!int.TryParse(tmpTableTakeDataNum, out preNum)) { preNum = 0; } var allCount = OracleDal.GetCount(_tmpOracleDBname, item.Key, ""); logger.DebugFormat("*********Table: {0},已上传:{1} ,Oracle 现在有数据:{2}.", item.Key, preNum, allCount); if (preNum >= allCount) { logger.DebugFormat("*********(已上传数) {0} >= {1} (Oracle 现在有数据),无需更新.", preNum, allCount); } else { var tmptoUpdate = (allCount - preNum); logger.DebugFormat("*********需更新数:{0}", tmptoUpdate); var tmpds = OracleDal.GetData(_tmpOracleDBname, item.Key, "", "", preNum, tmptoUpdate); tmpds.DataSetName = item.Key; OracleDal.StartToMSSQL(_is1, false, tmpds, ""); } } } catch (Exception ex) { AutoUpdateData.jobflag("Error:" + ex.Message); logger.ErrorFormat("{0}表同步有问题,开始同步下个表。Error:{1}.", item.Key, ex.Message); continue; } } } else { logger.Error("no Table,Please check Set.ini,and add Table."); } } catch (Exception ex) { AutoUpdateData.jobflag("Error:" + ex.Message); logger.Error(ex); } finally { AutoUpdateData._isUploading = false; } AutoUpdateData.jobflag("Notice: Current Job is Run Over, Next Exec Job Time:" + context.NextFireTimeUtc.Value.DateTime); }
/// <summary> /// 导出 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btnBuild_Click(object sender, EventArgs e) { _savePath = this.tbSavePath.Text; if (_savePath.IsNullOrWhiteSpace()) { this.ShowWarningDialog("没有设置导出目录"); return; } var templatePath = this.cbbTemplate.SelectedValue?.ToString(); if (templatePath.IsNullOrWhiteSpace()) { this.ShowWarningDialog("没有选择模板"); return; } var configIndex = this.cbbConfig.SelectedIndex; if (configIndex < 0) { this.ShowWarningDialog("没有选择配置"); return; } var configModel = jsonUtil.Items[configIndex]; var nameSpace = this.tbNameSapce.Text; if (nameSpace.IsNullOrWhiteSpace()) { this.ShowWarningDialog("没有添加命名空间"); return; } IDbDal dal = null; if (configModel.ConnType == ((int)DbBuildEntity.Util.Enums.DbType.SqlServer).ToString()) { dal = new SqlServerDal(); } else if (configModel.ConnType == ((int)DbBuildEntity.Util.Enums.DbType.Oracle).ToString()) { dal = new OracleDal(); } else if (configModel.ConnType == ((int)DbBuildEntity.Util.Enums.DbType.MySQL).ToString()) { } else if (configModel.ConnType == ((int)DbBuildEntity.Util.Enums.DbType.SQlite).ToString()) { dal = new SqliteDal(); } var tables = dal.GetTables(configModel.ConnString); int index = 0; if (Path.GetFileNameWithoutExtension(templatePath) != "实体枚举模板") { ShowStatus("提示", "导出实体中......", tables.Count, 0); foreach (var table in tables) { //StatusDescription = "导出实体中(" + index + "%)......"; StatusStepIt(); var columnsList = dal.GetColumnList(table.TableName, configModel.ConnString); foreach (var column in columnsList) { if (column.TypeName.Contains("varchar") || column.TypeName.Contains("VARCHAR")) { column.TypeName = "string"; } string value = string.Empty; var dResult = dbTypeDic.TryGetValue(column.TypeName.ToUpper(), out value); if (dResult) { column.TypeName = value; } else { column.TypeName = "object"; } } //var buildContent = RazorBuildUtil.GetBuildContent(templatePath, columnsList, table); var buildContent = RazorBuildUtil.GetBuildContentNew(templatePath, table.TableName, columnsList, table, nameSpace); FileUtil.Save(buildContent, Path.Combine(_savePath, $"{table.TableName}.cs")); index++; } } else { ShowStatus("提示", "加载数据中......", tables.Count, 0); List <TableModels> list = new List <TableModels>(); TableModels model = null; foreach (var table in tables) { //StatusDescription = "加载数据中(" + index + "%)......"; StatusStepIt(); var columnsList = dal.GetColumnList(table.TableName, configModel.ConnString); model = new TableModels(); model.Columns = columnsList; model.TableName = table.TableName; model.Description = table.Description; list.Add(model); index++; } var buildContent = RazorBuildUtil.GetBuildContent_Enum(templatePath, "TableNames", list, nameSpace, "TableNames"); FileUtil.Save(buildContent, Path.Combine(_savePath, "TableNames.cs")); } ConfigUtil.SetAppSettingValue("DefaultPath", _savePath, Global.nameSpaceConfigFullPath); UIMessageTip.ShowOk("导出成功", 2000, true); }