职员信息查询

一、先看一下实现效果：

SQL Server 表查询

SQL Server 表结构

二、案例源码：

      //----------------------显示职员信息查询窗体-----------------------------

        private void menuItem8_Click(object sender, System.EventArgs e)

        {

            if (this.checkChildFrmExist("LookupStafferInfo") == true) return;

 

            LookupStafferInfo newFrm = new LookupStafferInfo();

            newFrm.MdiParent = this;

            newFrm.Show();  

        }

后台代码：

            // sql数据适配器           

            this.sqlDataAdapter1.SelectCommand = this.sqlSelectCommand1;

            this.sqlDataAdapter1.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] {

            new System.Data.Common.DataTableMapping("Table", "职员基本信息表", new System.Data.Common.DataColumnMapping[] {

                        new System.Data.Common.DataColumnMapping("姓名", "姓名"),

                        new System.Data.Common.DataColumnMapping("姓名简码", "姓名简码"),

                        new System.Data.Common.DataColumnMapping("性别", "性别"),

                        new System.Data.Common.DataColumnMapping("出生日期", "出生日期"),

                        new System.Data.Common.DataColumnMapping("籍贯", "籍贯"),

                        new System.Data.Common.DataColumnMapping("民族", "民族"),

                        new System.Data.Common.DataColumnMapping("办公电话", "办公电话"),

                        new System.Data.Common.DataColumnMapping("婚姻状况", "婚姻状况"),

                        new System.Data.Common.DataColumnMapping("单位名称", "单位名称"),

                        new System.Data.Common.DataColumnMapping("文化程度", "文化程度"),

                        new System.Data.Common.DataColumnMapping("职员编号", "职员编号"),

                        new System.Data.Common.DataColumnMapping("单位编号", "单位编号")})});

 

            // sql 命令

            this.sqlSelectCommand1.CommandText = "SELECT a.姓名, a.姓名简码, a.性别, a.出生日期, a.籍贯, a.民族, a.办公电话, a.婚姻状况, b.单位名称, a.文化程度,

a."职员编号, b.单位编号 FROM 职员基本信息表 a INNER JOIN 组织机构编码表 b ON a.单位编号 = b.单位编号";

            this.sqlSelectCommand1.Connection = this.sqlConnection1;

 

            // sql连接

            this.sqlConnection1.ConnectionString = "workstation id=localhost;Integrated Security=SSPI;Database=hrmbook;";

            this.sqlConnection1.FireInfoMessageEventOnUserErrors = false;

 

            // 查找职员信息

            this.AcceptButton = this.btn_Research;

            this.AutoScaleBaseSize = new System.Drawing.Size(8, 18);

            this.ClientSize = new System.Drawing.Size(1182, 423);

            this.Controls.Add(this.dataGrid1);

            this.Controls.Add(this.groupBox1);

            this.Icon = ((System.Drawing.Icon)(resources.GetObject("$this.Icon")));

            this.Name = "LookupStafferInfo";

            this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;

            this.Text = "【职员信息查询】";

            this.Load += new System.EventHandler(this.LookupStafferInfo_Load);

            this.groupBox1.ResumeLayout(false);

            this.groupBox1.PerformLayout();

            ((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).EndInit();

            this.ResumeLayout(false);

 

        }

 

        //--------------------初始化窗体，读入数据------------------

        private void LookupStafferInfo_Load(object sender, System.EventArgs e)

        {

            this.sqlDataAdapter1.Fill(tempTable);

            this.dataGrid1.DataSource = tempTable.DefaultView;

            DataGridStateControl();

        }

 

        //--------------将数据显示在表格中，并设置表格参数------------------

        private void DataGridStateControl()

        {

            DataGridTableStyle ts = new DataGridTableStyle();

            DataGridNoActiveCellColumn aColumnTextColumn;

            ts.AlternatingBackColor = Color.LightGray;

            ts.MappingName = tempTable.TableName;

            ts.AllowSorting = false;//不允许进行排序

            int numCols = tempTable.Columns.Count;

            for (int i = 0;i< numCols-2;i++)//将数据填充到表格中

            {

                aColumnTextColumn = new DataGridNoActiveCellColumn();

                aColumnTextColumn.MappingName = tempTable.Columns[i].ColumnName;

                aColumnTextColumn.HeaderText = tempTable.Columns[i].ColumnName;

                aColumnTextColumn.NullText = "";

                aColumnTextColumn.Format = "D";               

                ts.GridColumnStyles.Add(aColumnTextColumn);

            }

            this.dataGrid1.TableStyles.Add(ts);

            this.dataGrid1.Select(0);

        }

 

        //--------------将符合查询条件的数据查询并显示在表中------------------

        private void btn_Research_Click(object sender, System.EventArgs e)

        {

            string strRowFilter = "";

            string strCmb1 = this.cmb1.Text.Trim();

            string strCmb2 = this.cmb2.Text.Trim();

            string strCmb3 = this.cmb3.Text.Trim();

 

            if(this.txt1.Text.Trim() != "")//设置表的过滤条件

                strRowFilter += "姓名简码 like '%" + txt1.Text.Trim() + "%' and ";

            if(this.txt2.Text.Trim() != "")

                strRowFilter += "姓名 like '%" + txt2.Text.Trim() + "%' and ";

            if(strCmb1 != "")

                strRowFilter += "性别 like '%" + strCmb1 + "%' and ";

            if(strCmb2 != "")

                strRowFilter += "民族 like '%" + strCmb2 + "%' and ";

            if(strCmb3 != "")

                strRowFilter += "籍贯 like '%" + strCmb3 + "%' and ";

 

            if(strRowFilter != "")    // 存在查询条件

                strRowFilter = strRowFilter.Substring(0,strRowFilter.Length-5);

 

            tempTable.DefaultView.RowFilter = strRowFilter;

        }

 

        //--------------退出窗体-----------------

        private void btnQuit_Click(object sender, System.EventArgs e)

        {

            this.Close();

        }