/// <summary>
/// 将QueryCondition转换成Where SQL Script
/// </summary>
/// <param name="table">表名</param>
/// <param name="pk">主键字段</param>
/// <param name="parms">参数列表</param>
/// <param name="isAddOrder">是否要排序</param>
/// <returns></returns>
public string ParseWhereSql(string table, string pk, out SqlParameter[] parms, bool isAddOrder = true)
{
int totalParms = 0;
totalParms += _conditions.Count;
//条件数即参数个数
foreach (OrCondition orc in _orConditions)
{
totalParms += orc.Conditions.Count;
}
parms = new SqlParameter[totalParms];
#region 验证条件
if (string.IsNullOrEmpty(table))
{
throw new ApplicationException("必须设置查询的表名！");
}
#endregion
StringBuilder sb = new StringBuilder();
#region 普通条件
int current = 0;
foreach (Condition c in _conditions)
{
if (c.Op == QueryOperator.In)
{
sb.Append(" AND ").Append(c.FieldName).AppendFormat(" IN ({0})", c.Value);
continue;
}
if (c.Op == QueryOperator.Like)
{
sb.Append(" AND ").Append(c.FieldName).Append(" LIKE '%'+@PARM" + current + "+'%'");
}
else
{
sb.Append(" AND ").Append(c.FieldName).Append(QueryOperatorManager.GetOperator(c.Op) + "@PARM").
Append(current);
}
SqlParameter parm = new SqlParameter("@PARM" + current, c.Value);
parms[current] = parm;
current++;
}
#endregion
#region 或条件
int orCount = 0;
if (_orConditions.Count > 0)
{
sb.Append(" AND (");
foreach (OrCondition orc in _orConditions)
{
if (orc.Conditions.Count > 0)
{
foreach (Condition c in orc.Conditions)
{
if (orCount > 0)
{
sb.Append(" OR ");
}
if (c.Op == QueryOperator.In)
{
sb.Append(c.FieldName).AppendFormat(" IN ({0})", c.Value);
continue;
}
if (c.Op == QueryOperator.Like)
{
sb.Append(c.FieldName).Append(" LIKE '%'+@PARM" + current + "+'%'");
}
else
{
sb.Append(c.FieldName).Append(QueryOperatorManager.GetOperator(c.Op) + "@PARM").Append(
current);
}
orCount++;
SqlParameter parm = new SqlParameter("@PARM" + current, c.Value);
parms[current] = parm;
current++;
}
}
}
sb.Append(")");
}
#endregion
#region 排序
if (!_getCount)
{
if (_orderBys.Count > 0 && isAddOrder)
{
int i = 0;
sb.Append(" ORDER BY ");
foreach (KeyValuePair <string, bool> order in _orderBys)
{
if (i > 0)
{
sb.Append(",");
}
sb.Append(order.Key).Append(" ").Append(order.Value ? "ASC" : "DESC");
i++;
}
}
}
#endregion
return(sb.ToString());
}
private string ParseJoinSql(out SqlParameter[] parms)
{
int totalParms = 0;
totalParms += _conditions.Count;
//条件数即参数个数
foreach (OrCondition orc in _orConditions)
{
totalParms += orc.Conditions.Count;
}
parms = new SqlParameter[totalParms];
#region 验证条件
if (string.IsNullOrEmpty(_joinMaster))
{
throw new ApplicationException("多表查询请通过SetJoinMaster(table)设置联查主表的表名！");
}
if (_joinTables.Count == 0)
{
throw new ApplicationException("多表查询请通过AddJoinTable(table,JoinOnCondition)设置需要联查的表名！");
}
if (SelectFields.Count == 0)
{
throw new ApplicationException("多表查询请通过AddSelectFields(table,field)方法设置需要查询的字段名！");
}
#endregion
StringBuilder sb = new StringBuilder();
if (_po == null)
{
sb.Append("SELECT ");
if (_getCount)
{
sb.Append("COUNT(*)");
}
else
{
string fields = GetSelectFields();
sb.Append(fields);
}
sb.Append(" FROM " + _joinMaster + " " + _joinMaster.ToLower());
foreach (KeyValuePair <string, JoinOnCondition> kvp in _joinTables)
{
sb.Append(" " + TableJoinModeManager.GetJoinMode(kvp.Value.Mode) + " " + kvp.Key + " " + kvp.Key.ToLower());
sb.Append(" on");
sb.Append(" " + kvp.Value.Table1.ToLower() + "." + kvp.Value.Table1Field + "=" + kvp.Value.Table2.ToLower() + "." + kvp.Value.Table2Field);
}
sb.Append(" WHERE 1=1 ");
}
else
{
if (string.IsNullOrEmpty(_joinMasterPk))
{
throw new ApplicationException("分页查询请通过SetJoinMasterPK(pk)方法设置主表的主键！");
}
sb.Append("DECLARE @indextable table(TCWANGQIANIndexTableId int identity(1,1) PRIMARY KEY,TCWANGQIANIndexTableNid int);");
sb.AppendFormat("INSERT INTO @indextable(TCWANGQIANIndexTableNid) SELECT {0} FROM {1} WITH(nolock) WHERE 1=1", _joinMasterPk, _joinMaster);
}
#region 普通条件
int current = 0;
foreach (Condition c in _conditions)
{
if (c.Op == QueryOperator.In)
{
sb.Append(" AND ").Append(c.FieldName).AppendFormat(" IN ({0})", c.Value);
continue;
}
if (c.Op == QueryOperator.Like)
{
sb.Append(" AND ").Append(c.FieldName).Append(" LIKE '%'+@PARM" + current + "+'%'");
}
else
{
sb.Append(" AND ").Append(c.FieldName).Append(QueryOperatorManager.GetOperator(c.Op) + "@PARM").Append(current);
}
SqlParameter parm = new SqlParameter("@PARM" + current, c.Value);
parms[current] = parm;
current++;
}
#endregion
#region 或条件
foreach (OrCondition orc in _orConditions)
{
if (orc.Conditions.Count > 0)
{
sb.Append(" AND (");
int i = 0;
foreach (Condition c in orc.Conditions)
{
if (i > 0)
{
sb.Append(" OR ");
}
if (c.Op == QueryOperator.In)
{
sb.Append(c.FieldName).AppendFormat(" IN ({0})", c.Value);
continue;
}
if (c.Op == QueryOperator.Like)
{
sb.Append(c.FieldName).Append(" LIKE '%'+@PARM" + current + "+'%'");
}
else
{
sb.Append(c.FieldName).Append(QueryOperatorManager.GetOperator(c.Op) + "@PARM").Append(current);
}
i++;
SqlParameter parm = new SqlParameter("@PARM" + current, c.Value);
parms[current] = parm;
current++;
}
sb.Append(")");
}
}
#endregion
#region 排序
if (!_getCount)
{
if (_orderBys.Count > 0)
{
int i = 0;
sb.Append(" ORDER BY ");
foreach (KeyValuePair <string, bool> order in _orderBys)
{
if (i > 0)
{
sb.Append(",");
}
sb.Append(order.Key).Append(" ").Append(order.Value ? "ASC" : "DESC");
i++;
}
}
}
#endregion
if (_po != null)
{
sb.Append(";SELECT recrowcount=@@ROWCOUNT;");
sb.AppendFormat("SELECT {0} FROM @indextable as t0 JOIN {1} AS t1 WITH (nolock) ON (t0.TCWANGQIANIndexTableNid = t1.{2} AND t0.TCWANGQIANIndexTableId > {3} AND t0.TCWANGQIANIndexTableId <= {4})", GetSelectFields().ToLower().Replace(_joinMaster.ToLower() + ".", "t1."), _joinMaster, _joinMasterPk, (_po.CurrentPage - 1) * _po.PageSize, _po.CurrentPage * _po.PageSize);
}
return(sb.ToString());
}
/// <summary>
/// 转为为Where条件
/// </summary>
/// <param name="parms"></param>
/// <returns></returns>
private string GetWhere(SqlParameter[] parms)
{
StringBuilder sb = new StringBuilder();
#region 普通条件
var current = 0;
foreach (var c in _conditions)
{
if (c.Op == QueryOperator.In)
{
sb.Append(" AND ").Append(c.FieldName).AppendFormat(" IN ({0})", c.Value);
continue;
}
if (c.Op == QueryOperator.Like)
{
sb.Append(" AND ").Append(c.FieldName).Append(" LIKE '%'+@PARM" + current + "+'%'");
}
else
{
sb.Append(" AND ").Append(c.FieldName).Append(QueryOperatorManager.GetOperator(c.Op) + "@PARM").
Append(current);
}
var parm = new SqlParameter("@PARM" + current, c.Value);
parms[current] = parm;
current++;
}
#endregion
#region 或条件
var orCount = 0;
if (_orConditions.Count > 0)
{
sb.Append(" AND (");
foreach (var orc in _orConditions)
{
if (orc.Conditions.Count > 0)
{
foreach (var c in orc.Conditions)
{
if (orCount > 0)
{
sb.Append(" OR ");
}
if (c.Op == QueryOperator.In)
{
sb.Append(c.FieldName).AppendFormat(" IN ({0})", c.Value);
continue;
}
if (c.Op == QueryOperator.Like)
{
sb.Append(c.FieldName).Append(" LIKE '%'+@PARM" + current + "+'%'");
}
else
{
sb.Append(c.FieldName).Append(QueryOperatorManager.GetOperator(c.Op) + "@PARM").Append(
current);
}
orCount++;
var parm = new SqlParameter("@PARM" + current, c.Value);
parms[current] = parm;
current++;
}
}
}
sb.Append(")");
}
#endregion
return(sb.ToString());
}