C# WinForm界面上实现按条件检索数据

实现步骤:

1.定义事件

2.定义方法

3.完善步骤2中的方法

　　1)在步骤2中的参数方法,定义参数方法,主要获取界面查询条件

　　2)实现在步骤2中的数据SQL查询语句方法

实例操作如下:

 1.定义事件

  btnSelectUserList.Click += btnSelectUserList_Click;

2.定义方法

 void btnSelectUserList_Click(object sender, EventArgs e)
        {
            SelectUserList();
        }

3.完善步骤2中的方法

        private void SelectUserList()
        {
            UserService userService = new UserService();
            DataTable userTable = userService.GetUserList(GetWhereUser());
            bindingSource.DataSource = userTable;
            gridControl1.DataSource = bindingSource;
        }

　　1)在步骤2中的参数方法,定义参数方法,主要获取界面查询条件

  private string GetWhereUser()
        {
            string strWhere = string.Empty;
            if (txt_UserName.Text.Trim() != "")
                strWhere += string.Format(" and Users.UserName like '%{0}%'", txt_UserName.Text.Trim());
            if (this.time_LoginStart.Checked && this.time_LoginEnd.Checked)
                strWhere += string.Format(" and Users.DateCreated between '{0}' and '{1}'",
                    this.time_LoginStart.Text, this.time_LoginEnd.Text);
            if (this.time_LoginStart.Checked)
                strWhere += string.Format(" and Users.DateCreated >= '{0}'", this.time_LoginStart.Text);
            if (this.time_LoginEnd.Checked)
                strWhere += string.Format(" and Users.DateCreated <= '{0}'", this.time_LoginEnd.Text);
            return strWhere;

        }

 

　　2)实现在步骤2中的数据SQL查询语句方法

        /// <summary>
        /// 检索本地用户列表
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public DataTable GetUserList(string strWhere)
        {
            DataTable table = new DataTable();
            try
            {
                string strSql = string.Format(@"select Users.UserId,Users.UserName,users.AuthenticationMode,users.RoleId,users.DisplayName,users.Email,
Users.PhoneNumber,users.Status,users.DateLastLogon,users.CreatedBy,users.DateModifed
from Users where users.Status=1");
                strSql += strWhere;
                table = DataBase.Default.ExecuteDataTable(strSql, CommandType.Text);
            }
            catch(Exception ex)
            {
                throw new Exception("" + ex.Message + ex.StackTrace);
            }
            return table;
        }

下面是一个数据库操作的基础类(DBHelper)的一个方法

  public DataTable ExecuteDataTable(string strSql, CommandType commandType)
        {
            DataTable table = new DataTable();
            try
            {
                table = ExecuteDataTable(strSql, null, true);
            }
            catch (Exception ex)
            {
                log.Error("DB:" + GetKey() + Environment.NewLine + "SQL:" + strSql + Environment.NewLine + ex);
                throw new Exception("错误信息：" + ex.Message + ex.StackTrace);
            }
            return table;
        }

杂记:下面演示的是通过消息请求反应机制,按检索消息传过来的数据列表(用户名,创建时间的阶段).

private void SelectUserList()
        {
            //string str = "UserName '{0}%'";
            //str = string.Format(str, txt_UserName.Text.Trim());
            List<UserInfo> userInfoList = message.UserInfoList;
            if (!string.IsNullOrEmpty(txt_UserName.Text.Trim()))
            {
                userInfoList = message.UserInfoList.FindAll(c => c.UserName.StartsWith(txt_UserName.Text.Trim()));
            }
            if (this.time_LoginStart.Checked)
                userInfoList = userInfoList.FindAll(c => c.DateCreated.Value > time_LoginStart.Text.ToDateTime());
            if (this.time_LoginEnd.Checked)
                userInfoList = userInfoList.FindAll(c => c.DateCreated.Value < time_LoginEnd.Text.ToDateTime());
            bindingSource.DataSource = userInfoList;
            gridControl1.DataSource = bindingSource;
            gridView1.RefreshData();
            return;

        }