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);
}
