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,
  1. CREATE TABLE [dbo].[Post](
  2. [PostId] [int] IDENTITY(1,1) NOT NULL,
  3. [PostWeight] [int] NULL,
  4. [PostName] [varchar](max) NULL,
  5. [catId] [int] NULL,
  6. [fromdt] [datetime] NULL,
  7. [int_GenderID] [int] NULL,
  8. CONSTRAINT [PK_Post] PRIMARY KEY CLUSTERED
  9. (
  10. [PostId] ASC
  11. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  12. ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
  13. GO
  1. CREATE TABLE [dbo].[Category](
  2. [catId] [int] IDENTITY(1,1) NOT NULL,
  3. [catName] [nvarchar](50) NULL,
  4. [int_GenderID] [int] NULL,
  5. CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED
  6. (
  7. [catId] ASC
  8. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  9. ) ON [PRIMARY]
  10. GO
  1. CREATE TABLE [dbo].[Tbl_Gender](
  2. [int_GenderID] [int] NOT NULL,
  3. [vch_GenderName] [varchar](104) NOT NULL,
  4. CONSTRAINT [PK_Tbl_Gender] PRIMARY KEY CLUSTERED
  5. (
  6. [int_GenderID] ASC
  7. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  8. ) ON [PRIMARY]
  9. GO
Step 2
First, we need to prepare data for above tables,
  1. SET IDENTITY_INSERT [dbo].[Category] ON
  2. GO
  3. INSERT [dbo].[Category] ([catId], [catName], [int_GenderID]) VALUES (1, N'Plan', 1)
  4. GO
  5. INSERT [dbo].[Category] ([catId], [catName], [int_GenderID]) VALUES (2, N'Development', 3)
  6. GO
  7. INSERT [dbo].[Category] ([catId], [catName], [int_GenderID]) VALUES (3, N'End', 2)
  8. GO
  9. INSERT [dbo].[Category] ([catId], [catName], [int_GenderID]) VALUES (4, N'Processing', 3)
  10. GO
  11. SET IDENTITY_INSERT [dbo].[Category] OFF
  12. GO
  13. SET IDENTITY_INSERT [dbo].[Post] ON
  14. GO
  15. 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)
  16. GO
  17. 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)
  18. GO
  19. 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)
  20. GO
  21. 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)
  22. GO
  23. 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)
  24. GO
  25. 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)
  26. GO
  27. 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)
  28. GO
  29. 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)
  30. GO
  31. SET IDENTITY_INSERT [dbo].[Post] OFF
  32. GO
  33. INSERT [dbo].[Tbl_Gender] ([int_GenderID], [vch_GenderName]) VALUES (1, N'Male')
  34. GO
  35. INSERT [dbo].[Tbl_Gender] ([int_GenderID], [vch_GenderName]) VALUES (2, N'Female')
  36. GO
  37. INSERT [dbo].[Tbl_Gender] ([int_GenderID], [vch_GenderName]) VALUES (3, N'Transgender')
  38. GO
