C# AccessHelper.SelectToDataTable方法代碼示例

 /// <summary>
 /// 檢查指定表的邏輯關聯性
 /// </summary>
 /// <param name="sql"></param>
 /// <param name="table"></param>
 /// <param name="crl"></param>
 /// <param name="tName"></param>
 public void CheckLuoJi(string sql, KeyValuePair<string, string> table, MainCrl crl, string tName)
 {
     AccessHelper accessHelper = new AccessHelper();
     AccessHelper ah = new AccessHelper();
     //字段值大於等於某一字段值
     DataTable dt = accessHelper.SelectToDataTable("select * from GuiZe where 規則類型='邏輯關聯性'");
     if (dt.Rows.Count > 0)
     {
         if (table.Key.Equals("ZBXXB"))
         {
             string path = table.Value;
             DataTable dataTable = ah.SelectToDataTable("select * from ZBXXB", path);
             for(int i=0;i<dt.Rows.Count;i++)
             {
                 DataTable ResultTable = ah.SelectToDataTable(dt.Rows[i]["表達式"].ToString(), path);
                 if(ResultTable.Rows.Count>0)
                 {
                     for (int j = 0; j < ResultTable.Rows.Count;j++ )
                     {
                         for (int k = 0; k < dataTable.Rows.Count;k++ )
                         {
                             if (ResultTable.Rows[j]["ZDTYBM"].Equals(dataTable.Rows[k]["ZDTYBM"]))
                             {
                                 ComMsg.ResultShow.Add(new ResultEntity(tName, dt.Rows[i]["規則類型"].ToString(), dt.Rows[i]["規則編號"].ToString(), dt.Rows[i]["規則名稱"].ToString(),
                 dt.Rows[i]["字段名"] + dt.Rows[i]["錯誤描述"].ToString(), (k + 1) + "", dt.Rows[i]["嚴重程度"].ToString(), DateTime.Now.ToShortDateString()));
                             }
                         }
                     }
                 }
             }
         }
     }
 }

 private void CGWJPZFrm_Load(object sender, EventArgs e)
 {
     this.muLuTableAdapter.Fill(this.settingDataSet.MuLu);
     if (CGWJID > 0)
     {
         AccessHelper ah = new AccessHelper();
         string sql = "select * from WenJian where ID=" + CGWJID;
         DataTable dt = ah.SelectToDataTable(sql);
         if (dt.Rows.Count > 0)
         {
             DataRow dr = dt.Rows[0];
             cbAction.Text = dr["文件開始字符"].ToString();
             tbEnd.Text = dr["文件結束字符"].ToString();
             tbType.Text = dr["文件類型"].ToString();
             if ((bool)dr["是否必填"])
                 rbYes.Checked = true;
             else
                 rbNo.Checked = true;
             string type = dr["文件擴展名"].ToString();
             foreach(Control c in this.groupBox1.Controls)
             {
                 if (c is CheckBox)
                 {
                    if(type.Contains(((CheckBox)c).Text))
                    {
                        ((CheckBox)c).Checked = true;
                    }
                 }
             }
         }
     }
 }

 private void tbEdit_Click(object sender, EventArgs e)
 {
     AccessHelper ah = new AccessHelper();
     string pwd = System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(tbOldPwd.Text, "MD5");
     string sql = "select * from UserTable where uName='" + tbUserName.Text + "' and uPwd='" + pwd + "'";
     DataTable dt = ah.SelectToDataTable(sql);
     if (dt.Rows.Count == 0)
     {
         MessageBox.Show("用戶名或密碼錯誤！");
         return;
     }
     if(tbNewPwd.Text.Length<6)
     {
         MessageBox.Show("請輸入長度大於等於6位的密碼！");
         return;
     }
     if (!tbNewPwd.Text.Trim().Equals(tbRePwd.Text.Trim()))
     {
         MessageBox.Show("兩次輸入密碼不一致！");
         return;
     }
     string editPwd = System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(tbNewPwd.Text, "MD5");
     string editSql = "update UserTable set uName='"+tbUserName.Text.Trim()+"',uPwd='"+editPwd+"' where id=" + dt.Rows[0]["id"].ToString();
     if(ah.ExecuteSQLNonquery(editSql))
     {
         this.DialogResult = DialogResult.OK;
     }
     else
     {
         MessageBox.Show("修改失敗！");
     }
 }

 /// <summary>
 /// 檢查是否有相同條件的規則數據
 /// </summary>
 /// <param name="DJLX">登記類型</param>
 /// <param name="GZLX">規則類型</param>
 /// <param name="GZBM">規則編碼</param>
 /// <param name="GZZBM">規則子編碼</param>
 /// <returns></returns>
 public bool ExistGZ(string DJLX, string GZLX,string GZBM,string GZZBM)
 {
     string sql = "select * from GuiZe where 登記類型='" + DJLX + "' and 規則類型='" + GZLX + "' and 規則編號='"+GZBM+"' and 規則子編號="+GZZBM+"";
     AccessHelper ah = new AccessHelper();
     DataTable dataTable = ah.SelectToDataTable(sql);
     if (dataTable.Rows.Count > 0)
         return true;
     else
         return false;
 }

 /// <summary>
 /// 是否包含相同的目錄
 /// </summary>
 /// <param name="mulu">目錄</param>
 /// <returns>是否有相同的目錄數據，是true，否false</returns>
 public bool ExistSameMuLu(string mulu)
 {
     AccessHelper ah = new AccessHelper();
     string sql = "select * from MuLu where 目錄名稱='" + mulu + "'";
     DataTable dt = ah.SelectToDataTable(sql);
     if (dt.Rows.Count > 0)
         return true;
     else
         return false;
 }

 /// <summary>
 /// 根據表名和列名查詢檢查項是否存在
 /// </summary>
 /// <param name="table"></param>
 /// <param name="row"></param>
 /// <returns>true 不存在，false 存在相同數據</returns>
 public bool ExistZhiBiaoByTableAndRow(string table,string row)
 {
     string sql = "select * from ZhiBiao where 表名='"+table+"' and 列名='"+row+"'";
     AccessHelper ah = new AccessHelper();
     DataTable dataTable = ah.SelectToDataTable(sql);
     if (dataTable.Rows.Count > 0)
         return false;
     else
         return true;
 }

 /// <summary>
 /// 遞歸獲取行政區劃的完整級別
 /// </summary>
 /// <param name="code">行政區編碼</param>
 /// <param name="result">完整行政區名稱</param>
 /// <returns></returns>
 public void GetQuHua(string code,string result)
 {
     AccessHelper ah = new AccessHelper();
     string sql = "select * from QuHua where 行政區編碼='"+code+"'";
     DataTable dt = ah.SelectToDataTable(sql);
     if(dt.Rows.Count>0)
     {
         fullName = (dt.Rows[0]["行政區名稱"].ToString()+result);
         if(!dt.Rows[0]["父級編碼"].ToString().Equals("0"))
         {
             GetQuHua(dt.Rows[0]["父級編碼"].ToString(), fullName);
         }
     }
 }

 /// <summary>
 /// 返回字典編碼集合
 /// </summary>
 /// <param name="LXBM">類型編碼</param>
 /// <returns></returns>
 public List<string> GetZiDianCodeByLXBM(string LXBM)
 {
     List<string> returnList = new List<string>();
     AccessHelper ah = new AccessHelper();
     DataTable dt = ah.SelectToDataTable("select * from ZiDian where 類型編碼='" + LXBM + "'");
     if(dt.Rows.Count!=0)
     {
         for(int i=0;i<dt.Rows.Count;i++)
         {
             returnList.Add(dt.Rows[i]["字典代碼"].ToString());
         }
     }
     return returnList;
 }

 /// <summary>
 /// 登錄事件
 /// </summary>
 /// <param name="sender"></param>
 /// <param name="e"></param>
 private void button2_Click(object sender, EventArgs e)
 {
     AccessHelper ah = new AccessHelper();
     string pwd = System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(tbPassword.Text, "MD5");
     string sql = "select * from UserTable where uName='"+tbUserName.Text+"' and uPwd='"+pwd+"'";
     DataTable dt = ah.SelectToDataTable(sql);
     if(dt.Rows.Count==0)
     {
         MessageBox.Show("用戶名或密碼錯誤！");
     }
     else
     {
         MainFrm frm = new MainFrm();
         this.ShowInTaskbar = false;
         this.Hide();
         frm.Show();
     }
 }

 /// <summary>
 /// 根據字典類型編碼獲取字典名稱
 /// </summary>
 /// <param name="code">字典類型編碼</param>
 /// <returns>字典名稱</returns>
 public string GetZiDianName(string code)
 {
     if (ComMsg.ziDian.ContainsKey(code))
     {
         return ComMsg.ziDian[code];
     }
     else
     {
         AccessHelper ah = new AccessHelper();
         DataTable dt = ah.SelectToDataTable("select 類型名稱 from ZiDian where 類型編碼='" + code + "'");
         ah.Close();
         if (dt != null && dt.Rows.Count > 0)
         {
             ComMsg.ziDian.Add(code, dt.Rows[0]["類型名稱"].ToString());
             return dt.Rows[0]["類型名稱"].ToString();
         }
     }
     return "";
 }

 /// <summary>
 /// 查詢字典集合
 /// </summary>
 /// <returns>字典集合</returns>
 public List<ZiDianEntity> GetZiDianList()
 {
     List<ZiDianEntity> zdList = new List<ZiDianEntity>();
     AccessHelper ah = new AccessHelper();
     DataTable dt = ah.SelectToDataTable("select * from ZiDian");
     if(dt.Rows.Count!=0)
     {
         for(int i=0;i<dt.Rows.Count;i++)
         {
             ZiDianEntity zd = new ZiDianEntity();
             zd.Lxbm = dt.Rows[i]["類型編碼"].ToString();
             zd.Lxmc = dt.Rows[i]["類型名稱"].ToString();
             zd.Zddm = dt.Rows[i]["字典代碼"].ToString();
             zd.Zdmc = dt.Rows[i]["字典名稱"].ToString();
             zdList.Add(zd);
         }
     }
     return zdList;
 }

 /// <summary>
 /// 窗體加載事件
 /// </summary>
 private void XZQHBMFrm_Load(object sender, EventArgs e)
 {
     AccessHelper ah = new AccessHelper();
     string sql = "select * from QuHua where ID=" + XZQHID;
     DataTable dt = ah.SelectToDataTable(sql);
     if(dt.Rows.Count>0)
     {
         DataRow dr = dt.Rows[0];
         tbQHBM.Text = dr["行政區編碼"].ToString();
         oldQHBM = dr["行政區編碼"].ToString();
         tbQHMC.Text = dr["行政區名稱"].ToString();
         FJQHBM = dr["父級編碼"].ToString();
     }
     tbFJQHBM.Text = FJQHBM;
 }

        /// <summary>
        /// 檢查文件信息
        /// </summary>
        /// <param name="infoList"></param>
        public void CheckFileMsg(List<FileInfo> infoList,string path,MainCrl crl)
        {
            crl.rtbLog.Text += "\n " + DateTime.Now.ToLongTimeString() + "開始檢查文件目錄及文件";
            List<string> files = new List<string>();//去除所有父級目錄後的文件信息
            //讀取了目標文件夾下所有的文件信息，將這些文件和數據庫的信息進行成果比對
            if (infoList == null) return;
            for (int i = 0; i < infoList.Count(); i++)
            {
                //去除選定目錄的所有父級目錄，隻保留選定文件夾所包含的文件夾路徑
                files.Add(infoList[i].FullName.Replace(path + @"\", ""));
                //截取文件夾
                //folders.Add(files[i].Substring(0, files[i].LastIndexOf(@"\")));
                //截取文件
                // folderFiles.Add(files[i].Substring(files[i].LastIndexOf(@"\")+1));
            }
            //檢查當前文件是否在數據庫配置中
            AccessHelper ah = new AccessHelper();
            System.Data.DataTable dt = ah.SelectToDataTable("select * from WenJian");
            List<string> dateList = new List<string>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                if(((bool)dt.Rows[i]["是否必填"]))
                {
                    bool showError = true;//是否顯示問題數據
                    for (int j = 0; j < files.Count; j++)
                    {
                        if(dt.Rows[i]["文件開始字符"].ToString().Contains("|"))
                        {
                            string[] actionList = dt.Rows[i]["文件開始字符"].ToString().Split("|".ToArray());
                            if (actionList.Contains(files[j].Substring(0,files[j].LastIndexOf(@"\"))))
                            {
                                if (dt.Rows[i]["文件結束字符"].ToString().Contains("|"))//有“|”說明有多種選擇，將字符按|進行分割比較
                                {
                                    string[] fileNameList = dt.Rows[i]["文件結束字符"].ToString().Split("|".ToArray());
                                    foreach(string temp in fileNameList)
                                    {
                                        if(files[j].Contains(temp))
                                        {
                                            //在此判斷後綴
                                            if (dt.Rows[i]["文件擴展名"].ToString().Contains("|"))
                                            {
                                                string[] fileEndList = dt.Rows[i]["文件擴展名"].ToString().Split("|".ToArray());
                                                foreach(string endTemp in fileEndList)
                                                {
                                                    if (endTemp.Contains(files[j].Substring(files[j].LastIndexOf(".") + 1)))
                                                    {
                                                        files.Remove(files[j]);
                                                        showError = false;
                                                        break;
                                                    }
                                                }
                                                break;
                                            }
                                            else
                                            {
                                                if(dt.Rows[i]["文件擴展名"].ToString().Contains(files[j].Substring(files[j].LastIndexOf(".") + 1)))
                                                {
                                                    files.Remove(files[j]);
                                                    showError = false;
                                                    break;
                                                }
                                                break;
                                            }
                                        }
                                    }
                                }
                                else if (files[j].Contains(dt.Rows[i]["文件結束字符"].ToString()))
                                {
                                    if (dt.Rows[i]["文件擴展名"].ToString().Contains("|"))
                                    {
                                        string[] fileEndList = dt.Rows[i]["文件擴展名"].ToString().Split("|".ToArray());
                                        foreach (string endTemp in fileEndList)
                                        {
                                            if (endTemp.Contains(files[j].Substring(files[j].LastIndexOf(".") + 1)))
                                            {
                                                files.Remove(files[j]);
                                                showError = false;
                                                break;
                                            }
                                            break;
                                        }
                                    }
                                    else
                                    {
                                        if (dt.Rows[i]["文件擴展名"].ToString().Contains(files[j].Substring(files[j].LastIndexOf(".") + 1)))
                                        {
                                            files.Remove(files[j]);
                                            showError = false;
                                            break;
                                        }
                                    }
                                }
                            }
                        }
                        else if (files[j].Contains(dt.Rows[i]["文件開始字符"].ToString()))
                        {
//.........這裏部分代碼省略.........

        /// <summary>
        /// 字段值大於等於某一值
        /// </summary>
        /// <param name="sql">檢查的sql語句</param>
        /// <param name="table">檢查的數據表</param>
        /// <param name="crl">主頁麵需要顯示的窗體</param>
        public void ZDZDYDYMZ(string sql,KeyValuePair<string,string> table,MainCrl crl,string tName)
        {
            AccessHelper accessHelper = new AccessHelper();
            AccessHelper ah = new AccessHelper();
            DataTable checkTable = ah.SelectToDataTable(sql, table.Value); //待檢查數據
            //字段值大於等於某一字段值
            DataTable dt = accessHelper.SelectToDataTable("select * from GuiZe where 規則類型='基礎指標' and 校驗類型='SQL'");
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    if (dt.Rows[i]["表名"].Equals(table.Key))
                    {
                        string checkSql = "select * from " + table.Key + " where " + dt.Rows[i]["表達式"];
                        DataTable resultTable = ah.SelectToDataTable(checkSql, table.Value);

                        //獲取兩個數據源的差集
                        IEnumerable<DataRow> query2 = checkTable.AsEnumerable().Except(resultTable.AsEnumerable(), DataRowComparer.Default);
                        //兩個數據源的差集集合
                        if (query2 != null && query2.Count() > 0)
                        {
                            DataTable dt3 = query2.CopyToDataTable();
                            if (dt3.Rows.Count > 0)
                            {
                                for (int j = 0; j < checkTable.Rows.Count; j++)
                                {
                                    for (int k = 0; k < dt3.Rows.Count;k++ )
                                    {
                                        if (dt3.Rows[k]["ZDTYBM"].Equals(checkTable.Rows[j]["ZDTYBM"]))
                                        {
                                            ComMsg.ResultShow.Add(new ResultEntity(tName, dt.Rows[i]["規則類型"].ToString(), dt.Rows[i]["規則編號"].ToString(), dt.Rows[i]["規則名稱"].ToString(),
                            dt.Rows[i]["字段名"]+dt.Rows[i]["錯誤描述"].ToString(), (j + 1) + "", dt.Rows[i]["嚴重程度"].ToString(), DateTime.Now.ToShortDateString()));
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }

 /// <summary>
 /// 查詢所有的需要質檢的表數據
 /// </summary>
 /// <param name="path">路徑</param>
 /// <returns></returns>
 public List<ZBXXBEntity> GetZBXXBList(string path)
 {
     AccessHelper ah = new AccessHelper();
     DataTable dt = ah.SelectToDataTable(" select * from ZBXXB", path);
     if(dt.Rows.Count>0)
     {
         List<ZBXXBEntity> zbxxbList = new List<ZBXXBEntity>();
         foreach( DataRow row in dt.Rows)
         {
             ZBXXBEntity zbxxb = new ZBXXBEntity();
             zbxxb.BJS_JSSJ = row["BJS_JSSJ"].ToString();
             zbxxb.BJS_KSSJ = row["BJS_KSSJ"].ToString();
             zbxxb.CFFY = row["CFFY"].ToString();
             zbxxb.CFRQ = row["CFRQ"].ToString();
             zbxxb.CFSXH = row["CFSXH"].ToString();
             zbxxb.CFWH = row["CFWH"].ToString();
             zbxxb.CFZT = row["CFZT"].ToString();
             zbxxb.CFZZRQ = row["CFZZRQ"].ToString();
             zbxxb.CSR_SGZH = row["CSR_SGZH"].ToString();
             zbxxb.DJLX = row["DJLX"].ToString();
             zbxxb.DJRQ = row["DJRQ"].ToString();
             zbxxb.DWXZ = row["DWXZ"].ToString();
             zbxxb.DYJE = row["DYJE"].ToString();
             zbxxb.DYMJ = row["DYMJ"].ToString();
             zbxxb.DYQDJ = row["DYQDJ"].ToString();
             zbxxb.DYR=row["DYR"].ToString();
             zbxxb.DYRLX=row["DYRLX"].ToString();
             zbxxb.DYRQ = row["DYRQ"].ToString();
             zbxxb.DYRZJLX = row["DYRZJLX"].ToString();
             zbxxb.DYSXH = row["DYSXH"].ToString();
             zbxxb.DYTDJG = row["DYTDJG"].ToString();
             zbxxb.DYZT = row["DYZT"].ToString();
             zbxxb.DYZZRQ = row["DYZZRQ"].ToString();
             zbxxb.FTMJ = row["FTMJ"].ToString();
             zbxxb.FZJG = row["FZJG"].ToString();
             zbxxb.GDHTH = row["GDHTH"].ToString();
             zbxxb.GDJGH = row["GDJGH"].ToString();
             zbxxb.GG_JSSJ = row["GG_JSSJ"].ToString();
             zbxxb.GLQLR = row["GLQLR"].ToString();
             zbxxb.GLTDQLZSH = row["GLTDQLZSH"].ToString();
             zbxxb.GLTDTXQLZMSH = row["GLTDTXQLZMSH"].ToString();
             zbxxb.GLZDBH = row["GLZDBH"].ToString();
             zbxxb.GYR = row["GYR"].ToString();
             zbxxb.HBLX = row["HBLX"].ToString();
             zbxxb.HYDM = row["HYDM"].ToString();
             zbxxb.JBR_SGZH = row["JBR_SGZH"].ToString();
             zbxxb.JFRQ = row["JFRQ"].ToString();
             zbxxb.JZMD = row["JZMD"].ToString();
             zbxxb.JZRJL = row["JZRJL"].ToString();
             zbxxb.JZWMJ = row["JZWMJ"].ToString();
             zbxxb.LCHS = row["LCHS"].ToString();
             zbxxb.PHID = row["PHID"].ToString();
             zbxxb.PZJG = row["PZJG"].ToString();
             zbxxb.PZYT = row["PZYT"].ToString();
             zbxxb.PZYTBM = row["PZYTBM"].ToString();
             zbxxb.QDJG = row["QDJG"].ToString();
             zbxxb.QLR = row["QLR"].ToString();
             zbxxb.QSXZ = row["QSXZ"].ToString();
             zbxxb.SFJNQK = row["SFJNQK"].ToString();
             zbxxb.SFSJXZ = row["SFSJXZ"].ToString();
             zbxxb.SFZZYDFGDJ = row["SFZZYDFGDJ"].ToString();
             zbxxb.SHR_SGZH = row["SHR_SGZH"].ToString();
             zbxxb.SHRMC = row["SHRMC"].ToString();
             zbxxb.SJXZSM = row["SJXZSM"].ToString();
             zbxxb.SYQLX = row["SYQLX"].ToString();
             zbxxb.SYQMJ = row["SYQMJ"].ToString();
             zbxxb.SYQX = row["SYQX"].ToString();
             zbxxb.TDDYMJ = row["TDDYMJ"].ToString();
             zbxxb.TFBH = row["TFBH"].ToString();
             zbxxb.TXZSBH = row["TXZSBH"].ToString();
             zbxxb.XYR = row["XYR"].ToString();
             zbxxb.XZQBM = row["YGDJ"].ToString();
             zbxxb.YGDJ = row["YGDJ"].ToString();
             zbxxb.YGR = row["YGR"].ToString();
             zbxxb.YTDQLRBSM = row["YTDQLRBSM"].ToString();
             zbxxb.YYDJ = row["YYDJ"].ToString();
             zbxxb.YYR = row["YYR"].ToString();
             zbxxb.YZDBSM = row["YZDBSM"].ToString();
             zbxxb.ZDBH = row["ZDBH"].ToString();
             zbxxb.ZDLX = row["ZDLX"].ToString();
             zbxxb.ZDMJ = row["ZDMJ"].ToString();
             zbxxb.ZDTYBM = row["ZDTYBM"].ToString();
             zbxxb.ZDZL = row["ZDZL"].ToString();
             zbxxb.ZDZT = row["ZDZT"].ToString();
             zbxxb.ZJBH = row["ZJBH"].ToString();
             zbxxb.ZJLX = row["ZJLX"].ToString();
             zbxxb.ZSBH = row["ZSBH"].ToString();
             zbxxb.ZZRQ = row["ZZRQ"].ToString();
             zbxxbList.Add(zbxxb);
         }
         return zbxxbList;
     }
     return null;
 }