/// <summary>
/// 将 IDataItem 转换成 查询条件 SQL + SqlParameterCollection
/// </summary>
/// <param name="dataitem">数据项集合</param>
/// <param name="spc">返回 SQL 参数的集合</param>
/// <param name="searchSQL">返回查询条件 SQL 语句</param>
public static void GetSearchClause(this List <IDataItem> dataitem, SqlParameterCollection spc, ref string searchSQL)
{
GetSearchClause(dataitem, spc, ref searchSQL, true, true);
}
/// <summary>
/// 将 IDataItem 转换成 查询条件 SQL + SqlParameterCollection
/// </summary>
/// <param name="dataitem">数据项集合</param>
/// <param name="spc">返回 SQL 参数的集合</param>
/// <param name="searchSQL">返回查询条件 SQL 语句</param>
/// <param name="isAndRelation">dataitem与dataitem之间是否是And关系.true表示And关系,false表示Or关系.</param>
/// <param name="isAndRelation2">dataitem集合与其它集合之间是否是And关系.true表示And关系,false表示Or关系.</param>
/// <returns></returns>
public static void GetSearchClause(this List <IDataItem> dataitem, SqlParameterCollection spc, ref string searchSQL, bool isAndRelation, bool isAndRelation2)
{
if (dataitem == null)
{
return;
}
if (spc == null)
{
throw new Exception("参数：spc 不能为 null！");
}
string sql = "";
foreach (IDataItem di in dataitem)
{
if (di.ItemName.IsEmpty() || di.ItemData.IsEmpty())
{
continue;
}
string data = di.ItemData;
if (di.ItemType == DataType.Date)
{
switch (di.ItemRelation)
{
default:
data = data.ToDate2();
break;
case DataRelation.LessThan:
case DataRelation.LessThanOrEqual:
data = data.ToDate().ToString("yyyy-MM-dd 23:59:59");
break;
}
}
string parameterName = "@p" + (spc.Count + 1);
string temp = "";
switch (di.ItemRelation)
{
case DataRelation.Equal:
{
temp = di.ItemName + " = " + parameterName;
spc.Add(parameterName, data);
}
break;
case DataRelation.NotEqual:
{
temp = di.ItemName + " <> " + parameterName;
spc.Add(parameterName, data);
}
break;
case DataRelation.GreaterThan:
{
temp = di.ItemName + " > " + parameterName;
spc.Add(parameterName, data);
}
break;
case DataRelation.GreaterThanOrEqual:
{
temp = di.ItemName + " >= " + parameterName;
spc.Add(parameterName, data);
}
break;
case DataRelation.LessThan:
{
temp = di.ItemName + " < " + parameterName;
spc.Add(parameterName, data);
}
break;
case DataRelation.LessThanOrEqual:
{
temp = di.ItemName + " <= " + parameterName;
spc.Add(parameterName, data);
}
break;
case DataRelation.Like:
{
temp = di.ItemName + " like " + parameterName;
spc.Add(parameterName, "%" + data + "%");
}
break;
case DataRelation.LeftLike:
{
temp = di.ItemName + " like " + parameterName;
spc.Add(parameterName, "%" + data);
}
break;
case DataRelation.RightLike:
{
temp = di.ItemName + " like " + parameterName;
spc.Add(parameterName, data + "%");
}
break;
}
if (temp == "")
{
continue;
}
sql = (sql == "") ? temp : (isAndRelation ? sql + " and " + temp : sql + " or " + temp);
}
searchSQL += (sql == "") ? string.Empty : (isAndRelation2 ? " and (" + sql + ")" : " or (" + sql + ")");
}
/// <summary>
/// 执行 INSERT/DELETE/UPDATE SQL 语句, 返回受影响的行数,当前为 SELECT SQL 语句时，返回 -1
/// </summary>
/// <param name="commandSQL">INSERT/DELETE/UPDATE SQL 语句</param>
/// <param name="sqlParameterCollection">SQL 参数集合</param>
/// <returns>返回受影响的行数</returns>
public override int ExecuteNonQuerySql(string commandSQL, SqlParameterCollection sqlParameterCollection)
{
commandSQL = Bigdesk2010.Security.AntiSqlInjection.GetSafeSql(commandSQL);
return(this.ExecuteNonQuerySql2(commandSQL, sqlParameterCollection));
}
