C# SqlDatabase.Close方法代码示例

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

        protected void btnExport_Click(object sender, EventArgs e)
        {
            DataSet ds = new DataSet();
            string sql = string.Empty;

            sql = " SELECT *";
            sql += " FROM v_GuiXeThang";
            sql += " WHERE BuildingId = '" + Func.ParseString(Session["__BUILDINGID__"]) + "' ";
            sql += " AND ((NgayKetThuc is null) OR ";
            sql += "      (NgayKetThuc is not null and substring(NgayKetThuc,1,6) >= '" + drpYear.SelectedValue + drpMonth.SelectedValue + "'))";

            using (SqlDatabase db = new SqlDatabase())
            {
                using (SqlCommand cm = db.CreateCommand(sql))
                {
                    SqlDataAdapter da = new SqlDataAdapter(cm);
                    da.Fill(ds);
                    db.Close();

                    if (ds != null)
                    {
                        C1XLBook xlbBook = new C1XLBook();
                        string fileName = HttpContext.Current.Server.MapPath(@"~\Report\Template\GuixeThang.xls");
                        if (!Directory.Exists(@"~\Report\Building\" + Func.ParseString(Session["__BUILDINGID__"])))
                        {
                            Directory.CreateDirectory(HttpContext.Current.Server.MapPath(@"~\Report\Building\" + Func.ParseString(Session["__BUILDINGID__"])));
                        }

                        string strDT = DateTime.Now.ToString("yyyyMMddHHmmss");
                        string strFilePath = @"~\Report\Building\" + Func.ParseString(Session["__BUILDINGID__"]) + @"\GuiXeThang" + strDT + ".xls";
                        string strFilePathExport = "Report/Building/" + Func.ParseString(Session["__BUILDINGID__"]) + "/GuiXeThang" + strDT + ".xls";

                        string fileNameDes = HttpContext.Current.Server.MapPath(strFilePath);

                        //string fileNameDes = HttpContext.Current.Server.MapPath(@"~\Report\Building\" + Func.ParseString(Session["__BUILDINGID__"]) + @"\TongHopDienTich" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls");
                        File.Copy(fileName, fileNameDes);

                        xlbBook.Load(fileNameDes);
                        XLSheet xlsSheet = xlbBook.Sheets["GuiXeThang"];
                        //xlsSheet.Name = drpMonth.SelectedValue + "_" + drpYear.SelectedValue;

                        int i = 4;
                        XLCellRange mrCell = new XLCellRange(0, 0, 0, 2);
                        xlsSheet.MergedCells.Add(mrCell);

                        XLStyle xlstStyle = new XLStyle(xlbBook);
                        xlstStyle.AlignHorz = XLAlignHorzEnum.Center;
                        xlstStyle.WordWrap = true;
                        xlstStyle.Font = new Font("", 8, FontStyle.Regular);
                        xlstStyle.SetBorderColor(Color.Black);
                        xlstStyle.BorderBottom = XLLineStyleEnum.Thin;
                        xlstStyle.BorderTop = XLLineStyleEnum.Thin;
                        xlstStyle.BorderLeft = XLLineStyleEnum.Thin;
                        xlstStyle.BorderRight = XLLineStyleEnum.Thin;

                        XLStyle xlstStyle01 = new XLStyle(xlbBook);
                        xlstStyle01.AlignHorz = XLAlignHorzEnum.Center;
                        xlstStyle01.Font = new Font("", 10, FontStyle.Bold);
                        xlstStyle.SetBorderColor(Color.Black);

                        xlsSheet[1, 0].Value = xlsSheet[1, 0].Value.ToString().Replace("{%TOA_NHA%}", DbHelper.GetScalar("Select Name From Mst_Building Where BuildingId = '" + Func.ParseString(Session["__BUILDINGID__"]) + "'"));
                        xlsSheet[0, 0].Value = xlsSheet[0, 0].Value.ToString().Replace("{%THANG%}", drpMonth.SelectedValue+"/"+drpYear.SelectedValue);

                        int stt = 0;
                        DataTable dt = ds.Tables[0];
                        foreach (DataRow rowType in dt.Rows)
                        {
                            string col01 = rowType[0].ToString();
                            string col02 = rowType[1].ToString();
                            string col03 = rowType[2].ToString();
                            string col04 = rowType[3].ToString();
                            string col05 = rowType[4].ToString();
                            string col06 = rowType[5].ToString();
                            string col07 = Func.FormatDMY(rowType[6].ToString());
                            string col08 = Func.FormatDMY(rowType[7].ToString());
                            string col09 = rowType[8].ToString();

                            xlsSheet[i, 0].Value = ++stt;
                            xlsSheet[i, 1].Value = col03;
                            xlsSheet[i, 2].Value = col04;
                            xlsSheet[i, 3].Value = col05;
                            xlsSheet[i, 4].Value = col06;
                            xlsSheet[i, 5].Value = col07;
                            xlsSheet[i, 6].Value = col08;
                            xlsSheet[i, 7].Value = col09;

                            xlsSheet[i, 0].Style = xlstStyle;
                            xlsSheet[i, 1].Style = xlstStyle;
                            xlsSheet[i, 2].Style = xlstStyle;
                            xlsSheet[i, 3].Style = xlstStyle;
                            xlsSheet[i, 4].Style = xlstStyle;
                            xlsSheet[i, 5].Style = xlstStyle;
                            xlsSheet[i, 6].Style = xlstStyle;
                            xlsSheet[i, 7].Style = xlstStyle;
                            ++i;
                        }

                        ////ScriptManager.RegisterStartupScript(Page, this.GetType(), "", "PopUp('/CSV/DownloadZipFile.aspx'," + PopupWidth + "," + PopupHeight + ",'EditFlat', true);", true);

                        ////xlsSheet[i++, 0].Value = "Ghi chú:";
//.........这里部分代码省略.........

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnExport_Click(object sender, EventArgs e)
        {
            string job = "";
            switch (hidJobType.Value)
            {
                case "1":
                    job = "BV";
                    break;
                case "2":
                    job = "VS";
                    break;
                case "3":
                    job = "KT";
                    break;
                case "4":
                    job = "QL";
                    break;

                default:
                    break;
            }
            string buildingId = Func.ParseString(Session["__BUILDINGID__"]);

            Hashtable staffIdRow = new Hashtable();
            string[] dateOfWeekVN = { "T2", "T3", "T4", "T5", "T6", "T7", "CN" };
            string[] dateOfWeekEN = { "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Sartuday", "Sunday" };

            Dictionary<string, string> dictionary = new Dictionary<string, string>();
            dictionary.Add("monday", "T2");
            dictionary.Add("tuesday", "T3");
            dictionary.Add("wednesday", "T4");
            dictionary.Add("thursday", "T5");
            dictionary.Add("friday", "T6");
            dictionary.Add("saturday", "T7");
            dictionary.Add("sunday", "CN");

            DataSet ds = new DataSet();
            string sql = string.Empty;

            sql = " SELECT *";
            sql += " FROM BD_WorkingWorkedInfo";
            sql += " WHERE BuildingId = '" + buildingId + "' and DelFlag = 0 and jobtypeid = '" + hidJobType.Value + "'";
            sql += " and YearMonth = '" + drpYear.SelectedValue + drpMonth.SelectedValue + "'";

            Hashtable scheduleLst = new Hashtable();

            using (SqlDatabase db = new SqlDatabase())
            {
                using (SqlCommand cm = db.CreateCommand(sql))
                {
                    SqlDataAdapter da = new SqlDataAdapter(cm);
                    da.Fill(ds);
                    db.Close();

                    if (ds != null)
                    {
                        DataTable dt = ds.Tables[0];
                        foreach (DataRow rowType in dt.Rows)
                        {
                            string StaffId = rowType["StaffId"].ToString();
                            string WorkingHourId = rowType["WorkingHourId"].ToString();
                            string WorkingDate = rowType["WorkingDate"].ToString().Substring(6, 2);
                            if (!String.IsNullOrEmpty(WorkingHourId) && scheduleLst.ContainsKey(StaffId + WorkingDate))
                            {
                                scheduleLst.Add(StaffId + WorkingDate, WorkingHourId);
                            }
                        }
                    }
                }
            }

            ds = new DataSet();
            sql = " SELECT *";
            sql += " FROM BD_Staff";
            sql += " WHERE BuildingId = '" + buildingId + "' and DelFlag = 0 and jobtypeid = '" + hidJobType.Value + "' and SUBSTRING(JobBegin,0,7) <= '" + drpYear.SelectedValue + drpMonth.SelectedValue + "' and (JobEnd = '' or JobEnd is Null or SUBSTRING(JobEnd,0,7) >= '" + drpYear.SelectedValue + drpMonth.SelectedValue + "')";
            sql += " Order By Name";

            using (SqlDatabase db = new SqlDatabase())
            {
                using (SqlCommand cm = db.CreateCommand(sql))
                {
                    SqlDataAdapter da = new SqlDataAdapter(cm);
                    da.Fill(ds);
                    db.Close();

                    if (ds != null)
                    {
                        mvMessage.SetCompleteMessage("File CSV đã xuất thành công.");

                        C1XLBook xlbBook = new C1XLBook();

                        string fileName = HttpContext.Current.Server.MapPath(@"~\Report\Template\LichLamViec.xls");
                        if (!Directory.Exists(@"~\Report\Building\" + Func.ParseString(Session["__BUILDINGID__"])))
                        {
                            Directory.CreateDirectory(HttpContext.Current.Server.MapPath(@"~\Report\Building\" + buildingId));
//.........这里部分代码省略.........

//.........这里部分代码省略.........

            //divRent.Visible = false;
            //divManager.Visible = false;
            //divParking.Visible = false;
            //divService.Visible = false;
            //divWater.Visible = false;
            //divExtraTime.Visible = false;
            //divElec.Visible = false;

            lsYearmonth = lsYearmonth.Substring(1);

            SqlDatabase db = new SqlDatabase();
            string sql = string.Empty;
            if (!Func.IsValid(ListSortExpression))
            {
                ListSortExpression = "Name";
                ListSortDirection = SortDirection.Descending;
            }
            try
            {
                //Rent And Manager Price
                sql = string.Empty;
                string sort = ListSortExpression + " " + (ListSortDirection == SortDirection.Ascending ? " asc " : " desc ");

                //Xuất ra toàn bộ nội dung theo Trang
                sql += " Select *";
                sql += " FROM PaymentRoom";
                sql += " WHERE BuildingId = '" + Func.ParseString(Session["__BUILDINGID__"]) + "' and CustomerId = '" + hidId.Value + "' and YearMonth in (" + lsYearmonth + ")";

                SqlCommand cm = db.CreateCommand(sql);
                SqlDataAdapter da = new SqlDataAdapter(cm);
                DataSet ds = new DataSet();
                da.Fill(ds);
                db.Close();
                rptRoomRent.DataSource = ds.Tables[0].DefaultView;
                rptRoomRent.DataBind();

                rptRoomManager.DataSource = ds.Tables[0].DefaultView;
                rptRoomManager.DataBind();

                //Parking
                sql = string.Empty;
                sort = "TariffsParkingName" + " " + (ListSortDirection == SortDirection.Ascending ? " asc " : " desc ");

                //Xuất ra toàn bộ nội dung theo Trang
                sql += " SELECT COUNT(*) AS Num, YearMonth, TariffsParkingName, PriceVND, PriceUSD, SUM(VatVND) AS VatVND,SUM(VatUSD) AS VatUSD, SUM(SumVND) AS SumVND, SUM(SumUSD) AS SumUSD, SUM(LastPriceVND) AS LastPriceVND";
                sql += "        , SUM(LastPriceUSD) AS LastPriceUSD";
                sql += " FROM         dbo.PaymentParking";
                sql += " WHERE BuildingId = '" + Func.ParseString(Session["__BUILDINGID__"]) + "' and CustomerId = '" + hidId.Value + "' and YearMonth in (" + lsYearmonth + ")";
                sql += " GROUP BY YearMonth, TariffsParkingName, PriceVND, PriceUSD, Vat, daysParking";

                cm = db.CreateCommand(sql);
                da = new SqlDataAdapter(cm);
                ds = new DataSet();
                da.Fill(ds);
                db.Close();
                rptParking.DataSource = ds.Tables[0].DefaultView;
                rptParking.DataBind();

                //Extra Time
                sql = string.Empty;
                sort = "WorkingDate" + " " + (ListSortDirection == SortDirection.Ascending ? " asc " : " desc ");

                //Xuất ra toàn bộ nội dung theo Trang
                sql += " SELECT * ";
                sql += " FROM   PaymentExtraTime";

        /// <summary>
        /// 
        /// </summary>
        private void ShowData()
        {
            PopupWidth = 850;
            PopupName = "ListSyncSong";

            SqlDatabase db = new SqlDatabase();
            string sql = string.Empty;
            if (!Func.IsValid(ListSortExpression))
            {
                ListSortExpression = "SongId";
                ListSortDirection = SortDirection.Descending;
            }
            try
            {
                //件数を数える
                sql += " SELECT COUNT(SongId) ";
                sql += " FROM SongImport ";
                sql += " WHERE (SongId IS NOT NULL) and SessionId = '" + Session.SessionID + "' and ImportType = '" + importType + "'";

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

                //検索条件で取得情報のSQL文を作成する
                sql = " SELECT *,ROW_NUMBER() OVER(ORDER BY " + sort + ") as RowNum  FROM SongImport ";
                sql += " WHERE (SongId IS NOT NULL) and SessionId = '" + Session.SessionID + "' and ImportType = '" + importType + "'";

                //ページによるレコーダを取得する

                sql = " SELECT *,RowNum FROM (" + sql + ") AS TMP ";
                sql += " WHERE RowNum BETWEEN @PageIndex*@PageSize + 1  AND (@PageIndex+1)*@PageSize ORDER BY TMP.RowNum ";

                //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>
        /// 
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnExport_Click(object sender, EventArgs e)
        {
            string[] dateOfWeekVN = { "T2", "T3", "T4", "T5", "T6", "T7", "CN" };
            string[] dateOfWeekEN = { "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Sartuday", "Sunday" };

            Dictionary<string, string> dictionary = new Dictionary<string, string>();
            dictionary.Add("monday", "T2");
            dictionary.Add("tuesday", "T3");
            dictionary.Add("wednesday", "T4");
            dictionary.Add("thursday", "T5");
            dictionary.Add("friday", "T6");
            dictionary.Add("saturday", "T7");
            dictionary.Add("sunday", "CN");

            DataSet ds = new DataSet();
            string sql = string.Empty;

            sql = " SELECT *";
            sql += " FROM BD_Staff";
            sql += " WHERE BuildingId = '" + Func.ParseString(Session["__BUILDINGID__"]) + "' and DelFlag <> 1 and jobtypeid = '"+ hidJobType.Value +"'";
            sql += " Order By Name";

            using (SqlDatabase db = new SqlDatabase())
            {
                using (SqlCommand cm = db.CreateCommand(sql))
                {
                    SqlDataAdapter da = new SqlDataAdapter(cm);
                    da.Fill(ds);
                    db.Close();

                    if (ds != null)
                    {
                        mvMessage.SetCompleteMessage("File CSV đã xuất thành công.");

                        C1XLBook xlbBook = new C1XLBook();
                        XLSheet xlsSheet = xlbBook.Sheets[0];
                        xlsSheet.Name = drpMonth.SelectedValue + "_" + drpYear.SelectedValue;

                        int i = 0;
                        XLCellRange mrCell = new XLCellRange(0, 0, 0, 2);
                        xlsSheet.MergedCells.Add(mrCell);

                        XLStyle xlstStyle = new XLStyle(xlbBook);
                        xlstStyle.AlignHorz = XLAlignHorzEnum.Center;
                        xlstStyle.Font = new Font("", 12, FontStyle.Bold);
                        xlstStyle.SetBorderColor(Color.Black);

                        xlsSheet[i, 0].Value = "Tháng " + drpMonth.SelectedValue + "/" + drpYear.SelectedValue;
                        xlsSheet[i, 0].Style = xlstStyle;

                        xlsSheet[i + 1, 0].Value = "STT";
                        xlsSheet[i + 1, 1].Value = "Mã Nhân Viên";
                        xlsSheet[i + 1, 2].Value = "Họ và Tên";

                        XLStyle xlstStyle01 = new XLStyle(xlbBook);
                        xlstStyle01.AlignHorz = XLAlignHorzEnum.Center;
                        xlstStyle01.Font = new Font("", 10, FontStyle.Bold);
                        xlstStyle.SetBorderColor(Color.Black);

                        for (int j = 1; j <= 31; j++)
                        {
                            xlsSheet[i, 2 + j].Value = j;
                            DateTime date = new DateTime(Func.ParseInt(drpYear.SelectedValue), Func.ParseInt(drpMonth.SelectedValue), j);
                            xlsSheet[i + 1, 2 + j].Value = dictionary[date.DayOfWeek.ToString().ToLower()];

                            xlsSheet[i, 2 + j].Style = xlstStyle01;
                            xlsSheet[i + 1, 2 + j].Style = xlstStyle01;
                            if (j == DateTime.DaysInMonth(Func.ParseInt(drpYear.SelectedValue), Func.ParseInt(drpMonth.SelectedValue)))
                            {
                                break;
                            }
                        }

                        i++;
                        DataTable dt = ds.Tables[0];
                        foreach (DataRow rowType in dt.Rows)
                        {
                            int No = i;
                            i++;
                            string StaffId = rowType["StaffId"].ToString();
                            string Name = rowType["Name"].ToString();

                            xlsSheet[i, 0].Value = No;
                            xlsSheet[i, 1].Value = StaffId;
                            xlsSheet[i, 2].Value = Name;

                            xlsSheet[i, 0].Style = xlstStyle01;
                            xlsSheet[i, 1].Style = xlstStyle01;
                            xlsSheet[i, 2].Style = xlstStyle01;

                        }

                        //ScriptManager.RegisterStartupScript(Page, this.GetType(), "", "PopUp('/CSV/DownloadZipFile.aspx'," + PopupWidth + "," + PopupHeight + ",'EditFlat', true);", true);

                        //xlsSheet[i++, 0].Value = "Ghi chú:";
//.........这里部分代码省略.........

        /// <summary>
        ///  Access database to recive and render content to control
        /// </summary>
        public void Render()
        {
            string sql = string.Format("SELECT [Creator], [Created], [ModifiedBy], [Modified] FROM [{0}] WHERE [{1}] = '{2}'; ", TableName, KeyColumnName, KeyValue);
            SqlDatabase db = new SqlDatabase();
            try
            {
                DataSet ds = db.ExecuteDataSet(sql);
                if (ds.Tables.Count >= 1)
                {
                    DataTable dt = ds.Tables[0];
                    if (dt.Rows.Count >= 1)
                    {
                        DataRow dr = dt.Rows[0];
                        lblEditDate.Text = Func.Formatdmyhms(dr[3].ToString());
                        lblEditer.Text = Func.GetFullNameByUserName(dr[2].ToString());
                        lblRegister.Text = Func.GetFullNameByUserName(dr[0].ToString());
                        lblRegisterDate.Text = Func.Formatdmyhms(dr[1].ToString());
                    }
                }

            }
            catch
            {
                lblEditDate.Text = " ";
                lblEditer.Text = " ";
                lblRegister.Text = " ";
                lblRegisterDate.Text = " ";
            }

            db.Close();
        }

        public static void UpdateProcessConfigXml(PTR3Core core, String converterName, String processConfigurationXml)
        {
            String ptrServer = ConfigXmlReader.XmlGetConfigParam(core.OptionalConfigurationXml, "data-base:address");
              String ptrDatabase = ConfigXmlReader.XmlGetConfigParam(core.OptionalConfigurationXml, "data-base:name");
              String ptrUser = ConfigXmlReader.XmlGetConfigParam(core.OptionalConfigurationXml, "data-base:user");
              String ptrPassword = PasswordEncoder.PtrDecryptText(ConfigXmlReader.XmlGetConfigParam(core.OptionalConfigurationXml, "data-base:password"));

              // Zapis xml do bazy danych.
              PTR3Core.LOG.Log(MsgStatusEnum.MS_Deb1, "Create connection with PTR DB");
              SqlDatabase ptrDb = new SqlDatabase(ptrServer, ptrDatabase, ptrUser, ptrPassword);

              if(ptrDb == null)
              {
            throw new Exception("Connection with PTR database failed");
              }

              SqlDatabase ptrConfModDb = new SqlDatabase(ptrServer, ptrDatabase, ptrUser, ptrPassword);

              if(ptrConfModDb == null)
              {
            throw new Exception("Connection with PTR database failed");
              }

              try
              {
            PTR3Core.LOG.Log(MsgStatusEnum.MS_Deb1, "Lock " + converterName + " Resources");

            string machineName = "";
            string processDescr = "";
            string lockResource = String.Format("{0}:{1}:{2}", ptrServer, ptrDatabase, converterName);
            DateTime lockTime = DateTime.MinValue;
            if(!core.LockResource(ptrDb, lockResource, 60, out machineName, out processDescr, out lockTime))
            {
              throw new Exception(String.Format("Resource: {0} was blocked by another process [Machine name: {1}, Process descr: {2}, at: {3}]",
              lockResource, machineName, processDescr, lockTime));
            }

            ptrConfModDb.BeginTransaction();
            core.UpdateProcessConfiguration(ptrConfModDb, processConfigurationXml);
            ptrConfModDb.CommitTransaction();
              }
              catch(Exception ex)
              {
            if(ptrDb != null)
            {
              ptrDb.RollbackTransaction();
            }

            if(ptrConfModDb != null)
            {
              ptrConfModDb.RollbackTransaction();
            }

            throw ex;
              }
              finally
              {
            if(ptrDb != null)
            {
              ptrDb.Close();
              ptrDb = null;
            }

            if(ptrConfModDb != null)
            {
              ptrConfModDb.Close();
              ptrConfModDb = null;
            }
              }
        }

        protected void btnRegister_Click(object sender, EventArgs e)
        {
            try
            {
                //string delete = " Delete PaymentMonthWaterFee From PaymentMonthWaterFee A, PaymentMonthWaterFeeTmp B Where A.FlatID = B.TenementID + B.FlatID and A.YearMonth = B.YearMonth and SessionId = '" + Session.SessionID + "';";
                //delete += " Delete PaymentMonthWaterFeeDetail From PaymentMonthWaterFeeDetail A, PaymentMonthWaterFeeTmp B Where A.FlatID = B.TenementID + B.FlatID and A.YearMonth = B.YearMonth and SessionId = '" + Session.SessionID + "'";

                string delete = " Delete PaymentMonthWaterFee Where FlatID like '" + hidTenementID.Value + "%' and YearMonth = '"+ hidYearmonth.Value +"';";
                DbHelper.ExecuteNonQuery(delete);

                delete = " Delete PaymentMonthWaterFeeDetail Where FlatID like '" + hidTenementID.Value + "%' and YearMonth = '" + hidYearmonth.Value + "'";
                DbHelper.ExecuteNonQuery(delete);

                DataSet ds = new DataSet();

                string select = "Select A.*,C.LossAvg from PaymentMonthWaterFeeTmp A, MST_Flat B, MST_Tenement C Where C.TenementId = B.TenementId and A.TenementID + A.FlatId = B.FlatID and SessionId = '" + Session.SessionID + "'";

                using (SqlDatabase db = new SqlDatabase())
                {
                    using (SqlCommand cm = db.CreateCommand(select))
                    {
                        SqlDataAdapter da = new SqlDataAdapter(cm);
                        da.Fill(ds);
                        db.Close();

                        if (ds != null)
                        {
                            DataTable dt = ds.Tables[0];
                            using (SqlConnection rConn = new SqlConnection(Gnt.Configuration.ApplicationConfiguration.ConnectionString))
                            {
                                rConn.Open();
                                foreach (DataRow rowType in dt.Rows)
                                {
                                    string FlatID = rowType["FlatID"].ToString();
                                    string YearMonth = rowType["YearMonth"].ToString();
                                    string OldIndex = rowType["OldIndex"].ToString();
                                    string NewIndex = rowType["NewIndex"].ToString();
                                    string Created = rowType["Created"].ToString();
                                    string Creator = rowType["Creator"].ToString();
                                    string Modified = rowType["Modified"].ToString();
                                    string Updator = rowType["Updator"].ToString();
                                    string TenementID = rowType["TenementID"].ToString();

                                    string LossAvg = rowType["LossAvg"].ToString();

                                    string insert = " INSERT INTO PaymentMonthWaterFee (FlatID,YearMonth,OldIndex,NewIndex,DelFlag,Created,Creator,Modified,Updator,TenementID,LossAvg)" +
                                                    " Values (@FlatID,@YearMonth,@OldIndex,@NewIndex,0,@Created,@Creator,@Modified,@Updator,@TenementID,@LossAvg)";
                                    using (SqlCommand command = new SqlCommand(insert, rConn))
                                    {
                                        command.CommandType = CommandType.Text;
                                        command.Parameters.Add(new SqlParameter("@FlatID", TenementID + FlatID));
                                        command.Parameters.Add(new SqlParameter("@YearMonth", YearMonth));
                                        command.Parameters.Add(new SqlParameter("@OldIndex", OldIndex));
                                        command.Parameters.Add(new SqlParameter("@NewIndex", NewIndex));

                                        command.Parameters.Add(new SqlParameter("@Created", Created));
                                        command.Parameters.Add(new SqlParameter("@Creator", Creator));
                                        command.Parameters.Add(new SqlParameter("@Modified", Modified));
                                        command.Parameters.Add(new SqlParameter("@Updator", Updator));

                                        command.Parameters.Add(new SqlParameter("@TenementID", TenementID));
                                        command.Parameters.Add(new SqlParameter("@LossAvg", LossAvg));

                                        command.ExecuteNonQuery();
                                    }
                                }
                                rConn.Close();
                            }

                        }
                    }
                }
                DbHelper.ExecuteNonQuery("Delete From PaymentMonthWaterFeeTmp where SessionId = '" + Session.SessionID + "'");
                ShowData();
                importDiv.Visible = false;
                mvldMessage.SetCompleteMessage("Dữ liệu đã lưu, hãy kiểm tra dữ liệu.");
            }
            catch (Exception exc)
            {
                mvldMessage.AddError("Lỗi Phát Sinh：" + exc.Message);
            }
        }

//.........这里部分代码省略.........
                        copy.DestinationTableName = "SongImport";
                        copy.BatchSize = 3000;
                        copy.BulkCopyTimeout = 99999;
                        for (int i = 0; i < Constants.ImportFullDataHeader.Length; i++)
                        {
                            copy.ColumnMappings.Add(i, Constants.ImportFullDataDbRef[i]);
                        }

                        copy.ColumnMappings.Add(dt.Columns.Count - 8, "SessionId");
                        copy.ColumnMappings.Add(dt.Columns.Count - 7, "ImportType");
                        copy.ColumnMappings.Add(dt.Columns.Count - 6, "Status");

                        copy.ColumnMappings.Add(dt.Columns.Count - 5, "DelFlag");
                        copy.ColumnMappings.Add(dt.Columns.Count - 4, "Updated");
                        copy.ColumnMappings.Add(dt.Columns.Count - 3, "Updator");
                        copy.ColumnMappings.Add(dt.Columns.Count - 2, "Created");
                        copy.ColumnMappings.Add(dt.Columns.Count - 1, "Creator");

                        copy.WriteToServer(dt);
                    }
                }

                DbHelper.ExecuteNonQuery("Update SongImport set SongImport.CopyrightOrg = S.CopyrightOrg, SongImport.CopyrightContractId = S.CopyrightContractId, ContractorId = S.ContractorId From SongImport, Song S Where SongImport.SongId = S.SongId and SessionId = '" + Session.SessionID + "' and SongImport.ImportType = '" + importType + "'");
                //DbHelper.ExecuteNonQuery("Update SongImport set SongImport.CopyrightOrg = Song.CopyrightOrg From SongImport, Song Where SongImport.SongId = Song.SongId and SongImport.ImportType = '" + importType + "' ");
                //DbHelper.ExecuteNonQuery("Update SongImport set ContractorId = S.ContractorId From SongImport, Song S where SessionId = '" + Session.SessionID + "' and SongImport.ImportType = '" + importType + "' and S.SongId = SongImport.SongId ");

                sql = "Select * from SongImport where SessionId = '" + Session.SessionID + "' and ImportType = '" + importType + "'";
                SqlDatabase db = new SqlDatabase();
                DataSet ds = new DataSet();
                DataSet dsTmp = new DataSet();

                SqlCommand cm = db.CreateCommand(sql);
                SqlDataAdapter da = new SqlDataAdapter(cm);
                da.Fill(ds);
                dt = ds.Tables[0];

                ArrayList sqlUpdate = new ArrayList();

                foreach (DataRow row in dt.Rows)
                {
                    string contractorId = row["ContractorId"].ToString();
                    string hbunRitsu = "";
                    if (!"".Equals(contractorId))
                    {
                        ContractorData data = new ContractorData();
                        ITransaction tran = factory.GetLoadObject(data, contractorId);
                        Execute(tran);
                        if (!HasError)
                        {
                            //編集の場合、DBに既存データを取得して設定する。
                            data = (ContractorData)tran.Result;
                            hbunRitsu = data.HbunRitsu;
                        }
                    }

                    string price = row["Price"].ToString();
                    string rate = hbunRitsu;
                    string priceNoTax = "";
                    string buyUnique = "";
                    string copyrightFeeUnique = "";
                    string KDDICommissionUnique = "";
                    string profitUnique = "";

                    if (!"".Equals(price) && !"".Equals(rate))
                    {
                        priceNoTax = Func.GetPriceNoTax(price);
                        buyUnique = Func.GetBuyUnique(priceNoTax, rate);
                        copyrightFeeUnique = Func.GetCopyrightFeeUnique(row["CopyrightContractId"].ToString(), priceNoTax, "1");
                        KDDICommissionUnique = Func.GetKDDICommissionUnique(priceNoTax);
                        profitUnique = Func.GetProfitUnique(priceNoTax, buyUnique, copyrightFeeUnique, KDDICommissionUnique);
                    }

                    string songId = row["SongId"].ToString();
                    sqlUpdate.Add("Update SongImport set hbunRitsu = '" + hbunRitsu + "', PriceNoTax = '" + priceNoTax + "', BuyUnique = '" + buyUnique + "', CopyrightFeeUnique = '" + copyrightFeeUnique + "', KDDICommissionUnique = '" + KDDICommissionUnique + "', ProfitUnique = '" + profitUnique + "'where SongId = '" + songId + "' and SessionId = '" + Session.SessionID + "' and SongImport.ImportType = '" + importType + "'");
                }

                for (int i = 0; i < sqlUpdate.Count; i++)
                {
                    DbHelper.ExecuteNonQuery((string)sqlUpdate[i]);
                }

                DbHelper.ExecuteNonQuery("Update SongImport set SongImport.Status = 1 From SongImport, SongMedia SM Where SongImport.SongId = SM.SongMediaId and SM.TypeId = '1' and SessionId = '" + Session.SessionID + "' and SongImport.ImportType = '" + importType + "'");

                db.Close();
                ///////////
                Session["FolderPath"] = fileUpload.FileName;
                Response.Redirect("FullUpdateListMasterImport.aspx", false);
            }
            catch (Exception ex)
            {
                mvImportMaster.AddError("エラーが発生しました: " + ex.Message);
            }
            finally
            {
                if (csvRead != null)
                {
                    csvRead.Dispose();
                }
            }
        }

        protected void btnExport_Click(object sender, EventArgs e)
        {
            DataSet ds = new DataSet();
            string sql = string.Empty;
            C1XLBook xlbBook = new C1XLBook();
            string fileName = HttpContext.Current.Server.MapPath(@"~\Report\Template\GuixeThang.xlsx");
            if (!Directory.Exists(@"~\Report\Building\" + Func.ParseString(Session["__BUILDINGID__"]) + @"\GuixeThang"))
            {
                Directory.CreateDirectory(HttpContext.Current.Server.MapPath(@"~\Report\Building\" + Func.ParseString(Session["__BUILDINGID__"]) + @"\GuixeThang"));
            }

            string strDT = DateTime.Now.ToString("yyyyMMddHHmmss");
            string strFilePath = @"~\Report\Building\" + Func.ParseString(Session["__BUILDINGID__"]) + @"\GuixeThang\GuiXeThang" + strDT + ".xlsx";
            string strFilePathExport = "Report/Building/" + Func.ParseString(Session["__BUILDINGID__"]) + "/GuixeThang/GuiXeThang" + strDT + ".xlsx";

            string fileNameDes = HttpContext.Current.Server.MapPath(strFilePath);

            File.Copy(fileName, fileNameDes);

            xlbBook.Load(fileNameDes);

            sql = " SELECT *";
            sql += " FROM v_GuiXeThang";
            sql += " WHERE BuildingId = '" + Func.ParseString(Session["__BUILDINGID__"]) + "' ";
            sql += " AND substring(NgayGui,1,6) <= '" + drpYear.SelectedValue + drpMonth.SelectedValue + "' AND ((NgayKetThuc is null) OR ";
            sql += "      (NgayKetThuc is not null and rtrim(LTRIM(NgayKetThuc)) <> '' and substring(NgayKetThuc,1,6) >= '" + drpYear.SelectedValue + drpMonth.SelectedValue + "')) Order by CustomerId";

            string[] sheetName = { "Oto", "XeMay", "XeDap", "Oto_HetHD", "XeMay_HetHD", "XeDap_HetHD" };
            int[] lines = { 4, 4, 4, 4, 4, 4 };

            using (SqlDatabase db = new SqlDatabase())
            {
                using (SqlCommand cm = db.CreateCommand(sql))
                {
                    SqlDataAdapter da = new SqlDataAdapter(cm);
                    da.Fill(ds);
                    db.Close();

                    if (ds != null)
                    {
                        int i = 4;
                        XLCellRange mrCell = new XLCellRange(0, 0, 0, 2);

                        XLStyle xlstStyle = new XLStyle(xlbBook);
                        //xlstStyle.AlignHorz = XLAlignHorzEnum.Left;
                        xlstStyle.AlignVert = XLAlignVertEnum.Top;
                        xlstStyle.WordWrap = true;
                        xlstStyle.Font = new Font("", 8, FontStyle.Regular);
                        xlstStyle.SetBorderColor(Color.Black);
                        xlstStyle.BorderBottom = XLLineStyleEnum.Thin;
                        xlstStyle.BorderTop = XLLineStyleEnum.Thin;
                        xlstStyle.BorderLeft = XLLineStyleEnum.Thin;
                        xlstStyle.BorderRight = XLLineStyleEnum.Thin;
                        xlstStyle.Format = "#,##0.00_);(#,##0.00)";

                        XLStyle xlstStyle01 = new XLStyle(xlbBook);
                        xlstStyle01.AlignHorz = XLAlignHorzEnum.Center;
                        xlstStyle01.AlignVert = XLAlignVertEnum.Top;
                        xlstStyle01.WordWrap = true;
                        xlstStyle01.Font = new Font("", 8, FontStyle.Regular);
                        xlstStyle01.SetBorderColor(Color.Black);
                        xlstStyle01.BorderBottom = XLLineStyleEnum.Thin;
                        xlstStyle01.BorderTop = XLLineStyleEnum.Thin;
                        xlstStyle01.BorderLeft = XLLineStyleEnum.Thin;
                        xlstStyle01.BorderRight = XLLineStyleEnum.Thin;

                        DataTable dt = ds.Tables[0];
                        foreach (DataRow rowType in dt.Rows)
                        {
                            XLSheet xlsSheet = xlbBook.Sheets[sheetName[Func.ParseInt(rowType[10]) - 1]];

                            string col01 = rowType[0].ToString();
                            string col02 = rowType[1].ToString();
                            string col03 = rowType[2].ToString();
                            string col04 = rowType[3].ToString();
                            string col05 = rowType[4].ToString();
                            string col06 = rowType[5].ToString();
                            string col07 = Func.FormatDMY(rowType[6].ToString());
                            string col08 = Func.FormatDMY(rowType[7].ToString());
                            string col09 = rowType[8].ToString();

                            i = lines[Func.ParseInt(rowType[10]) - 1];

                            xlsSheet[i, 0].Value = i - 3;
                            xlsSheet[i, 1].Value = col03;
                            xlsSheet[i, 2].Value = col04;
                            xlsSheet[i, 3].Value = col05;
                            xlsSheet[i, 4].Value = col06;
                            xlsSheet[i, 5].Value = col07;
                            xlsSheet[i, 6].Value = col08;
                            xlsSheet[i, 7].Value = col09;

                            xlsSheet[i, 0].Style = xlstStyle01;
                            xlsSheet[i, 1].Style = xlstStyle;
                            xlsSheet[i, 2].Style = xlstStyle;
                            xlsSheet[i, 3].Style = xlstStyle01;
                            xlsSheet[i, 4].Style = xlstStyle;
                            xlsSheet[i, 5].Style = xlstStyle01;
                            xlsSheet[i, 6].Style = xlstStyle01;
                            xlsSheet[i, 7].Style = xlstStyle;
//.........这里部分代码省略.........

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

 /// <summary>
 /// 曲メディアグリッドのデータを取得する処理
 /// </summary>
 /// <param name="rpt"></param>
 /// <param name="parentId"></param>
 /// <param name="panDetail"></param>
 private void ShowDetailData(Repeater rpt, string parentId, Panel panDetail)
 {
     SqlDatabase db = new SqlDatabase();
     SqlCommand cm = db.CreateCommand("Select * from SongMediaTemp where SongId = '" + parentId + "' and SessionId = '" + Session.SessionID + "' and ImportType = '" + importType + "'");
     SqlDataAdapter da = new SqlDataAdapter(cm);
     DataSet ds = new DataSet();
     da.Fill(ds);
     db.Close();
     rpt.DataSource = ds.Tables[0].DefaultView;
     rpt.DataBind();
     rpt.Visible = true;
     panDetail.Visible = true;
 }

        protected void btnRegister_Click(object sender, EventArgs e)
        {
            string[] yomiArray = { "ｱｲｳｴｵ", "ｶｷｸｹｺ", "ｻｼｽﾃﾓ", "ﾀﾁﾂﾃﾄ", "ﾅﾆﾇﾈﾉ", "ﾊﾋﾌﾍﾎ", "ﾏﾐﾑﾒﾓ", "ﾔﾕﾖ", "ﾗﾘﾙﾚﾛ", "ﾜｦﾝ" };
            string[] yomi = { "ｱ", "ｶ", "ｻ", "ﾀ", "ﾅ", "ﾊ", "ﾏ", "ﾔ", "ﾗ", "ﾜ" };

            try
            {
                string updator = Page.User.Identity.Name;
                string creator = Page.User.Identity.Name;
                string created = DateTime.Now.ToString("yyyyMMddHHmmss");
                string updated = DateTime.Now.ToString("yyyyMMddHHmmss");

                SqlDatabase db = new SqlDatabase();
                string sql = string.Empty;
                DataSet ds = new DataSet();
                sql = "Select Distinct AlbumId,AlbumTitle,AlbumTitleReadingFull,AlbumCdId,SaleDate From SongImport where AlbumId not in (Select AlbumId from Album) and SessionId = '" + Session.SessionID + "' and ImportType = '" + importType + "'";
                SqlCommand cm = db.CreateCommand(sql);
                SqlDataAdapter da = new SqlDataAdapter(cm);
                da.Fill(ds);
                db.Close();

                if (ds.Tables[0] != null)
                {
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        DataRow dr = ds.Tables[0].Rows[i];
                        string AlbumId = dr["AlbumId"].ToString().Replace("'", "''");
                        string AlbumTitle = dr["AlbumTitle"].ToString().Replace("'", "''");
                        string AlbumTitleReadingFull = dr["AlbumTitleReadingFull"].ToString().Replace("'", "''");
                        string AlbumCdId = dr["AlbumCdId"].ToString().Replace("'", "''");
                        string firstYomi = AlbumTitleReadingFull.Substring(0, 1);
                        string saleDate = Func.FormatYmd(dr["SaleDate"].ToString()).Replace("/", "");

                        for (int j = 0; j < yomiArray.Length; j++)
                        {
                            if (yomiArray[j].IndexOf(firstYomi) > 0)
                            {
                                firstYomi = yomi[j];
                            }
                        }
                        DbHelper.ExecuteNonQuery("INSERT INTO Album (AlbumId,Title,TitleReading,TitleReadingFull,CdId,SaleDate, DelFlag,Updated,Updator,Created,Creator)" +
                            " values('" + AlbumId + "','" + AlbumTitle + "','" + firstYomi + "','" + AlbumTitleReadingFull + "','" + AlbumCdId + "','" + saleDate + "','0','" + updated + "','" + updator + "','" + created + "','" + creator + "')");
                    }
                }

                sql = string.Empty;
                ds = new DataSet();
                sql = "Select Distinct ArtistId,ArtistName,ArtistNameReadingFull,AlphabetName From SongImport where ArtistId not in (Select ArtistId from Artist) and SessionId = '" + Session.SessionID + "' and ImportType = '" + importType + "'";
                cm = db.CreateCommand(sql);
                da = new SqlDataAdapter(cm);
                da.Fill(ds);
                db.Close();

                if (ds.Tables[0] != null)
                {
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        DataRow dr = ds.Tables[0].Rows[i];
                        string ArtistId = dr["ArtistId"].ToString().Replace("'", "''");
                        string ArtistName = dr["ArtistName"].ToString().Replace("'", "''");
                        string ArtistNameReadingFull = dr["ArtistNameReadingFull"].ToString().Replace("'", "''");
                        string firstYomi = ArtistNameReadingFull.Substring(0, 1);
                        string alphabetName = dr["AlphabetName"].ToString().Replace("'", "''");
                        for (int j = 0; j < yomiArray.Length; j++)
                        {
                            if (yomiArray[j].IndexOf(firstYomi) > 0)
                            {
                                firstYomi = yomi[j];
                            }
                        }
                        DbHelper.ExecuteNonQuery("INSERT INTO Artist (ArtistId,Name,NameReading,NameReadingFull,AlphabetName,DelFlag,Updated,Updator,Created,Creator)" +
                            " values('" + ArtistId + "','" + ArtistName + "','" + firstYomi + "','" + ArtistNameReadingFull + "','" + alphabetName + "','0','" + updated + "','" + updator + "','" + created + "','" + creator + "')");
                    }
                }

                sql = string.Empty;
                ds = new DataSet();
                sql = "Select Distinct LabelId,LabelName From SongImport where LabelId not in (Select LabelId from Label) and SessionId = '" + Session.SessionID + "' and ImportType = '" + importType + "'";
                cm = db.CreateCommand(sql);
                da = new SqlDataAdapter(cm);
                da.Fill(ds);
                db.Close();

                if (ds.Tables[0] != null)
                {
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        DataRow dr = ds.Tables[0].Rows[i];
                        string LabelId = dr["LabelId"].ToString().Replace("'", "''");
                        string LabelName = dr["LabelName"].ToString().Replace("'", "''");
                        DbHelper.ExecuteNonQuery("INSERT INTO Label (LabelId,Name,DelFlag,Updated,Updator,Created,Creator)" +
                            " values('" + LabelId + "','" + LabelName + "','0','" + updated + "','" + updator + "','" + created + "','" + creator + "')");
                    }
                }

                //新規
                //Full
                string insert = " INSERT INTO Song  (SongId,     Title,TitleReadingFull ,TitleReading, AlphabetTitle, ArtistId,GenreId,AlbumId,LabelId,ContractorId,IVT," +
                                " IVTType,CopyrightContractId,JasracWorksCode,SongWriter                     ,SongComposer                       , PRText,DelFlag,Updated , Updator, Created, Creator, DcSearchTitle, SbSearchTitle)	" +
                                " Select             SongId, SongTitle, SongTitleReading,    SongYomi, AlphabetTitle, ArtistId,GenreId,AlbumId,LabelId,ContractorId,cast(IVT as varchar(1))," +
//.........这里部分代码省略.........

        protected void btnExport_Click(object sender, EventArgs e)
        {
            DataSet ds = new DataSet();
            string sql = string.Empty;

            sql = " SELECT *";
            sql += " FROM v_MonthParkingCount";
            sql += " WHERE BuildingId = '" + Func.ParseString(Session["__BUILDINGID__"]) + "' ";
            sql += " Order By CompanyName";

            using (SqlDatabase db = new SqlDatabase())
            {
                using (SqlCommand cm = db.CreateCommand(sql))
                {
                    SqlDataAdapter da = new SqlDataAdapter(cm);
                    da.Fill(ds);
                    db.Close();

                    if (ds != null)
                    {
                        C1XLBook xlbBook = new C1XLBook();
                        string fileName = HttpContext.Current.Server.MapPath(@"\Report\Template\THSLXT_tpl.xlsx");

                        xlbBook.Load(fileName);
                        XLSheet xlsSheet = xlbBook.Sheets["DANH SÁCH BẢO VỆ"];
                        //xlsSheet.Name = drpMonth.SelectedValue + "_" + drpYear.SelectedValue;

                        int i = 0;
                        XLCellRange mrCell = new XLCellRange(0, 0, 0, 2);
                        xlsSheet.MergedCells.Add(mrCell);

                        XLStyle xlstStyle = new XLStyle(xlbBook);
                        xlstStyle.AlignHorz = XLAlignHorzEnum.Center;
                        xlstStyle.Font = new Font("", 12, FontStyle.Bold);
                        xlstStyle.SetBorderColor(Color.Black);

                        xlsSheet[i, 0].Value = "Tháng " + drpMonth.SelectedValue + "/" + drpYear.SelectedValue;
                        xlsSheet[i, 0].Style = xlstStyle;

                        xlsSheet[i + 1, 0].Value = "STT";
                        xlsSheet[i + 1, 1].Value = "Mã Nhân Viên";
                        xlsSheet[i + 1, 2].Value = "Họ và Tên";

                        XLStyle xlstStyle01 = new XLStyle(xlbBook);
                        xlstStyle01.AlignHorz = XLAlignHorzEnum.Center;
                        xlstStyle01.Font = new Font("", 10, FontStyle.Bold);
                        xlstStyle.SetBorderColor(Color.Black);

                        for (int j = 1; j <= 31; j++)
                        {
                            //xlsSheet[i, 2 + j].Value = j;
                            //DateTime date = new DateTime(Func.ParseInt(drpYear.SelectedValue), Func.ParseInt(drpMonth.SelectedValue), j);
                            //xlsSheet[i + 1, 2 + j].Value = dictionary[date.DayOfWeek.ToString().ToLower()];

                            //xlsSheet[i, 2 + j].Style = xlstStyle01;
                            //xlsSheet[i + 1, 2 + j].Style = xlstStyle01;
                            //if (j == DateTime.DaysInMonth(Func.ParseInt(drpYear.SelectedValue), Func.ParseInt(drpMonth.SelectedValue)))
                            //{
                            //    break;
                            //}
                        }

                        //i++;
                        //DataTable dt = ds.Tables[0];
                        //foreach (DataRow rowType in dt.Rows)
                        //{
                        //    int No = i;
                        //    i++;
                        //    string StaffId = rowType["StaffId"].ToString();
                        //    string Name = rowType["Name"].ToString();

                        //    xlsSheet[i, 0].Value = No;
                        //    xlsSheet[i, 1].Value = StaffId;
                        //    xlsSheet[i, 2].Value = Name;

                        //    xlsSheet[i, 0].Style = xlstStyle01;
                        //    xlsSheet[i, 1].Style = xlstStyle01;
                        //    xlsSheet[i, 2].Style = xlstStyle01;

                        //}

                        ////ScriptManager.RegisterStartupScript(Page, this.GetType(), "", "PopUp('/CSV/DownloadZipFile.aspx'," + PopupWidth + "," + PopupHeight + ",'EditFlat', true);", true);

                        ////xlsSheet[i++, 0].Value = "Ghi chú:";
                        //DataSet ds1 = new DataSet();
                        //sql = string.Empty;

                        //sql = " SELECT *";
                        //sql += " FROM BD_WorkingHour";
                        //sql += " WHERE BuildingId = '" + Func.ParseString(Session["__BUILDINGID__"]) + "' and DelFlag <> 1";
                        //sql += " Order By Name";

                        //using (SqlCommand cm1 = db.CreateCommand(sql))
                        //{
                        //    SqlDataAdapter da1 = new SqlDataAdapter(cm1);
                        //    da1.Fill(ds1);
                        //    db.Close();

                        //    if (ds != null)
                        //    {
//.........这里部分代码省略.........