Part 33 : calling stored procedure part 2

#3 Minutes of Reading

I shown you limitation with FromSqlRaw method, it works only with already available module and not return result set of multiple tables or table join.
EF provide alternate way to achieve this with traditional ExecuteReader method. Let’s see how to achieve this with EF core.
Create required database tables and stored procedure.

CREATE TABLE [dbo].[tblRole] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[Role] VARCHAR (20) NULL
);
CREATE TABLE [dbo].[tblUserRole] (
[RoleID] INT NULL,
[UserId] INT NULL
);
ALTER procedure getUserRoles
@user varchar(20)
As
Begin
Select U.UserName, R.Role from
Users U inner join tblUserRole UR on UR.UserId = U.id inner join tblRole R on UR.RoleID = R.Id
Where U.UserName = @user
End
 
Create model under Models folder.
public class UserRole
{
public string User { get; set; }
public string Role { get; set; }
}

Create Index2 action method and GetUserRoles method.

GetUserRoles method communicate and return multiple table result set from database. At line number 34, EF core support to create traditional command object and then add SqlParameter, command text as stored procedure to command object.
ExecuteReader of command object return table result of type DbDataReader. To read data from DbDatareader use Read method from datareader object shown at line number 40. Create view Index2 for showing UserRole.

Run application and view changes in Browser.

Part 32 : Calling stored procedure using EF core

#3 Minutes of Readin

In this article I will show you how to call stored procedure using EF core in asp.net Core 3.1.
Before creating web application, create database table and stored procedure in MS SQL server.

CREATE TABLE [dbo].[Users] (
[id] INT IDENTITY (1, 1) NOT NULL,
[UserName] NVARCHAR (MAX) NULL,
[Password] NVARCHAR (MAX) NULL
);

CREATE PROCEDURE getUserByUserName
@username varchar(50)
AS
SELECT id, username, Password from dbo.Users
where username = @username
RETURN 0

Once table and stored procedure created, create asp.net web application.
Create User model under Models folder.

public class User
{
[Key]
public int id { get; set; }
public string UserName { get; set; }
public string Password { get; set; }
}

Create SqlDbContext.cs class
public class SqlDbContext:DbContext
{
public SqlDbContext(DbContextOptions options) : base(options)
{
}
public DbSet Users { get; set; }
}

Open AppSettings.Json and create connection string configuration under appsettings json file.

Open Startup.cs class and replace below piece of code.
public void ConfigureServices(IServiceCollection services)
{
services.AddControllersWithViews();
services.AddDbContextPool(options => options.UseSqlServer(Configuration.GetConnectionString("SQLConnection")));
}

Create Home controller with below piece of code. GetUser method is responsible to get user from User table. In asp.net core 3.1, we can call stored procedure but with limitation. Users is DBSet property of actual database User table and result return from stored procedure should contains property which is same as user model.
EF core not support SP result which contains join or multiple table result.

Create View Index.cshtml with below piece of code.

Run application and check in browser window.

Part 15 : Pseudo class selector in React

# 5 minutes of reading

As I already shown you in previous article how to use inline CSS and external CSS from separate file.
Pseudo class selector work fine with in react when you reference CSS from external file but not work with inline CSS style. In react Inline CSS is JavaScript(JSX code) and you cannot apply pseudo class selector directly but with the help radium library. Source code available here

Pseudo class selector:
div:hover {
background-color: blue;
}
In above CSS class example, hover is pseudo class selector. Pseudo class selector written after class name separated by colon character.
Above CSS changes color of div element when mouse hover to the div.
Let’s see how to use Pseudo selector class can be used in inline CSS with react.
Install third party package radium, open VS code and click on terminal from menu bar of VS code. It will open below window and type highlighted command shown.

Once radium installed successfully, change below highlighted code in App.js file.

In App.js file need to import radium package/library highlighted at line number 4 and before exporting App component it should be wrap in radium highlighted at line number 78. Radium is providing libraries from radium package as extra functionality to App component on top.
Change below piece of code from line number 34 to 38 and line number 64 to 67. When Employee list not present on screen and mouse house on button, the color of button turn to purple on over. If employees list is present on screen and user hover on button, color of button turn to yellow.

Let’s see in action on browser window.

Part 14 : Apply CSS Dynamically in React

In this and upcoming few article I will show different ways of using CSS in react application. I have added style variable in app.js file at line number 29 shown below. CSS applied to button dynamically and based on employee is visible or nor, color of button gets change. If showEmployees variable is true, color of toggle button is blue else orange at line number 51. Style applied to button at line number 57.

We can call it as inline style but style variable is nothing but JavaScript variable and JSX convert it into inline CSS in background. For more detail launch browser window and press F12 to view developer mode. Highlighted is the inline style rendered properly on browser.

If you notice at line number 55, I’m using ClassName=’App’ and App class is referenced from external CSS file imported at line number 2..

Open terminal window in VS code, select new command prompt and navigate till project path and run application using npm start command.

Initially toggle button should be in blue color and once you click on toggle button, color of button should change from blue to orange.

Apply CSS Style using external CSS file: Let’s see how to apply CSS class from external file in more details in react application. CSS style defined in CSS file (App.css) and reference of this css file mentioned in App.js file. Open App.css file and update below highlighted code from line number 5 to 11.

Open App.js file and add code available at line number 36 and 58.
I have created JavaScript variable which holds CSS class name declared in app.css file shown in above screen and at line number 58 Classname holds created JavaScript variable which contains CSS class name separated with space using join function of JavaScript(line 36).

With above change run application and observe changes on browser.

Apply CSS dynamically: Modify below piece of code in app.js function from line number 36 to 41 and at line number 62. I have used if else statement and based on employee count it apply CSS style to header.

Run application and click on card to delete card and text color and font gets change for header.

Part 13 : Edit Employee Card

In this article, I will show you how to update name of a employee from employee card efficiently. I already shown this in article 10 but not in efficient way. This is good time to discuss and implement in efficient way. Source code available here
From below screen if I update textbox value the label field of employee name automatically updated.

Initially it was John and I have updated 007 in textbox which also update Name of employee on card.
Let’s implement this functionality in efficiently. React expect key while updating anything on react with list (map function). Key help react to update records efficiently on DOM. Application will work without key also but it gives warning on browser console (Press F12 on browser and open console).
Add EditText highlighted in below screen to update name of employee card from textbox. Edit text is method which perform edit operation in background.

UpdateNameHandler is arrow function called from EditText. It expect two parameter actual event object and id of employee.
At line number 30, … followed by this.state.employees array. Three dots represents spread operator and spread operator is used to copy data from existing object and stored in newly created variable/object.

Run application and check browser in action.

Change name in above highlighted textbox shown in screen. Label field of sam also changed according to textbox value shown below.

Part 12 : Deleting Employee Card

#2 Minutes Of Reading In this article I will show you how we can delete employee card on click of employee card.
I show in previous article, map function which iterate through each row in 2-D array.
To implement delete functionality, I have added one more input parameter to map function (index). Index is nothing but index of an array or employee card index. Which helps in deleting employee card.
Map function iterate through each row in 2-D array and index parameter is index of an 2-D array row. Index starts from 0 up to arrayLength-1. Here, example I showed is 3 row so index count should start from 0 to 2. For more details please check below screen at line number.
Source code available here

DeleteEmp arraow function created to delete employee card and called from Employee component. Basically from on click of div shown in below screen. Splice function from javascript remove requrested index row. Actually function accept two parameter requested rows and how mant row to delete. I have passed requested row as index and how many rows to delete is 1. As I Just wanted to delete clicked employee card only from all cards.