按名字和姓氏搜索数据库

I am using ASP.NET with C# and subsonic. I am trying to setup a search on a text field by first and last name.

我使用ASP.NET与C#和亚音速。我试图通过名字和姓氏在文本字段上设置搜索。

    First or Last Name: <asp:TextBox ID="txtSearchName" runat="server"></asp:TextBox> 
<asp:linkButton runat="server" Text="Send" onclick="btnSubmit_Click" />
    <asp:GridView 
    border="0"
    cellpadding="3"
    cellspacing="3"
    ShowHeader="True"
    allowsorting="true"
    ID="GridView1" 
    runat="server" 
    AutoGenerateColumns="false" 
    Visible="false"
    AllowPaging="True" 
    PageSize="10" 
    PagerStyle-Mode="NumericPages"
    OnPageIndexChanging="GridView1_PageIndexChanging"
    >

In the code behind, I have this:

在后面的代码中,我有这个:

private void BuildGridView1()
    {
        GridView1.DataSource = new Select(PastAwardName.Schema.TableName + ".*", PastAwardType.Schema.TableName + ".*")
              .From(PastAwardName.Schema)
              .InnerJoin(PastAwardType.Schema.TableName, PastAwardType.Columns.VolID, PastAwardName.Schema.TableName, PastAwardName.Columns.VolID)
              .Where(PastAwardName.Columns.LName).IsEqualTo(this.txtSearchName.Text)
              .Or(PastAwardName.Columns.FName).IsEqualTo(this.txtSearchName.Text)
              .OrderAsc(PastAwardType.Columns.AwardYear)
              .ExecuteDataSet();

    }

protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
    BuildGridView1();
    GridView1.PageIndex = e.NewPageIndex;
    GridView1.DataBind();
}

This works if you enter either the first or last name, but I want to be able to search for them both at the same time from one text field. Is this possible?

如果您输入名字或姓氏,这是有效的,但我希望能够从一个文本字段同时搜索它们。这可能吗?

2 个解决方案

#1

You should be able to build your query and then append the Or/And restrictions based on whether you have more than one name. The following should work but you should be aware that edge cases like double-barelled names, a user entering a middle name etc. will catch you out:

您应该能够构建查询,然后根据您是否有多个名称附加Or / And限制。以下应该可以工作,但你应该知道边缘情况,如双重名称,输入中间名等的用户会引起你的注意:

private void BuildGridView1()
{
  string[] names = this.txtSearchName.Text.Split(" ".ToCharArray());

  SqlQuery query = DB.Select(PastAwardName.Schema.TableName + ".*", PastAwardType.Schema.TableName + ".*")
    .From(PastAwardName.Schema)
    .InnerJoin(PastAwardType.Schema.TableName, PastAwardType.Columns.VolID, PastAwardName.Schema.TableName, PastAwardName.Columns.VolID)
    .Where(PastAwardName.Columns.FName).IsEqualTo(names[0])

  if(names.Length > 1)
    query = query.And(PastAwardName.Columns.LName).IsEqualTo(names[1])
  else
    query = query.Or(PastAwardName.Columns.LName).IsEqualTo(names[0]

  GridView1.DataSource = query.OrderAsc(PastAwardType.Columns.AwardYear)
    .ExecuteDataSet();
}

#2

Split the contents of this.txtSearchName.Text, if it contains one word use your original query, if it contains two words, then use a modified version of your query:

拆分this.txtSearchName.Text的内容,如果它包含一个单词使用原始查询,如果它包含两个单词,则使用查询的修改版本:

private void BuildGridView1()
    {
        string[] names = String.Split(this.txtSearchName.Text, ' ');
        if (names.length == 1) {
            GridView1.DataSource = new Select(PastAwardName.Schema.TableName + ".*", PastAwardType.Schema.TableName + ".*")
              .From(PastAwardName.Schema)
              .InnerJoin(PastAwardType.Schema.TableName, PastAwardType.Columns.VolID, PastAwardName.Schema.TableName, PastAwardName.Columns.VolID)
              .Where(PastAwardName.Columns.LName).IsEqualTo(names[0])
              .Or(PastAwardName.Columns.FName).IsEqualTo(names[0])
              .OrderAsc(PastAwardType.Columns.AwardYear)
              .ExecuteDataSet();
        }
        else if (names.Length == 2) {
            GridView1.DataSource = new Select(PastAwardName.Schema.TableName + ".*", PastAwardType.Schema.TableName + ".*")
              .From(PastAwardName.Schema)
              .InnerJoin(PastAwardType.Schema.TableName, PastAwardType.Columns.VolID, PastAwardName.Schema.TableName, PastAwardName.Columns.VolID)
              .Where(PastAwardName.Columns.LName).IsEqualTo(names[1])
              .And(PastAwardName.Columns.FName).IsEqualTo(names[0])
              .OrderAsc(PastAwardType.Columns.AwardYear)
              .ExecuteDataSet();
        }
    }

You'll probably also want to add error handling/validation for the case when the user does not enter a value in the text box or enters 3 or more words.

当用户未在文本框中输入值或输入3个或更多单词时,您可能还需要为案例添加错误处理/验证。