参数查询的like
Codepublic DataSet GetList(Hashtable hs)
{
if (Object.Equals(hs, null) || hs.Count <= 0)
{
return null;
}
StringBuilder strSql = new StringBuilder();
strSql.Append(" SELECT ");
strSql.Append(" [OrderID],[UserHouseID],[UserEmpNO],[UserID],[ContacterName],[Address],[Mobile],[Phone], ");
strSql.Append(" [Email],[CustomerStatus],[DealStatus],[DeliverStatus],[Archiving],[MoneyTotal],[MoneyAllot], ");
strSql.Append(" [MoneyStock],[GoodsNum],[OrderTime],[Remark],[HouseID],[EmpNO],[Pid],[Seter],[SetTime] ");
strSql.Append("FROM [Order] ");
strSql.Append("WHERE 1 = 1 ");
//foreach (DictionaryEntry deKeyValue in hs)
//{
// if (deKeyValue.Key.ToString().ToLower() != "ordertimebgn" && deKeyValue.Key.ToString().ToLower() != "ordertimeend")
// {
// if (deKeyValue.Key.ToString().ToLower() == "dealstatus" ||
// deKeyValue.Key.ToString().ToLower() == "deliverstatus" ||
// deKeyValue.Key.ToString().ToLower() == "archiving")
// {
// strSql.Append(" and ");
// strSql.Append(deKeyValue.Key.ToString());
// strSql.Append(" = '").Append(deKeyValue.Value.ToString()).Append("' ");
// }
// else
// {
// strSql.Append(" and ( ");
// strSql.Append(deKeyValue.Key.ToString());
// strSql.Append(" like '%").Append(deKeyValue.Value.ToString()).Append("%' ) ");
// }
// }
// else
// {
// if (deKeyValue.Key.ToString().ToLower() != "ordertimebgn")
// {
// strSql.Append(" and convert(nvarchar(10),OrderTime,120) >= ");
// strSql.Append(" convert(nvarchar(10),'").Append(Convert.ToDateTime(deKeyValue.Value)).Append("',120)");
// strSql.Append(" ");
// }
// if (deKeyValue.Key.ToString().ToLower() != "ordertimeend")
// {
// strSql.Append(" and convert(nvarchar(10),OrderTime,120) <= ");
// strSql.Append(" convert(nvarchar(10),'").Append(Convert.ToDateTime(deKeyValue.Value)).Append("',120)");
// strSql.Append(" ");
// }
// }
//}
int hsCount = hs.Count;
if (hsCount > 0)
{
SqlParameter[] parameters = new SqlParameter[hsCount];
//定义一个索引
int index = 0;
foreach (DictionaryEntry deKeyValue in hs)
{
if (deKeyValue.Key.ToString().ToLower() != "ordertimebgn" && deKeyValue.Key.ToString().ToLower() != "ordertimeend")
{
if (deKeyValue.Key.ToString().ToLower() == "dealstatus" ||
deKeyValue.Key.ToString().ToLower() == "deliverstatus" ||
deKeyValue.Key.ToString().ToLower() == "archiving")
{
strSql.Append(" and ").Append(deKeyValue.Key.ToString()).Append(" = @").Append(deKeyValue.Key.ToString());
parameters[index] = new SqlParameter("@" + deKeyValue.Key.ToString(), SqlDbType.NVarChar, 1);
parameters[index].Value = deKeyValue.Value.ToString();
}
else
{
strSql.Append(" and (").Append(deKeyValue.Key.ToString()).Append(" like N'%'+@").Append(deKeyValue.Key.ToString()).Append("+'%' ) ");
parameters[index] = new SqlParameter("@" + deKeyValue.Key.ToString(), SqlDbType.NVarChar, 50);
parameters[index].Value = deKeyValue.Value.ToString();
}
}
else
{
if (deKeyValue.Key.ToString().ToLower() != "ordertimebgn")
{
strSql.Append(" and convert(nvarchar(10),OrderTime,120) >= ").Append(" convert(nvarchar(10),@OrderTimeBgn,120) ");
parameters[index] = new SqlParameter("@OrderTimeBgn", SqlDbType.DateTime);
parameters[index].Value = Convert.ToDateTime(deKeyValue.Value);
}
if (deKeyValue.Key.ToString().ToLower() != "ordertimeend")
{
strSql.Append(" and convert(nvarchar(10),OrderTime,120) <= ").Append(" convert(nvarchar(10),@OrderTimeEnd,120) ");
parameters[index] = new SqlParameter("@OrderTimeEnd", SqlDbType.DateTime);
parameters[index].Value = Convert.ToDateTime(deKeyValue.Value);
}
}
//索引增加
index++;
}
return ExecParamsQuery.QueryData(strSql.ToString(), parameters);
}
//return ExecSqlQuery.QueryData(strSql.ToString());
return null;
