private ODAScript GetNormalFunctionSql()
{
ODAScript sql = new ODAScript();
sql.SqlScript.Append(_FuncName);
sql.SqlScript.Append("(");
for (int k = 0; k < _FunArgs.Count; k++)
{
if (_FunArgs[k] is ODAColumns)
{
string colSql;
ODAParameter[] prmSub = ((IODAColumns)_FunArgs[k]).GetSelectColumn(out colSql);
sql.SqlScript.Append(colSql).Append(",");
if (prmSub != null && prmSub.Length > 0)
{
sql.ParamList.AddRange(prmSub);
}
}
else
{
ODAParameter paramSub = new ODAParameter();
paramSub.ColumnName = "@FunctionParameter";
paramSub.ParamsName = ODAParameter.ODAParamsMark + _Cmd.GetAlias();
paramSub.ParamsValue = _FunArgs[k];
paramSub.Direction = System.Data.ParameterDirection.Input;
paramSub.Size = 2000;
paramSub.DBDataType = (_FunArgs[k] is DateTime ? ODAdbType.ODatetime : _FunArgs[k].GetType().IsPrimitive ? ODAdbType.ODecimal : ODAdbType.OVarchar);
sql.SqlScript.Append(paramSub.ParamsName).Append(",");
sql.ParamList.Add(paramSub);
}
}
sql.SqlScript.Remove(sql.SqlScript.Length - 1, 1);
sql.SqlScript.Append(")");
return(sql);
}
protected virtual ODAParameter GetProcedureParams()
{
ODAParameter P = new ODAParameter();
P.ColumnName = _ColumnName;
P.ParamsName = _ColumnName;
P.ParamsValue = _CompareValue == null ? System.DBNull.Value : _CompareValue;
P.DBDataType = _DBDataType;
P.Direction = _PDirection;
P.Size = _Size;
return(P);
}
protected virtual ODAParameter[] GetInsertSubstring(out string SubSql, out string SubSqlParams)
{
ODAParameter P = new ODAParameter();
string PName = ODAParameter.ODAParamsMark + _Cmd.GetAlias();
P.ColumnName = _ColumnName;
P.ParamsName = PName;
P.ParamsValue = _CompareValue == null ? System.DBNull.Value : _CompareValue;
P.DBDataType = _DBDataType;
P.Direction = ParameterDirection.Input;
P.Size = _Size;
SubSql = _ColumnName;
SubSqlParams = P.ParamsName;
return(new ODAParameter[] { P });
}
public ODAColumns VisualColumn(DateTime Val)
{
var prm = _Cmd.GetAlias();
ODAParameter FuncPrm = new ODAParameter()
{
ColumnName = prm,
DBDataType = ODAdbType.ODatetime,
Direction = System.Data.ParameterDirection.Input,
ParamsName = ODAParameter.ODAParamsMark + prm,
ParamsValue = Val,
Size = 22
};
return(Express(FuncPrm.ParamsName, FuncPrm));
}
protected virtual ODAScript GetWhereSubstring()
{
ODAScript sql = new ODAScript();
sql.SqlScript.Append(GetColumnName());
if (_CompareValue is ODAColumns)
{
switch (_Symbol)
{
case CmdConditionSymbol.BIGGER:
sql.SqlScript.Append(" > ");
break;
case CmdConditionSymbol.EQUAL:
sql.SqlScript.Append(" = ");
break;
case CmdConditionSymbol.LIKE:
sql.SqlScript.Append(" LIKE ");
break;
case CmdConditionSymbol.NOTLIKE:
sql.SqlScript.Append(" NOT LIKE ");
break;
case CmdConditionSymbol.NOTBIGGER:
sql.SqlScript.Append(" <= ");
break;
case CmdConditionSymbol.NOTEQUAL:
sql.SqlScript.Append(" <> ");
break;
case CmdConditionSymbol.NOTSMALLER:
sql.SqlScript.Append(" >= ");
break;
case CmdConditionSymbol.SMALLER:
sql.SqlScript.Append(" < ");
break;
case CmdConditionSymbol.ADD:
sql.SqlScript.Append(" + ");
break;
case CmdConditionSymbol.REDUCE:
sql.SqlScript.Append(" - ");
break;
case CmdConditionSymbol.TAKE:
sql.SqlScript.Append(" * ");
break;
case CmdConditionSymbol.REMOVE:
sql.SqlScript.Append(" / ");
break;
case CmdConditionSymbol.STAY:
sql.SqlScript.Append(" % ");
break;
default:
throw new ODAException(20005, string.IsNullOrEmpty(_ColumnComment) ? _ColumnName + " not assign" : _ColumnComment + "CmdConditionSymbol Errror");
}
if (((ODAColumns)_CompareValue)._Symbol == CmdConditionSymbol.NONE)
{
sql.SqlScript.Append(((ODAColumns)_CompareValue).ODAColumnName);
}
else
{
var sub = ((IODAColumns)_CompareValue).GetWhereSubstring();
sql.Merge(sub);
}
}
else
{
ODAParameter param = new ODAParameter();
string PName = ODAParameter.ODAParamsMark + _Cmd.GetAlias();
param.ColumnName = this.ODAColumnName;
param.ParamsName = PName;
param.ParamsValue = _CompareValue == null ? System.DBNull.Value : _CompareValue;
param.DBDataType = _DBDataType;
param.Direction = _PDirection;
param.Size = _Size;
switch (_Symbol)
{
case CmdConditionSymbol.BIGGER:
sql.SqlScript.Append(" > ").Append(param.ParamsName);
sql.ParamList.Add(param);
break;
case CmdConditionSymbol.EQUAL:
sql.SqlScript.Append(" = ").Append(param.ParamsName);
sql.ParamList.Add(param);
break;
case CmdConditionSymbol.IN:
if (_InCmd == null && _CompareValue != System.DBNull.Value)
{
object[] ValueList = (object[])_CompareValue;
string paramName = _Cmd.GetAlias();
sql.SqlScript.Append(" IN (");
for (int k = 0; k < ValueList.Length; k++)
{
ODAParameter paramSub = new ODAParameter();
paramSub.ColumnName = this.ODAColumnName;
paramSub.ParamsName = ODAParameter.ODAParamsMark + paramName + "_" + k.ToString();
paramSub.ParamsValue = ValueList[k];
paramSub.DBDataType = _DBDataType;
paramSub.Direction = _PDirection;
paramSub.Size = _Size;
sql.SqlScript.Append(paramSub.ParamsName).Append(",");
sql.ParamList.Add(paramSub);
}
sql.SqlScript.Remove(sql.SqlScript.Length - 1, 1).Append(")");
}
else
{
var subSql = ((ODACmd)_InCmd).GetSelectSql(_InColumn);
string inSql = " IN ( " + subSql.SqlScript.ToString() + ")";
subSql.SqlScript.Clear();
subSql.SqlScript.Append(inSql);
sql.Merge(subSql);
}
break;
case CmdConditionSymbol.NOTIN:
if (_InCmd == null && _CompareValue != System.DBNull.Value)
{
object[] ValueList = (object[])_CompareValue;
sql.SqlScript.Append(" NOT IN (");
string paramName = _Cmd.GetAlias();
for (int k = 0; k < ValueList.Length; k++)
{
ODAParameter paramSub = new ODAParameter();
paramSub.ColumnName = this.ODAColumnName;
paramSub.ParamsName = ODAParameter.ODAParamsMark + paramName + "_" + k.ToString();
paramSub.ParamsValue = ValueList[k];
paramSub.DBDataType = _DBDataType;
paramSub.Direction = _PDirection;
paramSub.Size = _Size;
sql.SqlScript.Append(paramSub.ParamsName).Append(",");
sql.ParamList.Add(paramSub);
}
sql.SqlScript.Remove(sql.SqlScript.Length - 1, 1).Append(")");
}
else
{
var subSql = ((ODACmd)_InCmd).GetSelectSql(_InColumn);
string inSql = " NOT IN ( " + subSql.SqlScript.ToString() + ")";
subSql.SqlScript.Clear();
subSql.SqlScript.Append(inSql);
sql.Merge(subSql);
}
break;
case CmdConditionSymbol.LIKE:
sql.SqlScript.Append(" LIKE ").Append(param.ParamsName);
sql.ParamList.Add(param);
break;
case CmdConditionSymbol.NOTLIKE:
sql.SqlScript.Append(" NOT LIKE ").Append(param.ParamsName);
sql.ParamList.Add(param);
break;
case CmdConditionSymbol.NOTBIGGER:
sql.SqlScript.Append(" <= ").Append(param.ParamsName);
sql.ParamList.Add(param);
break;
case CmdConditionSymbol.NOTEQUAL:
sql.SqlScript.Append(" <> ").Append(param.ParamsName);
sql.ParamList.Add(param);
break;
case CmdConditionSymbol.NOTSMALLER:
sql.SqlScript.Append(" >= ").Append(param.ParamsName);
sql.ParamList.Add(param);
break;
case CmdConditionSymbol.SMALLER:
sql.SqlScript.Append(" < ").Append(param.ParamsName);
sql.ParamList.Add(param);
break;
case CmdConditionSymbol.ISNOTNULL:
sql.SqlScript.Append(" IS NOT NULL ");
break;
case CmdConditionSymbol.ISNULL:
sql.SqlScript.Append(" IS NULL ");
break;
case CmdConditionSymbol.ADD:
sql.SqlScript.Append(" + ").Append(param.ParamsName);
sql.ParamList.Add(param);
break;
case CmdConditionSymbol.REDUCE:
sql.SqlScript.Append(" - ").Append(param.ParamsName);
sql.ParamList.Add(param);
break;
case CmdConditionSymbol.TAKE:
sql.SqlScript.Append(" * ").Append(param.ParamsName);
sql.ParamList.Add(param);
break;
case CmdConditionSymbol.REMOVE:
sql.SqlScript.Append(" / ").Append(param.ParamsName);
sql.ParamList.Add(param);
break;
case CmdConditionSymbol.STAY:
sql.SqlScript.Append(" % ").Append(param.ParamsName);
sql.ParamList.Add(param);
break;
default:
throw new ODAException(20006, string.IsNullOrWhiteSpace(_ColumnComment) ? _ColumnName + " not assign" : _ColumnComment + "CmdConditionSymbol Errror");
}
}
var ar = new ODAScript();
for (int i = 0; i < _SqlColumnList.Count; i++)
{
ar.SqlScript.Append(_SqlColumnList[i].ConnScript);
var cc = _SqlColumnList[i].SqlColumn.GetWhereSubstring();
ar.Merge(cc);
}
if (ar.SqlScript.Length > 0)
{
sql.Merge(ar);
sql.SqlScript.Insert(0, "(").Append(")");
}
return(sql);
}
protected virtual ODAParameter[] GetUpdateSubstring(out string SubSql)
{
SubSql = "";
List <ODAParameter> PList = new List <ODAParameter>();
switch (_Symbol)
{
case CmdConditionSymbol.EQUAL:
SubSql = _ColumnName + "=";
break;
case CmdConditionSymbol.ADD:
SubSql = _ColumnName + " + ";
break;
case CmdConditionSymbol.REDUCE:
SubSql = _ColumnName + " - ";
break;
case CmdConditionSymbol.TAKE:
SubSql = _ColumnName + " * ";
break;
case CmdConditionSymbol.REMOVE:
SubSql = _ColumnName + " / ";
break;
case CmdConditionSymbol.STAY:
SubSql = _ColumnName + " % ";
break;
default:
throw new ODAException(20004, (string.IsNullOrEmpty(_ColumnComment) ? _ColumnName : _ColumnComment) + "CmdConditionSymbol Errror");
}
if (_CompareValue is ODAColumns)
{
string SubSqltmp = "";
if (((ODAColumns)_CompareValue)._Symbol == CmdConditionSymbol.NONE)
{
SubSql += ((ODAColumns)_CompareValue).ODAColumnName;
}
else
{
ODAParameter[] p = ((IODAColumns)_CompareValue).GetUpdateSubstring(out SubSqltmp);
PList.AddRange(p);
SubSql += SubSqltmp;
}
}
else
{
ODAParameter P = new ODAParameter();
string PName = ODAParameter.ODAParamsMark + _Cmd.GetAlias();
P.ColumnName = _ColumnName;
P.ParamsName = PName;
P.ParamsValue = _CompareValue == null ? System.DBNull.Value : _CompareValue;
P.DBDataType = _DBDataType;
P.Direction = _PDirection;
P.Size = _Size;
SubSql += P.ParamsName;
PList.Add(P);
}
return(PList.ToArray());
}
private ODAScript GetCaseWhenSql()
{
ODAScript sql = new ODAScript();
sql.SqlScript.Append("( CASE ");
foreach (KeyValuePair <ODAColumns, object> wt in _WhenThen)
{
sql.SqlScript.Append(" WHEN ");
var wSql = ((IODAColumns)wt.Key).GetWhereSubstring();
sql.Merge(wSql);
if (wt.Value is ODAColumns)
{
sql.SqlScript.Append(" THEN ").Append(((ODAColumns)wt.Value).ODAColumnName);
}
else if (wt.Value is System.DBNull)
{
sql.SqlScript.Append(" THEN NULL ");
}
else
{
ODAParameter paramSub = new ODAParameter();
paramSub.ColumnName = wt.Key.ODAColumnName;
paramSub.ParamsName = ODAParameter.ODAParamsMark + _Cmd.GetAlias();
paramSub.ParamsValue = wt.Value;
paramSub.Direction = System.Data.ParameterDirection.Input;
paramSub.Size = 2000;
paramSub.DBDataType = (wt.Value is DateTime ? ODAdbType.ODatetime : wt.Value.GetType().IsPrimitive ? ODAdbType.ODecimal : ODAdbType.OVarchar);
sql.SqlScript.Append(" THEN ").Append(paramSub.ParamsName);
sql.ParamList.Add(paramSub);
}
}
if (_CaseElseVal is ODAColumns)
{
string slt;
ODAParameter[] sltPrm = ((IODAColumns)_CaseElseVal).GetSelectColumn(out slt);
if (sltPrm != null && sltPrm.Length > 0)
{
sql.ParamList.AddRange(sltPrm);
}
sql.SqlScript.Append(" ELSE ").Append(slt).Append(" END )");
}
else if (_CaseElseVal is System.DBNull || _CaseElseVal == null || string.IsNullOrWhiteSpace(_CaseElseVal.ToString()))
{
sql.SqlScript.Append(" ELSE NULL END ) ");
}
else
{
ODAParameter paramSub = new ODAParameter();
paramSub.ColumnName = "";
paramSub.ParamsName = ODAParameter.ODAParamsMark + _Cmd.GetAlias();
paramSub.ParamsValue = _CaseElseVal;
paramSub.Direction = System.Data.ParameterDirection.Input;
paramSub.Size = 2000;
paramSub.DBDataType = (_CaseElseVal is DateTime ? ODAdbType.ODatetime : _CaseElseVal.GetType().IsPrimitive ? ODAdbType.ODecimal : ODAdbType.OVarchar);
sql.SqlScript.Append(" ELSE ").Append(paramSub.ParamsName).Append(" END ) ");
sql.ParamList.Add(paramSub);
}
_FuncName = "";
return(sql);
}
private ODAScript GetCaseSql()
{
ODAScript sql = new ODAScript();
sql.SqlScript.Append("( CASE ").Append(_CaseCol.ODAColumnName);
foreach (KeyValuePair <object, object> wt in _CaseThen)
{
if (wt.Key is ODAColumns)
{
sql.SqlScript.Append(" WHEN ").Append(((IODAColumns)wt.Key).GetColumnName());
}
else if (wt.Key is System.DBNull)
{
sql.SqlScript.Append(" WHEN NULL ");
}
else
{
ODAParameter paramSub = new ODAParameter();
paramSub.ColumnName = _CaseCol.ODAColumnName;
paramSub.ParamsName = ODAParameter.ODAParamsMark + _Cmd.GetAlias();
paramSub.ParamsValue = wt.Key;
paramSub.Direction = System.Data.ParameterDirection.Input;
paramSub.Size = 2000;
paramSub.DBDataType = (wt.Key is DateTime ? ODAdbType.ODatetime : wt.Value.GetType().IsPrimitive ? ODAdbType.ODecimal : ODAdbType.OVarchar);
sql.SqlScript.Append(" WHEN ").Append(paramSub.ParamsName);
sql.ParamList.Add(paramSub);
}
if (wt.Value is ODAColumns)
{
sql.SqlScript.Append(" THEN ").Append(((ODAColumns)wt.Value).ODAColumnName);
}
else if (wt.Value is System.DBNull)
{
sql.SqlScript.Append(" THEN NULL ");
}
else
{
ODAParameter paramSub = new ODAParameter();
paramSub.ColumnName = _CaseCol.ODAColumnName;
paramSub.ParamsName = ODAParameter.ODAParamsMark + _Cmd.GetAlias();;
paramSub.ParamsValue = wt.Value;
paramSub.Direction = System.Data.ParameterDirection.Input;
paramSub.Size = 2000;
paramSub.DBDataType = (wt.Value is DateTime ? ODAdbType.ODatetime : wt.Value.GetType().IsPrimitive ? ODAdbType.ODecimal : ODAdbType.OVarchar);
sql.SqlScript.Append(" THEN ").Append(paramSub.ParamsName);
sql.ParamList.Add(paramSub);
}
}
if (_CaseElseVal is ODAColumns)
{
sql.SqlScript.Append(" ELSE ").Append(((IODAColumns)_CaseElseVal).GetColumnName()).Append(" END )");
}
else if (_CaseElseVal is System.DBNull || _CaseElseVal == null || string.IsNullOrWhiteSpace(_CaseElseVal.ToString()))
{
sql.SqlScript.Append(" ELSE NULL END ) ");
}
else
{
ODAParameter paramSub = new ODAParameter();
paramSub.ColumnName = _CaseCol.ODAColumnName;
paramSub.ParamsName = ODAParameter.ODAParamsMark + _Cmd.GetAlias();
paramSub.ParamsValue = _CaseElseVal;
paramSub.Direction = System.Data.ParameterDirection.Input;
paramSub.Size = 2000;
paramSub.DBDataType = (_CaseElseVal is DateTime ? ODAdbType.ODatetime : _CaseElseVal.GetType().IsPrimitive ? ODAdbType.ODecimal : ODAdbType.OVarchar);
sql.SqlScript.Append(" ELSE ").Append(paramSub.ParamsName).Append(" END ) ");
sql.ParamList.Add(paramSub);
}
this._FuncName = "";
return(sql);
}