/// <summary>格式化数据为SQL数据</summary>
/// <param name="field">字段</param>
/// <param name="value">数值</param>
/// <returns></returns>
public virtual String FormatValue(IDataColumn field, Object value)
{
Boolean isNullable = true;
Type type = null;
if (field != null)
{
type = field.DataType;
isNullable = field.Nullable;
}
else if (value != null)
type = value.GetType();
var code = Type.GetTypeCode(type);
if (code == TypeCode.String)
{
if (value == null) return isNullable ? "null" : "''";
//!!! 为SQL格式化数值时，如果字符串是Empty，将不再格式化为null
//if (String.IsNullOrEmpty(value.ToString()) && isNullable) return "null";
return "'" + value.ToString().Replace("'", "''") + "'";
}
else if (code == TypeCode.DateTime)
{
if (value == null) return isNullable ? "null" : "''";
var dt = Convert.ToDateTime(value);
if (dt < DateTimeMin || dt > DateTime.MaxValue) return isNullable ? "null" : "''";
if ((dt == DateTime.MinValue || dt == DateTimeMin) && isNullable) return "null";
return FormatDateTime(dt);
}
else if (code == TypeCode.Boolean)
{
if (value == null) return isNullable ? "null" : "";
return Convert.ToBoolean(value) ? "1" : "0";
}
else if (type == typeof(Byte[]))
{
Byte[] bts = (Byte[])value;
if (bts == null || bts.Length < 1) return isNullable ? "null" : "0x0";
return "0x" + BitConverter.ToString(bts).Replace("-", null);
}
else if (field.DataType == typeof(Guid))
{
if (value == null) return isNullable ? "null" : "''";
return String.Format("'{0}'", value);
}
else
{
if (value == null) return isNullable ? "null" : "";
// 转为目标类型，比如枚举转为数字
value = value.ChangeType(type);
if (value == null) return isNullable ? "null" : "";
return value.ToString();
}
}
