Search Records Between Two Dates Using Stored Procedure, Web API And ReactJS

Sanwar Ranwa
Apr 09, 2020

116.8k
0
6
- facebook
- twitter
- linkedIn
- Reddit
- WhatsApp
- Email
- Print
- Other Artcile

Searching records between two dates is very simple. In here, we will see how we can perform this using a stored procedure ,Web API and ReactJS.

Prerequisites

Basic Knowledge of ReactJS
Visual Studio Code
Visual studio and SQL Server Management studio
Node and NPM installed
Bootstrap
React-datepicker

Create React.js Project

To create a new ReactJS project, open the command prompt and enter the following command.

npx create-react-app searchrecord

Open the newly created project in Visual Studio Code and Bootstrap in this project by using the following command.

npm install --save bootstrap

Now, open the index.js file and add Bootstrap reference.

import 'bootstrap/dist/css/bootstrap.min.css';

Now install 'react-datepicker' library in this project by using the following command.

npm install react-datepicker --save

Install the Axios library by using the following command. Learn more about Axios library

npm install --save axios

Now go to src folder and create a new component 'Searchdata.js' and add the following code in this component.

import React, { Component } from 'react'
import './App.css';
import axios from "axios";
import DatePicker from "react-datepicker";
import "react-datepicker/dist/react-datepicker.css";
export class Searchdata extends Component {
constructor(props) {
super(props)
this.state = {
employeedata: [],
startdate: '' ,
enddate:''
}
}
Changedate = (e) => {
this.setState({
startdate: e
});
};
enddate = (e) => {
this.setState({
enddate: e
});
};
componentDidMount() {
axios.get('http://localhost:1141/Api/Searchdata/showdata').then(response => {
console.log(response.data);
this.setState({
employeedata: response.data
});
});
}
onsubmit = (e) => {
debugger;
const data = { startdate:this.state.startdate, enddate:this.state.enddate};
e.preventDefault();
axios.post('http://localhost:1141/Api/Searchdata/search',data).then(response => {
console.log(response.data);
this.setState({
employeedata: response.data
});
});
}
render() {
return (
<div>
<div className="row">
<div className="col-sm-12 btn btn-info">
How to Search Data Between Two Dates Using Web API and ReactJS
</div>
</div>
<form onSubmit={this.onsubmit}>
<div className="row hdr" >
<div className="col-sm-3 form-group"> </div>
<div className="col-sm-3 form-group">
<DatePicker className="form-control"
selected={this.state.startdate} placeholderText="Select Date" showPopperArrow={false}
onChange={this.Changedate}
/>
</div>
<div className="col-sm-3 form-group">
<DatePicker className="form-control"
selected={this.state.enddate} placeholderText="Select Date" showPopperArrow={false}
onChange={this.enddate}
/>
</div>
<div className="col-sm-3 form-group">
<button type="submit" className="btn btn-success" >Search</button>
</div>
</div>
</form>
<table className="table">
<thead className="thead-dark">
<tr>
<th scope="col">Id</th>
<th scope="col">Name</th>
<th scope="col">City</th>
<th scope="col">JoiningDate</th>
</tr>
</thead>
<tbody>
{
this.state.employeedata.map((p, index) => {
return <tr key={index}>
<th scope="row">{p.Id}</th>
<td>{p.Name}</td>
<td>{p.City}</td>
<td>{p.JoiningDate }</td>
</tr>
})
}
</tbody>
</table>
</div>
)
}
}
export default Searchdata

Add reference of this component in app.js file,

import React from 'react';
import logo from './logo.svg';
import './App.css';
import Searchdata from "./Searchdata";
function App() {
return (
<div className="App">
<Searchdata></Searchdata>
</div>
);
}
export default App;

Create a Table in the Database

CREATE TABLE [dbo].[Employee](
[Id] [int] NOT NULL,
[Name] [varchar](50) NULL,
[City] [varchar](50) NULL,
[JoiningDate] [date] NULL,
PRIMARY KEY CLUSTERED
(
[Id] 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
SET ANSI_PADDING OFF
GO

Create a stored procedure to find the data between two dates.

CREATE PROC [dbo].[Usp_Empsearch]
@Fromdate DATETIME,@Todate DATETIME
AS
BEGIN
SELECT * FROM Employee WHERE JoiningDate BETWEEN @Fromdate AND @Todate
END

Create a New Web API Project

Open Visual Studio and create a new project.

Change the Name to Searchdata and Click ok.

Select Web API as its template.

Right-click the Models folder from Solution Explorer and go to Add >> New Item >> data.

Click on the "ADO.NET Entity Data Model" option and click "Add".

Select EF Designer from the database and click the "Next" button

Add the connection properties and select database name on the next page and click OK.

Check the "Table" and "stored procedures" checkbox. The internal options will be selected by default. Now, click the "Finish" button.

Our data model is successfully created now.

Right-click on the Models folder and add a class searchdata. Now, paste the following code in this class.

public class searchdata
{
public DateTime startdate { get; set; }
public DateTime enddate { get; set; }
}

Right-click on the Controllers folder and add a new controller. Name it as "Searchdata controller" and add the following namespace in the Searchdata controller.

using Searchdata.Models;

Now, add two methods to fetch data and search data by dates from the database.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;
using Searchdata.Models;
namespace Searchdata.Controllers
{
[RoutePrefix("Api/Searchdata")]
public class SearchdataController : ApiController
{
employeesEntities DB = new employeesEntities();
[Route("showdata")]
[HttpGet]
public object showdata()
{
var a = DB.Employees.ToList();
return a;
}
[Route("search")]
[HttpPost]
public object search(searchdata sd)
{
var a= DB.Usp_Empsearch(sd.startdate, sd.enddate);
return a;
}
}
}

Now, let's enable CORS. Go to Tools, open NuGet Package Manager, search for CORS, and install the "Microsoft.Asp.Net.WebApi.Cors" package. Open Webapiconfig.cs and add the following lines: