Click here to Skip to main content
15,867,686 members
Articles / Web Development / HTML

How to search for related query inside the Database using ASP.NET

Rate me:
Please Sign up or sign in to vote.
4.54/5 (14 votes)
11 Aug 2014CPOL8 min read 62K   701   13   25
This article explains how you can use ASP.NET (C#) and search for related queries in your Database.

Introduction

This article explains how you can implement the C# code using ASP.NET in your website and web page to make a better web search for your website using just a few lines. This article explains what algorithm you can use to find for the content inside your database and provide it to the user related to almost every field in your database.

Background

I have seen many people asking to build a better search engine, well I can't provide them a source code for Google or Bing, but I can tell them what the search engine actually does (just the database part, since I won't be working around with meta tags, spiders and crawlers etc) and how it provides the results back to the user depending on the query he provided to the server.

Maybe in future this article would help some other developer fellow to make a search engine for his own ASP.NET web site or for his company.

Environment Requirements

This project was built using WebMatrix, if you already have it installed on your system, then don't worry just continue and skip this section of the article. If somehow you don't have WebMatrix, you can always get it from Microsoft's Web Platform Installer. 

Just go to the Microsoft's website, (http://www.microsoft.com/web) and download the installer from that location. Once done, the software would install a software program in your machine. Don't worry, just accept and continue the process this would install the WebMatrix and all the related softwares that are required in order to run this website on your own personal computer machine. Otherwise this website project won't work and you'll not be able to test this project in your computer.

SQL Server CE is a must, since we're working with Databases, SQL Server CE is a free file based database, that you can get for free and work with. If you're going to work with SQL Server Express or any other edition, feel free to do so, just make sure you're having the Connection String added to the web.config file of the project.

What is Search Engine

Like all other Internet terms, this term is also a fancy term where users get a highly provoked feel about something belonging to some sort of alien stuff, but remember, this is just an english term, "Search Engine". In real, this is just a software or a logic or algorithm to find the content that you're looking for. 

For example, Google, they're not an Alien Company, they're just a company which provide you the result that you're trying to look for. They ask you for a query and then run a code against their database and look for the best fit for that. That is, they extract and using some special kind of ASP.NET (C#) codes they get which results suits you the best.

Similar thing can be done on a personal website too. You can create a database for your work and after it's done. You can create a table that you'll have the content saved in. After that, once you're done. You would know where and how to look forward for the data that the user is asking for. This is what other companies do, they use your query and look in their database to get the related data. 

For example, when you search for "Christmas", they search for every thing that has Christmas in it, like, "Merry Christmas" and they provide you with the result of "Merry Christmas". There is no black magic going around on the web server. It is just a code and some data in their hard drive.

Using the Project

This project has everything set up for you, a simple search field (although not a search field, just an input field) and a button to work with. Once you submit it, it goes to the (IIS) server and requests to read the database data. IIS does so, and provides the data back to the request. Then the response is generated with the content from the database that matches (or not matches) the query. 

After this stage, the remaining job is handled by the code that we have inside the body element. Where we check if there is any data or not, if there is, then we find the values and other content and so on. 

Building the Database

The database I have used in this project is SQL Server CE, which you can get from Microsoft and use it for free. But remember it is (or is going to be soon) deprecated. So, I would prefer you to do yourself a pavour to upgrade the server to SQL Server Express. It is also free database from Microsoft and you can enjoy all of the features of SQL Server.

There is only one table in that database. It contains the ID that we will extract when there is a match and a PostTitle column for Title search and a PostContent column for the content search. We will search for them both for any match that we can get into. 

Database ContentNote: This content was extracted from my wordpress accounts Link1 and Link2

Using the website

As already mentioned this website makes use of a simple HTML form, that gets an input from the user is as following,

HTML Form (Empty)

This is an empty form, and it has not been filled with any value. Fill it with the following value and see what happens, 

HTML form (Software value)

This form has been filled with enough details, now you can move forward and click Submit button. Once you do so you will see the following results. 

"Software" - result pageAs you can see, the results have come out, which have atleast 1 occurance of the term "Software". Others have been discarded from the List. Only the content that is related is posted on the website. There is another result showing the Posts whose content includes the term "Software". So this website searches for both, Title and Content for the Query. You can edit the UI to make it a better fit for your application, to either show the titles or the content or both as already done!

Now let's try another query, why not try a special character one? Try writing "C#" to the query box, and hit the search button, you'll see the following result this time

"C#" - resultsThis might look like the previous one to you, but it is not. You can see that the post this time with the Title is a new post, that was something else. So, you can see the code searches the database for the new and fresh data every time i.e. It is not using cache service. It keeps on searching for the relative data and provides you with the aaccurate queries that you're looking for and discarding the remaning items that you don't want to use anywhere or anymore.

What about the terms that we don't have in our database?

