frmselect.cs

对于中小型企业来说

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using houseAgency.mothedCls;
namespace houseAgency
{
    public partial class frmSelect : Form
    {
        StringBuilder strSql = new StringBuilder();
        string strMidle = string.Empty;
        string strWhere = string.Empty;//生成where语句
        ClsCon con = new ClsCon();
        public frmSelect()
        {
            InitializeComponent();
        }
        
        private void frmSelect_Load(object sender, EventArgs e)
        {
            #region//加载时列出所有房源信息
            try
            {
                con.ConDatabase();
                SqlDataAdapter da = new SqlDataAdapter("select * from view_house", con.conn);
                DataTable dt = new DataTable();
                da.Fill(dt);
                this.dataGridView1.DataSource = dt.DefaultView;
            }
            catch (Exception ey)
            {
            }

            flushFaove();
            flushfitment();
            flushfloor();
            flushmothed();
            flushseat();
            flushtype();
            unAble();
            #endregion
        }

        private void button1_Click(object sender, EventArgs e)
        {
            #region//生成SQL 

                #region//生成where条件字符串
                strSql.Append("select * from view_house where ");


                if (strMidle.IndexOf("house_companyName")!=-1)
                {
                    if (strWhere != string.Empty)
                    {
                        strWhere += "and " + "物业名称 like '%" + this.txtName.Text.Trim().ToString() + "%'" ;
                    }
                    else
                    {
                        strWhere += "物业名称 like '%" + this.txtName.Text.Trim().ToString() + "%'";
                    }
                    strMidle=strMidle.Replace("house_companyName", "#");
                 
                }
                if (strMidle.IndexOf("huose_typeID")!= -1)
                {
                    if (strWhere != string.Empty)
                    {
                        strWhere += "and " + "类型='" + this.cboXing.Text.ToString() + "'" ;
                    }
                    else
                    {
                        strWhere += "类型='" + this.cboXing.Text.ToString() + "'" ;
                    }
                    strMidle=strMidle.Replace("huose_typeID", "#");

                }
                if (strMidle.IndexOf("house_seatID") != -1)
                {
                    if (strWhere != string.Empty)
                    {
                        strWhere += "and " + "幢座='" + this.cobDong.Text.ToString() + "'" ;
                    }
                    else
                    {
                        strWhere += "幢座='" + this.cobDong.Text.ToString() + "'" ;
                    }
                    strMidle=strMidle.Replace("house_seatID", "#");

                }
                if (strMidle.IndexOf("house_favorID") != -1)
                {
                    if (strWhere != string.Empty)
                    {
                        strWhere += "and " + "朝向='" + this.cboFavoe.Text.ToString() + "'" ;
                    }
                    else
                    {
                        strWhere += "朝向='" + this.cboFavoe.Text.ToString() + "'" ;
                    }
                    strMidle=strMidle.Replace("house_favorID", "#");
                  
                }
              

                if (strMidle.IndexOf("house_fitmentID") != -1)
                {
                    if (strWhere != string.Empty)
                    {
                        strWhere += "and " + "装修='" + this.cobZhuang.Text.ToString() + "'" ;
                    }
                    else
                    {
                        strWhere += "装修='" + this.cobZhuang.Text.ToString() + "'" ;
                    }
                    strMidle = strMidle.Replace("house_fitmentID", "#");
                   
                }
                if (strMidle.IndexOf("house_mothedID") != -1)
                {
                    if (strWhere != string.Empty)
                    {
                        strWhere += "and " + "用途='" + this.cobUser.Text.ToString() + "'";
                    }
                    else
                    {
                        strWhere += "用途='" + this.cobUser.Text.ToString() + "'";
                    }
                    strMidle = strMidle.Replace("house_mothedID", "#");
                    //tbho.house_mothedID=tbmo.house_mothedID and 

                }
                if (strMidle.IndexOf("house_floorID") != -1)
                {
                    if (strWhere != string.Empty)
                    {
                        strWhere += "and " + "楼层='" + this.cobFlood.Text.ToString() + "'";
                    }
                    else
                    {
                        strWhere += "楼层='" + this.cobFlood.Text.ToString() + "'" ;
                    }
                    strMidle = strMidle.Replace("house_floorID", "#");
                }
               
                if (strMidle.IndexOf("house_buildYear") != -1)
                {
                    if (strWhere != string.Empty)
                    {
                        strWhere += "and " + "建筑年限='" + this.nudYear.Text.Trim().ToString() + "'" ;
                    }
                    else
                    {
                        strWhere += "建筑年限='" + this.nudYear.Text.Trim().ToString() + "'";
                    }
                    strMidle = strMidle.Replace("house_buildYear", "#");
     
                }
                if (strMidle.IndexOf("house_area") != -1)
                {
                    if (strWhere != string.Empty)
                    {
                        strWhere += "and " + "建筑面积 like '%" + this.txtArea.Text.Trim().ToString() + "%' ";
                    }
                    else
                    {
                        strWhere += "建筑面积 like '%" + this.txtArea.Text.Trim().ToString() + "%'";
                    }
                    strMidle = strMidle.Replace("house_area", "#");

                }
                if (strMidle.IndexOf("house_price") != -1)
                {
                    if (strWhere != string.Empty)
                    {
                        strWhere += "and " + "价钱 like '%" + this.textBox1.Text.Trim().ToString() + "%'" ;
                    }
                    else
                    {
                        strWhere += "价钱 like '%" + this.textBox1.Text.Trim().ToString() + "%'";
                    }
                    strMidle = strMidle.Replace("house_price", "#");
                }

                if (strMidle.IndexOf("house_ID") != -1)
                {
                    if (strWhere != string.Empty)
                    {
                        strWhere += "and " + "房屋编号 like '%" + this.textBox2.Text.Trim().ToString() + "%'";
                    }
                    else
                    {
                        strWhere += "房屋编号 like '%" + this.textBox2.Text.Trim().ToString() + "%'";
                    }
                    strMidle = strMidle.Replace("house_ID", "#");
                }
                #endregion 
                //******************************************************************//
                #region//生成数源
                try
                {
                    string strS = strWhere.Substring(strWhere.Length - 4);
                    if (strS.Trim() == "and")
                    {
                        strWhere = strWhere.Substring(0, strWhere.Length - 4);//去掉尾and
                    }
                }
                catch { return; }
                
                strSql.Append(strWhere);
                string strK = strSql.ToString();
                try
                {
                        SqlDataAdapter da = new SqlDataAdapter(strK, con.conn);
                        DataTable dt = new DataTable();
                        da.Fill(dt);
                        this.dataGridView1.DataSource = dt.DefaultView;

                        ChuShiHua();
                        clearAll();
                        this.button1.Enabled = false;
                }
                catch (Exception ey)
                {
                    MessageBox.Show(ey.Message);
                }
                strWhere = string.Empty;
                strMidle = string.Empty;
                strSql.Remove(0,strSql.ToString().Length);
                
                #endregion
            #endregion

            button1.Enabled = false;
            this.textBox2.Text = "";
            this.textBox2.Enabled = false;
            checkBox11.Checked = false;
        }
      
