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