组合查询和存储过程

来源：互联网发布：软件使用说明怎么写编辑：程序博客网时间：2024/06/11 19:17

父窗体代码

using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Linq;using System.Text;using System.Threading.Tasks;using System.Windows.Forms;using crcs.UI;using crcs.Entity;namespace crcs.UI{    public partial class frmZuhechaxunMuban : Form    {        public frmZuhechaxunMuban()        {            InitializeComponent();        }        //public class EmptyUI        //{        //}        //将查询的内容转换为数据库中的列名称        public virtual string ToName(string Combo)        {            return "";        }        //获得数据库名称        protected virtual string Getdbtable()        {            return "";        }        private void frmOpWorkRecord_Load(object sender, EventArgs e)        {          }        private void btnOK_Click(object sender, EventArgs e)        {            //组合查询判断是否为空            if (cmbRelation1.Text == "")            {                if (cmbName1.Text == "" || cmbOper1.Text == "" || txtContent1.Text == "")                {                    MessageBox.Show("查询内容不可为空，请重新输入", "提示");                    return;                }            }            if (cmbRelation1.Text != "" && cmbRelation2.Text == "")            {                if (cmbName1.Text == "" || cmbOper1.Text == "" || txtContent1.Text == "" || cmbName2.Text == "" || cmbOper2.Text == "" || txtContent2.Text == "")                {                    MessageBox.Show("查询内容不可为空，请重新输入", "警告");                    return;                }            }            if (cmbRelation1.Text != "" && cmbRelation2.Text != "")            {                if (cmbName1.Text == "" || cmbOper1.Text == "" || txtContent1.Text == "" || cmbName2.Text == "" || cmbOper2.Text == "" || txtContent2.Text == "" || cmbName3.Text == "" || cmbOper3.Text == "" || txtContent3.Text == "")                {                    MessageBox.Show("查询内容不可为空，请重新输入", "警告");                    return;                }            }            if (cmbRelation1.Text == "" && cmbRelation2.Text != "")            {                MessageBox.Show("输入错误", "警告");                return;            }            //将参数传递给实体            crcs.Entity.ZUHECHAXUN zuhechaxun = new ZUHECHAXUN();            zuhechaxun.name1 = ToName(cmbName1.Text);            zuhechaxun.name2 = ToName(cmbName2.Text);            zuhechaxun.name3 = ToName(cmbName3.Text);            zuhechaxun.Operator1 = cmbOper1.Text;            zuhechaxun.Operator2 = cmbOper2.Text;            zuhechaxun.Operator3 = cmbOper3.Text;            zuhechaxun.Context1 = txtContent1.Text;            zuhechaxun.Context2 = txtContent2.Text;            zuhechaxun.Context3 = txtContent3.Text;            zuhechaxun.Relation1 = ToName(cmbRelation1.Text);            zuhechaxun.Relation2 = ToName(cmbRelation2.Text);            zuhechaxun.table = Getdbtable();            //开始查询            crcs.Facade.QueryFactory ZHCX = new Facade.QueryFactory ();            DataTable table = ZHCX .Fzuhechaxun(zuhechaxun);            if (table.Rows.Count >1)            {                dataGridView1.DataSource = table;            }            else            {                MessageBox.Show("没有数据");            }        }    }}

子窗体代码

using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Text;using System.Windows.Forms;namespace crcs.UI{    public partial class frmInquiryLineState : crcs.UI.frmZuhechaxunMuban    {        public frmInquiryLineState()        {            InitializeComponent();        }        public override string ToName(string Combo)        {            switch (Combo)            {                case"账号":                    return "userID";                case"姓名":                    return "StudentName";                case "性别":                    return "Sex";                case "学院":                    return "College";                case "年级":                    return "Grade";                case "专业":                    return "Profession";                case "注册日期":                    return "Date";                case "注册时间":                    return "Time";                case "使用状态":                    return "Status";                case "注册者":                    return "Caozuoyuan";                case"和":                    return "and";                case"或":                    return"or";                default:                    return "";                            }        }      }}

存储过程代码

USE [crcs]GO/****** Object:  StoredProcedure [dbo].[zuhechaxun]    Script Date: 2017/7/23 11:51:19 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[zuhechaxun] -- Add the parameters for the stored procedure here@name1 varchar(20),@Operator1 varchar(20),@Context1 varchar(20),@name2 varchar(20),@Operator2 varchar(20),@Context2 varchar(20),@name3 varchar(20),@Operator3 varchar(20),@Context3 varchar(20),@Relation1 varchar(20),@Relation2 varchar(20),@Getdbtable varchar(20)--将数据表也设成一个变量，这样就可以使代码更加灵活ASdeclare @sql varchar(500)--临时存放sql语句BEGINSET NOCOUNT ON;    --char(32)空格；char(39)是单引号；SET @sql ='select * from '+@Getdbtable +' where ' +@name1 +@Operator1+char(39) + @Context1 +char(39)if (@Relation1!= '')--如果第一个组合关系不为空BEGIN--在第一个组合关系的基础上，加上第二个查询条件SET @sql=@sql +char(32) +@Relation1 +char(32)+@name2 +@Operator2+char(39) + @Context2 +char(39)if @Relation2!= ''BEGIN--加上第三个查询条件set @sql =@sql+@Relation2+char(32)+@name3 +@Operator3+char(39) + @Context3 +char(39)ENDENDEXECUTE(@sql)END

阅读全文

0 0