參數化拼接in查詢條件，個人備份

標籤：資料   os   for   io   sql   new   

/// <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);
        }