Filter Gridview by textbox changed

Question

I've searched the interwebs long and wide for an answer to my question, though nothing worked.
I have an existing gridview in C# WCF, that gets its data by an sql datasource.
I want to have it filter after what ever is input the textbox.
The thing is that when I am trying to follow some of the methods I found I always get a null value or nothing is changed.

I've tried the following:

private void tbSearchContracts_TextChanged(object sender, EventArgs e)
{
  BindingSource src = new BindingSource();
  src.DataSource = dgvPapers.DataSource;
  src.Filter = string.Format("PaperName like '%{0}%'", tbSearchContracts.Text.Trim());
  dgvPapers.DataSource = src;
  dgvPapers.Refresh();
}

This doesn't give me an error, but simply just refreshes the view, as if the filter is never applied.

This is another solution I've found and tried:

private void tbSearchContracts_TextChanged(object sender, EventArgs e)
{
  (dgvPapers.DataSource as DataTable).DefaultView.RowFilter = string.Format("PaperName like '%{0}%'", tbSearchContracts.Text.Trim());
}

Where this returns null when trying to convert the datasource to a datatable.

*dgvPapers.DataSource is returning a list of Objects.

score 1 · Answer 1 · answered May 29 '17 at 11:36

Here is a solution that worked for me with the help of JQuery:

Step 1: SQL Write a Stored Procedure (accepting one input parameter) to Retrieve Paper Names from your database (Assuming you're using SQL Server as a DBMS):

create proc [dbo].[spGetMatchingPaperIds]
@PaperId nvarchar(Whatever size)
as
begin
select PaperId from TableStoringYourPaperIds where PaperId like @PaperId + '%'
end
GO

Step 2: Web Service Create a web service (and name it PaperSearchService.asmx or whatever naming convention you follow) and write a function that will query your database and retrieve a list of your paper names like so:

    [WebMethod]
    public List<string> getPaperNames(string DeviceIdString)
    {
        List<string> paperNames = new List<string>();
        string cs = ConfigurationManager.ConnectionStrings["YourConnectionStringToTheDatabase"].ConnectionString;
        using (SqlConnection con = new SqlConnection(cs))
        {
            SqlCommand cmd = new SqlCommand("spGetMatchingPaperIds", con);
            cmd.CommandType = CommandType.StoredProcedure;

            SqlParameter parameter = new SqlParameter("@PaperId", PaperId);
            cmd.Parameters.Add(parameter);
            con.Open();
            SqlDataReader rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                paperNames.Add(rdr["PaperId"].ToString());
            }
        }

        return paperNames;
    }

Step 3: HTML Markup I'm assuming you are using a master page so this should go inside one of your content placeholders.

Use developer tools to check for JQuery errors that it may throw on your browser by clicking F12 while debugging and download the relevant JQuery library from the CDN and just include it in your master page but last time I checked its jquery-1.7.min.js - just double check. This aforementioned library has the autocomplete functionality otherwise it won't work.

   <script type="text/javascript">
    $(function () {
                $('#<%= tbSearchContracts.ClientID %>').autocomplete({
                    source: function (request, response) {
                        $.ajax({
                            url: "PaperSearchService.asmx/getPaperNames",
                            data: "{ 'PaperId': '" + request.term + "' }",
                            type: "POST",
                            dataType: "json",
                            contentType: "application/json;charset=utf-8",
                            success: function (data) {
                                response(data.d);
                            },
                            error: function (result) {
                                alert('There is a problem processing your request');
                            }
                        });
                    },
                    minLength: 0
                });
            });

           //Below are some nice-to-have's:
           function CreateWaterMark(defaultText, textBoxControl) {
            if (textBoxControl.value.length == 0) {
                textBoxControl.style.color = "gray";
                textBoxControl.value = defaultText;
            }
        }

        function ClearWaterMark(defaultText, textBoxControl) {
            if (textBoxControl.value == defaultText) {
                textBoxControl.style.color = "black";
                textBoxControl.value = "";
            }
        }
       </script>

<asp:TextBox ID="tbSearchContracts" CssClass="whatever styling you have used" onblur="CreateWaterMark('Search Paper Names Here', this);" onfocus="ClearWaterMark('Search Paper Names Here', this);" onkeyup="hasPendingChanges()" Text="Search Paper Names Here" runat="server"></asp:TextBox>

Sadly iam not using this on a webpage but in a wcf application. — Mathias Rønnow Nørtoft, May 30 '17 at 07:03
@MathiasRønnowNørtoft WCF is meant for service implementation which can be consumed by multiple clients e.g windows forms application, website, etc. You have an event `private void tbSearchContracts_TextChanged(object sender, EventArgs e)` which I am assuming is a button click or some form of control on a WPF or WinForms application acting as a client consuming your service calls. You can still achieve the above if you add a test WinForms application to your solution and include a Web Browser control to call the JQuery function in a script tag. — Harold_Finch, May 30 '17 at 07:12
Have a look at this: https://stackoverflow.com/questions/42095271/how-to-embed-jquery-in-a-winforms-app-to-use-in-a-webbrowser-control — Harold_Finch, May 30 '17 at 07:12
That is actually kinda interesting, i will have a look at that! — Mathias Rønnow Nørtoft, May 30 '17 at 07:24
@MathiasRønnowNørtoft If this implementation helps you please feel free to give it an upvote. Happy coding. — Harold_Finch, May 30 '17 at 07:47

score 0 · Accepted Answer · answered May 30 '17 at 07:04

What i ended up doing was not the cleanest way, but i found it to be the easieast way to get things done.

  List<Object> products = ((IEnumerable)dgvPapers.DataSource).Cast<object>().ToList();
  List<ContractPaperStepDTO> altered = new List<ContractPaperStepDTO>();
  foreach (var item in products)
  {
    altered.Add((ContractPaperStepDTO)item);
  }
  altered = altered.Where(c => c.PaperName.ToLower().Contains(tbSearchContracts.Text.ToLower())).ToList();
  dgvPapers.DataSource = altered;
  dgvPapers.Update();
}

Your `dgvPapers.Update();` line will constantly make a trip to the database thus hampering your performance drastically. — Harold_Finch, May 30 '17 at 08:00

Filter Gridview by textbox changed

2 Answers2