Step 3
Here we need to build dynamic sql using stored procedure for filtering records,
  1. SET ANSI_NULLS ON
  2. GO
  3. SET QUOTED_IDENTIFIER ON
  4. GO
  6. ----Author:Satyaprakash
  7. ----exec GetDataByIdName 'GET','h','1999-06-10', '1999-07-10'
  8. ----exec GetDataByIdName 'GET','h'
  9. ----Dynamic sql in stored procedure for filter records using multiple parameter
  10. -- exec GetGenderName >> for gender loading
  12. ALTER PROCEDURE [dbo].[GetDataByIdName]
  13. @status varchar(10),
  14. @name nvarchar(max)=null,
  15. @Fromdate DATETIME=null,
  16. @Todate DATETIME=null,
  17. @GenderId int = null
  18. AS
  19. BEGIN
  20. if @status ='GET'
  21. BEGIN
  22. Set NoCount ON
  23. Declare @SQLQuery AS NVarchar(4000)
  24. Declare @ParamDefinition AS NVarchar(2000)
  25. Set @SQLQuery ='SELECT P.*,C.catName,g.Vch_GenderName as Gender from [dbo].[Post] P
  26. Join Tbl_Gender g on P.int_GenderID = g.int_GenderID
  27. JOIN [dbo].[Category] C ON P.catId=C.catId
  28. where p.catid<>0'
  29. If (@Fromdate Is Not Null) AND (@Todate Is Not Null)
  30. Set @SQLQuery = @SQLQuery + 'And (p.fromdt BETWEEN @Fromdate AND @Todate)'
  32. If (@name Is Not Null) and (@name <> '')
  33. Set @SQLQuery = @SQLQuery + 'and P.PostName LIKE '''+ '%' + @name + '%' + ''''
  35. If (@GenderId Is Not Null) and (@GenderId <> 0)
  36. Set @SQLQuery = @SQLQuery + 'and g.int_GenderID = @GenderId'
  38. Set @ParamDefinition = '@Fromdate DATETIME,@Todate DATETIME,@name nvarchar(max),@GenderId int'
  39. Execute sp_Executesql @SQLQuery,@ParamDefinition,@Fromdate,@Todate,@name,@GenderId
  40. END
  41. 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.
  1. @status varchar(10),
  2. @name nvarchar(max)=null,
  3. @Fromdate DATETIME=null,
  4. @Todate DATETIME=null,
  5. @GenderId int = null
These are the Input Parameters.
  1. Declare @SQLQuery AS NVarchar(4000)
  2. Declare @ParamDefinition AS NVarchar(2000)
These are the Variable Declarations.
  1. Set @SQLQuery ='SELECT P.*,C.catName,g.Vch_GenderName as Gender from [dbo].[Post] P
  2. Join Tbl_Gender g on P.int_GenderID = g.int_GenderID
  3. JOIN [dbo].[Category] C ON P.catId=C.catId
  4. where p.catid<>0'
This is about building the Transact-SQL String with the input parameters.
  1. If (@Fromdate Is Not Null) AND (@Todate Is Not Null)
  2. Set @SQLQuery = @SQLQuery + 'And (p.fromdt BETWEEN @Fromdate AND @Todate)'
  4. If (@name Is Not Null) and (@name <> '')
  5. Set @SQLQuery = @SQLQuery + 'and P.PostName LIKE '''+ '%' + @name + '%' + ''''
  7. If (@GenderId Is Not Null) and (@GenderId <> 0)
  8. Set @SQLQuery = @SQLQuery + 'and g.int_GenderID = @GenderId'
This is about checking for the condition and building the WHERE clause accordingly.
  1. 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.
  1. 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"
  1. using System;
  2. using System.Collections.Generic;
  3. using System.ComponentModel.DataAnnotations;
  4. using System.ComponentModel.DataAnnotations.Schema;
  5. using System.Linq;
  6. using System.Web;
  8. namespace WebApplication1.Models
  9. {
  10. public class PostDetail
  11. {
  12. public int PostId { get; set; }
  13. public Nullable<int> PostWeight { get; set; }
  14. [Display(Name = "Post Name")]
  15. public string PostName { get; set; }
  16. public Nullable<int> catId { get; set; }
  17. [NotMapped]
  18. [Display(Name = "Categoty Name")]
  19. public string catName { get; set; }
  20. public DateTime fromdt { get; set; }
  21. public List<PostDetail> usersinfo { get; set; }
  22. [Display(Name = "Gender")]
  23. public string Gender { get; set; }
  24. }
  25. }
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
  1. public ActionResult List(DateTime? From, DateTime? To, string name, int? GenderId)
  2. {
  3. //for alert purpose
  4. if (From > To)
  5. {
  6. TempData["SelectOption"] = 1;
  7. }
  8. //for alert purpose
  10. string mainconn = ConfigurationManager.ConnectionStrings["dbconnection"].ConnectionString; //added connection string
  11. PostDetail objuser = new PostDetail();
  12. DataSet ds = new DataSet();
  13. DataTable dt = new DataTable();
  14. using (SqlConnection con = new SqlConnection(mainconn))
  15. {
  16. using (SqlCommand cmd = new SqlCommand("GetDataByIdName", con)) //stored procedure name
  17. {
  18. con.Open();
  19. cmd.CommandType = CommandType.StoredProcedure;
  20. cmd.Parameters.AddWithValue("@status", "GET"); //Parameters for filter records
  21. cmd.Parameters.AddWithValue("@name", name);
  22. cmd.Parameters.AddWithValue("@Fromdate", From);
  23. cmd.Parameters.AddWithValue("@Todate", To);
  24. cmd.Parameters.AddWithValue("@GenderId", GenderId);
  26. SqlDataAdapter da = new SqlDataAdapter(cmd);
  27. da.Fill(ds);
  28. List<PostDetail> userlist = new List<PostDetail>();
  29. for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
  30. {
  31. PostDetail uobj = new PostDetail();
  33. uobj.PostName = ds.Tables[0].Rows[i]["PostName"].ToString(); //show records with selected columns
  34. uobj.catName = ds.Tables[0].Rows[i]["catName"].ToString();
  35. uobj.fromdt = Convert.ToDateTime(ds.Tables[0].Rows[i]["fromdt"]);
  36. uobj.Gender = ds.Tables[0].Rows[i]["Gender"].ToString();
  37. userlist.Add(uobj);
  38. }
  39. objuser.usersinfo = userlist;
  40. }
  41. con.Close();
  42. }
  43. return View(objuser);
  46. }
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.
Filter Records By Passing Multiple Parameters In Stored Procedure Using MVC
Code Ref
  1. @model WebApplication1.Models.PostDetail
  3. @{
  4. /**/
  6. ViewBag.Title = "List";
  7. }
  9. @*Post Data To Controller Without Page Refresh In*@
  10. <script src="~/Scripts/jquery-3.3.1.js"></script>
  11. <script src="~/Scripts/jquery.unobtrusive-ajax.js"></script>
  13. <h4>Choose Below Options:</h4>
  15. <style>
  16. table {
  17. font-family: arial, sans-serif;
  18. border-collapse: collapse;
  19. width: 100%;
  20. }
  22. td, th {
  23. border: 1px solid #dddddd;
  24. text-align: left;
  25. padding: 8px;
  26. }
  28. tr:nth-child(even) {
  29. background-color: #dddddd;
  30. }
  32. .button {
  33. background-color: #4CAF50;
  34. border: none;
  35. color: white;
  36. padding: 15px 32px;
  37. text-align: center;
  38. text-decoration: none;
  39. display: inline-block;
  40. font-size: 16px;
  41. margin: 4px 2px;
  42. cursor: pointer;
  43. }
  45. .button4 {
  46. border-radius: 9px;
  47. }
  49. input[type=date], select {
  50. width: 60%;
  51. padding: 12px 20px;
  52. margin: 8px 0;
  53. display: inline-block;
  54. border: 1px solid #ccc;
  55. border-radius: 4px;
  56. box-sizing: border-box;
  57. }
  59. input[type=text], select {
  60. width: 60%;
  61. padding: 12px 20px;
  62. margin: 8px 0;
  63. display: inline-block;
  64. border: 1px solid #ccc;
  65. border-radius: 4px;
  66. box-sizing: border-box;
  67. }
  68. </style>
  69. @*Filter records*@
  71. @using (Html.BeginForm("List", "Home", FormMethod.Get))
  72. {
  73. <span style="color:blue">From Date:</span><input type="date" name="From" />
  74. <span style="color:blue">To Date:</span><input type="date" name="To" /> <span> </span> <span> </span>
  75. <span style="color:red">OR</span><span> </span> <span> </span> <span> </span> <span> </span>
  76. <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>
  77. <span style="color:blue">Select Gen:</span>@Html.DropDownList("GenderId", new List<SelectListItem>{
  78. new SelectListItem{ Text="Select Gender", Value = "0" },
  79. new SelectListItem{ Text="Male", Value = "1" },
  80. new SelectListItem{ Text="Female", Value = "2" },
  81. new SelectListItem{ Text="Transgender", Value = "3" },
  82. })
  83. <input type="submit" name="submit" value="Search" class="button button4" />
  84. }
  86. @if (Model != null)
  87. {
  88. if (Model.usersinfo.Count > 0) /*Display records*/
  89. {
  90. <table align="center" border="1" cellpadding="4" cellspacing="4">
  91. <tr>
  92. <th style="background-color: Yellow;color: blue">Post Name</th>
  93. <th style="background-color: Yellow;color: blue">Categoty Name</th>
  94. <th style="background-color: Yellow;color: blue">Joining Date</th>
  95. <th style="background-color: Yellow;color: blue">Gender</th>
  96. </tr>
  97. @foreach (var item in Model.usersinfo)
  98. {
  99. <tr>
  100. <td>@Html.DisplayFor(modelitem => item.PostName) </td>
  101. <td>@Html.DisplayFor(modelitem => item.catName)</td>
  102. <td>@Html.DisplayFor(modelitem => item.fromdt)</td>
  103. <td>@Html.DisplayFor(modelitem => item.Gender)</td>
  104. </tr>
  105. }
  106. </table>
  107. }
  108. else
  109. {
  110. <span style="color:red"><b>No Details Found.</b></span>
  111. }
  112. }
  114. @if (TempData["SelectOption"] != null)
  115. {
  116. <script type="text/javascript">
  117. alert("From Date should be less than To Date");
  118. </script>
  119. }
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.
Filter Records By Passing Multiple Parameters In Stored Procedure Using MVC
Step 8
Add some flavor for the view page by modifying in _Layout.cshtml.
Code Ref
  1. <!DOCTYPE html>
  2. <html>
  3. <head>
  4. <meta charset="utf-8" />
  5. <meta name="viewport" content="width=device-width, initial-scale=1.0">
  6. <title>@ViewBag.Title - My ASP.NET Application</title>
  7. @Styles.Render("~/Content/css")
  8. @Scripts.Render("~/bundles/modernizr")
  9. </head>
  10. <body>
  11. <div class="navbar navbar-fixed-top" style="background-color:orangered;">
  12. <h4 style="color:white; text-align:center">Filter Records Using Multiple Parameter In MVC</h4>
  13. </div>
  14. <div class="container body-content">
  15. @RenderBody()
  16. <hr />
  17. <footer>
  18. <p style="background-color: Yellow; font-weight: bold; color:blue; text-align: center; font-style: oblique">© @DateTime.Now.ToLocalTime()</p> @*Add Date Time*@
  19. </footer>
  20. </div>
  22. @Scripts.Render("~/bundles/jquery")
  23. @Scripts.Render("~/bundles/bootstrap")
  24. @RenderSection("scripts", required: false)
  25. </body>
  26. </html>

OUTPUT

The landing page is shown as mentioned below:
Filter Records By Passing Multiple Parameters In Stored Procedure Using MVC
Then filter data using gender and post name.
Filter Records By Passing Multiple Parameters In Stored Procedure Using MVC
Then filter data using gender.
Filter Records By Passing Multiple Parameters In Stored Procedure Using MVC
Then filter data using name.
Filter Records By Passing Multiple Parameters In Stored Procedure Using MVC
Then filter data using from date and to date.
Filter Records By Passing Multiple Parameters In Stored Procedure Using MVC
Then filter records using gender and date.
Filter Records By Passing Multiple Parameters In Stored Procedure Using MVC
Then filter records using all parameters.
Filter Records By Passing Multiple Parameters In Stored Procedure Using MVC
If no records are found then it is shown like this.
Filter Records By Passing Multiple Parameters In Stored Procedure Using MVC
Then the alert is mentioned between from date and to date compare. Pic-1
Filter Records By Passing Multiple Parameters In Stored Procedure Using MVC
Then the alert is mentioned between from date and to date compare. Pic-2
Filter Records By Passing Multiple Parameters In Stored Procedure Using MVC
Link To Source Code

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