public ColumnMapping(int colIndex, string excelColName, InformationSchemaColumns dbColInfo)
{
this.ColIndex = colIndex;
this.ExcelColName = excelColName;
this.DbColInfo = dbColInfo;
if (dbColInfo.DATA_TYPE == "char" ||
dbColInfo.DATA_TYPE == "nchar" ||
dbColInfo.DATA_TYPE == "varchar" ||
dbColInfo.DATA_TYPE == "nvarchar" ||
dbColInfo.DATA_TYPE == "text" ||
dbColInfo.DATA_TYPE == "mediumtext" ||
dbColInfo.DATA_TYPE == "longtext" ||
dbColInfo.DATA_TYPE == "date" ||
dbColInfo.DATA_TYPE == "time" ||
dbColInfo.DATA_TYPE == "datetime" ||
dbColInfo.DATA_TYPE == "timestamp")
{
this.NeedSingleQuotes = true;
}
else
{
this.NeedSingleQuotes = false;
}
}
public IList <SISColumn> GetPrimaryKeyColumns()
{
return(InformationSchemaColumns
.Where(IsColumnPrimaryKey)
.ToList());
}
private bool Validate(InformationSchemaColumns field, string columnValue, out string error)
{
bool ret = true;
error = String.Empty;
// 非空验证
if (String.IsNullOrEmpty(columnValue) || columnValue.ToUpper() == "NULL")
{
if (field.IS_NULLABLE == "NO")
{
error = "非空验证未通过";
ret = false;
}
}
else if (field.DATA_TYPE == "tinyint")
{
int val;
if (!Int32.TryParse(columnValue, out val))
{
error = "非合法数字";
ret = false;
}
else if (val < SByte.MinValue || val > SByte.MaxValue)
{
error = "数值超出范围 [-128 ~ 127]";
ret = false;
}
}
else if (field.DATA_TYPE == "smallint")
{
int val;
if (!Int32.TryParse(columnValue, out val))
{
error = "非合法数字";
ret = false;
}
else if (val < Int16.MinValue || val > Int16.MaxValue)
{
error = "数值超出范围 [-32,768 ~ 32,767]";
ret = false;
}
}
else if (field.DATA_TYPE == "mediumint")
{
int val;
if (!Int32.TryParse(columnValue, out val))
{
error = "非合法数字";
ret = false;
}
else if (val < -8388608 || val > 8388607)
{
error = "数值超出范围 [-8,388,608 ~ 8,388,607]";
ret = false;
}
}
else if (field.DATA_TYPE == "int")
{
int val;
if (!Int32.TryParse(columnValue, out val))
{
error = "非合法数字";
ret = false;
}
else if (val < Int32.MinValue || val > Int32.MaxValue)
{
error = "数值超出范围 [-2,147,483,648 ~ 2,147,483,647]";
ret = false;
}
}
else if (field.DATA_TYPE == "bigint")
{
long val;
if (!Int64.TryParse(columnValue, out val))
{
error = "非合法数字";
ret = false;
}
else if (val < Int64.MinValue || val > Int64.MaxValue)
{
error = "数值超出范围 [-9,223,372,036,854,775,808 ~ 9,223,372,036,854,775,807]";
ret = false;
}
}
else if (field.DATA_TYPE == "float")
{
float val;
if (!Single.TryParse(columnValue, out val))
{
error = "非合法数字";
ret = false;
}
}
else if (field.DATA_TYPE == "double")
{
double val;
if (!Double.TryParse(columnValue, out val))
{
error = "非合法数字";
ret = false;
}
}
else if (field.DATA_TYPE == "decimal")
{
decimal val;
if (!Decimal.TryParse(columnValue, out val))
{
error = "非合法数字";
ret = false;
}
}
else if (field.DATA_TYPE == "char" ||
field.DATA_TYPE == "nchar" ||
field.DATA_TYPE == "varchar" ||
field.DATA_TYPE == "nvarchar" ||
field.DATA_TYPE == "text" ||
field.DATA_TYPE == "mediumtext" ||
field.DATA_TYPE == "longtext")
{
uint maxLen = UInt32.Parse(field.CHARACTER_MAXIMUM_LENGTH);
if (columnValue.Length > maxLen)
{
error = String.Format("字符数超出最大限制 [{0}]", maxLen);
ret = false;
}
}
else if (field.DATA_TYPE == "date" ||
field.DATA_TYPE == "time" ||
field.DATA_TYPE == "datetime" ||
field.DATA_TYPE == "timestamp")
{
if (columnValue.ToUpper() != "NOW()")
{
DateTime val;
if (!DateTime.TryParse(columnValue, out val))
{
error = "非合法日期/时间";
ret = false;
}
}
}
return(ret);
}
