/// <summary>
/// 在数据库中Procedure
/// </summary>
/// <param name="Params">存储过程的参数及其值</param>
/// <returns></returns>
public virtual DataSet Procedure(params IODAColumns[] Params)
{
try
{
string sqlScript = "";
var prms = this.GetProcedureSql(out sqlScript, Params);
var sql = new ODAScript()
{
ScriptType = SQLType.Procedure,
};
sql.ParamList.AddRange(prms);
sql.TableList.Add(sqlScript);
var db = this.GetDBAccess(sql);
if (db == null)
throw new ODAException(10017, "ODACmd Procedure 没有执行程序");
return db.ExecuteProcedure(sqlScript, prms);
}
finally
{
this.Clear();
}
}
/// <summary>
/// 查询数据并转换为对象列表
/// </summary>
/// <typeparam name="T">对象的类型</typeparam>
/// <param name="Cols">查询的字段，可为空，空则返回所有字段</param>
/// <returns></returns>
public virtual List<T> Select<T>(params IODAColumns[] Cols) where T : class
{
try
{
ODAScript sql = this.GetSelectSql(Cols);
IDBAccess db = this.GetDBAccess(sql);
if (db == null)
throw new ODAException(10009, "ODACmd Select 没有执行程序");
var prms = sql.ParamList.ToArray();
if (string.IsNullOrEmpty(_StartWithExpress) || string.IsNullOrEmpty(_ConnectByParent) || string.IsNullOrEmpty(_PriorChild))
{
return db.Select<T>(sql.SqlScript.ToString(), prms);
}
else
{
return db.Select<T>(sql.SqlScript.ToString(), prms, _StartWithExpress, _ConnectByParent, _PriorChild, _ConnectColumn, _ConnectStr, _MaxLevel);
}
}
finally
{
this.Clear();
}
}
/// <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;
}
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);
}
/// <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);
}
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);
}