Home > DeveloperSection > Articles > Search Functionality in ASP.NET MVC 4

Search Functionality in ASP.NET MVC 4


ASP.NET MVC ASP.NET MVC 
Ratings:
4 Comment(s)
 28062  View(s)
Rate this:

Search Functionality in ASP.NET MVC 4

This article discusses about searching text from existing table. In this demonstration I’m going to explain you how can you use SQL query for finding a particular record from table, based on user input keyword. For making this demonstration I’ve used Visual Studio Express 2012 with framework 4.5SQL Server 2008 and Windows 8 Operating System. Steps are given below

Step 1: First, create a table and insert some data. Below I’ve shown my table schema with data.

-- Create database e.g. MindStick_Demo

CREATE DATABASE MindStick_Demo

-- Used above created database

USE MindStick_Demo

 

-- Create table e.g. SearchDetails

CREATE TABLE SearchDetails

(

[ID] BIGINT IDENTITY PRIMARY KEY,

[Name] VARCHAR(50),

[Designation] VARCHAR(50),

[Domain] VARCHAR(50),

[Task] VARCHAR(MAX)

)

 

-- Insert data into SearchDetails table

INSERT INTO [SearchDetails]([Name],[Designation],[Domain],[Task]) VALUES('Samuel Fernandes','Software Developer','.NET, PHP, Database','Procedure,Function,MVC 4, ASP.NET, JSON, JQuery, JavaScript,IIS')

INSERT INTO [SearchDetails]([Name],[Designation],[Domain],[Task]) VALUES('Takeshi Okada','Jr. Software Developer','.NET','Windows Application,Procedure,Function,MVC 4, ASP.NET, JSON, JQuery, JavaScript,IIS')

INSERT INTO [SearchDetails]([Name],[Designation],[Domain],[Task]) VALUES('Alexander Sabato','Jr. Software Developer','.NET, Database','Procedure, Function, MVC 4, ASP.NET, JQuery, JavaScript,Web Service, Windows Application')

INSERT INTO [SearchDetails]([Name],[Designation],[Domain],[Task]) VALUES('Benjamin Richards','Software Developer','.NET,Testing','Procedure,Function,MVC 4, ASP.NET, JQuery, JavaScript')

 

Step 2: Open MVC Project in your Visual Studio with empty template as below imageSearch Functionality in ASP.NET MVC 4

Step 3:  Create controller and write following lines of code.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web.Mvc;

using SimpleSearch.Models;

 

namespace SimpleSearch.Controllers

{

    public class HomeController : Controller

    {

        protected List<string> keywords = new List<string>();

        public ActionResult Index()

        {

            ViewBag.Message = false;

            return View();

        }

 

        /// <summary>

        /// Get value from view textbox and send to model

        /// </summary>

        /// <param name="txtValue"></param>

        /// <returns></returns>

        [HttpPost]

        public ActionResult Index(string txtValue)

        {

            // Check length before for process

            if (txtValue.Length > 0)

            {

                // Turn user input to a list of keywords.

                string[] keywords = txtValue.Trim().Split(new string[] { " " }, StringSplitOptions.RemoveEmptyEntries);

                this.keywords = keywords.ToList();

                DisplayData dataAccess = new DisplayData();

 

                // call search method and passed generated keyword

                List<Content> list = dataAccess.Search(this.keywords);

                return View(list);

            }

            else

            {

                // If no keyword are entered than send error message to user

                ViewBag.Message = true;

                return View();

            }

        }

    }

}

 

Note: In the above code, for textbox validation I have used ViewBag.  For used proper validation in MVC, read below article.

https://www.mindstick.com/Articles/871/validation-in-asp-dot-net-mvc3

Step 4: Create model (e.g. Content) where we make properties and methods that access by controller and view. Line of code are given below

using System.Collections.Generic;

using System.Configuration;

using System.Data.SqlClient;

using System.Text;

 

namespace SimpleSearch.Models

{

 

    /// <summary>

    /// This class is used to create properties

    /// </summary>

    public class Content

    {

        public long lID { get; set; }

        public string sName { get; set; }

        public string sDesignation { get; set; }

        public string sDomain { get; set; }

        public string sTask { get; set; }

    }

 

    /// <summary>

    /// This class is used to access database.

    /// </summary>

    public class DisplayData

    {

        /// <summary>

        /// Search records from database.

        /// </summary>

        /// <param name="keywords">the list of keywords</param>

        /// <returns>all found records</returns>

        public List<Content> Search(List<string> keywords)

        {

            // Generate a complex Sql command.

            StringBuilder sqlBuilder = new StringBuilder();

            sqlBuilder.Append("select * from [SearchDetails] where ");

            foreach (string item in keywords)

            {

                sqlBuilder.AppendFormat("([Name] like '%{0}%' or [Designation] like '%{0}%' or [Domain] like '%{0}%' or [Task] like '%{0}%') and ", item);

            }

 

            // Remove unnecessary string " and " at the end of the command.

            string sql = sqlBuilder.ToString(0, sqlBuilder.Length - 5);

 

            return QueryList(sql);

        }

 

        /// <summary>

        /// Excute a Sql command.

        /// </summary>

        /// <param name="cmdText">Command text</param>

        /// <returns></returns>

        protected List<Content> QueryList(string cmdText)