Well, this is another feature of this website, that doesn't show you any exception or error or any bad UX. It tells you politely about the error that you're facing. It tells you that there is no result for Query you entered, simple as that. 

You can try to write "WebMatrix" in the search box and search for it, the following page would generate

"WebMatrix" - results

This would be the result provided by the ASP.NET code and you can understand that currently you don't have any of the content that works with WebMatrix so you need to write some content to the database so that the user can get some results for the WebMatrix thing from your website.

Code for the website

The code for the website is pretty simple, it actually works as a search engine but not as much powerfull since there are only a few of the lines of the server side ASP.NET code. Which handles the Database handling, Query extraction and the UI HTML rendering too.

ASP.NET
@{
    Layout = "~/_SiteLayout.cshtml";
    Page.Title = "Home Page";
}

@{
    bool executed = false;
    List<int> titleResultStrings = new List<int>();
    List<int> contentResultStrings = new List<int>();

    if(IsPost) {
        // request was made, get the input and search the database.
        var query = "%" + Request.Form["input"] + "%";
        var db = Database.Open("StarterSite");

        // select the Query, parameters on
        var selectTitleQuery = "SELECT * FROM Content WHERE PostTitle LIKE @0";
        var selectContentQuery = "SELECT * FROM Content WHERE PostContent LIKE @0";
        var titleResult = db.Query(selectTitleQuery, query);
        var contentResult = db.Query(selectContentQuery, query);

        // append the post id to the list
        foreach (var row in titleResult) {
            titleResultStrings.Add(row.PostId);
        }

        foreach (var row in contentResult) {
            contentResultStrings.Add(row.PostId);
        }

        // got the content, now do the C# on it!
        executed = true;
    }
}

<form method="post">
    Write the input and get the result!<br />
    <input type="text" name="input" /><br />
    <input type="submit" value="Submit" />
</form>

// code executed
@if(executed) {
    <p>Code has been executed!</p>
    // if the code was executed show the result.
    if(titleResultStrings.Count() != 0) {
        var db2 = Database.Open("StarterSite");
        <h4>Posts whose title have this character are</h4>
        int i = 0;
        foreach (var item in titleResultStrings) {
            // get the data for each item!
            var post = db2.Query("SELECT * FROM Content WHERE PostId = @0", titleResultStrings[i]);
            i++;
            foreach (var row in post) {
                <p>@row.PostTitle</p>
            }
        }
    } else {
        <p>No post for query "<b>@Request.Form["input"]</b>" was found!</p>
    }

    if(contentResultStrings.Count() != 0) {
        var db2 = Database.Open("StarterSite");
        <h4>Posts whose content have this character are</h4>
        int i = 0;
        foreach (var item in contentResultStrings) {
            // get the data for each item!
            var post = db2.Query("SELECT * FROM Content WHERE PostId = @0", contentResultStrings[i]);
            i++;
            foreach (var row in post) {
                <p>@row.PostContent</p>
            }
        }
    } else {
        <p>No post for query "<b>@Request.Form["input"]</b>" content was found!</p>
    }
}

The code is pretty easy once you go through it. It is just 20% algorithm, 30% database extraction and query management and 50% HTML rendering for viewing the results.

Preventing SQL Injection

SQL injection is a method used by hacker user to break your SQL query and do bad to your database. There are many types of injection that might edit the content in your database, update it, or even delete your tables. The example query for the Database search is

var result = db.Query("SELECT * FROM table_name WHERE column_name = value");

If you pass values that are valid, then it would execute correctly. But user attempts to pass a value that might break the query, entire database might be exposed to him by the server. Suppose value to be: "'; DROP TABLE table_name --".

Above value would break the query, delete the table and comment out (-- is comment in SQL) the remaining query code.

One thing that you can do to handle this kind of situation is to prevent the Query from breaking. Untill the query is OK, the Database won't be exposed even if there is no match for the data. 

I have made up a new variable that the code would fill up! Since the ASP.NET Web Pages don't allow the parametrization inside the query, the variable is filled with the data and passed on.

var query = "%" +Request.Form["input"] + "%";

var selectTitleQuery = "SELECT * FROM Content WHERE PostTitle LIKE @0";
var selectContentQuery = "SELECT * FROM Content WHERE PostContent LIKE @0";

This above code does the job.

Points of Interest

I have learned a new thing today, that I can convert from IEnumerable to List object by adding each of the object frm IEnumberable to the List object! Second thing I learned is that it is not necessary to always use a Class in the List object, you can use any data type since it is a generic data type object.

Another thing, that I have learned is the user of @ operator, I already did know, but I kind of forgot it and so today I learned it. That you cannot use @ inside @. 

History

First post.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer
Pakistan Pakistan
Afzaal Ahmad Zeeshan is a computer programmer from Rabwah, Pakistan, currently living in The Netherlands, likes .NET Core and Node.js for regular everyday development. Afzaal Ahmad works at Adyen as a Developer Advocate.

