mysql in查询 拼接_参数化拼接in查询条件，个人备份

///

/// 查询数据报表

///

/// 日期范围尾

/// 分析角度

/// 派车任务模型

///

public DataTable GetVehDisReport(string TrueOutTimeTo, string anslyse, VehDispTaskModel model)

{

string sql = string.Empty;//存储sql语句头

StringBuilder sqlWhere = new StringBuilder();//存储查询条件的sql

List listStr = new List();//用于动态存储参数最后转成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);

}