/// <summary>
/// Initializes a parameter.
/// </summary>
/// <param name="parameterType">Parameter datatype</param>
/// <param name="column">Column for the parameter to map to.</param>
public Parameter(ETypeCode parameterType, TableColumn column)
{
Name = column.Name;
DataType = parameterType;
IsColumn = true;
Column = column;
}
public Filter(string columnName1, ECompare operator1, string columnName2, ETypeCode dataType)
{
Column1 = new TableColumn(columnName1);
Operator = operator1;
Column2 = new TableColumn(columnName2);
CompareDataType = dataType;
}
public Parameter(ETypeCode parameterType, string columnName)
{
Name = columnName;
DataType = parameterType;
IsColumn = true;
Column = new TableColumn(columnName);
}
/// <summary>
/// 将所有CIM参数写入到ini，部分以mode为section保存，其余是公共参数
/// </summary>
public void WriteCimConfig()
{
#region params
WriteCimConfig(CIM_PARAMS.SendQueue.ToString(), StrSendQueue, ETypeMode.ToString());
WriteCimConfig(CIM_PARAMS.ReadQueue.ToString(), StrReadQueue, ETypeMode.ToString());
WriteCimConfig(CIM_PARAMS.IP.ToString(), StrIP, ETypeMode.ToString());
WriteCimConfig(CIM_PARAMS.Port.ToString(), StrPort, ETypeMode.ToString());
WriteCimConfig(CIM_PARAMS.CycTimes.ToString(), iCycTimes.ToString());
WriteCimConfig(CIM_PARAMS.UserID.ToString(), StrUserID);
WriteCimConfig(CIM_PARAMS.Fab.ToString(), StrFab);
WriteCimConfig(CIM_PARAMS.Area.ToString(), StrArea);
WriteCimConfig(CIM_PARAMS.Line.ToString(), StrLine);
WriteCimConfig(CIM_PARAMS.EqpId.ToString(), EqpId);
WriteCimConfig(CIM_PARAMS.Operation.ToString(), StrOperation);
WriteCimConfig(CIM_PARAMS.Wo_id.ToString(), Wo_id);
WriteCimConfig(CIM_PARAMS.ModelNo.ToString(), StrModelNo);
WriteCimConfig(CIM_PARAMS.RunCard.ToString(), StrLot);
WriteCimConfig(CIM_PARAMS.COM.ToString(), StrCom);
WriteCimConfig(CIM_PARAMS.CodeDelay.ToString(), iCodeDelay.ToString());
WriteCimConfig(CIM_PARAMS.Baudrate.ToString(), iBaudrate.ToString());
WriteCimConfig(CIM_PARAMS.CodeType.ToString(), ETypeCode.ToString());
WriteCimConfig(CIM_PARAMS.ModeType.ToString(), ETypeMode.ToString());
WriteCimConfig(CIM_PARAMS.BlLog.ToString(), BlLog.ToString());
#endregion
}
/// <summary>
/// Sets a simple filter comparing two columns against each other.
/// </summary>
/// <param name="column1">Column name from incoming data</param>
/// <param name="operator1">Comparison Operator</param>
/// <param name="column2">Static value to compare to</param>
/// <param name="dataType">Data type of the column</param>
public Filter(TableColumn column1, ECompare operator1, TableColumn column2, ETypeCode dataType)
{
Column1 = column1;
Operator = operator1;
Column2 = column2;
CompareDataType = dataType;
}
public static SqlDbType GetSqlDbType(ETypeCode typeCode)
{
switch (typeCode)
{
case ETypeCode.Byte:
return(SqlDbType.VarChar);
case ETypeCode.SByte:
return(SqlDbType.SmallInt);
case ETypeCode.UInt16:
return(SqlDbType.Int);
case ETypeCode.UInt32:
return(SqlDbType.BigInt);
case ETypeCode.UInt64:
return(SqlDbType.VarChar);
case ETypeCode.Int16:
return(SqlDbType.SmallInt);
case ETypeCode.Int32:
return(SqlDbType.Int);
case ETypeCode.Int64:
return(SqlDbType.BigInt);
case ETypeCode.Decimal:
return(SqlDbType.Decimal);
case ETypeCode.Double:
return(SqlDbType.Float);
case ETypeCode.Single:
return(SqlDbType.Real);
case ETypeCode.String:
return(SqlDbType.NVarChar);
case ETypeCode.Boolean:
return(SqlDbType.Bit);
case ETypeCode.DateTime:
return(SqlDbType.DateTime);
case ETypeCode.Time:
return(SqlDbType.Time);
case ETypeCode.Guid:
return(SqlDbType.UniqueIdentifier);
case ETypeCode.Binary:
return(SqlDbType.Binary);
default:
return(SqlDbType.VarChar);
}
}
public TableColumn(string columName, ETypeCode dataType, EDeltaType deltaType, string parentTable = null)
{
Name = columName;
LogicalName = columName;
DataType = dataType;
DeltaType = deltaType;
ReferenceTable = parentTable;
}
public void AddColumn(string columnName, ETypeCode dataType = ETypeCode.String, TableColumn.EDeltaType deltaType = TableColumn.EDeltaType.TrackingField)
{
if (Columns == null)
{
Columns = new TableColumns();
}
Columns.Add(new TableColumn(columnName, dataType, deltaType, Name));
}
public void AddColumn(string columnName, ETypeCode dataType = ETypeCode.String)
{
if (Columns == null)
{
Columns = new TableColumns();
}
Columns.Add(new TableColumn(columnName, dataType, Name));
}
public override object GetConnectionMaxValue(ETypeCode typeCode, int length = 0)
{
switch (typeCode)
{
case ETypeCode.DateTime:
return(new DateTime(9999, 12, 31));
default:
return(Dexih.Utils.DataType.DataType.GetDataTypeMaxValue(typeCode, length));
}
}
public override object GetConnectionMinValue(ETypeCode typeCode)
{
switch (typeCode)
{
case ETypeCode.DateTime:
return(new DateTime(1753, 1, 1));
default:
return(Dexih.Utils.DataType.DataType.GetDataTypeMinValue(typeCode));
}
}
public override object GetConnectionMaxValue(ETypeCode typeCode, int length = 0)
{
switch (typeCode)
{
case ETypeCode.Decimal:
return((decimal)999999999999999);
default:
return(Dexih.Utils.DataType.DataType.GetDataTypeMaxValue(typeCode, length));
}
}
public override object GetConnectionMinValue(ETypeCode typeCode)
{
switch (typeCode)
{
case ETypeCode.Decimal:
return((decimal) - 999999999999999);
default:
return(Dexih.Utils.DataType.DataType.GetDataTypeMinValue(typeCode));
}
}
public override object GetConnectionMaxValue(ETypeCode typeCode, int length = 0)
{
switch (typeCode)
{
case ETypeCode.UInt64:
return((ulong)long.MaxValue);
case ETypeCode.DateTime:
return(new DateTime(9999, 12, 31, 23, 59, 59, 999));
default:
return(GetDataTypeMaxValue(typeCode, length));
}
}
public override object GetConnectionMinValue(ETypeCode typeCode)
{
switch (typeCode)
{
case ETypeCode.Double:
return((double)-1000000000000000F);
case ETypeCode.Single:
return(-1E20F);
default:
return(Dexih.Utils.DataType.DataType.GetDataTypeMinValue(typeCode));
}
}
public void Parameter_Tests(ETypeCode dataType, object value, bool expectedSuccess)
{
//test boolean
var parameter = new dexih.functions.Parameter("test", dataType);
try
{
parameter.SetValue(value);
Assert.True(expectedSuccess);
}
catch (Exception)
{
Assert.False(expectedSuccess);
}
}
public override object GetConnectionMinValue(ETypeCode typeCode)
{
switch (typeCode)
{
case ETypeCode.DateTime:
return(new DateTime(1000, 1, 1));
case ETypeCode.Double:
return(-1E+100);
case ETypeCode.Single:
return(-1E+37F);
default:
return(GetDataTypeMinValue(typeCode));
}
}
public override object GetConnectionMinValue(ETypeCode typeCode)
{
// Note: Excel only support max 15 digits.
switch (typeCode)
{
case ETypeCode.DateTime:
return(new DateTime(1900, 01, 01, 0, 0, 0, 0));
case ETypeCode.Int64:
return((long)-999999999999999);
case ETypeCode.Decimal:
return((decimal) - 999999999999999);
default:
return(Dexih.Utils.DataType.DataType.GetDataTypeMinValue(typeCode));
}
}
/// <summary>
/// Initializes are parameter
/// </summary>
/// <param name="name">Paramter name</param>
/// <param name="parameterType">Parameter datatype</param>
/// <param name="isColumn">Indicates if the parameter is a column (vs a hard coded value)</param>
/// <param name="value">Value for the parameter (note: requires isColumn = false)</param>
/// <param name="column">Column for the parameter to map to (note: requires isColumn = true)</param>
/// <param name="isArray">Inidicates the parameter is an array of values (note array parameter</param>
public Parameter(
string name,
ETypeCode parameterType,
bool isColumn = false,
object value = null,
TableColumn column = null,
bool isArray = false
)
{
Name = name;
DataType = parameterType;
if (value != null)
{
SetValue(value);
}
Column = column;
IsColumn = isColumn;
IsArray = isArray;
}
public override object GetConnectionMaxValue(ETypeCode typeCode, int length = 0)
{
switch (typeCode)
{
case ETypeCode.DateTime:
return(DateTime.MaxValue.ToUniversalTime());
case ETypeCode.UInt64:
return((ulong)long.MaxValue);
case ETypeCode.Double:
return(1E+100);
case ETypeCode.Single:
return(1E+37F);
default:
return(GetDataTypeMaxValue(typeCode, length));
}
}
// public override object ConvertParameterType(object value)
// {
// switch (value)
// {
// case UInt16 uint16:
// return (Int32)uint16;
// case UInt32 uint32:
// return (Int64)uint32;
// case UInt64 uint64:
// return (Int64)uint64;
// default:
// return value;
// }
// }
public override object GetConnectionMaxValue(ETypeCode typeCode, int length = 0)
{
switch (typeCode)
{
case ETypeCode.DateTime:
return(new DateTime(9999, 12, 31));
case ETypeCode.Time:
return(TimeSpan.FromDays(1) - TimeSpan.FromSeconds(1)); //mysql doesn't support milliseconds
case ETypeCode.Double:
return(1E+100);
case ETypeCode.Single:
return(1E+37F);
default:
return(GetDataTypeMaxValue(typeCode, length));
}
}
public override object GetConnectionMaxValue(ETypeCode typeCode, int length = 0)
{
switch (typeCode)
{
case ETypeCode.DateTime:
return(new DateTime(9999, 12, 31, 23, 59, 59, 999));
case ETypeCode.UInt64:
return((ulong)long.MaxValue);
//TODO Oracle driver giving error when converting any numeric with scientific number
case ETypeCode.Double:
return((double)1000000000000000F);
case ETypeCode.Single:
return(1E20F);
default:
return(GetDataTypeMaxValue(typeCode, length));
}
}
public override object GetConnectionMaxValue(ETypeCode typeCode, int length = 0)
{
// Note: Excel only support max 15 digits.
switch (typeCode)
{
case ETypeCode.DateTime:
return(new DateTime(9999, 12, 31, 23, 59, 59, 999));
case ETypeCode.UInt64:
return((ulong)999999999999999);
case ETypeCode.Int64:
return((long)999999999999999);
case ETypeCode.Decimal:
return((decimal)999999999999999);
default:
return(Dexih.Utils.DataType.DataType.GetDataTypeMaxValue(typeCode, length));
}
}
/// <summary>
/// Gets the start quote to go around the values in sql insert statement based in the column type.
/// </summary>
/// <returns></returns>
protected override string GetSqlFieldValueQuote(ETypeCode type, object value)
{
string returnValue;
if (value == null || value.GetType().ToString() == "System.DBNull")
{
return("null");
}
//if (value is string && type != ETypeCode.String && string.IsNullOrWhiteSpace((string)value))
// return "null";
switch (type)
{
case ETypeCode.Byte:
case ETypeCode.Single:
case ETypeCode.Int16:
case ETypeCode.Int32:
case ETypeCode.Int64:
case ETypeCode.SByte:
case ETypeCode.UInt16:
case ETypeCode.UInt32:
case ETypeCode.UInt64:
case ETypeCode.Double:
case ETypeCode.Decimal:
returnValue = AddEscape(value.ToString());
break;
case ETypeCode.Boolean:
returnValue = (bool)value ? "1" : "0";
break;
case ETypeCode.String:
case ETypeCode.Text:
case ETypeCode.Json:
case ETypeCode.Xml:
case ETypeCode.Guid:
case ETypeCode.Unknown:
returnValue = "'" + AddEscape(value.ToString()) + "'";
break;
case ETypeCode.DateTime:
case ETypeCode.Time:
//sqlite does not have date fields, so convert to format that will work for greater/less compares
if (value is DateTime)
{
returnValue = "'" + AddEscape(((DateTime)value).ToString("yyyy-MM-dd HH:mm:ss.ff")) + "'";
}
else if (value is TimeSpan)
{
returnValue = "'" + AddEscape(((TimeSpan)value).ToString()) + "'";
}
else
{
returnValue = "'" + AddEscape((string)value) + "'";
}
break;
default:
throw new Exception("The datatype " + type.ToString() +
" is not compatible with the create table.");
}
return(returnValue);
}
protected abstract string GetSqlFieldValueQuote(ETypeCode type, object value);
public virtual object GetConnectionMaxValue(ETypeCode typeCode, int length = 0)
{
return(GetDataTypeMaxValue(typeCode, length));
}
public virtual object GetConnectionMinValue(ETypeCode typeCode)
{
return(GetDataTypeMinValue(typeCode));
}
/// <summary>
/// Gets the start quote to go around the values in sql insert statement based in the column type.
/// </summary>
/// <returns></returns>
protected override string GetSqlFieldValueQuote(ETypeCode type, object value)
{
string returnValue;
if (value == null || value is DBNull)
{
return("null");
}
//if (value is string && type != ETypeCode.String && string.IsNullOrWhiteSpace((string)value))
// return "null";
switch (type)
{
case ETypeCode.Byte:
case ETypeCode.Single:
case ETypeCode.Int16:
case ETypeCode.Int32:
case ETypeCode.Int64:
case ETypeCode.SByte:
case ETypeCode.UInt16:
case ETypeCode.UInt32:
case ETypeCode.UInt64:
case ETypeCode.Double:
case ETypeCode.Decimal:
returnValue = AddEscape(value.ToString());
break;
case ETypeCode.String:
case ETypeCode.Guid:
case ETypeCode.Boolean:
case ETypeCode.Text:
case ETypeCode.Json:
case ETypeCode.Xml:
case ETypeCode.Unknown:
returnValue = "'" + AddEscape(value.ToString()) + "'";
break;
case ETypeCode.DateTime:
if (value is DateTime)
{
returnValue = "convert(datetime, '" + AddEscape(((DateTime)value).ToString("yyyy-MM-dd HH:mm:ss.ff")) + "')";
}
else
{
returnValue = "convert(datetime, '" + AddEscape((string)value) + "')";
}
break;
case ETypeCode.Time:
if (value is TimeSpan)
{
returnValue = "convert(time, '" + AddEscape(((TimeSpan)value).ToString("c")) + "')";
}
else
{
returnValue = "convert(time, '" + AddEscape((string)value) + "')";
}
break;
default:
throw new Exception("The datatype " + type.ToString() + " is not compatible with the sql statement.");
}
return(returnValue);
}
private (NpgsqlDbType type, object value) GetSqlDbType(ETypeCode typeCode, Object value)
{
switch (typeCode)
{
case ETypeCode.Byte:
return(NpgsqlDbType.Smallint, TryParse(ETypeCode.Int16, value));
case ETypeCode.SByte:
return(NpgsqlDbType.Smallint, TryParse(ETypeCode.Int16, value));
case ETypeCode.UInt16:
return(NpgsqlDbType.Integer, TryParse(ETypeCode.Int32, value));
case ETypeCode.UInt32:
return(NpgsqlDbType.Bigint, TryParse(ETypeCode.Int64, value));
case ETypeCode.UInt64:
return(NpgsqlDbType.Bigint, TryParse(ETypeCode.Int64, value));
case ETypeCode.Int16:
return(NpgsqlDbType.Smallint, TryParse(ETypeCode.Int16, value));
case ETypeCode.Int32:
return(NpgsqlDbType.Integer, TryParse(ETypeCode.Int32, value));
case ETypeCode.Int64:
return(NpgsqlDbType.Bigint, TryParse(ETypeCode.Int64, value));
case ETypeCode.Decimal:
return(NpgsqlDbType.Numeric, TryParse(ETypeCode.Decimal, value));
case ETypeCode.Double:
return(NpgsqlDbType.Double, TryParse(ETypeCode.Double, value));
case ETypeCode.Single:
return(NpgsqlDbType.Real, TryParse(ETypeCode.Single, value));
case ETypeCode.String:
return(NpgsqlDbType.Varchar, TryParse(ETypeCode.String, value));
case ETypeCode.Text:
return(NpgsqlDbType.Text, TryParse(ETypeCode.Text, value));
case ETypeCode.Boolean:
return(NpgsqlDbType.Boolean, TryParse(ETypeCode.Boolean, value));
case ETypeCode.DateTime:
return(NpgsqlDbType.Timestamp, TryParse(ETypeCode.DateTime, value));
case ETypeCode.Time:
return(NpgsqlDbType.Time, TryParse(ETypeCode.Time, value));
case ETypeCode.Guid:
return(NpgsqlDbType.Varchar, value.ToString());
case ETypeCode.Binary:
return(NpgsqlDbType.Bytea, value);
default:
return(NpgsqlDbType.Varchar, TryParse(ETypeCode.String, value));
}
}
/// <summary>
/// Gets the start quote to go around the values in sql insert statement based in the column type.
/// </summary>
/// <returns></returns>
protected override string GetSqlFieldValueQuote(ETypeCode type, object value)
{
string returnValue;
if (value == null || value is DBNull)
{
return("null");
}
//if (value is string && type != ETypeCode.String && string.IsNullOrWhiteSpace((string)value))
// return "null";
switch (type)
{
case ETypeCode.Byte:
case ETypeCode.Single:
case ETypeCode.Int16:
case ETypeCode.Int32:
case ETypeCode.Int64:
case ETypeCode.SByte:
case ETypeCode.UInt16:
case ETypeCode.UInt32:
case ETypeCode.UInt64:
case ETypeCode.Double:
case ETypeCode.Decimal:
case ETypeCode.Boolean:
returnValue = MySqlHelper.EscapeString(value.ToString());
break;
case ETypeCode.String:
case ETypeCode.Text:
case ETypeCode.Json:
case ETypeCode.Xml:
case ETypeCode.Guid:
case ETypeCode.Unknown:
returnValue = "'" + MySqlHelper.EscapeString(value.ToString()) + "'";
break;
case ETypeCode.DateTime:
if (value is DateTime)
{
returnValue = "STR_TO_DATE('" + MySqlHelper.EscapeString(((DateTime)value).ToString("yyyy-MM-dd HH:mm:ss.ff")) + "', '%Y-%m-%d %H:%i:%s.%f')";
}
else
{
returnValue = "STR_TO_DATE('" + MySqlHelper.EscapeString((string)value) + "', '%Y-%m-%d %H:%i:%s.%f')";
}
break;
case ETypeCode.Time:
if (value is TimeSpan span)
{
returnValue = "TIME_FORMAT('" + MySqlHelper.EscapeString(span.ToString("c")) + "', '%H:%i:%s.%f')";
}
else
{
returnValue = "TIME_FORMAT('" + MySqlHelper.EscapeString((string)value) + "', '%H:%i:%s.%f')";
}
break;
case ETypeCode.Binary:
returnValue = "X'" + TryParse(ETypeCode.String, value) + "'";
break;
default:
throw new Exception("The datatype " + type + " is not compatible with the sql insert statement.");
}
return(returnValue);
}
