public string CreateValueString(object value)
{
if (value == DBNull.Value || value.ToString().Trim().ToUpper() == "NULL")
{
return("NULL");
}
switch (DataTypeName.ToLower())
{
case "uniqueidentifier":
case "varchar":
case "char":
case "text":
return($"'{value.ToString().Replace("'", "''")}'");
case "nvarchar":
case "nchar":
case "ntext":
return($"N'{value.ToString().Replace("'", "''")}'");
case "money":
case "smallmoney":
return(value.ToString().Replace(',', '.'));
case "float":
case "real":
var strValueF = FormatDecimal(value);
return($"CAST({strValueF} AS Float({NumericPrecision})");
case "numeric":
case "decimal":
var strValueD = FormatDecimal(value);
return($"CAST({strValueD} AS Decimal({NumericPrecision}, {NumericScale}))");
case "bit":
var boolValue = Convert.ToBoolean(value);
return(boolValue ? "1" : "0");
case "datetime":
DateTime datValue;
if (!DateTime.TryParse(value.ToString(), out datValue))
{
// Excel can internally store DateTime as double
var decValue = GetDouble(value);
datValue = DateTime.FromOADate(decValue);
}
return($"'{datValue.ToString("yyyy-MM-dd HH:mm:ss")}'");
default:
return(value.ToString());
}
}
private SqlDbType GetSqlDbType()
{
switch (DataTypeName.ToLower())
{
case "bigint":
return(SqlDbType.BigInt);
case "bit":
return(SqlDbType.Bit);
case "char":
return(SqlDbType.Char);
case "date":
return(SqlDbType.Date);
case "datetime":
return(SqlDbType.DateTime);
case "datetime2":
return(SqlDbType.DateTime2);
case "float":
return(SqlDbType.Float);
case "int":
return(SqlDbType.Int);
case "nvarchar":
return(SqlDbType.NVarChar);
case "real":
return(SqlDbType.Real);
case "smallint":
return(SqlDbType.SmallInt);
case "varbinary":
return(SqlDbType.VarBinary);
case "varchar":
return(SqlDbType.VarChar);
default:
return(SqlDbType.VarChar);
}
}
private object GetCastedValue(string fieldValue)
{
if (String.IsNullOrEmpty(fieldValue))
{
if (IsNullable)
{
return(DBNull.Value);
}
if (DataTypeName == "bit")
{
return(false);
}
throw new NotSupportedException("This field cannot support a null value");
}
//Mappings of object types....
//https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-data-type-mappings
//--bigint ==> int
//--int ==> int
//--smallint ==> int
//--char ==> string
//--nvarchar ==> string
//--varchar ==> string
//--bit ==> bool
//--date ==> DateTime
//--datetime ==> DateTime
//--datetime2 ==> DateTime
//--float ==> Double
//--real ==> single
//--varbinary ==> byte[]
switch (DataTypeName.ToLower())
{
case "bigint":
case "int":
case "smallint":
return(Int32.Parse(fieldValue));
case "char":
case "nvarchar":
case "varchar":
return(fieldValue);
case "date":
case "datetime":
case "datetime2":
return(DateTime.Parse(fieldValue));
case "bit":
if (fieldValue == "1")
{
return(true);
}
else if (fieldValue == "0")
{
return(false);
}
return(Boolean.Parse(fieldValue));
case "float":
return(Double.Parse(fieldValue));
case "real":
return(Single.Parse(fieldValue));
case "varbinary":
return(Byte.Parse(fieldValue));
default:
return(fieldValue);
}
}