/// <summary>
/// 检测数据是否合法
/// </summary>
/// <param name="csvDataDR"></param>
/// <param name="tableName"></param>
/// <param name="fromCsvItemsDT"></param>
/// <param name="fromDefaulValueItemsDT"></param>
/// <param name="rowNo"></param>
/// <param name="listErrInfo"></param>
/// <param name="msg"></param>zzzz
/// <returns></returns>
public static bool CheckRowData(DataRow csvDataDR, string tableName, DataTable fromCsvItemsDT, DataTable fromDefaulValueItemsDT, string rowNo, ref List <ErrInfo> listErrInfo, ref string msg)
{
bool retVal = true;
#region 数据重复Check
DataTable dt = new ImportItemsRule().GetImportItemsDtbyPk(fromCsvItemsDT.Rows[0]["ObjName"].ToString());
string sql = string.Format("select * from {0} where ", tableName);
bool sqlError = false;
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
if (dr["ColType"].ToString().Equals(((int)PageConst.ImportItemTypeEnum.FromImportFile).ToString()))
{
//导入文件中的数据
sql += string.Format("{0}='{1}' and ", dr["xColumn"].ToString().Trim(), csvDataDR[dr["CName"].ToString().Trim()]);
}
else
{
//默认值数据
string pDataSource = new ReplaceParaHelper.ImportCsvReplaceParameter().ReplaceSqlValue(dr["DataSource"].ToString().Replace("\"", "'"), csvDataDR);
try
{
DataTable pDT = DBAccess.DbHelperSQL.QueryBySQL(pDataSource);
if (pDT.Rows.Count > 0)
{
sql += string.Format("{0}='{1}' and ", dr["xColumn"].ToString().Trim(), pDT.Rows[0][0].ToString().Trim());
}
else
{
sqlError = true;
}
}
catch (Exception ex)
{
sqlError = true;
msg = ex.Message;
}
}
}
if (sqlError == false)
{
dt = DBAccess.DbHelperSQL.QueryBySQL(sql.Trim().Substring(0, sql.Length - 4));
if (dt.Rows.Count > 0)
{
var paras = new List <string>(1);
//主键重复
var er = new ErrInfo
{
ErrRowNo = rowNo,
ErrType = PageConst.ImportDataErrorType.DataHasExist,
ErrMsg = GetMessage("700002", paras),
ErrData = GetImportFileRowData(csvDataDR)
};
listErrInfo.Add(er);
return(false);
}
}
else
{
//SQL执行错误
var paras = new List <string>(1);
var er = new ErrInfo
{
ErrRowNo = rowNo,
ErrType = PageConst.ImportDataErrorType.DataIsValid,
ErrMsg = GetMessage("700010", paras),
ErrData = GetImportFileRowData(csvDataDR)
};
listErrInfo.Add(er);
retVal = false;
}
}
#endregion
//检测导入文件中的字段是否符合规范
foreach (DataRow dr in fromCsvItemsDT.Rows)
{
#region 模板文件中不存在该列
if (!CheckItemExist(dr["CName"].ToString(), csvDataDR))
{
var paras = new List <string>(1)
{
dr["CName"].ToString()
};
var er = new ErrInfo
{
ErrRowNo = rowNo,
ErrType = PageConst.ImportDataErrorType.ItemNotExist,
ErrMsg = GetMessage("700011", paras),
ErrData = dr["CName"].ToString()
};
listErrInfo.Add(er);
return(false);
}
#endregion
#region 找不到符合条件的数据，一般出现在数据替换的时候
if (!string.IsNullOrEmpty(dr["DataSource"].ToString()))
{
string pDataSource = new ReplaceParaHelper.ImportCsvReplaceParameter().ReplaceSqlValue(dr["DataSource"].ToString().Replace("\"", "'"), csvDataDR);
DataTable dt2 = DBAccess.DbHelperSQL.QueryBySQL(pDataSource);
if (dt2.Rows.Count <= 0)
{
var paras = new List <string>(2)
{
csvDataDR[dr["CName"].ToString()].ToString(),
dr["CName"].ToString()
};
var er = new ErrInfo
{
ErrRowNo = rowNo,
ErrType = PageConst.ImportDataErrorType.KeyDataNotExist,
ErrMsg = GetMessage("700012", paras),
ErrData = csvDataDR[dr["CName"].ToString()].ToString()
};
listErrInfo.Add(er);
}
}
#endregion
#region 检测数据类型是否正确
if (dr["DataType"].ToString().ToLower().StartsWith("varchar") ||
dr["DataType"].ToString().ToLower().StartsWith("char") ||
dr["DataType"].ToString().ToLower().StartsWith("nvarchar"))
{
//获取长度
int left = dr["DataType"].ToString().ToLower().IndexOf('(') + 1;
int right = dr["DataType"].ToString().ToLower().IndexOf(')');
int length = Convert.ToInt32(dr["DataType"].ToString().ToLower().Substring(left, right - left));
#region Check字符窜长度是否超出范围
if (PageHelper.CheckCharIsLong(csvDataDR[dr["CName"].ToString()].ToString(), length))
{
var paras = new List <string>(2)
{
dr["CName"].ToString(), length.ToString()
};
int halfLength = length / 2;
paras.Add(halfLength.ToString());
var er = new ErrInfo
{
ErrRowNo = rowNo,
ErrType = PageConst.ImportDataErrorType.DataIsTooLarge,
ErrMsg = GetMessage("700004", paras),
ErrData = csvDataDR[dr["CName"].ToString()].ToString()
};
listErrInfo.Add(er);
retVal = false;
}
#endregion
}
else if (dr["DataType"].ToString().ToLower().StartsWith("int"))
{
#region Check整数
if (!PageHelper.CheckIsInteger(csvDataDR[dr["CName"].ToString()].ToString()))
{
//不是整数时
var paras = new List <string>(1)
{
dr["CName"].ToString()
};
var er = new ErrInfo
{
ErrRowNo = rowNo,
ErrType = PageConst.ImportDataErrorType.DataTypeNotMatch,
ErrMsg = GetMessage("700005", paras),
ErrData = csvDataDR[dr["CName"].ToString()].ToString()
};
listErrInfo.Add(er);
retVal = false;
}
#endregion
}
else if (dr["DataType"].ToString().ToLower().StartsWith("decimal"))
{
//Check高精度浮点数
string subStr = dr["DataType"].ToString().ToLower().Substring(dr["DataType"].ToString().ToLower().IndexOf("(") + 1).TrimEnd(')');
string[] strs = subStr.Split(',');
int decimalPart = Convert.ToInt32(strs[1]);
int integerPart = Convert.ToInt32(strs[0]) - decimalPart;
#region Check是否为Decimal类型
if (!PageHelper.CheckIsDecimal(csvDataDR[dr["CName"].ToString()].ToString()))
{
//不是高精度浮点数类型
var paras = new List <string>(1)
{
dr["CName"].ToString()
};
var er = new ErrInfo
{
ErrRowNo = rowNo,
ErrType = PageConst.ImportDataErrorType.DataTypeNotMatch,
ErrMsg = GetMessage("700007", paras),
ErrData = csvDataDR[dr["CName"].ToString()].ToString()
};
listErrInfo.Add(er);
retVal = false;
}
#endregion
#region Check高精度型的位数是否正确
else
{
//是高精度浮点数，但超出范围
if (PageHelper.CheckDecimalIsBigger(csvDataDR[dr["CName"].ToString()].ToString(), integerPart, decimalPart))
{
//不是高精度浮点数类型
var paras = new List <string>(3)
{
dr["CName"].ToString(),
integerPart.ToString(),
decimalPart.ToString()
};
var er = new ErrInfo
{
ErrRowNo = rowNo,
ErrType = PageConst.ImportDataErrorType.DataTypeNotMatch,
ErrMsg = GetMessage("700008", paras),
ErrData = csvDataDR[dr["CName"].ToString()].ToString()
};
listErrInfo.Add(er);
retVal = false;
}
}
#endregion
}
else if (dr["DataType"].ToString().ToLower().StartsWith("datetime"))
{
#region Check日期格式
if (!PageHelper.CheckIsDateTime(csvDataDR[dr["CName"].ToString()].ToString()))
{
var paras = new List <string>(1)
{
dr["CName"].ToString()
};
var er = new ErrInfo
{
ErrRowNo = rowNo,
ErrType = PageConst.ImportDataErrorType.DataTypeNotMatch,
ErrMsg = GetMessage("700009", paras),
ErrData = csvDataDR[dr["CName"].ToString()].ToString()
};
listErrInfo.Add(er);
retVal = false;
}
#endregion
}
#endregion
#region 检测是否符合某些验证规范
if (!String.IsNullOrEmpty(dr["CheckMethod"].ToString()))
{
string[] methods = dr["CheckMethod"].ToString().Split(';');
bool hasChecked = false;
foreach (string method in methods)
{
if (hasChecked)
{
break;
}
switch (method)
{
#region 检测是否不为空
case "CheckIsNotEmpty":
if (!PageHelper.CheckIsNotEmpty(csvDataDR[dr["CName"].ToString()].ToString()))
{
var paras = new List <string>(1)
{
dr["CName"].ToString()
};
var er = new ErrInfo
{
ErrRowNo = rowNo,
ErrType = PageConst.ImportDataErrorType.DataTypeNotMatch,
ErrMsg = GetMessage("700014", paras),
ErrData = csvDataDR[dr["CName"].ToString()].ToString()
};
listErrInfo.Add(er);
retVal = false;
hasChecked = true;
}
break;
#endregion
#region 检测是否为整数
case "CheckIsInteger":
if (!PageHelper.CheckIsInteger(csvDataDR[dr["CName"].ToString()].ToString()))
{
var paras = new List <string>(1)
{
dr["CName"].ToString()
};
var er = new ErrInfo
{
ErrRowNo = rowNo,
ErrType = PageConst.ImportDataErrorType.DataTypeNotMatch,
ErrMsg = GetMessage("700005", paras),
ErrData = csvDataDR[dr["CName"].ToString()].ToString()
};
listErrInfo.Add(er);
retVal = false;
hasChecked = true;
}
break;
#endregion
#region 检测是否为数字类型
case "CheckIsDecimal":
if (!PageHelper.CheckIsDecimal(csvDataDR[dr["CName"].ToString()].ToString()))
{
var paras = new List <string>(1)
{
dr["CName"].ToString()
};
var er = new ErrInfo
{
ErrRowNo = rowNo,
ErrType = PageConst.ImportDataErrorType.DataTypeNotMatch,
ErrMsg = GetMessage("700007", paras),
ErrData = csvDataDR[dr["CName"].ToString()].ToString()
};
listErrInfo.Add(er);
retVal = false;
hasChecked = true;
}
break;
#endregion
#region 检测是否为字符类型
case "CheckIsChar":
if (!PageHelper.CheckIsChar(csvDataDR[dr["CName"].ToString()].ToString()))
{
var paras = new List <string>(1)
{
dr["CName"].ToString()
};
var er = new ErrInfo
{
ErrRowNo = rowNo,
ErrType = PageConst.ImportDataErrorType.DataTypeNotMatch,
ErrMsg = GetMessage("700015", paras),
ErrData = csvDataDR[dr["CName"].ToString()].ToString()
};
listErrInfo.Add(er);
retVal = false;
hasChecked = true;
}
break;
#endregion
#region 检测是否为数字字母类型
case "CheckIsNumChar":
if (!PageHelper.CheckIsNumChar(csvDataDR[dr["CName"].ToString()].ToString()))
{
var paras = new List <string>(1)
{
dr["CName"].ToString()
};
var er = new ErrInfo
{
ErrRowNo = rowNo,
ErrType = PageConst.ImportDataErrorType.DataTypeNotMatch,
ErrMsg = GetMessage("700016", paras),
ErrData = csvDataDR[dr["CName"].ToString()].ToString()
};
listErrInfo.Add(er);
retVal = false;
hasChecked = true;
}
break;
#endregion
#region 检测是否为邮件类型
case "CheckIsEmail":
if (!PageHelper.CheckIsEmail(csvDataDR[dr["CName"].ToString()].ToString()))
{
var paras = new List <string>(1)
{
dr["CName"].ToString()
};
var er = new ErrInfo
{
ErrRowNo = rowNo,
ErrType = PageConst.ImportDataErrorType.DataTypeNotMatch,
ErrMsg = GetMessage("700017", paras),
ErrData = csvDataDR[dr["CName"].ToString()].ToString()
};
listErrInfo.Add(er);
retVal = false;
hasChecked = true;
}
break;
#endregion
#region 检测是否为日期类型
case "CheckIsDate":
if (!PageHelper.CheckIsDateTime(csvDataDR[dr["CName"].ToString()].ToString()))
{
var paras = new List <string>(1)
{
dr["CName"].ToString()
};
var er = new ErrInfo
{
ErrRowNo = rowNo,
ErrType = PageConst.ImportDataErrorType.DataTypeNotMatch,
ErrMsg = GetMessage("700009", paras),
ErrData = csvDataDR[dr["CName"].ToString()].ToString()
};
listErrInfo.Add(er);
retVal = false;
hasChecked = true;
}
break;
#endregion
#region 检测是否为有效的身份证号码
case "CheckIsIdCardNo":
if (!PageHelper.CheckIsIdCardNo(csvDataDR[dr["CName"].ToString()].ToString()))
{
var paras = new List <string>(1)
{
dr["CName"].ToString()
};
var er = new ErrInfo
{
ErrRowNo = rowNo,
ErrType = PageConst.ImportDataErrorType.DataTypeNotMatch,
ErrMsg = GetMessage("700018", paras),
ErrData = csvDataDR[dr["CName"].ToString()].ToString()
};
listErrInfo.Add(er);
retVal = false;
hasChecked = true;
}
break;
#endregion
#region 检测是否为正数
case "ChechIsSignless":
if (!PageHelper.ChechIsSignless(csvDataDR[dr["CName"].ToString()].ToString()))
{
var paras = new List <string>(1)
{
dr["CName"].ToString()
};
var er = new ErrInfo
{
ErrRowNo = rowNo,
ErrType = PageConst.ImportDataErrorType.DataTypeNotMatch,
ErrMsg = GetMessage("700019", paras),
ErrData = csvDataDR[dr["CName"].ToString()].ToString()
};
listErrInfo.Add(er);
retVal = false;
hasChecked = true;
}
break;
#endregion
#region 检测是否为有效的手机号码
case "CheckIsMobile":
if (!PageHelper.CheckIsMobile(csvDataDR[dr["CName"].ToString()].ToString()))
{
var paras = new List <string>(1)
{
dr["CName"].ToString()
};
var er = new ErrInfo
{
ErrRowNo = rowNo,
ErrType = PageConst.ImportDataErrorType.DataTypeNotMatch,
ErrMsg = GetMessage("700020", paras),
ErrData = csvDataDR[dr["CName"].ToString()].ToString()
};
listErrInfo.Add(er);
retVal = false;
hasChecked = true;
}
break;
#endregion
#region 检测是否为正整数类型
case "CheckIsSignlessInteger":
if (!PageHelper.CheckIsSignlessInteger(csvDataDR[dr["CName"].ToString()].ToString()))
{
var paras = new List <string>(1)
{
dr["CName"].ToString()
};
var er = new ErrInfo
{
ErrRowNo = rowNo,
ErrType = PageConst.ImportDataErrorType.DataTypeNotMatch,
ErrMsg = GetMessage("700021", paras),
ErrData = csvDataDR[dr["CName"].ToString()].ToString()
};
listErrInfo.Add(er);
retVal = false;
hasChecked = true;
}
break;
#endregion
#region 检测是否为中文类型
case "isChinese":
if (!PageHelper.IsChinese(csvDataDR[dr["CName"].ToString()].ToString()))
{
var paras = new List <string>(1)
{
dr["CName"].ToString()
};
var er = new ErrInfo
{
ErrRowNo = rowNo,
ErrType = PageConst.ImportDataErrorType.DataTypeNotMatch,
ErrMsg = GetMessage("700023", paras),
ErrData = csvDataDR[dr["CName"].ToString()].ToString()
};
listErrInfo.Add(er);
retVal = false;
hasChecked = true;
}
break;
#endregion
#region 检测是否为非负数
case "CheckIsNonnegativeInteger":
if (!PageHelper.CheckIsNonnegativeInteger(csvDataDR[dr["CName"].ToString()].ToString()))
{
var paras = new List <string>(1)
{
dr["CName"].ToString()
};
var er = new ErrInfo
{
ErrRowNo = rowNo,
ErrType = PageConst.ImportDataErrorType.DataTypeNotMatch,
ErrMsg = GetMessage("700022", paras),
ErrData = csvDataDR[dr["CName"].ToString()].ToString()
};
listErrInfo.Add(er);
retVal = false;
hasChecked = true;
}
break;
#endregion
}
}
}
#endregion
}
return(retVal);
}
/// <summary>
/// 如果检测成功，则把数据导入到数据库
/// </summary>
/// <param name="tableName"></param>
/// <param name="dt"></param>
/// <param name="fromImportItemsDT"></param>
/// <param name="fromDefaulValueItemsDT"></param>
/// <param name="errInfos"></param>
virtual public bool ImportDate(string tableName, DataTable dt, DataTable fromImportItemsDT, DataTable fromDefaulValueItemsDT, ref List <ErrInfo> errInfos)
{
string sql = string.Format("insert into {0}", tableName);
string fields;
string values;
List <SqlParameter> paramList;
int j = 0;
foreach (DataRow dr in dt.Rows)
{
j++;
fields = "(";
values = "(";
paramList = new List <SqlParameter>(dt.Rows.Count);
//导入文件中的数据
if (fromImportItemsDT != null && fromImportItemsDT.Rows.Count > 0)
{
foreach (DataRow dr2 in fromImportItemsDT.Rows)
{
fields += string.Format("{0},", dr2["xColumn"].ToString().Trim());
string pDataSource = new ReplaceParaHelper.ImportCsvReplaceParameter().ReplaceSqlValue(
dr2["DataSource"].ToString().Replace("\"", "'"), dr);
if (!pDataSource.Equals(string.Empty))
{
DataTable pDataSourceDT = DBAccess.DbHelperSQL.Fill(pDataSource);
if (pDataSourceDT.Rows.Count > 0)
{
string value = pDataSourceDT.Rows[0][0].ToString();
paramList.Add(new SqlParameter(string.Format("@{0}", dr2["xColumn"].ToString().Trim()), value));
values += string.Format("@{0},", dr2["xColumn"].ToString().Trim());
}
else
{
paramList.Add(new SqlParameter(string.Format("@{0}", dr2["xColumn"].ToString().Trim()), DBNull.Value));
values += string.Format("@{0},", dr2["xColumn"].ToString().Trim());
}
}
else
{
paramList.Add(new SqlParameter(string.Format("@{0}", dr2["xColumn"].ToString().Trim()),
dr[dr2["CName"].ToString()].ToString()));
values += string.Format("@{0},", dr2["xColumn"].ToString().Trim());
}
//paramList.Add(new SqlParameter("@" + dr2["xColumn"].ToString(), dr[dr2["CName"].ToString()].ToString()));
//values += "@" + dr2["xColumn"].ToString() + ",";
}
}
//导入默认值的数据
if (fromDefaulValueItemsDT.Rows.Count > 0)
{
foreach (DataRow dr2 in fromDefaulValueItemsDT.Rows)
{
fields += string.Format("{0},", dr2["xColumn"].ToString().Trim());
string pDataSource = new ReplaceParaHelper.ImportCsvReplaceParameter().ReplaceSqlValue(dr2["DataSource"].ToString().Replace("\"", "'"), dr);
if (!pDataSource.Equals(string.Empty))
{
DataTable pDataSourceDT = DBAccess.DbHelperSQL.Query(pDataSource).Tables[0];
if (pDataSourceDT.Rows.Count > 0)
{
string value = pDataSourceDT.Rows[0][0].ToString();
paramList.Add(new SqlParameter(string.Format("@{0}", dr2["xColumn"].ToString().Trim()), value));
values += string.Format("@{0},", dr2["xColumn"].ToString().Trim());
}
else
{
paramList.Add(new SqlParameter(string.Format("@{0}", dr2["xColumn"].ToString().Trim()), DBNull.Value));
values += string.Format("@{0},", dr2["xColumn"].ToString().Trim());
}
}
else
{
paramList.Add(new SqlParameter(string.Format("@{0}", dr2["xColumn"].ToString().Trim()), DBNull.Value));
values += string.Format("@{0},", dr2["xColumn"].ToString().Trim());
}
}
}
fields = fields.Substring(0, fields.Length - 1);
values = values.Substring(0, values.Length - 1);
fields += (")");
values += (")");
string insertSql = string.Format("{0}{1} Values {2};SELECT @@identity AS 'id'", sql, fields, values);
try
{
DbHelper.ExecuteSql(insertSql, paramList);
}
catch (Exception exception)
{
var err = new ErrInfo
{
ErrRowNo = j.ToString(),
ErrType = "",
ErrData = "",
ErrMsg = exception.Message.Replace("'", "")
};
errInfos.Add(err);
return(false);
}
}
return(true);
}
