/// <summary>
/// Update Mode： 1-Deleted First,Then Adding
/// </summary>
/// <param name="_is1"></param>
/// <param name="strSQLinsert"></param>
/// <param name="tmpcolDS"></param>
/// <param name="item"></param>
/// <param name="row"></param>
public static void updateMode(bool isOne, List <String> strSQLinsert, DataSet tmpcolDS, DataSet item, DataRow row)
{
//get all key for table
var tmpKeyname = item.DataSetName + "_KEY";
var tmpExistForSQL = "";
var tmpDeleteForSQL = "";
var tmpUpdateForSQL = "";
var tmpInsertForSQL = "";
try
{
if (!AutoUpdateData._tableKeyList.ContainsKey(tmpKeyname))
{
logger.DebugFormat("1*****************表：{0},无主键，无法：1-删除后再追加。", item.DataSetName);
}
else
{
var tmpkeyValue = AutoUpdateData._tableKeyList[tmpKeyname];
var tmpkeys = tmpkeyValue.Split(',');
if (tmpkeys.Count() <= 0)
{
logger.DebugFormat("2*****************表：{0},无主键，无法：1-删除后再追加。", item.DataSetName);
}
else
{
tmpExistForSQL = OracleDal.getSQLColumnsForDeleteByKeys(true, tmpcolDS, item.DataSetName, tmpkeys, row);
tmpDeleteForSQL = OracleDal.getSQLColumnsForDeleteByKeys(false, tmpcolDS, item.DataSetName, tmpkeys, row);
tmpUpdateForSQL = OracleDal.getSQLColumnsForUpdateByKeys(tmpcolDS, item.DataSetName, tmpkeys, row);
}
}
//update mode 1 or 2
if (isOne)
{
// for mode 1
if (!string.IsNullOrEmpty(tmpDeleteForSQL))
{
strSQLinsert.Add(tmpDeleteForSQL);
}
//for insert
//gen sql by batch
tmpInsertForSQL = OracleDal.getSQLColumnsForInsert(tmpcolDS, item.DataSetName, row);
if (!string.IsNullOrEmpty(tmpInsertForSQL))
{
strSQLinsert.Add(tmpInsertForSQL);
}
}
else
{
var tmpExitThis = DbHelperSQL.Exists(tmpExistForSQL);
// exit
if (tmpExitThis)
{
//for mode 2
if (!string.IsNullOrEmpty(tmpUpdateForSQL))
{
strSQLinsert.Add(tmpUpdateForSQL);
}
}
else
{
//for insert
//gen sql by batch
tmpInsertForSQL = OracleDal.getSQLColumnsForInsert(tmpcolDS, item.DataSetName, row);
if (!string.IsNullOrEmpty(tmpInsertForSQL))
{
strSQLinsert.Add(tmpInsertForSQL);
}
}
}
}
catch (Exception ex)
{
throw new Exception(ex.Message + ",SQL:" + tmpExistForSQL + "," + tmpDeleteForSQL + "," + tmpUpdateForSQL + "," + tmpInsertForSQL);
}
}
/// <summary>
///
/// </summary>
/// <param name="is1"></param>
/// <param name="item"></param>
/// <param name="setLastValue"></param>
public static int StartToMSSQL(bool is1, bool isSon, DataSet item, string setLastValue)
{
var allExecCount = 0;
if (item.Tables.Count <= 0)
{
logger.DebugFormat("#####################################开始同步表：{0},表中无任何记录。", item.DataSetName);
return(allExecCount);
}
logger.DebugFormat("#####################################开始同步表：{0}，更新条数为：{1}。更新方式：{2}", item.DataSetName, item.Tables[0].Rows.Count, AutoUpdateData._updatemode);
//get Allcount
var allCount = item.Tables[0].Rows.Count;
var strSQLinsert = new List <String>();
var strSQLinsertAll = new StringBuilder();
try
{
//get colmuns
var tmpcolDS = OracleDal.GetTableColumns(AutoUpdateData._DBOracle11DBname, item.DataSetName);
//gen sql
if (allCount <= AutoUpdateData._txt1batchNum)
{
//init str
foreach (DataRow row in item.Tables[0].Rows)
{
//update mode 1:d
updateMode(is1, strSQLinsert, tmpcolDS, item, row);
}
//foreach (var aitem in strSQLinsert)
//{
// strSQLinsertAll.AppendLine(aitem);
//}
//upload to mssql
allExecCount = updateToMSSQL(is1, item, strSQLinsert, setLastValue, "Upload ");
}
else
{
var nextdiffCount = 0;
var tmpCount = 1;
do
{
//init str
strSQLinsert = new List <String>();
for (int i = nextdiffCount; i < allCount; i++)
{
nextdiffCount++;
//update mode and gen SQL
updateMode(is1, strSQLinsert, tmpcolDS, item, item.Tables[0].Rows[i]);
if (nextdiffCount % AutoUpdateData._txt1batchNum == 0)
{
break;
}
}
//foreach (var aitem in strSQLinsert)
//{
// strSQLinsertAll.AppendLine(aitem);
//}
//upload to mssql
allExecCount += updateToMSSQL(is1, item, strSQLinsert, setLastValue, "Upload ");
tmpCount++;
} while (nextdiffCount < allCount);
}
if (!isSon)
{
//strSQLinsertAll.ToString()
ilog("success", updateJob._typeOfTable, updateJob._time_start, updateJob._time_done, updateJob._sql, allCount, AutoUpdateData._ipAddMac, AutoUpdateData._CONTRACT + "|" + AutoUpdateData._updatemode);
}
return(allExecCount);
}
catch (Exception ex)
{
if (!isSon)
{
ilog("error", updateJob._typeOfTable, updateJob._time_start, updateJob._time_done, updateJob._sql, allCount, AutoUpdateData._ipAddMac, AutoUpdateData._CONTRACT + "|" + AutoUpdateData._updatemode);
}
logger.ErrorFormat("*************{0}:更新出现问题，继续同步下个表.error：{1}", item.DataSetName, ex.Message);
return(0);
}
}
