DataTable和DataParameter联系

最新推荐文章于 2021-06-23 16:44:00 发布

k_1991

最新推荐文章于 2021-06-23 16:44:00 发布

阅读量717

点赞数

分类专栏：工作随笔 C#

本文链接：https://blog.csdn.net/k_1991/article/details/41248349

版权

C# 同时被 2 个专栏收录

44 篇文章 0 订阅

订阅专栏

工作随笔

16 篇文章 0 订阅

订阅专栏

将这些查询条件存入一个新建的DataTable里面种

DataTable dtAri = new DataTable();
            dtAri.Columns.Add("id", typeof(System.Int32));
            dtAri.Columns.Add("cgd", typeof(System.String));
            dtAri.Columns.Add("mtr", typeof(System.String));
            dtAri.Columns.Add("cmt", typeof(System.String));
            dtAri.Columns.Add("txt", typeof(System.String));
            dtAri.Columns.Add("psn_txt", typeof(System.String));
            dtAri.Columns.Add("mtr_mcs", typeof(System.Int32));
            dtAri.Columns.Add("pvd_txt", typeof(System.String));
            DataRow ariRow = dtAri.NewRow();
          
            if (!String.IsNullOrEmpty(dtxtMcsSup.Text))
            {
                ariRow["mtr_mcs"] = dtxtMcsSup.Value;
            }
            if (!String.IsNullOrEmpty(dtxtPvd.Text))
            {
                ariRow["pvd_txt"] = dtxtPvd.Value;
            }
            if (!String.IsNullOrEmpty(txtMtrTxt.Text))
            {
                ariRow["txt"] = txtMtrTxt.Text;
            }
            if (!String.IsNullOrEmpty(txtWlCode.Text))
            {
                ariRow["mtr"] = txtWlCode.Text;
            }
            if (!String.IsNullOrEmpty(txtShtCode.Text))
            {
                ariRow["cgd"] = txtShtCode.Text;
            }
            if (!String.IsNullOrEmpty(txtPerPer.Text))
            {
                ariRow["psn_txt"] = txtPerPer.Text;
            }
            ariRow["cmt"] = this.LogonUserID;
            dtAri.Rows.Add(ariRow);

后台数据操作，创建一个数组，存储查询条件。根据上面传过来的DataTable的一行值进行判断

KYDataParameter[] parameters = new KYDataParameter[9];
            parameters[0] = new KYDataParameter("mtr_type", String.Empty);
            parameters[1] = new KYDataParameter("pvd_txt", String.Empty);
            parameters[2] = new KYDataParameter("mtr_txt", String.Empty);
            parameters[3] = new KYDataParameter("mtr_sht", String.Empty);
            parameters[4] = new KYDataParameter("ari_cgd", String.Empty);
            parameters[5] = new KYDataParameter("sgd_psntxt", String.Empty);
            parameters[6] = new KYDataParameter("bdt", DateTime.Now.Date);
            parameters[7] = new KYDataParameter("edt", DateTime.Now.Date);
            parameters[8] = new KYDataParameter("usr", KYDbType.VarChar, 50);

            if (queryData!=null&&queryData.Rows.Count>0)
            {
                DataRow ariRow = queryData.Rows[0];
                if (!DBNull.Value.Equals(ariRow["mtr_mcs"]))
                {
                    whereBuilder.Append(" AND mtr.mcs = @mtr_type  ");
                    parameters[0].Value = ariRow["mtr_mcs"];

                }
                if (!DBNull.Value.Equals(ariRow["pvd_txt"]))
                {
                    whereBuilder.Append("  AND ari.pvd = @pvd_txt ");
                    parameters[1].Value = ariRow["pvd_txt"];

                }
                if (!DBNull.Value.Equals(ariRow["txt"]))
                {
                    whereBuilder.Append(" AND mtr.txt = @mtr_txt ");
                    parameters[2].Value = ariRow["txt"];

                }
                if (!DBNull.Value.Equals(ariRow["mtr"]))
                {
                    whereBuilder.Append(" AND mtr.sht = @mtr_sht  ");
                    parameters[3].Value = ariRow["mtr"];

                }
                if (!DBNull.Value.Equals(ariRow["cgd"]))
                {
                    // whereBuilder.Append("  AND ari.cgd = @ari_cgd  ");
                    whereBuilder.Append("  AND ari.cgd LIKE '%' + @ari_cgd + '%' ");

                    parameters[4].Value = ariRow["cgd"];


                }
                if (!DBNull.Value.Equals(ariRow["psn_txt"]))
                {
                    whereBuilder.Append(" AND  sgd.psn_txt = @sgd_psntxt ");
                    parameters[5].Value = ariRow["psn_txt"];

                }

                if (queryDate.Length == 2)
                {
                    whereBuilder.Append(" AND ari.dat BETWEEN @bdt AND @edt ");
                    parameters[6].Value = queryDate[0];
                    parameters[7].Value = queryDate[1];

                }

                parameters[8].Value = ariRow["cmt"];
            }