Filter Records By Passing Multiple Parameters In Stored Procedure Using MVC

Introduction

 

In this article, we will learn a step by step process to filter records by passing multiples in stored procedure using Asp.net MVC and ADO.NET. Here the user can search records by using name, from date and to date with gender. Every parameter will filter records individually also filter records in combined manner. 

 

This article is written based on a real scenario; that is how to build dynamic sql in stored procedure using join of multiple tables and implement it using MVC. Sometimes a  client needs multiple filter parameters to find records and this article helps a lot for better understanding of the real life requirements.

 

Prerequisites

• Visual Studio
• Sql server

Note

Before going through this session, visit my previous articles related to ASP.NET MVC and Sql Server for better understanding for setting up the project.

 

Step 1

 

First, we need to create the below tables as mentioned,

CREATE TABLE [dbo].[Post](
    [PostId] [int] IDENTITY(1,1) NOT NULL,
    [PostWeight] [int] NULL,
    [PostName] [varchar](max) NULL,
    [catId] [int] NULL,
    [fromdt] [datetime] NULL,
    [int_GenderID] [int] NULL,
 CONSTRAINT [PK_Post] PRIMARY KEY CLUSTERED
(
    [PostId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[Category](
    [catId] [int] IDENTITY(1,1) NOT NULL,
    [catName] [nvarchar](50) NULL,
    [int_GenderID] [int] NULL,
 CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED
(
    [catId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Tbl_Gender](
    [int_GenderID] [int] NOT NULL,
    [vch_GenderName] [varchar](104) NOT NULL,
 CONSTRAINT [PK_Tbl_Gender] PRIMARY KEY CLUSTERED
(
    [int_GenderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Step 2

 

First, we need to prepare data for above tables,

SET IDENTITY_INSERT [dbo].[Category] ON
GO
INSERT [dbo].[Category] ([catId], [catName], [int_GenderID]) VALUES (1, N'Plan', 1)
GO
INSERT [dbo].[Category] ([catId], [catName], [int_GenderID]) VALUES (2, N'Development', 3)
GO
INSERT [dbo].[Category] ([catId], [catName], [int_GenderID]) VALUES (3, N'End', 2)
GO
INSERT [dbo].[Category] ([catId], [catName], [int_GenderID]) VALUES (4, N'Processing', 3)
GO
SET IDENTITY_INSERT [dbo].[Category] OFF
GO
SET IDENTITY_INSERT [dbo].[Post] ON
GO
INSERT [dbo].[Post] ([PostId], [PostWeight], [PostName], [catId], [fromdt], [int_GenderID]) VALUES (4, 1, N'reza', 1, CAST(N'2000-06-10T00:00:00.000' AS DateTime), 1)
GO
INSERT [dbo].[Post] ([PostId], [PostWeight], [PostName], [catId], [fromdt], [int_GenderID]) VALUES (5, 5, N'rezsa', 2, CAST(N'1999-06-10T00:00:00.000' AS DateTime), 2)
GO
INSERT [dbo].[Post] ([PostId], [PostWeight], [PostName], [catId], [fromdt], [int_GenderID]) VALUES (6, 1, N'hello', 3, CAST(N'1999-06-10T00:00:00.000' AS DateTime), 3)
GO
INSERT [dbo].[Post] ([PostId], [PostWeight], [PostName], [catId], [fromdt], [int_GenderID]) VALUES (7, 1, N'hello2', 4, CAST(N'2000-06-10T00:00:00.000' AS DateTime), 1)
GO
INSERT [dbo].[Post] ([PostId], [PostWeight], [PostName], [catId], [fromdt], [int_GenderID]) VALUES (8, 3, N'myTask', 2, CAST(N'2000-06-10T00:00:00.000' AS DateTime), 2)
GO
INSERT [dbo].[Post] ([PostId], [PostWeight], [PostName], [catId], [fromdt], [int_GenderID]) VALUES (9, 8, N'yellow', 2, CAST(N'2001-06-10T00:00:00.000' AS DateTime), 3)
GO
INSERT [dbo].[Post] ([PostId], [PostWeight], [PostName], [catId], [fromdt], [int_GenderID]) VALUES (10, 2, N'red', 3, CAST(N'2001-06-10T00:00:00.000' AS DateTime), 2)
GO
INSERT [dbo].[Post] ([PostId], [PostWeight], [PostName], [catId], [fromdt], [int_GenderID]) VALUES (11, 2, N'<p>gfhh</p>', 1, CAST(N'2002-06-10T00:00:00.000' AS DateTime), 1)
GO
SET IDENTITY_INSERT [dbo].[Post] OFF
GO
INSERT [dbo].[Tbl_Gender] ([int_GenderID], [vch_GenderName]) VALUES (1, N'Male')
GO
INSERT [dbo].[Tbl_Gender] ([int_GenderID], [vch_GenderName]) VALUES (2, N'Female')
GO
INSERT [dbo].[Tbl_Gender] ([int_GenderID], [vch_GenderName]) VALUES (3, N'Transgender')
GO

Step 3

 

Here we need to build dynamic sql using stored procedure for filtering records,

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

----Author:Satyaprakash
----exec GetDataByIdName 'GET','h','1999-06-10', '1999-07-10'
----exec GetDataByIdName 'GET','h'
----Dynamic sql in stored procedure for filter records using multiple parameter
--  exec GetGenderName >> for gender loading

ALTER PROCEDURE [dbo].[GetDataByIdName]
    @status varchar(10),
    @name nvarchar(max)=null,
    @Fromdate DATETIME=null,
    @Todate DATETIME=null,
    @GenderId int = null
AS
BEGIN
if @status ='GET'
BEGIN
    Set NoCount ON
Declare @SQLQuery AS NVarchar(4000)
Declare @ParamDefinition AS NVarchar(2000)
    Set @SQLQuery ='SELECT P.*,C.catName,g.Vch_GenderName as Gender from [dbo].[Post] P
    Join Tbl_Gender g on P.int_GenderID = g.int_GenderID
    JOIN [dbo].[Category] C ON P.catId=C.catId
    where p.catid<>0'
    If (@Fromdate Is Not Null) AND (@Todate Is Not Null)
         Set @SQLQuery = @SQLQuery + 'And (p.fromdt BETWEEN @Fromdate AND @Todate)'

    If (@name Is Not Null) and (@name <> '')
    Set @SQLQuery = @SQLQuery + 'and P.PostName LIKE '''+ '%' + @name + '%' + ''''

    If (@GenderId Is Not Null) and (@GenderId <> 0)
    Set @SQLQuery = @SQLQuery + 'and g.int_GenderID = @GenderId'

         Set @ParamDefinition = '@Fromdate DATETIME,@Todate DATETIME,@name nvarchar(max),@GenderId int'
         Execute sp_Executesql     @SQLQuery,@ParamDefinition,@Fromdate,@Todate,@name,@GenderId
    END
END

A dynamic SQL in a stored procedure is a single T-SQL statement or a set of statements stored in a variable and executed using a SQL command. A Dynamic SQL is required when we need to fetch a set of records based on different search parameters. A dynamically build Transact-SQL statements can be executed using EXECUTE Command or sp_executesql statement. sp_executesql which is more efficient, faster in execution and also supports parameter substitution.

@status varchar(10),
    @name nvarchar(max)=null,
    @Fromdate DATETIME=null,
    @Todate DATETIME=null,
    @GenderId int = null

These are the Input Parameters.

Declare @SQLQuery AS NVarchar(4000)
Declare @ParamDefinition AS NVarchar(2000)

These are the Variable Declarations.

Set @SQLQuery ='SELECT P.*,C.catName,g.Vch_GenderName as Gender from [dbo].[Post] P
    Join Tbl_Gender g on P.int_GenderID = g.int_GenderID
    JOIN [dbo].[Category] C ON P.catId=C.catId
    where p.catid<>0'

This is about building the Transact-SQL String with the input parameters.

If (@Fromdate Is Not Null) AND (@Todate Is Not Null)
         Set @SQLQuery = @SQLQuery + 'And (p.fromdt BETWEEN @Fromdate AND @Todate)'

    If (@name Is Not Null) and (@name <> '')
    Set @SQLQuery = @SQLQuery + 'and P.PostName LIKE '''+ '%' + @name + '%' + ''''

    If (@GenderId Is Not Null) and (@GenderId <> 0)
    Set @SQLQuery = @SQLQuery + 'and g.int_GenderID = @GenderId'

This is about checking for the condition and building the WHERE clause accordingly.

Set @ParamDefinition = '@Fromdate DATETIME,@Todate DATETIME,@name nvarchar(max),@GenderId int'

This is about Specify Parameter Format for all input parameters included in the statement.

Execute sp_Executesql     @SQLQuery,@ParamDefinition,@Fromdate,@Todate,@name,@GenderId

This is about executing the Transact-SQL String with all parameter values Using sp_executesql Command.

 

This stored procedure passes a few parameter's as input and uses two variables to build and execute; @SQLQuery which is required to create the dynamic SQL-statement and  @ParamDefinition which is required to define the Parameter's format. Whiling making the SQL string in each step, an IF statement is required to verify whether that input parameter is null or not. If it is not NULL, then that parameter will be included in the SQL statement which basically adds a condition in the WHERE clause of the SQL statement. You can clearly see in the procedure that the variable @ParamDefinition contains all the parameter lists and finally sp_Executesql takes SQL-query, parameter list and the parameter values to executes a SELECT statement.

 

Step 4

 

Here we need create a model class with entities which should be same as stored procedure column names. This is named as "PostDetail.cs"

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Web;

namespace WebApplication1.Models
{
    public class PostDetail
    {
        public int PostId { get; set; }
        public Nullable<int> PostWeight { get; set; }
        [Display(Name = "Post Name")]
        public string PostName { get; set; }
        public Nullable<int> catId { get; set; }
        [NotMapped]
        [Display(Name = "Categoty Name")]
        public string catName { get; set; }
        public DateTime fromdt { get; set; }
        public List<PostDetail> usersinfo { get; set; }
        [Display(Name = "Gender")]
        public string Gender { get; set; }
    }
}

Step 5

 

Here we need to create a controller named HomeController.cs inside Controllers folder. Inside Home controller we added a controller action method named as List.

 

Code Ref 

public ActionResult List(DateTime? From, DateTime? To, string name, int? GenderId)
        {
            //for alert purpose
            if (From > To)
            {
                TempData["SelectOption"] = 1;
            }
            //for alert purpose

            string mainconn = ConfigurationManager.ConnectionStrings["dbconnection"].ConnectionString; //added connection string
            PostDetail objuser = new PostDetail();
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            using (SqlConnection con = new SqlConnection(mainconn))
            {
                using (SqlCommand cmd = new SqlCommand("GetDataByIdName", con)) //stored procedure name
                {
                    con.Open();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@status", "GET"); //Parameters for filter records
                    cmd.Parameters.AddWithValue("@name", name);
                    cmd.Parameters.AddWithValue("@Fromdate", From);
                    cmd.Parameters.AddWithValue("@Todate", To);
                    cmd.Parameters.AddWithValue("@GenderId", GenderId);

                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    da.Fill(ds);
                    List<PostDetail> userlist = new List<PostDetail>();
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        PostDetail uobj = new PostDetail();

                        uobj.PostName = ds.Tables[0].Rows[i]["PostName"].ToString(); //show records with selected columns
                        uobj.catName = ds.Tables[0].Rows[i]["catName"].ToString();
                        uobj.fromdt = Convert.ToDateTime(ds.Tables[0].Rows[i]["fromdt"]);
                        uobj.Gender = ds.Tables[0].Rows[i]["Gender"].ToString();
                        userlist.Add(uobj);
                    }
                    objuser.usersinfo = userlist;
                }
                con.Close();
            }
            return View(objuser);


        }

Code Description

 

Here I added code with a description in a green comment mark "//" at one place for better and faster understanding.

 

Step 6

 

We need to add view as mentioned in screenshot.

 

 

Code Ref

@model WebApplication1.Models.PostDetail

@{
    /**/

    ViewBag.Title = "List";
}

@*Post Data To Controller Without Page Refresh In*@
<script src="~/Scripts/jquery-3.3.1.js"></script>
<script src="~/Scripts/jquery.unobtrusive-ajax.js"></script>

<h4>Choose Below Options:</h4>

<style>
    table {
        font-family: arial, sans-serif;
        border-collapse: collapse;
        width: 100%;
    }

    td, th {
        border: 1px solid #dddddd;
        text-align: left;
        padding: 8px;
    }

    tr:nth-child(even) {
        background-color: #dddddd;
    }

    .button {
        background-color: #4CAF50;
        border: none;
        color: white;
        padding: 15px 32px;
        text-align: center;
        text-decoration: none;
        display: inline-block;
        font-size: 16px;
        margin: 4px 2px;
        cursor: pointer;
    }

    .button4 {
        border-radius: 9px;
    }

    input[type=date], select {
        width: 60%;
        padding: 12px 20px;
        margin: 8px 0;
        display: inline-block;
        border: 1px solid #ccc;
        border-radius: 4px;
        box-sizing: border-box;
    }

    input[type=text], select {
        width: 60%;
        padding: 12px 20px;
        margin: 8px 0;
        display: inline-block;
        border: 1px solid #ccc;
        border-radius: 4px;
        box-sizing: border-box;
    }
</style>
@*Filter records*@

@using (Html.BeginForm("List", "Home", FormMethod.Get))
{
    <span style="color:blue">From Date:</span><input type="date" name="From" />
    <span style="color:blue">To Date:</span><input type="date" name="To" /> <span> </span> <span> </span>
    <span style="color:red">OR</span><span> </span> <span> </span> <span> </span> <span> </span>
    <span style="color:blue">Post Name:</span><input type="text" name="name" placeholder="Enter Post Name" /> <span> </span><span> </span><span> </span><span> </span><span> </span><span> </span>
    <span style="color:blue">Select Gen:</span>@Html.DropDownList("GenderId", new List<SelectListItem>{
new SelectListItem{ Text="Select Gender", Value = "0" },
new SelectListItem{ Text="Male", Value = "1" },
new SelectListItem{ Text="Female", Value = "2" },
new SelectListItem{ Text="Transgender", Value = "3" },
})
    <input type="submit" name="submit" value="Search" class="button button4" />
}

@if (Model != null)
{
    if (Model.usersinfo.Count > 0) /*Display records*/
    {
        <table align="center" border="1" cellpadding="4" cellspacing="4">
            <tr>
                <th style="background-color: Yellow;color: blue">Post Name</th>
                <th style="background-color: Yellow;color: blue">Categoty Name</th>
                <th style="background-color: Yellow;color: blue">Joining Date</th>
                <th style="background-color: Yellow;color: blue">Gender</th>
            </tr>
            @foreach (var item in Model.usersinfo)
            {
                <tr>
                    <td>@Html.DisplayFor(modelitem => item.PostName) </td>
                    <td>@Html.DisplayFor(modelitem => item.catName)</td>
                    <td>@Html.DisplayFor(modelitem => item.fromdt)</td>
                    <td>@Html.DisplayFor(modelitem => item.Gender)</td>
                </tr>
            }
        </table>
    }
    else
    {
        <span style="color:red"><b>No Details Found.</b></span>
    }
}

@if (TempData["SelectOption"] != null)
{
    <script type="text/javascript">
        alert("From Date should be less than To Date");
    </script>
}

Code Description

 

Here I added code with a description in a green comment mark at one place for better and faster understanding.

 

Step 7

 

We need to add JS files from Nuget package manager for posting data to Controller without page refresh. If you want to work Ajax.BeginForm functionality properly you should not forget to add the reference of the following jQuery library as mentioned in the screenshot. Download library using NuGet and reference into the project.

 

 

Step 8

 

Add some flavor for the view page by modifying in _Layout.cshtml.

 

Code Ref 

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>@ViewBag.Title - My ASP.NET Application</title>
    @Styles.Render("~/Content/css")
    @Scripts.Render("~/bundles/modernizr")
</head>
<body>
    <div class="navbar navbar-fixed-top" style="background-color:orangered;">
        <h4 style="color:white; text-align:center">Filter Records Using Multiple Parameter In MVC</h4>
    </div>
    <div class="container body-content">
        @RenderBody()
        <hr />
        <footer>
            <p style="background-color: Yellow; font-weight: bold; color:blue; text-align: center; font-style: oblique">© @DateTime.Now.ToLocalTime()</p> @*Add Date Time*@
        </footer>
    </div>

    @Scripts.Render("~/bundles/jquery")
    @Scripts.Render("~/bundles/bootstrap")
    @RenderSection("scripts", required: false)
</body>
</html>

OUTPUT

The landing page is shown as mentioned below:

 

 

Then filter data using gender and post name.

 

Then filter data using gender.

 

 

Then filter data using name.

 

 

Then filter data using from date and to date.

 

 

Then filter records using gender and date.

 

Then filter records using all parameters.

 

 

If no records are found then it is shown like this.

 

 

Then the alert is mentioned between from date and to date compare. Pic-1

 

 

Then the alert is mentioned between from date and to date compare. Pic-2

 

 

Link To Source Code

• Filter Records Using MVC 

Summary

 

In this article, we have learned,

• About dynamic sql with stored procedure and its merits
• Passing multiple parameters for filtering records
• Posting data to controller without page refresh using Ajax.BeginForm functionality
• Managing alert message in MVC and design view using layout