strSql = "select * from t_jcj_CJD";
loacalDt = DbService.Instance.QueryLoacal(strSql);
UpLoacaTable("CJDBH", "T_JCJ_CJD", loacalDt, null);
/// <summary>
/// 同步数据表
/// </summary>
/// <param name="keyColumnName">表主键，更新用</param>
/// <param name="tbName">表名</param>
/// <param name="dt">内存数据表</param>
/// <param name="noIncludeCloumns">不需要同步的列 集合</param>
/// <returns></returns>
int UpLoacaTable(string keyColumnName, string tbName, DataTable dt, List<string> noIncludeCloumns)
{
int scjs = 0;
int ret = 0;
if (null == dt || string.IsNullOrEmpty(tbName))
return ret;
bool noInclude = false;
int iNoIncludeCloumnCount = 0;
if (null != noIncludeCloumns && noIncludeCloumns.Count > 0)
iNoIncludeCloumnCount = noIncludeCloumns.Count;
string strfiled = string.Empty, strfields = string.Empty, strInsertValues = string.Empty, strUpdateValues = string.Empty;
try
{
for (int i = 0; i < dt.Columns.Count; i++)
{
strfiled = dt.Columns[i].ColumnName;
#region 检查是列是否需要参与数据处理
noInclude = false;
if (null != noIncludeCloumns)
{
for (int j = 0; j < noIncludeCloumns.Count; j++)
{
if (noIncludeCloumns[j].ToUpper() == strfiled.ToUpper())
{
noInclude = true;
break;
}
}
}
if (noInclude)
continue;
#endregion
strfields += strfiled + ",";
strInsertValues += "?,";
strUpdateValues += strfiled + "=?,";
}
if (strfields != "")
{
strfields = strfields.Substring(0, strfields.LastIndexOf(","));
strInsertValues = strInsertValues.Substring(0, strInsertValues.LastIndexOf(","));
strUpdateValues = strUpdateValues.Substring(0, strUpdateValues.LastIndexOf(","));
}
//组成sql语句
string strInsertSql = string.Empty, strUpdateSql = string.Empty;
strInsertSql = "insert into " + tbName + " ("
+ strfields
+ ") values ("
+ strInsertValues
+ ")";
strUpdateSql = "update " + tbName + " set " + strUpdateValues + " where " + keyColumnName + "=?";
DbParameter[] parametersInsert = new DbParameter[dt.Columns.Count - iNoIncludeCloumnCount];
DbParameter[] parametersUpdate = new DbParameter[dt.Columns.Count - iNoIncludeCloumnCount + 1];
string fieldValue = string.Empty;
string keyColumnValue = string.Empty;
noInclude = false;
for (int r = 0; r < dt.Rows.Count; r++)
{
int columnIndex = 0;
for (int c = 0; c < dt.Columns.Count; c++)
{
strfiled = dt.Columns[c].ColumnName;
#region 检查是列是否需要参与数据处理
noInclude = false;
if (null != noIncludeCloumns)
{
for (int j = 0; j < noIncludeCloumns.Count; j++)
{
if (noIncludeCloumns[j].ToUpper() == strfiled.ToUpper())
{
noInclude = true;
break;
}
}
}
if (noInclude)
continue;
#endregion
fieldValue = dt.Rows[r][c].ToString();
//本地库删除依据值
if (keyColumnName == strfiled)
keyColumnValue = fieldValue;
parametersInsert[columnIndex] = DbService.Instance.CreateParameter(dt.Columns[c].ColumnName, DbType.String, ParameterDirection.Input, fieldValue = string.IsNullOrEmpty(fieldValue) ? "" : fieldValue);
parametersUpdate[columnIndex] = DbService.Instance.CreateParameter(dt.Columns[c].ColumnName, DbType.String, ParameterDirection.Input, fieldValue = string.IsNullOrEmpty(fieldValue) ? "" : fieldValue);
columnIndex++;
}
parametersUpdate[parametersUpdate.Length - 1] = DbService.Instance.CreateParameter(keyColumnName, DbType.String, ParameterDirection.Input, dt.Rows[r][0].ToString());
//查找记录是否存在
string sqlExist = "select " + keyColumnName + " from " + tbName + " where " + keyColumnName + " =?";
DbParameter[] existParameters = new DbParameter[1];
existParameters[0] = DbService.Instance.CreateParameter(keyColumnName, DbType.String, ParameterDirection.Input, dt.Rows[r][0].ToString());
DbDataReader reader = DbService.Instance.Execute(sqlExist, existParameters);
//中心数据库是否存在该条记录
bool bRecExist = false;
bRecExist = (null != reader && reader.HasRows);
if (reader != null) reader.Dispose();//by李平20171016
int iRet = 0;//数据库操作结果
if (!bRecExist)//不存在，写入
{
iRet = DbService.Instance.ExecuteNoQuery(strInsertSql, parametersInsert);
if (iRet==1) scjs++;
}
//by lgh 2016-01-04 不用更新
/*
if (bRecExist)
iRet = DbService.Instance.ExecuteNoQuery(strUpdateSql, parametersUpdate);
*/
if (bRecExist) iRet = 1;
//删除上传的值
if (iRet == 1)
{
string sqlStr = "delete from " + tbName + " where " + keyColumnName + "='' or " + keyColumnName + "=?";
DbParameter[] pmts = new DbParameter[1];
pmts[0] = DbService.Instance.CreateParameter(keyColumnName, DbType.String, ParameterDirection.Input, keyColumnValue);
int delRet = DbService.Instance.ExecuteNoQueryLoacal(sqlStr, pmts);
}
}
return ret;
}
catch
{
return ret;
}
finally
{
LogService.Instance.Info("完成表"+tbName+"数据上传，共上传"+scjs+"条数据");
}
}
/// <summary> 上传本地数据表 </summary>
/// <param name="keyColumnName">关键列</param>
/// <param name="tbName">表名</param>
/// <param name="dt">内在数据表</param>
/// <param name="noIncludeCloumns">不包含列 集合</param>
/// <returns></returns>
int UpLoacaTable(string keyColumnName, string tbName, DataTable dt, List<string> noIncludeCloumns)
{
int scjs = 0; int ret = 0;
if (null == dt || string.IsNullOrEmpty(tbName)) return ret;
bool noInclude = false; int iNoIncludeCloumnCount = 0; if (null != noIncludeCloumns && noIncludeCloumns.Count > 0) iNoIncludeCloumnCount = noIncludeCloumns.Count;
string strfiled = string.Empty, strfields = string.Empty, strInsertValues = string.Empty, strUpdateValues = string.Empty; try
{
for (int i = 0; i < dt.Columns.Count; i++)
{
strfiled = dt.Columns[i].ColumnName;
#region 检查是列是否需要参与数据处理
noInclude = false;
if (null != noIncludeCloumns)
{
for (int j = 0; j < noIncludeCloumns.Count; j++)
{
if (noIncludeCloumns[j].ToUpper() == strfiled.ToUpper())
{
noInclude = true;
break;
}
}
}
if (noInclude)
continue;
#endregion
strfields += strfiled + ","; strInsertValues += "?,"; strUpdateValues += strfiled + "=?,";
}
if (strfields != "")
{
strfields = strfields.Substring(0, strfields.LastIndexOf(","));
strInsertValues = strInsertValues.Substring(0, strInsertValues.LastIndexOf(","));
strUpdateValues = strUpdateValues.Substring(0, strUpdateValues.LastIndexOf(","));
}
//组成sql语句
string strInsertSql = string.Empty, strUpdateSql = string.Empty;
strInsertSql = "insert into " + tbName + " (" + strfields + ") values (" + strInsertValues + ")";
strUpdateSql = "update " + tbName + " set " + strUpdateValues + " where " + keyColumnName + "=?";
DbParameter[] parametersInsert = new DbParameter[dt.Columns.Count - iNoIncludeCloumnCount];
DbParameter[] parametersUpdate = new DbParameter[dt.Columns.Count - iNoIncludeCloumnCount + 1];
string fieldValue = string.Empty; string keyColumnValue = string.Empty;
noInclude = false;
for (int r = 0; r < dt.Rows.Count; r++)
{
int columnIndex = 0; for (int c = 0; c < dt.Columns.Count; c++)
{
strfiled = dt.Columns[c].ColumnName;
#region 检查是列是否需要参与数据处理
noInclude = false;
if (null != noIncludeCloumns)
{
for (int j = 0; j < noIncludeCloumns.Count; j++)
{
if (noIncludeCloumns[j].ToUpper() == strfiled.ToUpper())
{
noInclude = true;
break;
}
}
}
if (noInclude) continue;
#endregion
fieldValue = dt.Rows[r][c].ToString();
//本地库删除依据值
if (keyColumnName == strfiled)
keyColumnValue = fieldValue;
parametersInsert[columnIndex] = DbService.Instance.CreateParameter(dt.Columns[c].ColumnName, DbType.String, ParameterDirection.Input, fieldValue = string.IsNullOrEmpty(fieldValue) ? "" : fieldValue);
parametersUpdate[columnIndex] = DbService.Instance.CreateParameter(dt.Columns[c].ColumnName, DbType.String, ParameterDirection.Input, fieldValue = string.IsNullOrEmpty(fieldValue) ? "" : fieldValue);
columnIndex++;
}
parametersUpdate[parametersUpdate.Length - 1] = DbService.Instance.CreateParameter(keyColumnName, DbType.String, ParameterDirection.Input, dt.Rows[r][0].ToString());
//查找记录是否存在
string sqlExist = "select " + keyColumnName + " from " + tbName + " where " + keyColumnName + " =?";
DbParameter[] existParameters = new DbParameter[1];
existParameters[0] = DbService.Instance.CreateParameter(keyColumnName, DbType.String, ParameterDirection.Input, dt.Rows[r][0].ToString());
DbDataReader reader = DbService.Instance.Execute(sqlExist, existParameters);
//中心数据库是否存在该条记录
bool bRecExist = false;
bRecExist = (null != reader && reader.HasRows);
if (reader != null) reader.Dispose();
int iRet = 0;//数据库操作结果
if (!bRecExist)//不存在，写入
{
iRet = DbService.Instance.ExecuteNoQuery(strInsertSql, parametersInsert);
if (iRet == 1) scjs++;
}
if (bRecExist) iRet = 1;
//删除上传的值
if (iRet == 1)
{
string sqlStr = "delete from " + tbName + " where " + keyColumnName + "='' or " + keyColumnName + "=?";
DbParameter[] pmts = new DbParameter[1];
pmts[0] = DbService.Instance.CreateParameter(keyColumnName, DbType.String, ParameterDirection.Input, keyColumnValue);
int delRet = DbService.Instance.ExecuteNoQueryLoacal(sqlStr, pmts);
}
}
return ret;
}
catch
{
return ret;
}
finally
{
LogService.Instance.Info("完成表" + tbName + "数据上传，共上传" + scjs + "条数据");
}
}