        private void button2_Click(object sender, EventArgs e)
        {
            ChuShiHua();
            clearAll();
            this.button1.Enabled = false;
        } 
        
        private void button4_Click(object sender, EventArgs e)
        {
            CheckAll();
            this.button1.Enabled = true;
        }

        #region//取清与全选
        private void clearAll()
        {
            this.checkBox1.Checked = false;
            this.checkBox2.Checked = false; 
            this.checkBox3.Checked = false;
            this.checkBox4.Checked = false;
            this.checkBox5.Checked = false;
            this.checkBox6.Checked = false;
            this.checkBox7.Checked = false;
            this.checkBox8.Checked = false;
            this.checkBox9.Checked = false;
            this.checkBox10.Checked = false;

        }

        private void CheckAll()
        {
            this.checkBox1.Checked = true;
            this.checkBox2.Checked = true;
            this.checkBox3.Checked = true;
            this.checkBox4.Checked = true;
            this.checkBox5.Checked = true;
            this.checkBox6.Checked = true;
            this.checkBox7.Checked = true;
            this.checkBox8.Checked = true;
            this.checkBox9.Checked = true;
            this.checkBox10.Checked = true;

        }
        #endregion

        #region//fulsh method
        private void flushFaove()
        {
            try
            {
                SqlDataAdapter da = new SqlDataAdapter("select * from tb_favor", con.conn);
                DataTable dt = new DataTable();
                da.Fill(dt);
                cboFavoe.DataSource = dt.DefaultView;
                cboFavoe.DisplayMember = "favor_name";
                cboFavoe.ValueMember = "house_favorID";

            }
            catch (Exception ey)
            {
                MessageBox.Show(ey.Message);
            }
        }

        private void flushfitment()
        {
            try
            {
                SqlDataAdapter da = new SqlDataAdapter("select * from tb_fitment", con.conn);
                DataTable dt = new DataTable();
                da.Fill(dt);
                cobZhuang.DataSource = dt.DefaultView;
                cobZhuang.DisplayMember = "fitment_name";
                cobZhuang.ValueMember = "house_fitmentID";

            }
            catch (Exception ey)
            {
                MessageBox.Show(ey.Message);
            }
        }

        private void flushfloor()
        {
            try
            {
                SqlDataAdapter da = new SqlDataAdapter("select * from tb_floor", con.conn);
                DataTable dt = new DataTable();
                da.Fill(dt);
                cobFlood.DataSource = dt.DefaultView;
                cobFlood.DisplayMember = "floor_name";
                cobFlood.ValueMember = "house_floorID";

            }
            catch (Exception ey)
            {
                MessageBox.Show(ey.Message);
            }
        }

        private void flushmothed()
        {
            try
            {
                SqlDataAdapter da = new SqlDataAdapter("select * from tb_mothed", con.conn);
                DataTable dt = new DataTable();
                da.Fill(dt);
                cobUser.DataSource = dt.DefaultView;
                cobUser.DisplayMember = "mothed_name";
                cobUser.ValueMember = "house_mothedID";

            }
            catch (Exception ey)
            {
                MessageBox.Show(ey.Message);
            }
        }