C# SqlDatabase.ExecuteCount方法代码示例

        /// <summary>
        /// List data
        /// </summary>
        private void ShowData()
        {
            SqlDatabase db = new SqlDatabase();
            string sql = string.Empty;
            if (!Func.IsValid(ListSortExpression))
            {
                    ListSortExpression = "Modified";
                    ListSortDirection = SortDirection.Descending;
            }
            try
            {
                //Đếm số lượng record
                sql += " Select COUNT(ID) ";
                sql += " FROM BD_Provider";
                sql += " WHERE (Id IS NOT NULL) and DelFlag <> 1 and BuildingId = '" + Func.ParseString(Session["__BUILDINGID__"]) + "' and Dept = '"+ hidDept.Value+"'";

                int count = db.ExecuteCount(sql);
                sql = string.Empty;
                string sort = ListSortExpression + " " + (ListSortDirection == SortDirection.Ascending ? " asc " : " desc ");

                //Xuất ra toàn bộ nội dung theo Trang
                sql += " Select *, ROW_NUMBER() OVER(ORDER BY " + sort + ") as RowNum ";
                sql += " FROM BD_Provider";
                sql += " WHERE Id IS NOT NULL and DelFlag <> 1 and BuildingId = '" + Func.ParseString(Session["__BUILDINGID__"]) + "' and Dept = '" + hidDept.Value + "'";

                //Phân trang
                sql = " Select * FROM (" + sql + ") AS TMP ";
                sql += " WHERE RowNum BETWEEN @PageIndex*@PageSize + 1  AND (@PageIndex+1)*@PageSize ORDER BY RowNum ";

                //Thực hiện câu SQL

                SqlCommand cm = db.CreateCommand(sql);
                cm.Parameters.AddWithValue("@PageIndex", pager.CurrentPageIndex);
                cm.Parameters.AddWithValue("@PageSize", pager.PageSize);
                SqlDataAdapter da = new SqlDataAdapter(cm);
                DataSet ds = new DataSet();
                da.Fill(ds);
                db.Close();
                rptList.DataSource = ds.Tables[0].DefaultView;
                rptList.DataBind();
                pager.Count = count;                
            }
            catch (Exception ex)
            {
                ApplicationLog.WriteError(ex);
            }
        }

        /// <summary>
        /// List data
        /// </summary>
        private void ShowData()
        {
            SqlDatabase db = new SqlDatabase();
            string sql = string.Empty;
            if (!Func.IsValid(ListSortExpression))
            {
                ListSortExpression = "Modified";
                ListSortDirection = SortDirection.Descending;
            }
            try
            {
                string buildingId = Func.ParseString(Session["__BUILDINGID__"]);
                //Đếm số lượng record
                sql += " Select COUNT(*) ";
                sql += " FROM Customer A Left outer Join (SELECT COUNT(*) num,BuildingId,CustomerId ";
                sql += " FROM BD_RoomBooking ";
                sql += " where substring(BookingDate,0,7) = '" + drpYear.SelectedValue + drpMonth.SelectedValue + "' and BuildingId = '" + buildingId + "'  and Status in ('0','1') and Delflag = 0 ";
                sql += " Group by CustomerId,BuildingId) B on A.CustomerId = B.CustomerId and A.BuildingId = B.BuildingId ";
                sql += " WHERE A.BuildingId = '" + buildingId + "'";
                sql += GetWhere();

                int count = db.ExecuteCount(sql);
                sql = string.Empty;
                string sort = ListSortExpression + " " + (ListSortDirection == SortDirection.Ascending ? " asc " : " desc ");

                //Xuất ra toàn bộ nội dung theo Trang
                sql += " Select A.*,B.num, ROW_NUMBER() OVER(ORDER BY " + sort + ") as RowNum ";
                sql += " FROM Customer A Left outer Join (SELECT COUNT(*) num,BuildingId,CustomerId ";
                sql += " FROM BD_RoomBooking ";
                sql += " where substring(BookingDate,0,7) = '" + drpYear.SelectedValue + drpMonth.SelectedValue + "' and BuildingId = '" + buildingId + "'  and Status in ('0','1') and Delflag = 0 ";
                sql += " Group by CustomerId,BuildingId) B on A.CustomerId = B.CustomerId and A.BuildingId = B.BuildingId ";
                sql += " WHERE A.BuildingId = '" + buildingId + "'";
                sql += GetWhere();

                //Phân trang
                sql = " Select * FROM (" + sql + ") AS TMP ";
                sql += " WHERE RowNum BETWEEN @PageIndex*@PageSize + 1  AND (@PageIndex+1)*@PageSize ORDER BY RowNum ";

                //Thực hiện câu SQL

                SqlCommand cm = db.CreateCommand(sql);
                cm.Parameters.AddWithValue("@PageIndex", pager.CurrentPageIndex);
                cm.Parameters.AddWithValue("@PageSize", pager.PageSize);
                SqlDataAdapter da = new SqlDataAdapter(cm);
                DataSet ds = new DataSet();
                da.Fill(ds);
                db.Close();
                rptList.DataSource = ds.Tables[0].DefaultView;
                rptList.DataBind();
                pager.Count = count;
            }
            catch (Exception ex)
            {
                ApplicationLog.WriteError(ex);
            }
        }

        protected void ShowData()
        {
            SqlDatabase db = new SqlDatabase();
            string sql = string.Empty;
            try
            {
                aspnet_MembershipData member = new aspnet_MembershipData();
                aspnet_UsersData user = new aspnet_UsersData();
                aspnet_UsersInRolesData roles = new aspnet_UsersInRolesData();

                sql = "SELECT count(distinct U.UserName)"
                 + " FROM aspnet_Membership AS M Inner JOIN aspnet_Users AS U ON M.UserId=U.UserId Inner Join Mst_Building AS A on M.BuildingId = A.BuildingId"
                 + " WHERE M.UserId=U.UserId and M.BuildingId = '" + Func.ParseString(Session["__BUILDINGID__"]) + "'" + GetWhere();
                int total = db.ExecuteCount(sql);

                if (!Func.IsValid(ListSortExpression))
                {
                    ListSortExpression = "M.FullName";
                    ListSortDirection = SortDirection.Ascending;
                }
                string sort = ListSortExpression + " " + (ListSortDirection == SortDirection.Ascending ? " asc " : " desc ");

                sql = "SELECT distinct M.FullName, U.UserName, A.Name as BuildingName, M.Email, M.IsApproved, M.LastLoginDate, ROW_NUMBER() OVER(ORDER BY " + sort + ") as RowNum "
                 + " FROM aspnet_Membership AS M Inner JOIN aspnet_Users AS U ON M.UserId=U.UserId Inner Join Mst_Building AS A on M.BuildingId = A.BuildingId"
                 + " WHERE M.UserId=U.UserId and M.BuildingId = '" + Func.ParseString(Session["__BUILDINGID__"]) + "'" + GetWhere();

                sql = "Select FullName, UserName, BuildingName, Email, IsApproved, LastLoginDate FROM (" + sql + ") as tmp WHERE RowNum BetWeen @PageIndex*@PageSize + 1  and (@PageIndex+1)*@PageSize Order by RowNum";
                SqlCommand cm = db.CreateCommand(sql);
                cm.Parameters.AddWithValue("@PageIndex", pager.CurrentPageIndex);
                cm.Parameters.AddWithValue("@PageSize", pager.PageSize);
                SqlDataAdapter da = new SqlDataAdapter(cm);
                DataSet ds = new DataSet();
                da.Fill(ds);
                db.Close();
                rptList.DataSource = ds.Tables[0].DefaultView;
                rptList.DataBind();
                pager.Count = total;
            }
            catch (Exception ex)
            {
                ApplicationLog.WriteError(ex);
            }
        }

        /// <summary>
        /// List data
        /// </summary>
        private void ShowData()
        {
            SqlDatabase db = new SqlDatabase();
            string sql = string.Empty;
            if (!Func.IsValid(ListSortExpression))
            {
                ListSortExpression = "A.Modified";
                ListSortDirection = SortDirection.Descending;
            }
            try
            {
                //Đếm số lượng record
                sql += " Select COUNT(StaffID) ";
                sql += " FROM BD_Staff A";
                sql += " WHERE (A.JobEnd is Null or A.JobEnd = '' or (A.JobEnd is not null and substring(A.JobEnd,0,7) >= " + DateTime.Now.ToString("yyyyMM") + "   ) ) And (StaffID IS NOT NULL) and DelFlag <> 1 and BuildingId = '" + Func.ParseString(Session["__BUILDINGID__"]) + "' and JobTypeId = '" + hidJobType.Value + "'";

                int count = db.ExecuteCount(sql);
                sql = string.Empty;
                string sort = ListSortExpression + " " + (ListSortDirection == SortDirection.Ascending ? " asc " : " desc ");

                //Xuất ra toàn bộ nội dung theo Trang
                sql += " Select A.*, B.Position JobName, ROW_NUMBER() OVER(ORDER BY " + sort + ") as RowNum ";
                sql += " FROM BD_Staff A left outer join Mst_Position B on A.Position = B.ID";
                sql += " WHERE (A.JobEnd is Null or A.JobEnd = '' or (A.JobEnd is not null and substring(A.JobEnd,0,7) >= " + DateTime.Now.ToString("yyyyMM") + "   ) ) And A.StaffID IS NOT NULL and A.DelFlag <> 1 and A.BuildingId = '" + Func.ParseString(Session["__BUILDINGID__"]) + "' and A.JobTypeId = '" + hidJobType.Value + "'";

                //Phân trang
                sql = " Select * FROM (" + sql + ") AS TMP ";
                sql += " WHERE RowNum BETWEEN @PageIndex*@PageSize + 1  AND (@PageIndex+1)*@PageSize ORDER BY RowNum ";

                //Thực hiện câu SQL

                SqlCommand cm = db.CreateCommand(sql);
                cm.Parameters.AddWithValue("@PageIndex", pager.CurrentPageIndex);
                cm.Parameters.AddWithValue("@PageSize", pager.PageSize);
                SqlDataAdapter da = new SqlDataAdapter(cm);
                DataSet ds = new DataSet();
                da.Fill(ds);
                db.Close();
                rptList.DataSource = ds.Tables[0].DefaultView;
                rptList.DataBind();
                pager.Count = count;
            }
            catch (Exception ex)
            {
                ApplicationLog.WriteError(ex);
            }
        }

        /// <summary>
        /// List data
        /// </summary>
        private void ShowData()
        {
            SqlDatabase db = new SqlDatabase();
            string sql = string.Empty;
            if (!Func.IsValid(ListSortExpression))
            {
                ListSortExpression = "ContractDate";
                ListSortDirection = SortDirection.Descending;
            }
            try
            {
                string buildingId = Func.ParseString(Session["__BUILDINGID__"]);
                Hashtable PaymentList = new Hashtable();
                string sqlWhere = "";
                //if (optDept.Checked)
                //{
                //    sqlWhere = " and (DeptUSD <> 0 or DeptVND <> 0)";
                //}
                //else if (optNoDept.Checked)
                //{
                //    sqlWhere = " and DeptUSD = 0 and DeptVND = 0";
                //}

                //string sql = " SELECT BookingId ,ContractNo ,ContractDate ,Name";
                //sql += " ,RentHourFrom ,RentMinuteFrom ,RentHourTo ,RentMinuteTo";
                //sql += " ,Rate ,LastPriceUSD ,LastPriceVND ,ServiceLastPriceUSD";
                //sql += " ,ServiceLastPriceVND ,PaidUSD ,PaidVND ,DeptUSD ,DeptVND";
                //sql += " FROM v_PaymentRoomBooking Where BuildingId = '" + buildingId + "' " + sqlWhere + " and CustomerId = '" + lblCustomerId.Text + "' and BookingId ='"+ hidId.Value +"'order by YearMonth";

                sql = " SELECT Count(*)";
                sql += " FROM v_PaymentRoomBooking Where BuildingId = '" + buildingId + "' " + sqlWhere + " and CustomerId = '" + lblCustomerId.Text + "' and BookingId ='" + hidId.Value + "'";
                sql += sqlWhere;

                int count = db.ExecuteCount(sql);
                sql = string.Empty;
                string sort = ListSortExpression + " " + (ListSortDirection == SortDirection.Ascending ? " asc " : " desc ");

                //Xuất ra toàn bộ nội dung theo Trang
                sql += " Select *, ROW_NUMBER() OVER(ORDER BY " + sort + ") as RowNum ";
                sql += " FROM v_PaymentRoomBooking Where BuildingId = '" + buildingId + "' " + sqlWhere + " and CustomerId = '" + lblCustomerId.Text + "' and BookingId ='" + hidId.Value + "'";
                sql += sqlWhere;

                //Phân trang
                sql = " Select * FROM (" + sql + ") AS TMP ";
                sql += " WHERE RowNum BETWEEN @PageIndex*@PageSize + 1  AND (@PageIndex+1)*@PageSize ORDER BY RowNum ";

                //Thực hiện câu SQL

                SqlCommand cm = db.CreateCommand(sql);
                cm.Parameters.AddWithValue("@PageIndex", pager.CurrentPageIndex);
                cm.Parameters.AddWithValue("@PageSize", pager.PageSize);
                SqlDataAdapter da = new SqlDataAdapter(cm);
                DataSet ds = new DataSet();
                da.Fill(ds);
                db.Close();
                rptList.DataSource = ds.Tables[0].DefaultView;
                rptList.DataBind();
                pager.Count = count;

                //DataTable dt = DbHelper.GetDataTable(sql);
                //foreach (DataRow dr in dt.Rows)
                //{
                //    string CustomerId = dr["CustomerId"].ToString();
                //    string YearMonth = dr["YearMonth"].ToString();
                //    string key = CustomerId + "_" + YearMonth;
                //    DeptInfo tmp;

                //    if (!PaymentList.Contains(key))
                //    {
                //        tmp = new DeptInfo();
                //        tmp.CustomerId = CustomerId;
                //        //tmp.Customer = Func.ParseString(CusList[CustomerId]);
                //        tmp.YearMonth = YearMonth;
                //        PaymentList.Add(key, tmp);
                //    }
                //    string PaymentType = dr["PaymentType"].ToString();

                //    double MoneyUSD = Func.ParseDouble(dr["MoneyUSD"]);
                //    double MoneyVND = Func.ParseDouble(dr["MoneyVND"]);
                //    double PaidUSD = Func.ParseDouble(dr["PaidUSD"]);
                //    double PaidVND = Func.ParseDouble(dr["PaidVND"]);

                //    tmp = (DeptInfo)PaymentList[key];
                //    switch (PaymentType)
                //    {
                //        case "1":
                //            tmp.RentUSD = MoneyUSD;
                //            tmp.RentVND = MoneyVND;
                //            tmp.RentPaidUSD = PaidUSD;
                //            tmp.RentPaidVND = PaidVND;
                //            break;
                //        case "2":
                //            tmp.ManagerUSD = MoneyUSD;
                //            tmp.ManagerVND = MoneyVND;
                //            tmp.ManagerPaidUSD = PaidUSD;
                //            tmp.ManagerPaidVND = PaidVND;
                //            break;
//.........这里部分代码省略.........

        /// <summary>
        /// List data
        /// </summary>
        private void ShowData()
        {
            SqlDatabase db = new SqlDatabase();
            string sql = string.Empty;
            if (!Func.IsValid(ListSortExpression))
            {
                ListSortExpression = "isnull(B.Modified,A.Modified)";
                ListSortDirection = SortDirection.Descending;
            }
            try
            {
                //Đếm số lượng record
                sql += " Select COUNT(ID) ";
                sql += " FROM BD_Supplies";
                sql += " WHERE (Id IS NOT NULL) and DelFlag <> 1 and BuildingId = '" + Func.ParseString(Session["__BUILDINGID__"]) + "' and SuppliesType = '" + hidSuppliesType.Value + "'";
                sql += GetWhere();

                int count = db.ExecuteCount(sql);
                sql = string.Empty;
                string sort = ListSortExpression + " " + (ListSortDirection == SortDirection.Ascending ? " asc " : " desc ");

                //Xuất ra toàn bộ nội dung theo Trang
                sql += " Select id,CreatedId,BuildingId,JobType,SuppliesType,ItemId,Name,Description,ProductOf,Comment,Created,CreatedBy,isnull(B.Modified,A.Modified) as Modified,isnull(A.ModifiedBy,B.ModifiedBy) as ModifiedBy,DelFlag,Model,Label,Regional, ROW_NUMBER() OVER(ORDER BY " + sort + ") as RowNum ";
                sql += " FROM BD_Supplies A left outer join (SELECT t.SuppliesId,Modified,ModifiedBy ";
                sql += " FROM ( SELECT SuppliesId , MAX(id) AS max_votes FROM BD_SuppliesExim GROUP BY SuppliesId) AS m ";
                sql += " INNER JOIN BD_SuppliesExim AS t ";
                sql += " ON t.SuppliesId = m.SuppliesId ";
                sql += " AND t.id = m.max_votes) B on A.id = B.SuppliesId";
                sql += " WHERE Id IS NOT NULL and DelFlag <> 1 and BuildingId = '" + Func.ParseString(Session["__BUILDINGID__"]) + "' and SuppliesType = '" + hidSuppliesType.Value + "'";
                sql += GetWhere();

                //Phân trang
                sql = " Select * FROM (" + sql + ") AS TMP ";
                sql += " WHERE RowNum BETWEEN @PageIndex*@PageSize + 1  AND (@PageIndex+1)*@PageSize ORDER BY RowNum ";

                //Thực hiện câu SQL

                SqlCommand cm = db.CreateCommand(sql);
                cm.Parameters.AddWithValue("@PageIndex", pager.CurrentPageIndex);
                cm.Parameters.AddWithValue("@PageSize", pager.PageSize);
                SqlDataAdapter da = new SqlDataAdapter(cm);
                DataSet ds = new DataSet();
                da.Fill(ds);
                db.Close();
                rptList.DataSource = ds.Tables[0].DefaultView;
                rptList.DataBind();
                pager.Count = count;
            }
            catch (Exception ex)
            {
                ApplicationLog.WriteError(ex);
            }
        }

        /// <summary>
        /// List data
        /// </summary>
        private void ShowData()
        {
            SqlDatabase db = new SqlDatabase();
            string sql = string.Empty;
            if (!Func.IsValid(ListSortExpression))
            {
                ListSortExpression = "ContractDate";
                ListSortDirection = SortDirection.Descending;
            }
            try
            {
                string buildingId = Func.ParseString(Session["__BUILDINGID__"]);
                Hashtable PaymentList = new Hashtable();
                string sqlWhere = "";
                if (optDept.Checked)
                {
                    sqlWhere = " and (DeptUSD <> 0 or DeptVND <> 0) and Status = 1";
                }
                else if (optNoDept.Checked)
                {
                    sqlWhere = " and DeptUSD = 0 and DeptVND = 0 and Status = 1";
                }
                else if (optBook.Checked)
                {
                    sqlWhere = " and Status = 0";
                }
                if (!String.IsNullOrEmpty(txtCustomer.Text))
                {
                    sqlWhere += " and CustomerName like N'%" + txtCustomer.Text + "%'";
                }
                if (!String.IsNullOrEmpty(txtRoomName.Text))
                {
                    sqlWhere += " and Name like N'%" + txtRoomName.Text + "%'";
                }

                if (!String.IsNullOrEmpty(txtFromDate.Text))
                {
                    sqlWhere += " and BookingDate >= '" + Func.FormatYYYYmmdd(txtFromDate.Text) + "'";
                }
                if (!String.IsNullOrEmpty(txtToDate.Text))
                {
                    sqlWhere += " and BookingDate <= '" + Func.FormatYYYYmmdd(txtToDate.Text) + "'";
                }

                //string sql = " SELECT BookingId ,ContractNo ,ContractDate ,Name";
                //sql += " ,RentHourFrom ,RentMinuteFrom ,RentHourTo ,RentMinuteTo";
                //sql += " ,Rate ,LastPriceUSD ,LastPriceVND ,ServiceLastPriceUSD";
                //sql += " ,ServiceLastPriceVND ,PaidUSD ,PaidVND ,DeptUSD ,DeptVND";
                //sql += " FROM v_PaymentRoomBooking Where BuildingId = '" + buildingId + "' " + sqlWhere + " and CustomerId = '" + lblCustomerId.Text + "' and BookingId ='"+ hidId.Value +"'order by YearMonth";

                sql = " SELECT Count(*)";
                sql += " FROM v_PaymentRoomBooking Where BuildingId = '" + buildingId + "' " + sqlWhere;
                sql += sqlWhere;

                int count = db.ExecuteCount(sql);
                sql = string.Empty;
                string sort = ListSortExpression + " " + (ListSortDirection == SortDirection.Ascending ? " asc " : " desc ");

                //Xuất ra toàn bộ nội dung theo Trang
                sql += " Select BookingId,ContractNo,ContractDate,Name,RentHourFrom,RentMinuteFrom";
                sql += " ,RentHourTo,RentMinuteTo,Rate,LastPriceUSD,LastPriceVND,ServiceLastPriceUSD";
                sql += " ,ServiceLastPriceVND,PaidUSD,PaidVND,DeptUSD,DeptVND,BuildingId";
                sql += " ,CustomerId,CustomerName,[Status],dbo.fnDateTime(BookingDate) BookingDate, ROW_NUMBER() OVER(ORDER BY " + sort + ") as RowNum ";
                sql += " FROM v_PaymentRoomBooking Where BuildingId = '" + buildingId + "' " + sqlWhere;
                sql += sqlWhere;

                //Phân trang
                sql = " Select * FROM (" + sql + ") AS TMP ";
                sql += " WHERE RowNum BETWEEN @PageIndex*@PageSize + 1  AND (@PageIndex+1)*@PageSize ORDER BY RowNum ";

                //Thực hiện câu SQL

                SqlCommand cm = db.CreateCommand(sql);
                cm.Parameters.AddWithValue("@PageIndex", pager.CurrentPageIndex);
                cm.Parameters.AddWithValue("@PageSize", pager.PageSize);
                SqlDataAdapter da = new SqlDataAdapter(cm);
                DataSet ds = new DataSet();
                da.Fill(ds);
                db.Close();
                rptList.DataSource = ds.Tables[0].DefaultView;
                rptList.DataBind();
                pager.Count = count;
            }
            catch (Exception ex)
            {
                ApplicationLog.WriteError(ex);
            }
        }

        /// <summary>
        /// List data
        /// </summary>
        private void ShowData()
        {
            SqlDatabase db = new SqlDatabase();
            string sql = string.Empty;
            if (!Func.IsValid(ListSortExpression))
            {
                ListSortExpression = "Modified";
                ListSortDirection = SortDirection.Ascending;
            }
            try
            {
                //Đếm số lượng record
                sql += " SELECT COUNT(ID) ";
                sql += " FROM [BD_RoomBookingService]";
                sql += " WHERE (ID IS NOT NULL) and DelFlag <> 1 and BookingId = '" + hidId.Value + "'";

                int count = db.ExecuteCount(sql);
                sql = string.Empty;
                string sort = ListSortExpression + " " + (ListSortDirection == SortDirection.Ascending ? " asc " : " desc ");

                //Xuất ra toàn bộ nội dung theo Trang
                sql += " SELECT (PriceVND*Mount+PriceVND*Mount*VAT/100) sumVND,(PriceUSD*Mount+PriceUSD*Mount*VAT/100) sumUSD, *,ROW_NUMBER() OVER(ORDER BY " + sort + ") as RowNum ";
                sql += " FROM [BD_RoomBookingService]";
                sql += " WHERE (ID IS NOT NULL) and DelFlag <> 1 and BookingId = '" + hidId.Value + "'";

                //Phân trang
                //sql = " SELECT * FROM (" + sql + ") AS TMP ";
                //sql += " WHERE RowNum BETWEEN @PageIndex*@PageSize + 1  AND (@PageIndex+1)*@PageSize ORDER BY RowNum ";

                //Thực hiện câu SQL

                SqlCommand cm = db.CreateCommand(sql);
                //cm.Parameters.AddWithValue("@PageIndex", pager.CurrentPageIndex);
                //cm.Parameters.AddWithValue("@PageSize", pager.PageSize);
                SqlDataAdapter da = new SqlDataAdapter(cm);
                DataSet ds = new DataSet();
                da.Fill(ds);
                db.Close();
                rptList.DataSource = ds.Tables[0].DefaultView;
                rptList.DataBind();
                //pager.Count = count;
            }
            catch (Exception ex)
            {
                ApplicationLog.WriteError(ex);
            }
        }

 public static int GetCount(string sql,string connectionString)
 {
     SqlDatabase db = new SqlDatabase(connectionString);
     return db.ExecuteCount(sql);
 }

        /// <summary>
        /// List data
        /// </summary>
        private void ShowData()
        {
            DataTable dtCount = DbHelper.GetDataTable("SELECT     COUNT(*) AS VehicleCount, A.VehicleType, C.CustomerId" +
                    " FROM         Mst_VehicleType AS A INNER JOIN" +
                    "              BD_TariffsPacking AS B ON A.Id = B.VehicleTypeId INNER JOIN" +
                    "              CustomerParking AS C ON B.id = C.TariffsParkingId" +
                    " WHERE     (C.DelFlag = 0) and (SUBSTRING(C.ParkingBegin ,1,6) <= '" + DateTime.Now.ToString("yyyyMM") + "'" +
                    " and ((C.ParkingEnd is null or C.ParkingEnd = '') or (C.ParkingEnd >=  '" + DateTime.Now.ToString("yyyyMMdd") + "'"+ "))) And C.CustomerId = '" + hidId.Value + "' and C.DelFlag = '0'" +
                    " GROUP BY A.VehicleType, C.CustomerId ");
            string tmp = "";
            foreach (DataRow rowType in dtCount.Rows)
            {
                tmp += rowType[1].ToString() + ":" + rowType[0].ToString() + " Chiếc </br>";
            }
            lblVehicleCount.Text = tmp;

            SqlDatabase db = new SqlDatabase();
            string sql = string.Empty;
            if (!Func.IsValid(ListSortExpression))
            {
                ListSortExpression = "Modified";
                ListSortDirection = SortDirection.Ascending;
            }
            try
            {
                //Đếm số lượng record
                sql += " SELECT COUNT(ID) ";
                sql += " FROM [CustomerParking]";
                sql += " WHERE (ID IS NOT NULL) and DelFlag <> 1 and CustomerId = '" + hidId.Value + "'";

                int count = db.ExecuteCount(sql);
                sql = string.Empty;
                string sort = ListSortExpression + " " + (ListSortDirection == SortDirection.Ascending ? " asc " : " desc ");

                //Xuất ra toàn bộ nội dung theo Trang
                sql += " SELECT *, ROW_NUMBER() OVER(ORDER BY " + sort + ") as RowNum ";
                sql += " FROM CustomerParking";
                sql += " WHERE (ID IS NOT NULL) and DelFlag <> 1 and CustomerId = '" + hidId.Value + "'";

                //Phân trang
                sql = " SELECT * FROM (" + sql + ") AS TMP ";
                sql += " WHERE RowNum BETWEEN @PageIndex*@PageSize + 1  AND (@PageIndex+1)*@PageSize ORDER BY RowNum ";

                //Thực hiện câu SQL

                SqlCommand cm = db.CreateCommand(sql);
                cm.Parameters.AddWithValue("@PageIndex", pager.CurrentPageIndex);
                cm.Parameters.AddWithValue("@PageSize", pager.PageSize);
                SqlDataAdapter da = new SqlDataAdapter(cm);
                DataSet ds = new DataSet();
                da.Fill(ds);
                db.Close();
                rptList.DataSource = ds.Tables[0].DefaultView;
                rptList.DataBind();
                pager.Count = count;

                //sql = "SELECT   A.Name, COUNT(*) AS Mount";
                //sql += " FROM   BD_TariffsPacking AS A INNER JOIN";
                //sql += "        CustomerParking AS B ON A.id = B.TariffsParkingId";
                //sql += " WHERE  CustomerId = '" + hidId.Value + "' and B.DelFlag = '0' and  (B.ParkingBegin <= CONVERT(varchar(10), GETDATE(), 112)) AND (B.ParkingEnd >= CONVERT(varchar(10), GETDATE(), 112)) OR";
                //sql += "        (B.ParkingBegin > CONVERT(varchar(10), GETDATE(), 112)) AND (B.ParkingBegin <= CONVERT(varchar(10), DATEADD(s, - 1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()+ 2), 0)), 112))";
                //sql += " GROUP BY A.Name";
                //cm = db.CreateCommand(sql);
                //da = new SqlDataAdapter(cm);
                //ds = new DataSet();
                //da.Fill(ds);
                //db.Close();
                //rptParking.DataSource = ds.Tables[0].DefaultView;
                //rptParking.DataBind();
            }
            catch (Exception ex)
            {
                ApplicationLog.WriteError(ex);
            }
        }

        /// <summary>
        /// List data
        /// </summary>
        private void ShowData()
        {
            SqlDatabase db = new SqlDatabase();
            string sql = string.Empty;
            if (!Func.IsValid(ListSortExpression))
            {
                ListSortExpression = "A.Modified";
                ListSortDirection = SortDirection.Descending;
            }
            try
            {
                elecHash = new Hashtable();
                waterHash = new Hashtable();

                sql = "SELECT RoomId,TarrifsType FROM BD_RoomUsedElecWater where delflag = '0' and yearmonth = '" + drpYear.SelectedValue + drpMonth.SelectedValue + "' and BuildingId = '" + Func.ParseString(Session["__BUILDINGID__"]) + "'";
                DataSet ds01 = new DataSet();

                using (SqlDatabase db01 = new SqlDatabase())
                {
                    using (SqlCommand cm01 = db.CreateCommand(sql))
                    {
                        SqlDataAdapter da01 = new SqlDataAdapter(cm01);
                        da01.Fill(ds01);
                        if (ds01 != null)
                        {
                            DataTable dt01 = ds01.Tables[0];
                            foreach (DataRow rowType in dt01.Rows)
                            {
                                string RoomId = rowType["RoomId"].ToString();
                                string TarrifsType = rowType["TarrifsType"].ToString();
                                if ("1".Equals(TarrifsType) && !elecHash.ContainsKey(RoomId))
                                {
                                    elecHash.Add(RoomId, RoomId);
                                }
                                if ("2".Equals(TarrifsType) && !waterHash.ContainsKey(RoomId))
                                {
                                    waterHash.Add(RoomId, RoomId);
                                }
                            }
                        }
                    }
                }
                //Đếm số lượng record
                sql = " Select COUNT(ID) ";
                sql += " FROM BD_Room A";
                sql += " WHERE (ID IS NOT NULL) and BuildingId = '" + Func.ParseString(Session["__BUILDINGID__"]) + "'";
                sql += GetWhere();

                int count = db.ExecuteCount(sql);
                sql = string.Empty;
                string sort = ListSortExpression + " " + (ListSortDirection == SortDirection.Ascending ? " asc " : " desc ");

                //Xuất ra toàn bộ nội dung theo Trang
                sql += " Select A.*, ROW_NUMBER() OVER(ORDER BY " + sort + ") as RowNum ";
                sql += " FROM BD_Room A";
                sql += " WHERE A.ID IS NOT NULL and A.BuildingId = '" + Func.ParseString(Session["__BUILDINGID__"]) + "'";
                sql += GetWhere();

                //Phân trang
                sql = " Select * FROM (" + sql + ") AS TMP ";
                sql += " WHERE RowNum BETWEEN @PageIndex*@PageSize + 1  AND (@PageIndex+1)*@PageSize ORDER BY RowNum ";

                //Thực hiện câu SQL

                SqlCommand cm = db.CreateCommand(sql);
                cm.Parameters.AddWithValue("@PageIndex", pager.CurrentPageIndex);
                cm.Parameters.AddWithValue("@PageSize", pager.PageSize);
                SqlDataAdapter da = new SqlDataAdapter(cm);
                DataSet ds = new DataSet();
                da.Fill(ds);
                db.Close();
                rptList.DataSource = ds.Tables[0].DefaultView;
                rptList.DataBind();
                pager.Count = count;
            }
            catch (Exception ex)
            {
                ApplicationLog.WriteError(ex);
            }
        }

        /// <summary>
        /// List data
        /// </summary>
        private void ShowData()
        {
            SqlDatabase db = new SqlDatabase();
            string sql = string.Empty;
            if (!Func.IsValid(ListSortExpression))
            {
                ListSortExpression = "A.Modified";
                ListSortDirection = SortDirection.Ascending;
            }
            try
            {
                //Đếm số lượng record
                sql += " SELECT COUNT(ID) ";
                sql += " FROM [RC_Room]";
                sql += " WHERE (ID IS NOT NULL) and DelFlag <> 1 and ContractId = '" + lblId.Text + "'";

                int count = db.ExecuteCount(sql);
                sql = string.Empty;
                string sort = ListSortExpression + " " + (ListSortDirection == SortDirection.Ascending ? " asc " : " desc ");

                //Xuất ra toàn bộ nội dung theo Trang
                sql += " SELECT A.*, B.Name as RoomName , B.Regional as RegionalRoom, B.Floor as FloorRoom,B.Area as AreaRoom, ROW_NUMBER() OVER(ORDER BY " + sort + ") as RowNum ";
                sql += " FROM [RC_Room] A, BD_Room B ";
                sql += " WHERE A.ContractID IS NOT NULL and A.DelFlag <> 1 and A.RoomId = B.id and A.ContractId = '" + lblId.Text + "'";

                //Phân trang
                sql = " SELECT * FROM (" + sql + ") AS TMP ";
                sql += " WHERE RowNum BETWEEN @PageIndex*@PageSize + 1  AND (@PageIndex+1)*@PageSize ORDER BY RowNum ";

                //Thực hiện câu SQL

                SqlCommand cm = db.CreateCommand(sql);
                cm.Parameters.AddWithValue("@PageIndex", pager.CurrentPageIndex);
                cm.Parameters.AddWithValue("@PageSize", pager.PageSize);
                SqlDataAdapter da = new SqlDataAdapter(cm);
                DataSet ds = new DataSet();
                da.Fill(ds);
                db.Close();
                rptList.DataSource = ds.Tables[0].DefaultView;
                rptList.DataBind();
                pager.Count = count;
            }
            catch (Exception ex)
            {
                ApplicationLog.WriteError(ex);
            }
        }

        /// <summary>
        /// List data
        /// </summary>
        private void ShowData()
        {
            string sqlWhere = GetWhere();
            SqlDatabase db = new SqlDatabase();
            string sql = string.Empty;
            if (!Func.IsValid(ListSortExpression))
            {
                ListSortExpression = "A.Modified";
                ListSortDirection = SortDirection.Descending;
            }
            try
            {
                string condition = "";
                switch (hidDocType.Value)
                {
                    case "1":
                        break;
                    case "2":
                        condition = " And Dept = '2'";
                        break;
                    case "3":
                        condition = " And Dept = '3'";
                        break;
                    case "4":
                        condition = " And Dept = '4'";
                        break;
                    //case "5":
                    //    condition = " And ContractId = '" + hidId.Value + "'";
                    //    break;
                    //case "6":
                    //    condition = " And CustomerId = '" + hidId.Value + "'";
                    //    break;
                    //case "7":
                    //    condition = " And StaffId = '" + hidId.Value + "'";
                    //    break;
                    //case "8":
                    //    condition = " And SuppliesId = '" + hidId.Value + "'";
                    //    break;
                    //case "9":
                    //    condition = " And EquipmentId = '" + hidId.Value + "'";
                    //    break;
                    case "5":
                    case "6":
                    case "7":
                    case "8":
                    case "9":
                        condition = " And DocTypeId = '" + hidId.Value + "'";
                        break;
                    default:
                        break;
                }

                //Đếm số lượng record
                sql += " SELECT COUNT(ID) ";
                sql += " FROM [BD_Document] A";
                sql += " WHERE (ID IS NOT NULL) and DelFlag <> 1 and BuildingId = '" + Func.ParseString(Session["__BUILDINGID__"]) + "' and DocType = '" + hidDocType.Value + "'" + condition;
                sql += sqlWhere;

                int count = db.ExecuteCount(sql);
                sql = string.Empty;
                string sort = ListSortExpression + " " + (ListSortDirection == SortDirection.Ascending ? " asc " : " desc ");

                //Xuất ra toàn bộ nội dung theo Trang
                sql += " SELECT A.*,B.DocSubject Subject, ROW_NUMBER() OVER(ORDER BY " + sort + ") as RowNum ";
                sql += " FROM BD_Document A left outer join BD_DocSubject B on A.DocSubject = B.id ";
                sql += " WHERE (A.ID IS NOT NULL) and A.DelFlag <> 1 and A.BuildingId = '" + Func.ParseString(Session["__BUILDINGID__"]) + "' and A.DocType = '" + hidDocType.Value + "'" + condition;
                sql += sqlWhere;

                //Phân trang
                sql = " SELECT * FROM (" + sql + ") AS TMP ";
                sql += " WHERE RowNum BETWEEN @PageIndex*@PageSize + 1  AND (@PageIndex+1)*@PageSize ORDER BY RowNum ";

                //Thực hiện câu SQL

                SqlCommand cm = db.CreateCommand(sql);
                cm.Parameters.AddWithValue("@PageIndex", pager.CurrentPageIndex);
                cm.Parameters.AddWithValue("@PageSize", pager.PageSize);
                SqlDataAdapter da = new SqlDataAdapter(cm);
                DataSet ds = new DataSet();
                da.Fill(ds);
                db.Close();
                rptList.DataSource = ds.Tables[0].DefaultView;
                rptList.DataBind();
                pager.Count = count;
                pager1.Count = count;
            }
            catch (Exception ex)
            {
                ApplicationLog.WriteError(ex);
            }
        }

        /// <summary>
        /// List data
        /// </summary>
        private void ShowData()
        {
            SqlDatabase db = new SqlDatabase();
            string sql = string.Empty;
            if (!Func.IsValid(ListSortExpression))
            {
                ListSortExpression = "A.FeeGroup, IndexFrom";
                ListSortDirection = SortDirection.Ascending;
            }
            try
            {
                DbHelper.FillList(drpFeeGroup, "Select * from BD_FeeGroup where delFlag = 0 and FeeGroup = '" + hidType.Value + "' and BuildingId = '" + Func.ParseString(Session["__BUILDINGID__"]) + "'", "Name", "id");

                //Đếm số lượng record
                sql += " SELECT COUNT(A.ID) ";
                sql += " FROM [BD_TarrifsElecWater] A, BD_FeeGroup B ";
                sql += " WHERE A.ID IS NOT NULL and A.DelFlag <> 1 and A.FeeGroup = B.id and B.FeeGroup = '" + hidType.Value + "' and A.BuildingId = '" + Func.ParseString(Session["__BUILDINGID__"]) + "'";

                int count = db.ExecuteCount(sql);
                sql = string.Empty;
                string sort = ListSortExpression + " " + (ListSortDirection == SortDirection.Ascending ? " asc " : " desc ");

                //Xuất ra toàn bộ nội dung theo Trang
                sql += " SELECT A.*, B.Name as FeeGroupName ,ROW_NUMBER() OVER(ORDER BY " + sort + ") as RowNum ";
                sql += " FROM [BD_TarrifsElecWater] A, BD_FeeGroup B ";
                sql += " WHERE A.ID IS NOT NULL and A.DelFlag <> 1 and A.FeeGroup = B.id and B.FeeGroup = '" + hidType.Value + "' and A.BuildingId = '" + Func.ParseString(Session["__BUILDINGID__"]) + "'";

                //Phân trang
                sql = " SELECT * FROM (" + sql + ") AS TMP ";
                sql += " WHERE RowNum BETWEEN @PageIndex*@PageSize + 1  AND (@PageIndex+1)*@PageSize ORDER BY RowNum ";

                //Thực hiện câu SQL

                SqlCommand cm = db.CreateCommand(sql);
                cm.Parameters.AddWithValue("@PageIndex", pager.CurrentPageIndex);
                cm.Parameters.AddWithValue("@PageSize", pager.PageSize);
                SqlDataAdapter da = new SqlDataAdapter(cm);
                DataSet ds = new DataSet();
                da.Fill(ds);
                db.Close();
                rptList.DataSource = ds.Tables[0].DefaultView;
                rptList.DataBind();
                pager.Count = count;
            }
            catch (Exception ex)
            {
                ApplicationLog.WriteError(ex);
            }
        }

        /// <summary>
        /// List data
        /// </summary>
        private void ShowData()
        {
            SqlDatabase db = new SqlDatabase();
            string sql = string.Empty;
            if (!Func.IsValid(ListSortExpression))
            {
                ListSortExpression = "A.BookingHourFrom";
                ListSortDirection = SortDirection.Descending;
            }
            try
            {
                string sqlWhere = "";
                if (!String.IsNullOrEmpty(txtBookingDate.Text.Trim()))
                {
                    sqlWhere = "and BookingDate = '" + Func.FormatYYYYmmdd(txtBookingDate.Text.Trim()) + "'";
                }

                //Đếm số lượng record
                sql += " Select COUNT(ID) ";
                sql += " FROM BD_RoomBooking A";
                sql += " Where Status in ('0','1') and DelFlag <> 1 and RoomId = '" + hidId.Value + "'" + sqlWhere;

                int count = db.ExecuteCount(sql);
                sql = string.Empty;
                string sort = ListSortExpression + " " + (ListSortDirection == SortDirection.Ascending ? " asc " : " desc ");

                //Xuất ra toàn bộ nội dung theo Trang
                sql += " Select A.*, ROW_NUMBER() OVER(ORDER BY " + sort + ") as RowNum ";
                sql += " FROM BD_RoomBooking A";
                sql += " Where Status in ('0','1') and DelFlag <> 1 and RoomId = '" + hidId.Value + "'" + sqlWhere;

                //Phân trang
                sql = " Select * FROM (" + sql + ") AS TMP ";
                sql += " WHERE RowNum BETWEEN @PageIndex*@PageSize + 1  AND (@PageIndex+1)*@PageSize ORDER BY RowNum ";

                //Thực hiện câu SQL

                SqlCommand cm = db.CreateCommand(sql);
                cm.Parameters.AddWithValue("@PageIndex", pager.CurrentPageIndex);
                cm.Parameters.AddWithValue("@PageSize", pager.PageSize);
                SqlDataAdapter da = new SqlDataAdapter(cm);
                DataSet ds = new DataSet();
                da.Fill(ds);
                db.Close();
                rptList.DataSource = ds.Tables[0].DefaultView;
                rptList.DataBind();
                pager.Count = count;
            }
            catch (Exception ex)
            {
                ApplicationLog.WriteError(ex);
            }
        }