C# execSQL.connectionClass.bindDataset方法代码示例

        private void Load_NguoiNhan()
        {
            try
            {
                string sql = "EXEC EOFFICE_NPC.ChungNT.ThongBao_DonVi_Select @ID, @noiSoan";
                SqlParameter[] pa = new SqlParameter[2];
                pa[0] = new SqlParameter("@ID", lbID.Text);
                pa[1] = new SqlParameter("@noiSoan", lbDonVi.Text);
                execSQL.connectionClass ob = new execSQL.connectionClass();
                DataSet ds = ob.bindDataset(sql, pa);
                DataView dv = ds.Tables[1].DefaultView;
                TreeNode nodePC, nodeDL;
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    nodePC = new TreeNode();
                    nodePC.Value = dr["MaDonViQL"].ToString();
                    nodePC.Text = dr["TenDonViQL"].ToString();
                    nodePC.SelectAction = TreeNodeSelectAction.None;
                    dv.RowFilter = string.Format("MaDonViQL = '{0}'", dr["MaDonViQL"].ToString());
                    foreach (DataRowView dr2 in dv)
                    {
                        nodeDL = new TreeNode();
                        nodeDL.Value = dr2.Row["MaDonVi"].ToString();
                        nodeDL.Text = dr2.Row["TenDonVi"].ToString();
                        nodeDL.SelectAction = TreeNodeSelectAction.None;
                        nodeDL.ShowCheckBox = true;
                        nodeDL.Checked = dr["MaDonViQL"].ToString() == "A00";
                        nodePC.ChildNodes.Add(nodeDL);
                    }
                    tvNguoiNhan.Nodes.Add(nodePC);
                }
                tvNguoiNhan.CollapseAll();

                // Danh sach NHOM
                sql = "EXEC EOFFICE_NPC.ChungNT.ThongBao_Nhom_Select @userID";
                pa = new SqlParameter[1];
                pa[0] = new SqlParameter("@userID", UserId);
                ds = ob.bindDataset(sql, pa);
                nodePC = new TreeNode();
                nodePC.Value = "A00";
                nodePC.Text = "Danh sách nhóm";
                nodePC.SelectAction = TreeNodeSelectAction.None;
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    nodeDL = new TreeNode();
                    nodeDL.Value = dr["ID"].ToString();
                    nodeDL.Text = string.Format("{0} ({1} đơn vị)", dr["TenNhom"].ToString(), dr["SoLuong"].ToString());
                    nodeDL.SelectAction = TreeNodeSelectAction.None;
                    nodeDL.ShowCheckBox = true;
                    nodePC.ChildNodes.Add(nodeDL);
                }
                tvNhom.Nodes.Add(nodePC);
                tvNhom.ExpandAll();
            }
            catch { }
        }

        private void Load_Data()
        {
            try
            {
                string sql = "EXEC LuuTru.CaNhan_CapHoSo_Select @userID, 1";
                SqlParameter[] pa = new SqlParameter[1];
                pa[0] = new SqlParameter("@userID", UserId);
                execSQL.connectionClass ob = new execSQL.connectionClass();
                dlCapHS.DataSource = ob.bindDataset(sql, pa).Tables[0];
                dlCapHS.DataBind();

                sql = "EXEC LuuTru.CaNhan_CongVan_Select @IDCapSo, @UserID";
                pa = new SqlParameter[2];
                pa[0] = new SqlParameter("@IDCapSo", Request["ID"]);
                pa[1] = new SqlParameter("@UserID", UserId);
                DataSet ds = ob.bindDataset(sql, pa);
                if (ds.Tables[0].Rows.Count == 0)
                {
                    lbError.Text = "Không tìm thấy công văn";
                    btLuu.Enabled = false;
                }
                else
                {
                    lbMaCV.Text = ds.Tables[0].Rows[0][0].ToString();
                    lbIDCapSo.Text = ds.Tables[0].Rows[0][1].ToString();
                    lbSo.Text = ds.Tables[0].Rows[0][2].ToString();
                    lbSoCT.Text = ds.Tables[0].Rows[0][3].ToString();
                    lbNgay.Text = ds.Tables[0].Rows[0][4].ToString();
                    lbNoiGui.Text = ds.Tables[0].Rows[0][5].ToString();
                    lbTrichYeu.Text = ds.Tables[0].Rows[0][6].ToString();
                    lbButPheLD.Text = ds.Tables[0].Rows[0][7].ToString();
                    try
                    {
                        dlCapHS.SelectedValue = ds.Tables[0].Rows[0][8].ToString();
                    }
                    catch { }
                    if (ds.Tables[0].Rows[0][8].ToString() != "0")
                        lbError.Text = "Công văn này đã được lưu vào hồ sơ";
                    else
                        lbError.Text = "";
                    btLuu.Enabled = true;
                }
            }
            catch (Exception ex)
            {
                lbError.Text = "Có lỗi trong quá trình đọc dữ liệu. " + ex.Message;
                btLuu.Enabled = false;
            }
        }

        public void load_data(string tbSearch)
        {
            tvLuuTru.Nodes.Clear();
            try
            {
                string sql = "EXEC LuuTru.CaNhan_Tree @userID, @search";
                SqlParameter[] pa = new SqlParameter[2];
                pa[0] = new SqlParameter("@userID", UserId);
                pa[1] = new SqlParameter("@search", tbSearch);
                execSQL.connectionClass ob = new execSQL.connectionClass();
                DataSet ds = ob.bindDataset(sql, pa);
                DataView dvChild = ds.Tables[4].DefaultView;
                TreeNode nParent, nChild;
                foreach (DataRow dr in ds.Tables[1].Rows)
                {
                    nParent = add_childNode(dr["IDHoSo"].ToString(), dr["TenHoSo"].ToString(), ds.Tables[2], ds.Tables[3]);

                    dvChild.RowFilter = "ParentID = " + dr["IDHoSo"].ToString();
                    foreach (DataRowView v in dvChild)
                    {
                        nChild = add_childNode(v.Row["IDHoSo"].ToString(), v.Row["TenHoSo"].ToString(), ds.Tables[2], ds.Tables[3]);
                        nParent.ChildNodes.Add(nChild);
                    }

                    tvLuuTru.Nodes.Add(nParent);
                }
                tvLuuTru.ExpandAll();
                tvLuuTru.ShowCheckBoxes = TreeNodeTypes.None;
                btXoa.Visible = false;
                btChon.Text = "Xóa";
            }
            catch (Exception ex) { Response.Write(ex.Message); }
        }

        protected void btLuu_Click(object sender, EventArgs e)
        {
            try
            {
                execSQL.connectionClass ob = new execSQL.connectionClass();
                string sql, hosoID;
                DataSet ds;
                if (lbThem.Text == "THEM")
                {
                    sql = "EXEC LuuTru.CaNhan_CapHoSo_Update 0, @TenHoSo, @NguoiTao, 1, 0";
                    SqlParameter[] pa = new SqlParameter[2];
                    pa[0] = new SqlParameter("@TenHoSo", tbCapHS.Text);
                    pa[1] = new SqlParameter("@NguoiTao", UserId);
                    ds = ob.bindDataset(sql, pa);
                    hosoID = ds.Tables[0].Rows[0][1].ToString();
                }
                else hosoID = dlCapHS.SelectedValue;

                sql = "EXEC LuuTru.CaNhan_CongVan_Luu @MaCV, @HoSo, @IDCapSo";
                SqlParameter[] para = new SqlParameter[3];
                para[0] = new SqlParameter("@MaCV", lbMaCV.Text);
                para[1] = new SqlParameter("@HoSo", hosoID);
                para[2] = new SqlParameter("@IDCapSo", lbIDCapSo.Text);
                ds = ob.bindDataset(sql, para);
                if (ds.Tables[0].Rows[0][0].ToString() == "1")
                {
                    lbError.Text = "Lưu công văn thành công";
                    btLuu.Enabled = false;
                }
                else lbError.Text = "Không thể lưu công văn";
            }
            catch (Exception ex) { lbError.Text = "Có lỗi trong quá trình ghi dữ liệu. " + ex.Message; }
        }

        private void Load_Data()
        {
            try
            {
                string sent = Request["SENT"] == null ? "2" : Request["SENT"];
                lbTieuDe.Text = sent == "2" ? "THÔNG BÁO TRỰC TIẾP" : sent == "1" ? hlThongBaoGui.Text : hlThongBaoDen.Text;

                string sql = "EXEC EOFFICE_NPC.ChungNT.ThongBao_NoiDung_Get @userID, @userDV, @sent, @search";
                SqlParameter[] pa = new SqlParameter[4];
                pa[0] = new SqlParameter("@userID", UserId);
                pa[1] = new SqlParameter("@userDV", UserInfo.Profile.GetPropertyValue("MaDonVi"));
                pa[2] = new SqlParameter("@sent", sent);
                pa[3] = new SqlParameter("@search", tbNoiDung.Text);
                execSQL.connectionClass ob = new execSQL.connectionClass();
                DataSet ds = ob.bindDataset(sql, pa);
                DataColumn dc = new DataColumn("Link");
                ds.Tables[0].Columns.Add(dc);
                ds.Tables[0].AcceptChanges();
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    dr.BeginEdit();
                    dr["Link"] = DotNetNuke.Common.Globals.NavigateURL(TabId, "SOAN", "mid/" + ModuleId, "ID/" + dr["ID"].ToString());
                    dr.EndEdit();
                }
                gvThongBao.DataSource = ds.Tables[0];
                gvThongBao.DataBind();
                foreach (GridViewRow dr in gvThongBao.Rows)
                    if (dr.Cells[0].Text == "0")
                        dr.CssClass = "new";
            }
            catch (Exception ex) { Response.Write("Có lỗi trong quá trình đọc dữ liệu. " + ex.Message); }
        }

 private void load_all()
 {
     try
     {
         string sql = "EXEC ChungNT.ThongBao_Nhom_Select @userID";
         SqlParameter[] pa = new SqlParameter[1];
         pa[0] = new SqlParameter("@userID", UserId);
         execSQL.connectionClass ob = new execSQL.connectionClass();
         gvData.DataSource = ob.bindDataset(sql, pa).Tables[0];
         gvData.DataBind();
     }
     catch (Exception ex) { lbError.Text = "Có lỗi trong quá trình đọc dữ liệu. " + ex.Message; }
 }

 protected void btChiaSe_Click(object sender, EventArgs e)
 {
     try
     {
         string sql = "EXEC LuuTru.CaNhan_ChiaSe @userID, @username, @hsID";
         SqlParameter[] pa = new SqlParameter[3];
         pa[0] = new SqlParameter("@userID", UserId);
         pa[1] = new SqlParameter("@username", tbNguoiNhan.Text);
         pa[2] = new SqlParameter("@hsID", dlHoSo.SelectedValue);
         execSQL.connectionClass ob = new execSQL.connectionClass();
         DataSet ds = ob.bindDataset(sql, pa);
         lbError.Text = string.Format("Chia sẻ thành công <b>{0}</b> tài liệu", ds.Tables[0].Rows[0][0].ToString());
     }
     catch (Exception ex) { lbError.Text = "Có lỗi trong quá trình đọc dữ liệu. " + ex.Message; }
 }

 private void load_data()
 {
     try
     {
         string sql = "EXEC LuuTru.CaNhan_CapHoSo_Select @userID, 2";
         SqlParameter[] pa = new SqlParameter[1];
         pa[0] = new SqlParameter("@userID", UserId);
         execSQL.connectionClass ob = new execSQL.connectionClass();
         DataSet ds = ob.bindDataset(sql, pa);
         gvData.DataSource = ds.Tables[0];
         gvData.DataBind();
         dlHoSo.DataSource = ds.Tables[1];
         dlHoSo.DataBind();
     }
     catch (Exception ex) { lbError.Text = "Có lỗi trong quá trình đọc dữ liệu. " + ex.Message; }
 }

 protected void Page_Load(object sender, EventArgs e)
 {
     if (IsPostBack) return;
     hlThoat.NavigateUrl = DotNetNuke.Common.Globals.NavigateURL(TabId);
     try
     {
         string sql = "EXEC LuuTru.CaNhan_CapHoSo_Select @userID, 1";
         SqlParameter[] pa = new SqlParameter[1];
         pa[0] = new SqlParameter("@userID", UserId);
         execSQL.connectionClass ob = new execSQL.connectionClass();
         DataSet ds = ob.bindDataset(sql, pa);
         dlHoSo.DataSource = ds.Tables[0];
         dlHoSo.DataBind();
     }
     catch (Exception ex) { lbError.Text = "Có lỗi trong quá trình đọc dữ liệu. " + ex.Message; }
 }

 protected void btLuu_Click(object sender, EventArgs e)
 {
     if (fileUpload.HasFile)
         try
         {
             // UPLOAD FILE
             string fileName = DateTime.Now.ToString("yyMMdd_HHmmss_") + UserId + fileUpload.FileName.Remove(0, fileUpload.FileName.LastIndexOf("."));
             string savePath = DateTime.Today.ToString("yyyy-MM");
             string filePath = Server.MapPath(@"DesktopModules\PHLT\") + savePath;
             if (!Directory.Exists(filePath))
                 Directory.CreateDirectory(filePath);
             filePath = filePath + @"\" + fileName;
             savePath = Request.ApplicationPath + "/DesktopModules/PHLT/" + savePath + "/" + fileName;
             fileUpload.SaveAs(filePath);
             // CAP HS
             execSQL.connectionClass ob = new execSQL.connectionClass();
             string sql, hosoID;
             DataSet ds;
             if (lbThem.Text == "THEM")
             {
                 sql = "EXEC LuuTru.CaNhan_CapHoSo_Update 0, @TenHoSo, @NguoiTao, 1, 0";
                 SqlParameter[] pa = new SqlParameter[2];
                 pa[0] = new SqlParameter("@TenHoSo", tbCapHS.Text);
                 pa[1] = new SqlParameter("@NguoiTao", UserId);
                 ds = ob.bindDataset(sql, pa);
                 hosoID = ds.Tables[0].Rows[0][1].ToString();
             }
             else hosoID = dlCapHS.SelectedValue;
             // VAN BAN
             sql = string.Format("EXEC LuuTru.CaNhan_VanBan_Update 0, N'{0}', '{1}', N'{2}', N'{3}', N'{4}', '{5}', N'{6}', {7}, {8}, 0",
                 tbKyHieu.Text, ob.dateFormat(tbNgayThang.Text), tbNoiSoan.Text, tbMoTa.Text, tbGhiChu.Text,
                 savePath, fileUpload.FileName, hosoID, UserId);
             ds = ob.fillDataset(sql);
             if (ds.Tables[0].Rows[0][0].ToString() == "1")
             {
                 lbError.Text = "Lưu văn bản thành công";
                 btLuu.Enabled = false;
             }
             else lbError.Text = "Không thể lưu văn bản";
         }
         catch (Exception ex) { lbError.Text = "Có lỗi trong quá trình ghi dữ liệu. " + ex.Message; }
     else lbError.Text = "Chưa có file đính kèm";
 }

        private DataSet tim_kiem(string key)
        {
            string maDV = UserInfo.Profile.GetPropertyValue("MaDonVi");

            execSQL.connectionClass ob = new execSQL.connectionClass();
            string sql = @"EXEC dbo.CV_spCongVanDen_Search @USerID, @MaDonVi, @MaDonViQL, -1, @Nam, -1, -1, 
            @TuSo, @TuSo, @TuNgay, @TuNgay, @KyHieu, -1, -1, @MaNoiGoi, NULL, NULL, @TrichYeu, 0, 0, -1, -1";
            SqlParameter[] pa = new SqlParameter[9];
            if (dlLoaiCV.SelectedValue == "1")
            {
                pa[0] = new SqlParameter("@USerID", UserId);
                pa[1] = new SqlParameter("@MaDonVi", maDV);
                pa[2] = new SqlParameter("@MaDonViQL", maDV.Substring(0, maDV.Length - 3));
                pa[3] = new SqlParameter("@Nam", dlNam.SelectedValue);
                pa[4] = new SqlParameter("@TuSo", tbSo.Text);
                if (tbNgay.Text == "")
                    pa[5] = new SqlParameter("@TuNgay", "NULL");
                else
                    pa[5] = new SqlParameter("@TuNgay", ob.dateFormat(tbNgay.Text));
                pa[6] = new SqlParameter("@KyHieu", tbKyHieu.Text);
                pa[7] = new SqlParameter("@MaNoiGoi", dlNoiSoan.SelectedValue);
                pa[8] = new SqlParameter("@TrichYeu", key);
            }
            else
            {
                sql = @"EXEC dbo.CV_spCongVanDi_Search @USerID, @MaDonVi, @MaDonViQL, -1, @Nam, -1, -1, 
                @TuSo, @TuSo, @TuNgay, @TuNgay, @KyHieu, -1, @MaNoiSoanThao, @TrichYeu, 0, 0, -1, -1";
                pa[0] = new SqlParameter("@USerID", UserId);
                pa[1] = new SqlParameter("@MaDonVi", maDV);
                pa[2] = new SqlParameter("@MaDonViQL", maDV.Substring(0, maDV.Length - 3));
                pa[3] = new SqlParameter("@Nam", dlNam.SelectedValue);
                pa[4] = new SqlParameter("@TuSo", tbSo.Text);
                if (tbNgay.Text == "")
                    pa[5] = new SqlParameter("@TuNgay", "NULL");
                else
                    pa[5] = new SqlParameter("@TuNgay", ob.dateFormat(tbNgay.Text));
                pa[6] = new SqlParameter("@KyHieu", tbKyHieu.Text);
                pa[7] = new SqlParameter("@MaNoiSoanThao", dlNoiSoan.SelectedValue);
                pa[8] = new SqlParameter("@TrichYeu", key);
            }
            return ob.bindDataset(sql, pa);
        }

 private void load_id()
 {
     try
     {
         tvData.Nodes.Clear();
         string sql = "EXEC ChungNT.ThongBao_Nhom_Select_ID @ID, @noiSoan";
         SqlParameter[] pa = new SqlParameter[2];
         pa[0] = new SqlParameter("@ID", lbID.Text);
         pa[1] = new SqlParameter("@noiSoan", UserInfo.Profile.GetPropertyValue("MaDonVi"));
         execSQL.connectionClass ob = new execSQL.connectionClass();
         DataSet ds = ob.bindDataset(sql, pa);
         if (ds.Tables[0].Rows.Count > 0)
             tbTenNhom.Text = ds.Tables[0].Rows[0]["TenNhom"].ToString();
         else
             tbTenNhom.Text = "";
         DataView dv = ds.Tables[2].DefaultView;
         TreeNode nodePC, nodeDL;
         foreach (DataRow dr in ds.Tables[1].Rows)
         {
             nodePC = new TreeNode();
             nodePC.Value = dr["MaDonViQL"].ToString();
             nodePC.Text = dr["TenDonViQL"].ToString();
             nodePC.SelectAction = TreeNodeSelectAction.None;
             dv.RowFilter = string.Format("MaDonViQL = '{0}'", dr["MaDonViQL"].ToString());
             foreach (DataRowView dr2 in dv)
             {
                 nodeDL = new TreeNode();
                 nodeDL.Value = dr2.Row["MaDonVi"].ToString();
                 nodeDL.Text = dr2.Row["TenDonVi"].ToString();
                 nodeDL.SelectAction = TreeNodeSelectAction.None;
                 nodeDL.ShowCheckBox = true;
                 nodeDL.Checked = dr["MaDonViQL"].ToString() == "A00";
                 nodePC.ChildNodes.Add(nodeDL);
             }
             tvData.Nodes.Add(nodePC);
         }
         tvData.CollapseAll();
     }
     catch (Exception ex) { lbError.Text = "Có lỗi trong quá trình đọc dữ liệu. " + ex.Message; }
 }

 protected void gvData_RowCommand(object sender, GridViewCommandEventArgs e)
 {
     if (e.CommandName == "SUA")
         try
         {
             int rowID = Convert.ToInt32(e.CommandArgument);
             lbID.Text = gvData.Rows[rowID].Cells[0].Text;
             string sql = "EXEC LuuTru.CaNhan_CapHoSo_Select_ID @ID";
             SqlParameter[] pa = new SqlParameter[1];
             pa[0] = new SqlParameter("@ID", lbID.Text);
             execSQL.connectionClass ob = new execSQL.connectionClass();
             DataSet ds = ob.bindDataset(sql, pa);
             tbHoSo.Text = ds.Tables[0].Rows[0]["TenHoSo"].ToString();
             cbTinhTrang.Checked = ds.Tables[0].Rows[0]["TinhTrang"].ToString() != "1";
             try
             {
                 dlHoSo.SelectedValue = ds.Tables[0].Rows[0]["ParentID"].ToString();
             }
             catch { }
             lbError.Text = "";
         }
         catch (Exception ex) { lbError.Text = "Có lỗi trong quá trình đọc dữ liệu. " + ex.Message; }
 }

 protected void btCapNhat_Click(object sender, EventArgs e)
 {
     try
     {
         string sql = "EXEC LuuTru.CaNhan_CapHoSo_Update @ID, @TenHoSo, @NguoiTao, @TinhTrang, @ParentID";
         SqlParameter[] pa = new SqlParameter[5];
         pa[0] = new SqlParameter("@ID", lbID.Text);
         pa[1] = new SqlParameter("@TenHoSo", tbHoSo.Text);
         pa[2] = new SqlParameter("@NguoiTao", UserId);
         pa[3] = new SqlParameter("@TinhTrang", cbTinhTrang.Checked ? 0 : 1);
         pa[4] = new SqlParameter("@ParentID", dlHoSo.SelectedValue);
         execSQL.connectionClass ob = new execSQL.connectionClass();
         DataSet ds = ob.bindDataset(sql, pa);
         if (ds.Tables[0].Rows[0][0].ToString() == "1")
         {
             btBoQua_Click(sender, e);
             load_data();
             lbError.Text = "Cập nhật thành công";
         }
         else lbError.Text = "Không thể cập nhật";
     }
     catch (Exception ex) { lbError.Text = "Có lỗi trong quá trình đọc dữ liệu. " + ex.Message; }
 }

 private void Load_DinhKem()
 {
     try
     {
         execSQL.connectionClass ob = new execSQL.connectionClass();
         string cmdTxt = "EXEC EOFFICE_NPC.ChungNT.ThongBao_DinhKem_Select @ID, @userID";
         SqlParameter[] pa = new SqlParameter[2];
         pa[0] = new SqlParameter("@ID", lbID.Text);
         pa[1] = new SqlParameter("@userID", UserId);
         gvDinhKem.DataSource = ob.bindDataset(cmdTxt, pa).Tables[0];
         gvDinhKem.DataBind();
     }
     catch { }
 }