        {

            List<Content> ctn = new List<Content>();

 

            SqlCommand cmd = GenerateSqlCommand(cmdText);

            using (cmd.Connection)

            {

                SqlDataReader reader = cmd.ExecuteReader();

 

                // Transform records to a list.

                if (reader.HasRows)

                {

                    while (reader.Read())

                    {

                        ctn.Add(ReadValue(reader));

                    }

                }

            }

            return ctn;

        }

 

        /// <summary>

        /// Create a connected SqlCommand object.

        /// </summary>

        /// <param name="cmdText">Command text</param>

        /// <returns>SqlCommand object</returns>

        protected SqlCommand GenerateSqlCommand(string cmdText)

        {

            // Read Connection String from web.config file.

            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString);

            SqlCommand cmd = new SqlCommand(cmdText, con);

            cmd.Connection.Open();

            return cmd;

        }

 

        /// <summary>

        /// Create an Content object from a SqlDataReader object.

        /// </summary>

        /// <param name="reader"></param>

        /// <returns></returns>

        protected Content ReadValue(SqlDataReader reader)

        {

            Content obj = new Content();

 

            obj.lID = (long)reader["ID"];

            obj.sName = (string)reader["Name"];

            obj.sDesignation = (string)reader["Designation"];

            obj.sDomain = (string)reader["Domain"];

            obj.sTask = (string)reader["Task"];

            return obj;

        }

    }

}

 

Note: Set database connection string in your application’s web.config as below line of code.

For Windows Authentication

<configuration>

 <connectionStrings>

    <add name="dbConnection" connectionString="Data Source=(local); Initial Catalog=MindStick_Demo; Integrated Security=True;" providerName="System.Data.SqlClient"/>

  </connectionStrings>

</configuration>

 

For SQL Server Authentication

<configuration>

 <connectionStrings>

    <add name="dbConnection" connectionString="Data Source=SeverName; Initial Catalog=MindStick_Demo; User id = XXXXX; Password = XXXXX" providerName="System.Data.SqlClient"/>

  </connectionStrings>

</configuration>

 

Step 5: Create View (e.g. Index) and use below lines of code

@model IEnumerable<SimpleSearch.Models.Content>

 

@{

    Layout = null;

}

 

<!DOCTYPE html>

 

<html>

<head>

    <meta name="viewport" content="width=device-width" />

    <title>Index</title>

    <style type="text/css">

        table, td, th {

            border: 1px solid green;

        }

 

        th {

            background-color: green;

            color: white;

        }

    </style>

</head>

<body>

    <div style="background-color: lightblue; height: 100px; padding-top: 10px;">

        <span style="text-align: center;">

            <h1> Search Functionality in ASP.NET MVC 4</h1>

        </span>

    </div>

    <div>

        <table style="margin-left: 35%; margin-top: 10px; border: none;">

            <tr>

                @using (Html.BeginForm())

                { <td style="border: none;">

                    <label id="lblName">Enter Keyword</label></td>

                    <td style="border: none;">@Html.TextBox("txtValue")</td>

                    <td style="border: none;">

                        <input id="btnSearch" type="submit" value="Search" /></td>

                }

            </tr>

        </table>

    </div>

    <div>

        @if (ViewBag.Message == true)

        {

            <label id="lblMessage" title="Please enter keyword" style="color:red;">Please enter keyword...!</label>

        }

        else

        {

            if (Model != null)

            {

                if (Model.Count() != 0)

                {

            <table style="border: 2px solid LightGray;">

                <tr>

                    <th>Name</th>

                    <th>Designation</th>

                    <th>Domain</th>

                    <th>Task</th>

                </tr>

                @foreach (var item in Model)

                {

                    <tr>

                        <td>@item.sName</td>

                        <td>@item.sDesignation</td>

                        <td>@item.sDomain</td>

                        <td>@item.sTask</td>

                    </tr>

                }

            </table>

                }

                else

                {

            <label id="lblErrorMsg" title="Record not fount...!" style="color:red;">Record not found...!</label>

                }

            }

        }

    </div>

</body>

</html>

 

Step 6: Build your application. Now it is time for a coffee and executes your application.Search Functionality in ASP.NET MVC 4

Step 7: Enter valid keyword which you insert into database. For example if you search php domain than result will be populate as below image.Search Functionality in ASP.NET MVC 4

If you’ve entered a wrong keyword which does not exist in your table then “Record not found!” will be visible.

Search Functionality in ASP.NET MVC 4

 


Paging and sorting on this Search COde

By Ravinder A on   3 years ago
Hi, Further to this how can i get the paging and sorting on this code.

re-Paging and sorting on this Search COde

By AVADHESH PATEL on   3 years ago
Hi Ravinder,

You can get the help for implementing paging from the following link


For shorting you can get the data with ascending or distending during fetching the data from database.     

I hope it helpful for you!

Paging and sorting on this Search Code using WebGrid

By Vijay Shukla on   3 years ago
Hi Ravinder,

You can use the WebGrid instead of <table></table> for paging and shorting if you want more help read below blog: -

Thank you!

Multiple Tables

By Tom E on   2 years ago
Hi AVADHESH PATEL
How can i using this Function on multiple tables

Don't want to miss updates? Please click the below button!

Follow MindStick