public DataTable GetCourseCountList(CourseQuery query, out int totalCount) { StringBuilder sql = new StringBuilder(); StringBuilder sqlFrom = new StringBuilder(); StringBuilder sqlWhere = new StringBuilder(); totalCount = 0; try { query.Replace4MySQL(); sql.Append(@"SELECT c.course_id,c.course_name ,pi.item_id,p.brand_id,pi.spec_id_1,pi.spec_id_2,cd.start_date,cd.end_date "); sqlFrom.Append(@" FROM course c LEFT JOIN course_detail cd ON cd.course_id=c.course_id LEFT JOIN course_product cp ON cp.course_id=c.course_id LEFT JOIN product p ON p.product_id=cp.product_id LEFT JOIN product_item pi ON pi.product_id=p.product_id "); // sqlFrom.Append(@" FROM product_item pi // LEFT JOIN product p ON p.product_id=pi.product_id // LEFT JOIN course_product cp ON cp.product_id=p.product_id // LEFT JOIN course c ON c.course_id=cp.course_id // LEFT JOIN course_detail cd ON cd.course_id=c.course_id"); if (!string.IsNullOrEmpty(query.Vendor_Name_Simple)) { sqlWhere.AppendFormat("and v.vendor_name_simple like '%{0}%' ", query.Vendor_Name_Simple); } if (query.Course_Id != 0) { sqlWhere.AppendFormat("and c.course_id='{0}' ", query.Course_Id); } if (!string.IsNullOrEmpty(query.Course_Name)) { sqlWhere.AppendFormat("and c.course_name like '%{0}%' ", query.Course_Name); } if (query.Start_Date != DateTime.MinValue) { sqlWhere.AppendFormat("and cd.start_date >='{0}' ", query.Start_Date); } if (query.End_Date != DateTime.MinValue) { sqlWhere.AppendFormat("and cd.end_date <='{0}' ", query.End_Date); } if (sqlWhere.Length != 0) { sqlFrom.Append(" WHERE " + sqlWhere.ToString().TrimStart().Remove(0, 3)); } if (query.IsPage) { DataTable _dtCount = _access.getDataTable("select count(c.course_id) as totalCount " + sqlFrom.ToString()); if (_dtCount.Rows.Count > 0) { totalCount = Convert.ToInt32(_dtCount.Rows[0]["totalCount"]); } sqlFrom.AppendFormat("LIMIT {0},{1} ;", query.Start, query.Limit); } sql.Append(sqlFrom.ToString()); return _access.getDataTable(sql.ToString()); } catch (Exception ex) { throw new Exception("TicketMasterDao-->GetCourseCountList-->" + sql.ToString() + ex.Message, ex); } }