从多个文本框C＃Select查询 [英] Select query from multiple textboxes c#

问题描述

我仍然相当新的C＃和SQL所以请记住这一点。我搜索网站，但无法找到答案。

我建立在C＃中一个基本的ASP网页的形式连接到MySQL数据库并填充的结果的GridView。

我的网页上有两个文本框：电话号码和用户ID。
然后我点击它时，运行以下code按钮：

 保护无效btnSubmit_Click（对象发件人，EventArgs的发送）
        {
            {
                尝试
                {
                    conn.Open（）;
                    // SQL
                    MySqlCommand的CMD =新的MySqlCommand（从消息，其中PHONE_NUMBER像@PatientMob和USER_ID像@UserID，康涅狄格州SELECT *）;
                    // PARAMATERS
                    cmd.Parameters.Add（新了MySQLParameter（@PatientMob，MySqlDbType.VarChar））值= PatientMobile.Text。
                    cmd.Parameters.Add（新了MySQLParameter（@用户名，MySqlDbType.VarChar））值= UserID.Text。                    MySqlDataAdapter的ADP =新MySqlDataAdapter的（CMD）;
                    DataSet的DS =新的DataSet（）;
                    adp.Fill（DS）;
                    gridview.DataSource = DS;
 

...等

我的问题是，我该如何格式化查询，以便它的作品即使只有一个文本框填充？
此刻，如果我输入一个电话号码，但不输入用户ID，结果显示与谁什么都没有输入到用户ID字段的电话号码的人。

我将增加更多的文本框后的称号，名字surname..etc，需要一个解决方案，将与场填写或不填写的任意组合使用。

感谢。

解决方案

我不知道任何伟大的方式做到这一点，很不幸，但你有几种选择。

1. 构建字符串动态

事情是这样的：

 列表＆LT;串GT;哪来=新的List＆LT;串GT;（）;
清单＆LT;＆了MySQLParameter GT;参数=新的List＆LT;＆了MySQLParameter GT;（）;如果（！string.IsNullOrWhiteSpace（PatientMobile.Text））
{
    wheres.Add（PHONE_NUMBER LIKE @PatientMob）;
    parameters.Add（新了MySQLParameter（@PatientMob，MySqlDbType.VarChar）
                   {
                       值= PatientMobile.Text
                   }）;
}...查询字符串=的String.Format（SELECT * FROM WHERE消息{0}的string.join（AND，哪来））;
 

等等，等等...

<醇开始=2>

检查动态什么是查询空

事情是这样的：

 查询字符串=SELECT * FROM WHERE消息（LEN（@PatientMob）= 0或PHONE_NUMBER LIKE @PatientMob）和（LEN（@UserID）= 0或USER_ID LIKE @UserID） ，conn）;在
 

<醇开始=3>

我看你使用 LIKE ，诚然我不是一个MySQL的所以这可能是遥远，但好像你可以包装您的参数与任何字符指定通配符。在T-SQL中，看起来像列LIKE'％'+ @参数+'％'。当然，这不会是完全匹配，但听起来像它可能是你在找什么。在空搜索框的情况下，这只会导致列LIKE'%%'，这将匹配任何值。

I am still fairly new to both c# and SQL so please keep that in mind. I searched the site but couldn't find an answer.

I am building a basic ASP web form in C# that connect to a mysql database and populates a gridview with the results.

I have two text boxes on the web page: Phone number and User ID. I then have a button which when clicked runs the following code:

        protected void btnSubmit_Click(object sender, EventArgs e)
        {    
            {
                try
                {
                    conn.Open();
                    //SQL 
                    MySqlCommand cmd = new MySqlCommand("Select * from message where phone_number like @PatientMob and user_ID like @UserID, conn);
                    //Paramaters
                    cmd.Parameters.Add(new MySqlParameter(@"PatientMob", MySqlDbType.VarChar)).Value = PatientMobile.Text;
                    cmd.Parameters.Add(new MySqlParameter(@"UserID", MySqlDbType.VarChar)).Value = UserID.Text;

                    MySqlDataAdapter adp = new MySqlDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    adp.Fill(ds);
                    gridview.DataSource = ds;

..etc

My question is, how do I format the query so that it works even if only one textbox is filled in? At the moment, if I enter a phone number but do not enter the user ID, the results are showing people with that phone number who have nothing entered into the user ID field.

I will be adding more text boxes later for title, first name, surname..etc and need a solution that will work with any combination of fields filled in or not filled in.

Thanks.

解决方案

I don't know of any great way to do this, unfortunately, but you do have a few options.

1. Build the string dynamically

Something like this:

List<string> wheres = new List<string>();
List<MySqlParameter> parameters = new List<MySqlParameter>();

if (!string.IsNullOrWhiteSpace(PatientMobile.Text))
{
    wheres.Add("phone_number LIKE @PatientMob");
    parameters.Add(new MySqlParameter(@"PatientMob", MySqlDbType.VarChar)
                   {
                       Value = PatientMobile.Text
                   });
}

...

string query = string.Format("SELECT * FROM Messages WHERE {0}", string.Join(" AND ", wheres));

And so on and so forth...

2. Check dynamically what's empty in the query

Something like this:

string query = "SELECT * FROM message WHERE (LEN(@PatientMob) = 0 OR phone_number LIKE @PatientMob) and (LEN(@UserID) = 0 OR user_ID LIKE @UserID)", conn);

3. I see you're using LIKE, and admittedly I'm not one for MySQL so this could be way off, but it seems like you could be wrapping your parameters with whatever character designates a wildcard match. In T-SQL, that would look something like column LIKE '%' + @param + '%'. Of course, that won't be an exact match, but that sounds like it might be what you're looking for. In cases of empty search boxes, that will just result in column LIKE '%%', which would match any values.

这篇关于从多个文本框C＃Select查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！