2023年6月21日发(作者：)

参数化拼接in查询条件，个⼈备份///

/// 查询数据报表 /// /// ⽇期范围尾 /// 分析⾓度 /// 派车任务模型 /// public DataTable GetVehDisReport(string TrueOutTimeTo, string anslyse, VehDispTaskModel model) { string sql = ;//存储sql语句头 StringBuilder sqlWhere = new StringBuilder();//存储查询条件的sql List listStr = new List();//⽤于动态存储参数最后转成sqlparameter[]即可 string[] arrLicensePlate = (',');//存储勾选的所有车牌 if (!OrEmpty(())) { //参数化拼接in('','','')查询语句 string sLicensePlateCondition = " AND LicencePlate in ("; for (int i = 0; i < ; i++) { if (!OrEmpty(arrLicensePlate[i])) { sLicensePlateCondition += "@Plate" + i+","; ( new SqlParameter("@Plate" + i, arrLicensePlate[i])); } } sLicensePlateCondition = d(','); sLicensePlateCondition += ")"; //加⼊查询条件 (sLicensePlateCondition); } string[] arrDriverId = (',');//存储所有勾选的司机⼯号 if (!OrEmpty(Id)) { //参数化拼接in('','','')查询语句 string sDriverIdCondition = "AND DriverId in ("; for (int i = 0; i < ; i++) { if (!OrEmpty(arrDriverId[i])) { sDriverIdCondition += "@DriverId" + i + ","; (new SqlParameter("@DriverId" + i, arrDriverId[i])); } } sDriverIdCondition = d(','); sDriverIdCondition += ")"; //加⼊查询条件 (sDriverIdCondition); } if (!OrEmpty(ng()) && !OrEmpty(TrueOutTimeTo)) { (new SqlParameter("@OutTimeFrom", pTime)); (new SqlParameter("@OutTimeTo",Time(TrueOutTimeTo))); (" AND VehDispTime between cast(@OutTimeFrom as datetime) and cast(@OutTimeTo as datetime)"); } if (anslyse == "1")//如果分析⾓度为1说明是司机,否则为公务车 { sql = (@"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 = (@"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 = y(); return aSet(sql, param); }

2023年6月21日发(作者：)

参数化拼接in查询条件，个⼈备份///

/// 查询数据报表 /// /// ⽇期范围尾 /// 分析⾓度 /// 派车任务模型 /// public DataTable GetVehDisReport(string TrueOutTimeTo, string anslyse, VehDispTaskModel model) { string sql = ;//存储sql语句头 StringBuilder sqlWhere = new StringBuilder();//存储查询条件的sql List listStr = new List();//⽤于动态存储参数最后转成sqlparameter[]即可 string[] arrLicensePlate = (',');//存储勾选的所有车牌 if (!OrEmpty(())) { //参数化拼接in('','','')查询语句 string sLicensePlateCondition = " AND LicencePlate in ("; for (int i = 0; i < ; i++) { if (!OrEmpty(arrLicensePlate[i])) { sLicensePlateCondition += "@Plate" + i+","; ( new SqlParameter("@Plate" + i, arrLicensePlate[i])); } } sLicensePlateCondition = d(','); sLicensePlateCondition += ")"; //加⼊查询条件 (sLicensePlateCondition); } string[] arrDriverId = (',');//存储所有勾选的司机⼯号 if (!OrEmpty(Id)) { //参数化拼接in('','','')查询语句 string sDriverIdCondition = "AND DriverId in ("; for (int i = 0; i < ; i++) { if (!OrEmpty(arrDriverId[i])) { sDriverIdCondition += "@DriverId" + i + ","; (new SqlParameter("@DriverId" + i, arrDriverId[i])); } } sDriverIdCondition = d(','); sDriverIdCondition += ")"; //加⼊查询条件 (sDriverIdCondition); } if (!OrEmpty(ng()) && !OrEmpty(TrueOutTimeTo)) { (new SqlParameter("@OutTimeFrom", pTime)); (new SqlParameter("@OutTimeTo",Time(TrueOutTimeTo))); (" AND VehDispTime between cast(@OutTimeFrom as datetime) and cast(@OutTimeTo as datetime)"); } if (anslyse == "1")//如果分析⾓度为1说明是司机,否则为公务车 { sql = (@"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 = (@"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 = y(); return aSet(sql, param); }