He is an expert with Cloud, Mobile, and API development. Afzaal has experience with the Azure platform and likes to build cross-platform libraries/software with .NET Core. Afzaal is an Alibaba Cloud MVP, twice he has been awarded Microsoft MVP status for his community leadership in software development, four times CodeProject MVP status for technical writing and mentoring, and 4 times C# Corner MVP status in the same field.

Comments and Discussions

 
QuestionDo you know what is merge and hash join Pin
Tridip Bhattacharjee23-Mar-17 22:46
professionalTridip Bhattacharjee23-Mar-17 22:46 
AnswerRe: Do you know what is merge and hash join Pin
Afzaal Ahmad Zeeshan24-Mar-17 0:20
professionalAfzaal Ahmad Zeeshan24-Mar-17 0:20 
GeneralRe: Do you know what is merge and hash join Pin
Tridip Bhattacharjee27-Mar-17 22:30
professionalTridip Bhattacharjee27-Mar-17 22:30 
if you ever use merge join in sql server?

is it good or would performer when i will join two table with two key. suppose one key is pk and another key is fk or may be not.

in this kind of scenario merge join in sql server will be good.

if share your idea if you know. thanks
tbhattacharjee

Question[My vote of 1] Your code is wide open to SQL Injection Pin
2374126-Sep-15 6:41
2374126-Sep-15 6:41 
GeneralMy vote of 5 Pin
Christian Amado12-Sep-14 5:16
professionalChristian Amado12-Sep-14 5:16 
GeneralMy vote of 4 Pin
Christian Amado12-Sep-14 3:41
professionalChristian Amado12-Sep-14 3:41 
GeneralRe: My vote of 4 Pin
Afzaal Ahmad Zeeshan12-Sep-14 3:46
professionalAfzaal Ahmad Zeeshan12-Sep-14 3:46 
GeneralRe: My vote of 4 Pin
Christian Amado12-Sep-14 3:59
professionalChristian Amado12-Sep-14 3:59 
AnswerRe: My vote of 4 Pin
Afzaal Ahmad Zeeshan12-Sep-14 5:11
professionalAfzaal Ahmad Zeeshan12-Sep-14 5:11 
AnswerRe: My vote of 4 Pin
Christian Amado12-Sep-14 5:15
professionalChristian Amado12-Sep-14 5:15 
QuestionConcerning the SqlParameter Pin
Wendelius13-Aug-14 7:06
mentorWendelius13-Aug-14 7:06 
AnswerRe: Concerning the SqlParameter Pin
Afzaal Ahmad Zeeshan13-Aug-14 20:24
professionalAfzaal Ahmad Zeeshan13-Aug-14 20:24 
GeneralMy vote of 1 Pin
Member 188040313-Aug-14 2:26
Member 188040313-Aug-14 2:26 
AnswerRe: My vote of 1 Pin
Afzaal Ahmad Zeeshan13-Aug-14 4:11
professionalAfzaal Ahmad Zeeshan13-Aug-14 4:11 
AnswerRe: My vote of 1 Pin
Afzaal Ahmad Zeeshan13-Aug-14 5:15
professionalAfzaal Ahmad Zeeshan13-Aug-14 5:15 
GeneralRe: My vote of 1 Pin
Member 188040313-Aug-14 5:27
Member 188040313-Aug-14 5:27 
AnswerRe: My vote of 1 Pin
Afzaal Ahmad Zeeshan13-Aug-14 20:29
professionalAfzaal Ahmad Zeeshan13-Aug-14 20:29 
GeneralRe: My vote of 1 Pin
Member 188040314-Aug-14 1:29
Member 188040314-Aug-14 1:29 
AnswerRe: My vote of 1 Pin
Afzaal Ahmad Zeeshan14-Aug-14 5:15
professionalAfzaal Ahmad Zeeshan14-Aug-14 5:15 
GeneralRe: My vote of 1 Pin
Member 188040315-Aug-14 2:19
Member 188040315-Aug-14 2:19 
GeneralRe: My vote of 1 Pin
Afzaal Ahmad Zeeshan15-Aug-14 11:07
professionalAfzaal Ahmad Zeeshan15-Aug-14 11:07 
QuestionSQLParameter Pin
Mike Meinz12-Aug-14 1:48
Mike Meinz12-Aug-14 1:48 
AnswerRe: SQLParameter Pin
Afzaal Ahmad Zeeshan12-Aug-14 9:58
professionalAfzaal Ahmad Zeeshan12-Aug-14 9:58 
QuestionOK as a start but.... Pin
Wombaticus11-Aug-14 23:05
Wombaticus11-Aug-14 23:05 
AnswerRe: OK as a start but.... Pin
Afzaal Ahmad Zeeshan12-Aug-14 10:02
professionalAfzaal Ahmad Zeeshan12-Aug-14 10:02 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.