/// <summary>
/// 获取查询语主中 form 字符串及变量
/// </summary>
/// <param name="SubSql"></param>
/// <returns></returns>
protected virtual ODAScript GetFromSubString()
{
var sql = new ODAScript();
sql.SqlScript.Append(" FROM ");
var cmdsql = GetCmdSql();
sql.Merge(cmdsql);
string AliasSql = String.IsNullOrWhiteSpace(Alias) ? "" : " " + Alias;
sql.SqlScript.Append(AliasSql);
for (int i = 0; i < _JoinCmd.Count; i++)
{
sql.SqlScript.Append(_JoinCmd[i].JoinScript);
var join = _JoinCmd[i].JoinCmd.GetCmdSql();
sql.Merge(join);
sql.SqlScript.Append(" ").Append(_JoinCmd[i].JoinCmd.Alias);
var wsql = GetWhereSubSql(_JoinCmd[i].JoinCmd._WhereList, " AND ");
if (wsql.SqlScript.Length > 0)
{
sql.SqlScript.Append(" ON ");
sql.Merge(wsql);
}
}
for (int i = 0; i < _ListCmd.Count; i++)
{
var lsql = _ListCmd[i].GetCmdSql();
sql.SqlScript.Append(",");
sql.Merge(lsql).SqlScript.Append(" ").Append(_ListCmd[i].Alias);
}
return(sql);
}
/// <summary>
/// 生成删除语
/// </summary>
/// <param name="Sql"></param>
/// <returns></returns>
protected virtual ODAScript GetDeleteSql()
{
ODAScript sql = new ODAScript()
{
ScriptType = SQLType.Delete,
DataBaseId = this.DataBaseId
};
sql.TableList.Add(this.DBObjectMap);
sql.SqlScript.Append("DELETE FROM ").Append(this.DBObjectMap);
if (!string.IsNullOrWhiteSpace(Alias))
{
Alias = "";//SQLite 不支持delete from 别名
//sql.SqlScript.Append(" ").Append(Alias);
}
if (_WhereList.Count > 0 || _OrList.Count > 0)
{
sql.SqlScript.Append(" WHERE ");
var asql = GetWhereSubSql(_WhereList, " AND ");
sql.Merge(asql);
if (_OrList.Count > 0)
{
if (_WhereList.Count > 0)
{
sql.SqlScript.Append(" OR ");
}
var osql = GetWhereSubSql(_OrList, " OR ");
sql.Merge(osql);
}
}
return(sql);
}
/// <summary>
/// 获取在 where 语句中使用的函表达式及其参数
/// </summary>
/// <param name="ConIndex">此参数无效</param>
/// <param name="SubSql">函数表达式</param>
/// <returns>函数表达式的参数</returns>
protected override ODAScript GetWhereSubstring()
{
ODAScript sql = new ODAScript();
if (_FuncType == Func.Exists)
{
var sSql = _FuncCmd.GetSelectSql(_FunColumnList.ToArray());
sql.Merge(sSql);
sql.SqlScript.Clear();
sql.SqlScript.Append(_FuncName).Append(" ( ").Append(sSql.SqlScript.ToString()).Append(")");
return(sql);
}
else if (_FuncType == Func.Normal) ///形如：having UserFunc(@params1,@params2) > 0
{
var wSql = base.GetWhereSubstring();
var fSql = this.GetNormalFunctionSql();
wSql.SqlScript.Replace(_FuncName, fSql.SqlScript.ToString());
sql.Merge(fSql);
sql.SqlScript.Clear();
sql.SqlScript.Append(wSql.SqlScript.ToString());
return(sql);
}
else if (_FuncType == Func.Express)
{
var wSql = base.GetWhereSubstring(); ///形如：having count(*) > 0
wSql.ParamList.InsertRange(0, _ExpressPrms);
return(wSql);
}
else
{
throw new ODAException(40007, string.Format("{0} Function not for Select", _FuncName));
}
}
/// <summary>
/// 生成统计数据行数的SQL
/// </summary>
/// <param name="CountSql"></param>
/// <param name="Col"></param>
/// <returns></returns>
protected virtual ODAScript GetCountSql(IODAColumns Col)
{
if (_Groupby.Count > 0 || _Having.Count > 0)
{
throw new ODAException(10006, "Do not count the [Group by] cmd,You should probably use [ ToView(Columns).Count()] instead.");
}
ODAScript sql = new ODAScript()
{
ScriptType = SQLType.Select,
DataBaseId = this.DataBaseId,
};
if (_Distinct)
{
sql.SqlScript.Append("SELECT COUNT(DISTINCT ");
}
else
{
sql.SqlScript.Append("SELECT COUNT(");
}
if (System.Object.ReferenceEquals(Col, null))
{
sql.SqlScript.Append("*");
}
else
{
string SubSelectSql = "";
ODAParameter[] SubSelectPrms = GetSelectColumns(",", out SubSelectSql, Col);
sql.SqlScript.Append(SubSelectSql);
if (SubSelectPrms != null && SubSelectPrms.Length > 0)
{
sql.ParamList.AddRange(SubSelectPrms);
}
}
sql.SqlScript.Append(") AS TOTAL_RECORD");
var fSql = GetFromSubString();
sql.Merge(fSql);
if (_WhereList.Count > 0 || _OrList.Count > 0)
{
sql.SqlScript.Append(" WHERE ");
var asql = GetWhereSubSql(_WhereList, " AND ");
sql.Merge(asql);
if (_OrList.Count > 0)
{
if (_WhereList.Count > 0)
{
sql.SqlScript.Append(" OR ");
}
var osql = GetWhereSubSql(_OrList, " OR ");
sql.Merge(osql);
}
}
return(sql);
}
/// <summary>
/// 生成update语句
/// </summary>
/// <param name="Sql">脚本</param>
/// <param name="Cols">变量列表及变操作符</param>
/// <returns>变量列表</returns>
protected virtual ODAScript GetUpdateSql(params IODAColumns[] Cols)
{
if (Cols == null || Cols.Length == 0)
{
throw new ODAException(10019, "NO Columns for update!");
}
this.Alias = "";
ODAScript sql = new ODAScript()
{
ScriptType = SQLType.Update,
DataBaseId = this.DataBaseId
};
sql.TableList.Add(this.DBObjectMap);
sql.SqlScript.Append("UPDATE ").Append(this.DBObjectMap).Append(" SET ");
string Column = "";
for (int i = 0; i < Cols.Length; i++)
{
string ColumnTmp = "";
ODAParameter[] P = Cols[i].GetUpdateSubstring(out ColumnTmp);
if (P != null)
{
sql.ParamList.AddRange(P);
}
Column += ColumnTmp + ",";
}
sql.SqlScript.Append(Column.Remove(Column.Length - 1, 1));
if (_WhereList.Count > 0 || _OrList.Count > 0)
{
sql.SqlScript.Append(" WHERE ");
var asql = GetWhereSubSql(_WhereList, " AND ");
sql.Merge(asql);
if (_OrList.Count > 0)
{
if (_WhereList.Count > 0)
{
sql.SqlScript.Append(" OR ");
}
var osql = GetWhereSubSql(_OrList, " OR ");
sql.Merge(osql);
}
}
return(sql);
}
/// <summary>
/// 获取查询语主中 where 字符串有变量
/// </summary>
/// <param name="ConditionList"></param>
/// <param name="RelationStr"></param>
/// <param name="SubSql"></param>
/// <returns></returns>
protected virtual ODAScript GetWhereSubSql(List <IODAColumns> ConditionList, string RelationStr)
{
var sql = new ODAScript();
if (ConditionList == null || ConditionList.Count == 0)
{
return(sql);
}
List <ODAParameter> ParamsList = new List <ODAParameter>();
foreach (IODAColumns W in ConditionList)
{
var sub = W.GetWhereSubstring();
sql.Merge(sub);
sql.SqlScript.Append(RelationStr);
}
if (sql.SqlScript.Length > 0)
{
sql.SqlScript.Remove(sql.SqlScript.Length - RelationStr.Length, RelationStr.Length);
}
return(sql);
}
/// <summary>
/// insert () select
/// </summary>
/// <param name="SelectCmd"></param>
/// <param name="Columns">select的字段</param>
/// <returns></returns>
public virtual bool Insert(ODACmd SelectCmd, params IODAColumns[] Columns)
{
try
{
var sql = new ODAScript()
{
ScriptType = SQLType.Insert,
};
sql.SqlScript.Append("INSERT INTO ").Append(this.CmdName).Append("(");
string Column = "";
for (int i = 0; i < Columns.Length; i++)
{
if (string.IsNullOrWhiteSpace(Columns[i].AliasName))
{
Column += Columns[i].ColumnName + ",";
}
else
{
Column += Columns[i].AliasName + ",";
}
}
sql.SqlScript.Append(Column.Remove(Column.Length - 1, 1)).Append(") ");
var sSql = SelectCmd.GetSelectSql(Columns);
sql.Merge(sSql);
var db = this.GetDBAccess(sql);
if (db == null)
{
throw new ODAException(10016, "ODACmd Insert 没有执行程序");
}
var prms = sql.ParamList.ToArray();
return(db.ExecuteSQL(sql.SqlScript.ToString(), prms) > 0);
}
finally
{
this.Clear();
}
}
/// <summary>
/// 生成查询语句
/// </summary>
/// <param name="SelectSql">sql脚本</param>
/// <param name="Cols">变量列表及变操作符</param>
/// <returns>变量列表</returns>
public virtual ODAScript GetSelectSql(params IODAColumns[] Cols)
{
ODAScript sql = new ODAScript()
{
ScriptType = SQLType.Select,
};
if (_Distinct)
{
sql.SqlScript.Append("SELECT DISTINCT ");
}
else
{
sql.SqlScript.Append("SELECT ");
}
if (Cols == null || Cols.Length == 0)
{
sql.SqlScript.Append(" * ");
}
else
{
string SubSelectSql = "";
ODAParameter[] SubSelectPrms = GetSelectColumns(",", out SubSelectSql, Cols);
sql.SqlScript.Append(SubSelectSql);
if (SubSelectPrms != null && SubSelectPrms.Length > 0)
{
sql.ParamList.AddRange(SubSelectPrms);
}
}
var fSql = this.GetFromSubString();
sql.Merge(fSql);
if (_WhereList.Count > 0 || _OrList.Count > 0)
{
sql.SqlScript.Append(" WHERE ");
var asql = GetWhereSubSql(_WhereList, " AND ");
sql.Merge(asql);
if (_OrList.Count > 0)
{
if (_WhereList.Count > 0)
{
sql.SqlScript.Append(" OR ");
}
var osql = GetWhereSubSql(_OrList, " OR ");
sql.Merge(osql);
}
}
if (_Groupby.Count > 0)
{
var gy = GetGroupByColumns(_Groupby.ToArray());
sql.SqlScript.Append(" GROUP BY ");
sql.Merge(gy);
}
if (_Having.Count > 0)
{
var hsql = GetWhereSubSql(_Having, " AND ");
sql.SqlScript.Append(" HAVING ");
sql.Merge(hsql);
}
if (_UnionCmd.Count > 0)
{
for (int i = 0; i < _UnionCmd.Count; i++)
{
sql.SqlScript.AppendLine(" ");
sql.SqlScript.AppendLine(_UnionCmd[i].JoinScript);
var Union = _UnionCmd[i].UnionCmd.GetCmdSql();
Union.SqlScript.Remove(0, 1);
Union.SqlScript.Remove(Union.SqlScript.Length - 1, 1);
sql.Merge(Union);
}
}
if (_Orderby.Count > 0)
{
var oy = GetOrderbyColumns(_Orderby.ToArray());
sql.OrderBy.Clear();
sql.OrderBy.Append(" ORDER BY ");
sql.OrderBy.Append(oy.OrderBy.ToString());
sql.SqlScript.Append(sql.OrderBy.ToString());
sql.Merge(oy);
}
return(sql);
}
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);
}
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);
}