Member Avatar for jigarsangoi

how to make advance search in asp.net & what is required to search from database

  • 2 Contributors
  • 2 Replies
  • 128 Views
  • 19 Hours Discussion Span
  • Latest Post Latest Post by jigarsangoi

Dani AI

Generated

As asked for clarification and confirmed the requirement, the simplest, safest pattern for "advanced" checkbox-driven search in ASP.NET is: map each checkbox to a database predicate, build a dynamic filter server-side, parameterize every value, execute the query, and bind the results. That keeps UI logic separate from data access and avoids SQL injection or malformed SQL when different combinations of checkboxes are checked.

A workable flow (WebForms or MVC controller) and a short ADO.NET example:

  1. Map checkboxes to criteria (column comparisons, IN lists, LIKE patterns).
  2. At search time, accumulate a list of WHERE fragments and a parallel list of SqlParameter objects.
  3. If multiple checkboxes produce multi-value criteria (categories, tags), create separate parameters for each item and use IN (@p0,@p1,...).
  4. Execute the parameterized command and bind the DataTable/IEnumerable to a GridView or view model.

Example (C# / ADO.NET):

protected void btnSearch_Click(object sender, EventArgs e)
{
    var filters = new List<string>();
    var parameters = new List<SqlParameter>();

    if (chkActive.Checked)
    {
        filters.Add("IsActive = @isActive");
        parameters.Add(new SqlParameter("@isActive", SqlDbType.Bit) { Value = true });
    }

    var cats = new List<string>();
    if (chkCatA.Checked) cats.Add("A");
    if (chkCatB.Checked) cats.Add("B");
    if (cats.Any())
    {
        var names = new List<string>();
        for (int i = 0; i < cats.Count; i++)
        {
            var p = "@cat" + i;
            names.Add(p);
            parameters.Add(new SqlParameter(p, SqlDbType.NVarChar, 50) { Value = cats[i] });
        }
        filters.Add("Category IN (" + string.Join(",", names) + ")");
    }

    string sql = "SELECT * FROM Items" + (filters.Any() ? " WHERE " + string.Join(" AND ", filters) : "");
    using (var conn = new SqlConnection(connString))
    using (var cmd = new SqlCommand(sql, conn))
    {
        cmd.Parameters.AddRange(parameters.ToArray());
        var dt = new DataTable();
        new SqlDataAdapter(cmd).Fill(dt);
        gvResults.DataSource = dt;
        gvResults.DataBind();
    }
}

Notes and cautions: always validate and type-check values before adding parameters; prefer stored procedures or an ORM (Entity Framework with expression predicates) for complex filters; add pagination and proper indexes for performance. For background on safe parameter use and SQL injection prevention see SQL injection (SQL Server) and the SqlParameter class.

Recommended Answers

All 2 Replies

Member Avatar for hericles

Could you provide any more information on exactly what you want to achieve? Do you want the users to be able include smart tags in their searches (like before: and after: to limit by date for example)?

Member Avatar for jigarsangoi

no only according to checkbox who are checked & from that search from the database & display the result

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.