How to search data bind gridview jquery datatable

Question

Hi,

 

Textbox wise search database data bind gridview using jquery datatable plugin but data bind with header also bind gridview,so avoid Header in gridview row

 

Jquery Script:

<%--animated Zoom In--%>
<%--for gridview search,sort,paging--%>
<link type="text/css" href="Design/css/dataTables.bootstrap.min.css" rel="stylesheet" />
<link type="text/css" href="Design/css/responsive.bootstrap.min.css" rel="stylesheet" />
<script type="text/jscript" src="Design/js/jquery.dataTables.min.js"></script>
<script type="text/javascript" src="Design/js/dataTables.bootstrap.min.js"></script>
<script type="text/javascript" src="Design/js/dataTables.responsive.min.js"></script>
<%--for gridview search,sort,paging--%>
<link href="Design/bootstrap-3.3.7-dist/css/bootstrap.css" rel="stylesheet" />
<script type="text/javascript">
$(function () {
$('#<%= gvCustomers.ClientID %>').prepend($("<thead></thead>").append($('[id$=gvCustomers]').find("tr:first"))).DataTable({
"responsive": true,
"destroy" : true,
"sPaginationType": "full_numbers",
fixedHeader: {
header: false,
footer: false
}
});
});
</script>
<div>
<asp:TextBox runat="server" ID="txtCountry" />
<asp:Button ID="btnSearch" Text="Search" runat="server" />
<br />
<div class="table-responsive">
<div class="table-wrapper">
<asp:GridView ID="gvCustomers" CssClass="table table-striped table-bordered table-hover" runat="server" AutoGenerateColumns="false">
<Columns>
<asp:BoundField DataField="ContactName" HeaderText="Contact Name" />
<asp:BoundField DataField="CustomerID" HeaderText="CustomerID" />
<asp:BoundField DataField="City" HeaderText="City" />
<asp:BoundField DataField="Country" HeaderText="Country" />
</Columns>
</asp:GridView>
</div>
</div>
</div>

Code:

private void BindGridView()
{
string strConnString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(strConnString))
{
SqlCommand cmd = new SqlCommand("SELECT ContactName,CustomerId,City,CompanyName [Country] FROM Customers", con);
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
using (DataSet ds = new DataSet())
{
sda.Fill(ds, "Customers");
gvCustomers.DataSource = ds;
gvCustomers.DataBind();
}
}
}
}
[System.Web.Services.WebMethod]
public static string GetCustomers(string searchTerm)
{
DataSet ds = new DataSet();
string strConnString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
SqlConnection con = new SqlConnection(strConnString);
string query = "SELECT ContactName,CustomerId,City, CompanyName [Country] FROM Customers";
if (!string.IsNullOrEmpty(searchTerm))
{
query += " WHERE CompanyName LIKE '" + searchTerm + "%'";
}
SqlCommand cmd = new SqlCommand(query, con);
cmd.CommandType = CommandType.Text;
SqlDataAdapter sda = new SqlDataAdapter(cmd);
sda.Fill(ds, "Customers");
return ds.GetXml();
}