首页 > 代码库 > 参数化拼接in查询条件,个人备份
参数化拼接in查询条件,个人备份
/// <summary>
/// 查询数据报表
/// </summary>
/// <param name="TrueOutTimeTo">日期范围尾</param>
/// <param name="anslyse">分析角度</param>
/// <param name="model">派车任务模型</param>
/// <returns></returns>
public DataTable GetVehDisReport(string TrueOutTimeTo, string anslyse, VehDispTaskModel model)
{
string sql = string.Empty;//存储sql语句头
StringBuilder sqlWhere = new StringBuilder();//存储查询条件的sql
List<SqlParameter> listStr = new List<SqlParameter>();//用于动态存储参数最后转成sqlparameter[]即可
string[] arrLicensePlate = model.LicencePlate.Split(‘,‘);//存储勾选的所有车牌
if (!string.IsNullOrEmpty(model.LicencePlate.Trim()))
{
//参数化拼接in(‘‘,‘‘,‘‘)查询语句
string sLicensePlateCondition = " AND LicencePlate in (";
for (int i = 0; i < arrLicensePlate.Length; i++)
{
if (!string.IsNullOrEmpty(arrLicensePlate[i]))
{
sLicensePlateCondition += "@Plate" + i+",";
listStr.Add( new SqlParameter("@Plate" + i, arrLicensePlate[i]));
}
}
sLicensePlateCondition = sLicensePlateCondition.TrimEnd(‘,‘);
sLicensePlateCondition += ")";
//加入查询条件
sqlWhere.Append(sLicensePlateCondition);
}
string[] arrDriverId = model.DriverId.Split(‘,‘);//存储所有勾选的司机工号
if (!string.IsNullOrEmpty(model.DriverId))
{
//参数化拼接in(‘‘,‘‘,‘‘)查询语句
string sDriverIdCondition = "AND DriverId in (";
for (int i = 0; i < arrDriverId.Length; i++)
{
if (!string.IsNullOrEmpty(arrDriverId[i]))
{
sDriverIdCondition += "@DriverId" + i + ",";
listStr.Add(new SqlParameter("@DriverId" + i, arrDriverId[i]));
}
}
sDriverIdCondition = sDriverIdCondition.TrimEnd(‘,‘);
sDriverIdCondition += ")";
//加入查询条件
sqlWhere.Append(sDriverIdCondition);
}
if (!string.IsNullOrEmpty(model.VehDispTime.ToString()) && !string.IsNullOrEmpty(TrueOutTimeTo))
{
listStr.Add(new SqlParameter("@OutTimeFrom", model.VehDispTime));
listStr.Add(new SqlParameter("@OutTimeTo",Convert.ToDateTime(TrueOutTimeTo)));
sqlWhere.Append(" AND VehDispTime between cast(@OutTimeFrom as datetime) and cast(@OutTimeTo as datetime)");
}
if (anslyse == "1")//如果分析角度为1说明是司机,否则为公务车
{
sql = string.Format(@"select DriverId as ‘司机工号DriverId‘,DriverName as ‘司机姓名DriverName‘,Convert(varchar(10),VehDispTime,120)as ‘实际派车日期VehDispTime‘,
round(sum(Datediff(hour,TrueoutTime,TrueBackTime)),0) as ‘派车时数Hours‘ from [dbo].[vwVehDispTask]
where Status = ‘Finished‘" + "{0}" + "group by DriverId,DriverName,Convert(varchar(10),VehDispTime,120)", sqlWhere);
}
else
{
sql = string.Format(@"select LicencePlate as ‘车牌LicencePlate‘,VehModel as ‘车型VehModel‘,Convert(varchar(10),VehDispTime,120)as ‘实际派车日期VehDispTime‘,
round(sum(Datediff(hour,TrueoutTime,TrueBackTime)),0) as ‘派车时数Hours‘ from [dbo].[vwVehDispTask]
where Status = ‘Finished‘" + "{0}" + "group by Convert(varchar(10),VehDispTime,120), LicencePlate,VehModel", sqlWhere);
}
SqlParameter[] param = listStr.ToArray();
return DBHelper.GetDataSet(sql, param);
}