Click here to Skip to main content
Click here to Skip to main content

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

, 11 Aug 2014 CPOL
Rate this:
Please Sign up or sign in to vote.
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.

@{
    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)

Share

About the Author

Afzaal Ahmad Zeeshan
Student De Codes
Pakistan Pakistan
Hi, my name is Afzaal Ahmad Zeeshan. I am a Pakistani, student, Ahmadi Muslim kid. I live in Rabwah and I am here since I was ~3years old. I have done my matriculation with major subjects for Science and Intermediate in Engineering.
 
I started learning programming when I was 15 and wrote small application that would get me double (not the data type) of the int value I would provide it with. In the beginning, I started Web applications, using ASP.NET when I was 16 and I really didn't know ASP.NET is written in C#, all I knew was that there is something called Razor syntax. I created simple web applications like Say hello to a friend, Add a friend in your list and get the elder among them. After craming the ASP.NET codes, I finally realized that there is no way to write application by cramming the API. So, I went learning, I learnt Visual C#, Java, Visual C++, started to learn Assembly but gave up in the middle, will restart this.
 
I have written some projects for my friends, I have made some web applications for cousins, I have developed a few applications for my cell phone, for desktop. I have developed an HTML keyboard for websites, like a On-Screen Keyboard, A Login system using simple C# code and JSON files. I am planning to write a Database system that would be much much much less costly than MS SQL. I am author at many blogs and teach programming to new developers to the extent that I have learnt programming.
 
I am never found complaining, I am self teaching, I am polite to elders, I accept my mistakes without criticizing the person who found a bug in me. I listen to Eminem, more than I listen to my girl. I am logical, you can make me quite my logically proving me wrong. Otherwise our dicussion might be a never ending loop.
Follow on   Twitter   Google+   LinkedIn

Comments and Discussions

 
GeneralMy vote of 5 PinprofessionalChristian Amado12-Sep-14 6:16 
GeneralMy vote of 4 PinprofessionalChristian Amado12-Sep-14 4:41 
GeneralRe: My vote of 4 PinprofessionalAfzaal Ahmad Zeeshan12-Sep-14 4:46 
GeneralRe: My vote of 4 PinprofessionalChristian Amado12-Sep-14 4:59 
AnswerRe: My vote of 4 PinprofessionalAfzaal Ahmad Zeeshan12-Sep-14 6:11 
AnswerRe: My vote of 4 PinprofessionalChristian Amado12-Sep-14 6:15 
QuestionConcerning the SqlParameter PinmentorMika Wendelius13-Aug-14 8:06 
AnswerRe: Concerning the SqlParameter PinprofessionalAfzaal Ahmad Zeeshan13-Aug-14 21:24 
GeneralMy vote of 1 PinmemberMember 188040313-Aug-14 3:26 
AnswerRe: My vote of 1 PinprofessionalAfzaal Ahmad Zeeshan13-Aug-14 5:11 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.150326.1 | Last Updated 12 Aug 2014
Article Copyright 2014 by Afzaal Ahmad Zeeshan
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid