• Download RelevanceSearch - 101.04 KB

Introduction

  Many a times we require to implement the Search algorithm in the Applications. So in a normal case we may search the term for various criteria and then find the best match for the Search Query. I also wanted a same thing where I wanted to search the Cars for my application. In my application I wanted five criteria namely, Brakes, Fuel, Lock, Engine, and Steering. So the customer will select the options from the combo box (only valid values from the columns) and then display the Cars that match maximum features.

Background

For implementing this article you must be aware of UNION, ORDER BY, GROUP BY operations of clauses and

Database connection in VB.net.

Using the code  

We are going to learn two parts:

1. How to load column in the Combo box. So that user can select only valid values.

2. How create a Search to get the best result.

So starting off with that. I have a small snippet of my code. I just implemented the code as logic. We have a five combo boxes and from which user will select values and then by searching the values will be loaded.  

Blocks of code should be set as style "Formatted" like this:

    Dim connObj As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\Car_Showroom.mdb")
    Dim commObj As New OleDbCommand
    Dim myDataReader As OleDbDataReader
    Dim myDataSet, myDataSet1 As New DataSet
    Dim query(3) As String
    Dim myAdapter As OleDbDataAdapter

  This are the Database Connection objects. Here is the code to load a single Combo with that specific column.

  myDataSet1.Clear()
        connObj.Open()
        ''''''' Load Steering
        myDataSet1.Clear() ' clear all the values from Data Set if any
        cmbSteering.Items.Clear() ' clear all the items of the Combo BOx
        commObj = New OleDbCommand("SELECT DISTINCT Steering FROM CarDetails", connObj) ' Create a command object and add connection
        myAdapter = New OleDbDataAdapter(commObj)
        myAdapter.Fill(myDataSet1, "cDetails") ' Execute the Query and fill the results in the dataset.
        cmbSteering.Text = "Select Company

' This is the most important code

For Each ds As DataRow In myDataSet1.Tables("cDetails").Rows 
            cmbSteering.Items.Add(ds("Steering").ToString())
        Next 

Explanation of the above code:

We are creating a DataRow object and then using a For Each Loop we are and then we adding the Steering Column value to the combo box.

This is the next part of the segment. This is what I searched on internet but couldn't' find one. So I worked upon and figured out this as one of the solution.

Following shows the SQL Query and later its explanation:

 Dim steer As String = cmbSteering.SelectedItem
        Dim engine As String = cmbEngine.SelectedItem
        Dim fuel As String = cmbFuel.SelectedItem
        Dim lock As String = cmbLock.SelectedItem
        Dim brakes As String = cmbBrakes.SelectedItem
        myDataSet.Clear()
        connObj.Open()
        query(1) = "SELECT PRODID,Occurence FROM (SELECT ProdId,count(*) AS Occurence FROM (SELECT ProdId FROM CarDetails WHERE Steering = '{0}' UNION ALL SELECT ProdId FROM CarDetails WHERE Engine ='{1}' UNION ALL SELECT ProdId FROM CarDetails WHERE Fuel ='{2}' UNION ALL SELECT ProdId FROM CarDetails WHERE Brakes ='{3}' UNION ALL SELECT ProdId FROM CarDetails WHERE Lock ='{4}' ) Group By ProdID )ORDER BY Occurence DESC"
        query(2) = String.Format(query(1), cmbSteering.SelectedItem, cmbEngine.SelectedItem, cmbFuel.SelectedItem, cmbBrakes.SelectedItem, cmbLock.SelectedItem)
        commObj = New OleDbCommand(query(2), connObj)
        myAdapter = New OleDbDataAdapter(commObj)
        myAdapter.Fill(myDataSet1, "cDetails")
        connObj.Close() 

So now this is the Query stored in var=query(1).

Here what are we doing is that first we ll search the terms individually in those columns.Like Sterring in Steering, Brakes in Brakes,etc.. 

 We ll then UNION all the results. UNION combines all the results in to single result set. But UNION does not adds repeated results. and we need repeated results because the one that occurs most is the best match. Hence we are using UNION ALL

So after the UNION we now have the combined result. So now we ll group all the results as per the number of occurences. So we have a Super Query ( and all the UNIONs as the sub query) , that use COUNT(*) and group them by GROUP BY clause.

But again this will sort the Result as the least matched to best matched. Hence we ll give COUNT(*) as Occurence and the sort the Occurence in Descending order using the ORDER BY clause.

So here after implementing this query I have returned the Product ID(Prod ID unique for all rows) and its occurence. This is just a snippet not a usable project. So you need to make changes as per your code.

I hope this post ws helpful .:)

Points of Interest 

  This is my first ever article on CodeProject. I had been searching this so curiously. But couldn't find any relevant article or code for this. So if any errors, doubts, correction then please let me know. Spare me for any mistake becuase I am not a professional. I am student.