Learn to use Union, Intersect, and Except Clauses

In this article, we will understand Union, Intersect, and Except clause in SQL Server. Also, we will see the difference between Intersect and Union in SQL Server. Also, the difference between Join with Intersect by Sagar Jaybhay

The intersect operator retrieves the common records between the left and right queries of the intersect operator.

  1. This is introduced in SQL Server 2005.
  2. The number of columns and order of columns should be the same.
  3. The data types must be the same or least compatible.
  4. It filters duplicate records and selects only distinct records that are common in the left and right queries. But if you use inner join then it will not filter distinct records.
  5. To use inner join behave like intersect you need to use distinct records

select * from Employee where EmpID between 1 and 100
intersect
select * from Employee where EmpID between 1 and 10;
Intersect Operator In SQL Server

In this we also get the result using inner join and below is a result

select * from Employee as e 
inner join (select * from Employee where EmpID between 1 and 10) as a on e.EmpID=a.EmpID
Join Result

In an inner join, it treats 2 Null values are different and if you join 2 tables on that column on which column has null values then inner join will not include that null value records where intersect treat null as same and return matching records.

What is the difference between Union, Intersect and Except operator in SQL Server?

Union:

The Union operator will return all unique rows from the left query and right query and union all operator will include duplicate also.

Intersect :

The intersect operator will retrieve all unique rows from the left and right queries.

Except:

The Except operator will retrieve all the unique rows from the left query that are not present in the right query result set.

Cross Apply and Outer Apply In SQL Server

The Apply Operator is introduced in SQL Server 2005 and it is basically used to join a table and table-valued function.

The table-valued function on the right-hand side gets called for each row in the left-hand side table.

The cross apply will return only matching rows and it is similar to an inner join.

Outer apply will return matching rows as well as non-matching rows and is similar to left join and the columns which are unmatched of table-valued function is returned as null.

https://sagarjaybhay.net

How Angular Routing Works and What is Routing?

In this article, we will understand How Angular Routing Works and What is the Meaning Of routing In Angular By Sagar Jaybhay.`

What is the Meaning Of routing In Angular

Routing basically means navigating between pages. You have seen many sites with links that direct you to a new page.

The Angular Router enables you to show different components and data to the user based on where the user is in the application. The router enables navigation from one view to the next as users perform tasks.

Angular Routing

Routing Collection is a place where we specify the URLs and components where loaded.

If you create an angular project using cli command then it will automatically add your_app_name-routing.module.ts file where we can define routes.

import  NgModule  from '@angular/core';
import  Routes, RouterModule  from '@angular/router';
import HomeComponent   from "./home/home.component";

import  LeftmenuComponent  from "./leftmenu/leftmenu.component";
import   SupplierComponent from "./supplier/supplier.component";
import  CustomerComponent  from './Customer.component';

const routes: Routes = [
path:'Home',component:HomeComponent,
path:'Customer',component:CustomerComponent,
path:'Supplier',component:SupplierComponent,
path:'',component:HomeComponent
];

@NgModule(
  imports: [RouterModule.forRoot(routes)],
  exports: [RouterModule]
)
export class AppRoutingModule  
angular routing file

This file needs to add in our main module file by default it is added but if it is not added by default you can go add this in this location.

Routing Module added

But for this path, we need to modify some of the Html code like below.

<p>home works!</p>
<a [routerLink]="['Home']">Home</a> <br/>
<a [routerLink]="['Customer']">Customer</a> <br/>
<a [routerLink]="['Supplier']">Supplier</a> <br/>
<hr>
<br/>
<br/>
<br/>
<div>
    <router-outlet></router-outlet>
</div>

In the above code, we use 2 different directives routerLink and router-outlet.

base href

Most routing applications should add an <base> element to the index.html as the first child in the <head> tag to tell the router how to compose navigation URLs.

If the app folder is the application root, as it is for the sample application, set the href value exactly as shown here.

src/index.html (base-href)

content_copy<base href="/">

routerLink

This is used to generate a router link. In our case  http://localhost:4200/Home you can pass a parameter to the route by using the following way.

a [routerLink]="['/user/bob']" [queryParams]="debug: true" fragment="education">
    link to the user component
  </a>

If you want to preserve state in the browser you can use state variable.

<a [routerLink]="['/user/bob']" [state]="tracingId: 123"> link to the user component
</a>	

router-outlet

It is used to placed dynamically added content in our case it is a component that is associated with view. Each outlet can have a unique name, determined by the optional name attribute. The name cannot be set or changed dynamically. If not set, the default value is “primary”.

<router-outlet></router-outlet>
<router-outlet name='left'></router-outlet>
<router-outlet name='right'></router-outlet>

A router outlet emits an activate event when a new component is instantiated, and a deactivate event when a component is destroyed.

<router-outlet
  (activate)='onActivate($event)'
  (deactivate)='onDeactivate($event)'></router-outlet>

GitHub Project Link: – https://github.com/Sagar-Jaybhay/angular9

Angular Binding how it works and the Significance of package.lock.json

In this article, Sagar Jaybhay explains different ways of angular binding in angular and Package.lock.json file significance in angular application.

Angular Binding

Their are 3 types of binding which are listed below.

  1. One-Way binding( UI – – -> Component): when you want to send data from UI to Component you can use this. () angular bracket this syntax is used for one way binding from UI to Component.
  2. One-Way binding [Component – – -> UI]: when you want to send data from Component to UI you can use Square brackets.
  3. Two-Waybindig: when you want to send data from UI to Component and Component to UI you can use [()] this syntax.
Customer ID : <input [(ngModel)]="CustomerModel.CustomerID" type="text">
<br/>
Customer Name : <input [(ngModel)]="CustomerModel.CustomerName" type="text">
<br/>
Customer Amount : <input [(ngModel)]="CustomerModel.CustomerAmount" type="text">

<br/>
<br/>
<hr>

<table>
    <tr><td>Customer ID</td><td>CustomerModel.CustomerID</td></tr>
    <tr><td>Customer Name </td><td>CustomerModel.CustomerName</td></tr>
    <tr><td>Customer Amount</td><td>CustomerModel.CustomerAmount</td></tr>
</table>

But above code is not worked it will throw an error

ngModel Angular Error: Can’t bind to ‘ngModel’ since it isn’t a known property of ‘input’

why because angular have the modular approach and to work binding, it needs formModule for that purpose we need to import this in our customer.module.ts file now our code becomes.

import  BrowserModule  from '@angular/platform-browser';
import  NgModule  from '@angular/core';

import FormsModule  from '@angular/forms'

import  AppRoutingModule  from './Customer-routing.module';
import  CustomerComponent  from './Customer.component';

@NgModule(
  declarations: [
    CustomerComponent
  ],
  imports: [
    BrowserModule,
    AppRoutingModule,
    FormsModule
  ],
  providers: [],
  bootstrap: [CustomerComponent]
)
export class AppModule  

angular formModule

Package.Lock.json:

This package.lock.json tells us how npm versioning works.

In the above figure, 5.2.1 is our version in this 5 represents a major version, 2 is a minor version and 1 is revision. So by using the above image, you will understand version number is divided into 3 parts major, minor and revision.

  1. Major version:– it means developer added new features remove the old one and major version incremented when there are breaking changes.
  2. Minor Version: It is incremented when there are new features added but no breaking changes present.
  3. Revision: it is incremented when patches or bug fixes.

The below image is for package.lock.json.

package.lock.json

So if you check the above image if no symbol present in-front of version number then it is not updated automatically. Npm does not increment the major version automatically. But it gives provision for upgrading minor version and provision by using below symbols in front of version numbers.

^:- this for latest minor and revision version

~:- this is for the only install the latest revision

This is the file created after npm resolution and it created automatically.

GitHub Project Link: https://github.com/Sagar-Jaybhay/angular9

How Angular know which module is the startup module?

In this article, we will learn about How Angular 9 knows which module is the startup module and which is not? Also, we will understand Which conventions Angular team Uses by Sagar Jaybhay.

Which One is the startup Module?

This is mentioned in the main.ts file and below is coed for this file.

import  enableProdMode  from '@angular/core';
import  platformBrowserDynamic  from '@angular/platform-browser-dynamic';

import  AppModule  from './app/app.module';
import  environment  from './environments/environment';

if (environment.production) 
  enableProdMode();


platformBrowserDynamic().bootstrapModule(AppModule)
  .catch(err => console.error(err));

platformBrowserDynamic is used to a bootstrap module. This used to set the first module. But another question is …..

How main.ts file is invoked in an angular application?

The main.ts file is invoked by index.html file. But if you see the index.html file code we didn’t find any kind of script invocation code because these all things are done in bundling and minification and when you see dist folder you can see some js files.

Before bundling index.html code.

<!doctype html>
<html lang="en">
<head>
  <meta charset="utf-8">
  <title>Customerapplication</title>
  <base href="/">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link rel="icon" type="image/x-icon" href="favicon.ico">
</head>
<body>
  <app-root></app-root>
</body>
</html>

After bundling index.html file in the dist folder. This bundling is done by a webpack.

<!doctype html>
<html lang="en">
<head>
  <meta charset="utf-8">
  <title>Customerapplication</title>
  <base href="/">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link rel="icon" type="image/x-icon" href="favicon.ico">
</head>
<body>
  <app-root></app-root>
http://runtime-es2015.js
http://runtime-es5.js
http://polyfills-es5.js
http://polyfills-es2015.js
http://styles-es2015.js
http://styles-es5.js
http://vendor-es2015.js
http://vendor-es5.js
http://main-es2015.js
http://main-es5.js</body>
</html>

In the above code, you will see the script files which angular refer.

  1. Runtime.js:- this file contains code for webpack runtime.
  2. Polyfill.js:- used to new code run in old browser
  3. Vendor.js:- this is actually our code or custom coded files where our component, module, and models have resided.
  4. Main.js:- this is the point where the first module in our program is called.
  5. Style.css:- it contains all CSS code

Naming Convention Used By Angular Team

Whatever the angular team followed for file naming convention by angular is Angular Style Guide. For more information about this, you can visit this link https://angular.io/guide/styleguide

default angular naming conventiones

In the above image, the app is the root of the application. Names are given us

  1. root_folder_name.component.ts
  2. root_folder_name.component.css
  3. root_folder_name.module.ts
  4. root_folder_name.component.html

In angular team thinking app comprises of Html, CSS, model.  This is given by the angular team style guide. But if you want to use your thought process, company guidelines in which you worked you can use.

Now onwards we create customer applications in that we create customer models and whatever the name in the app we renamed it, the customer.

Below is our customer model

export class Customer
    CustomerName:"";
    CustomerID:number;
    CustomerAmount:number;

To bind these properties with the component we need to use directives which are used for binding or data flow means from view to component or from component to view. This data flow is in one way or two way. It also used to manipulate the dom elements. We learn this in the upcoming chapters.


GitHub Project Link: https://github.com/Sagar-Jaybhay/angular9


T SQL Advanced Tutorial By Sagar Jaybhay 2020

In this article we will understand T SQL Advanced Tutorial means Transaction In SQL and Common Concurrency Problem and SQL server transaction Isolation level by Sagar Jaybhay

What is the Transaction?

A transaction is a group of commands that changed the data stored in a database. A transaction is treated as a single unit.

The transaction ensures that either all commands will succeed or none of them. Means anyone fails then all commands are rolled back and data that might change is reverted back to the original state. A transaction maintains the integrity of data in a database.

begin try
begin transaction
	update dbo.account set amount = amount-100 where id=1
	update dbo.account set amount=amount+100 where id=2
commit transaction
print 'transaction committed'
end try
begin catch
rollback transaction
print 'transaction rolled-back'
end catch
Concurrent Transaction

In the above example either both statements executed or none of them because it goes in catch block where we rolled-back transactions.

begin try
begin transaction
	update dbo.account set amount = amount-100 where id=1
	update dbo.account set amount=amount+100 where id='A'
commit transaction
print 'transaction commited'
end try
begin catch
rollback transaction
print 'tranaction rolledback'
end catch
Transaction-Fail-and-Roll-Back

Common Concurrency Problem

  1. Dirty reads
  2. Lost update
  3. Nonrepetable reads
  4. Phantom reads

SQL server transaction Isolation level

  1. Read Uncommitted
  2. Read committed
  3. Repeatable read
  4. Snapshot
  5. Serializable

How to overcome the concurrency issues?

One way to overcome this issue is to allow only one user at the time allowed for the transaction.

Dirty Read Concurrency Problem:

A dirty read happens when one transaction permitted to read data that modified by another transaction but that yet not committed. Most of the time it will not cause any problem because if any case transaction fails then the first transaction rolled back its data and the second transaction not have dirty data that also not exist anymore.

To do 2 transactions on one machine open 2 query editor that is your 2 transaction machine and you do an operation like below

Multiple-Transaction

For the first transaction, we update the amount in the account table and then given a delay for 1 min 30 seconds and after this, we rollback the transaction. And in the second window, we select data from a table where we can see uncommitted data and after transaction rollback, we see committed data.

We have default isolation level read committed to set different for reading uncommitted data you can use below command.

set transaction isolation level read uncommitted;


-- the First transaction

begin transaction

update account set amount=amount+1000000 where id=1;

waitfor delay '00:01:30'
rollback transaction

-- Second Transaction

set transaction isolation level read uncommitted;
select * from account;
Success-Ful-Multiple-Transaction.png

Lost Update

It means that 2 transactions read and update the same data. When one transaction silently overrides the data of another transaction modified this is called a lost update.

Both read committed and read uncommitted have lost update side effects.

Repeatable reads, snapshots, and serialization do not have these side effects.

Repeatable read has an additional locking mechanism that Is applied on a row that read by current transactions and prevents them from updated or deleted from another transaction.

-- first transaction

begin transaction
declare @amt float
select @amt=amount from account where id =1;

waitfor delay '00:01:20'
set @amt=@amt-1000
update account set amount=@amt where id=1;
print @amt
commit transaction
-- first tarnsaction
-- second transaction
begin transaction
declare @amt float
select @amt=amount from account where id =1;

waitfor delay '00:00:20'
set @amt=@amt-2000
update account set amount=@amt where id=1;
print @amt
commit transaction
Lost Update

Non-Repeatable read

It was when the first transaction reads the data twice and the second transaction updates the data in between the first and second transactions.

Phantom read

It happens when one transaction executes a query twice and it gets a different number of rows in the result set each time. This happens when a second transaction inserts a new record that matches where the clause of executed by the first query.

To fix phantom read problem we can use serializable and snapshot isolation levels. When we use the serializable isolation level it would apply the range lock. Means whatever range you have given in first transaction lock is applied to that range by doing so second transaction not able to insert data between this range.

Snapshot isolation level

Like a serializable isolation level snapshot also does not have any concurrency side effects.

What is the difference between serializable and Snapshot isolation level?

Serialization isolation level acquires it means during the transaction resources in our case tables acquires a lock for that current transaction. So acquiring the lock it reduces concurrency reduction.

Snapshot doesn’t acquire a lock it maintains versioning in TempDB. Since snapshot does not acquire lock resources it significantly increases the number of concurrent transactions while providing the same level of data consistency as serializable isolation does.

See below the image in that we use a serializable isolation level that acquires a lock so that we are able to see the execution of a query in progress.

Snap Shot Isolation Level

Now in the below example, we set a database for allowing snapshot isolation. For that, we need to execute the below command.

alter database temp
set allow_snapshot_isolation on

Doing so our database tempdb is allowed for snapshot transaction than on one window we use serialization isolation level and on the second we use snapshot isolation level. When we run both transactions we are able to see the snapshot isolation level transaction completed while serialization is in progress and after completing both transactions we see one window has updated data and others will have previous data. First

Isolatio level

Now after completing both transactions

  1. snapshot isolation never blocks the transaction.
  2. It will display that data which is before another transaction processing
  3. It means that snapshot isolation never locks resources and other transaction able read the data
  4. But here one transaction is updating the data another is reading that data so it’s ok
  5. When both transactions updating same data then transaction blocks and this blocks until the first transaction complete and then transaction 2 throws error lost update why because preventing overwriting the data and it fails and error is transaction is aborted you can’t use snapshot isolation level update, delete insert that had been deleted or modified by another transaction.
  6. If you want to complete the second transaction you need to rerun that transaction and data is modified successfully.

Read Committed Snapshot Isolation Level

It is not a different isolation level. It is an only different way of implementing Read committed isolation level. one problem in that if anyone transaction is updating the record while reading the same data by another transaction is blocked.

Difference between Snapshot isolation level and Read Committed Snapshot isolation level.

Snapshot IsolationRead Committed Snapshot isolation level
It is vulnerable to update conflictsNo update conflicts here
Can not use with a distributed transactionIt can work with a distributed transaction
Provides transaction-level read consistencyIt provides statement-level read consistency

My Other Site: https://sagarjaybhay.net

Cursor In RDBMS By Sagar Jaybhay 2020

In this article we will understand cursor in rdbms in our case we show example on SQL Server By Sagar Jaybhay. Also we will understand Merge statement in SQL Server and rerunnable SQL scripts and How to create a stored procedure with an optional parameter?

Cursors In RDBMS

In a relational database management system takes into consideration then it would process the data in sets inefficient manner.

But when you have a need to process the data row by row basis then the cursor is the choice. The cursor is very bad at performance and it should be avoided and also you can replace the cursor with join.

Different Types of Cursors In RDBMS

Their are four types of cursors in rdbms which are listed below

  1. Forward only
  2. Static
  3. Keyset
  4. Dynamic

The cursor is loop through each record one by one so that’s why it’s performance is not good.

declare @empid int
declare @deptid int
declare @fullname varchar(200)

declare empcurose cursor for 
select EmpID,full_name,DepartmentID from Employee

open empcurose

fetch next from empcurose into @empid,@fullname,@deptid

while(@@FETCH_STATUS=0)
begin

print 'EmpID '+cast(@empid as varchar(10))+ ' Name '+cast(@fullname as varchar(100)) + ' deptid '+cast(@deptid as varchar(100))
fetch next from empcurose into @empid,@fullname,@deptid
end
close empcurose
deallocate empcurose
cursor in rdbms
deallocate empcurose

This line is used to deallocate all resources which are allocated for that cursor.

What is rerunnable SQL scripts?

A re-runnable SQL script is a script that runs multiple times on the machine will not throw any kind of error.

For example, if you use create table statement to create a table then use if not exist in create a statement so it will not throw an error.

How to create a stored procedure with an optional parameter?

create procedure searchemployee
@name varchar(10)=null,
@deptid int=null,
@gender varchar(10)=null
as
begin

if(@name is not null)
print 'i am in name '+cast(@name as varchar(20))
select * from tblEmp where [name]=@name;
return;

if(@deptid is not null)
print 'i am in deptid '+cast(@deptid as varchar(20))
select * from tblEmp where deptid=@deptid;
return;

if(@gender is not null)
print 'i am in gender '+cast(@gender as varchar(20))
select * from tblEmp where geneder=@gender;
return;
print 'i m here '+cast(@gender as varchar(20))+' '+cast(@deptid as varchar(20)) +' '+cast(@name as varchar(20))
select * from tblEmp

end

execute searchemployee @deptid=2

Simply pass default values to stored procedure variables.

Merge statement In SQL server

Merge statement is introduced in SQL server 2008 it allows to insert, update, deletes in one statement. It means there is no need to use multiple statements for insert update and delete.

In this, if you want to use merge statement you need to 2 tables

  1. Source table– it contains the changes that need to apply to the target table.
  2. Target table– this is the table that requires changes insert, update, delete.

Merge statement joins the target table to source table by using a common column in both tables based on how you match up we perform insert, update and delete.


Transaction Link: https://www.codementor.io/@sagarjaybhay18091988/transaction-in-sql-server-155l4qr7f4


Subqueries & Correlated Subqueries SQL Server 2020

In this article Sagar Jaybhay explain how to write Subqueries and Correlated Subqueries in SQL Server and What to choose for Performance Subquery or Join?

Subqueries In SQL Server:

Subqueries are enclosed in parenthesis. Subquery also called an inner query and the query which enclosed that inner query is called an outer query. Many times subqueries can be replaced with joins.

select * from Employee where DepartmentID not in (select distinct DepartmentID  from Department)

Another example

select Department_Name,(select count(*) from Employee where DepartmentID=d.DepartmentID) from Department as d;

The above query is an example of use subquery in the select list. The above result can be achieved using join also see below query

select d.Department_Name,COUNT(e.empid) as empcount from Department d
join Employee e on e.DepartmentID=d.DepartmentID
group by d.Department_Name
order by empcount;

According to MSDN, you can nested up to 32 levels.

Columns present in subqueries can not be used in the outer select list of a query.

Correlated Subqueries:

If our subquery depends on the outer query for its value then it is called Correlated subqueries. It means subquery depends on outer subquery/ Correlated subqueries are executed for every single row executed by outer subqueries.

A correlated subquery can be executed independently

select distinct Department_Name,(select count(*) from Employee where DepartmentID=d.DepartmentID group by DepartmentID) as empcount from Department as d  order by empcount;

What to choose for Performance Subquery or Join?

According to MSDN, there is no big difference between queries that use sub-queries and joins.

But in some cases, we need to check the performance and Join produces better performance because the nested query is must be processed for each result of the outer query. In such cases, JOIN will perform better.

In general, JOIN works faster as compared to subqueries but in reality, it will depend on the execution plan generated by SQL Server. If the SQL server generates the same execution plan then you will get the same result.

Transaction Link: https://www.codementor.io/@sagarjaybhay18091988/transaction-in-sql-server-155l4qr7f4

You Should Know Use Of This Terms Angular 9

In this article you will understand importance of mainly used terms in Angular 9 by Sagar Jaybhay. You will understand Components, Modules, How angular 9 works?

Angular 9

Angular is binding frameworks.  It helps us to bind view and model. In angular, you can have multiple apps inside the src folder.

Binding Framework Angular 9

Components

In angular, the binding code which binds the UI means our Html and Model is component. Here Component is the part of angular, which receives data from the UI and Sends data back to UI.

COmponent In Angular 9

In an enterprise application, you can have a lot of views, lots of components and lots of models.

Modules

If you collect different component and group them into one repo is called modules and these component are generally belong to the same modules

In by default angular generated boilerplate code which doesn’t contain model but as per requirement we want that and we created in our application. The naming convention for this is app.model.ts.

Component In Details In Angular 9:

Below is the code for a component which is generated by default by using angular cli.

import  Component  from '@angular/core';

@Component(
  selector: 'app-root',
  templateUrl: './app.component.html',
  styleUrls: ['./app.component.css']
)
export class AppComponent 
  title = 'customerapplication';

In angular, we need to create a class first and after that, we use @component which is decorater and other programming languages are termed as an attribute, data annotation.

Now take each term in @Component.

  1. selector: Placed Html in that location
  2. templateUrl: it means whatever data or fields are present in that component is binded with this Html file which is given in this property.
  3. styleUrls: this means for above Html use this style sheet which is present in this location.

In our component class which is AppComponent in our case, we have given export keyword before this. If we didn’t give the export keyword then it is not accessible or visible outside of that .ts file.

Points to remember:

The connection between view and model is done by component.

Module In Angular 9:

The module is used to group related components under the hood. Below is code for a module which is auto-generated by angular cli.

import  BrowserModule  from '@angular/platform-browser';
import  NgModule  from '@angular/core';

import  AppRoutingModule  from './app-routing.module';
import  AppComponent  from './app.component';

@NgModule(
  declarations: [
    AppComponent
  ],
  imports: [
    BrowserModule,
    AppRoutingModule
  ],
  providers: [],
  bootstrap: [AppComponent]
)
export class AppModule  

So if you see above code AppModule is a class and it is decorated by @NgModule decorator. So we learn every property in this decorator.

  1. Declarations: this is used to declare the component which is used under this module. If you have comp1, comp2 like component then these are declared inside these declarations. When you use this component inside the declaration first you need to import them.
  2. Imports: it is used to import mainly supporting modules
  3. Providers: A provider is an instruction to the Dependency Injection system on how to obtain a value for a dependency.
  4. Bootstrap: this is used to give a starting component or root component where the application starts.

If you consider an enterprise application is having lots of modules and for a startup, we need only one so


GitHub Project Link: https://github.com/Sagar-Jaybhay/angular9


Prerequisite of Angular 9 By Sagar Jaybhay

In this blog series we will learn Angular 9 and this my first blog on Angular 9. So we start with what are Prerequisite of Angular 9 By Sagar Jaybhay.

Node

It is a javascript runtime. If you want to run javascript you need to run this inside the browser but by using Node you can run javascript outside the browser. To download node use this link https://nodejs.org/en/

The current version is 12.16.1 LTS to download this use this link:- https://nodejs.org/dist/v12.16.1/node-v12.16.1-x64.msi

To build node they use Chrome’s V8 JavaScript engine. And the Chrome engine is one of the best engines for JavaScript. It is a fully programming platform and in NODE we have Http component, file component, URL component and NPM for angular.

If we want to learn Angular 9 we required only NPM( Node Package Manager Commands).

How does the Node Package Manager work?

In the software industry, there are lots of package managers present like maven, nugget and currently npm. A package manager is a software which finds whatever package you required, installs that package on your local machine it also manages that package and if you want to uninstall it will remove that package from your system.

Right now there are lots of packages on the internet and if you want to go out and find that site install it on the machine is a little bit time-consuming task so package manager solves that problem you only need to fire command and it will find it and install it on your machine.

How-Node-Pacakage-Manager-Works
npm install jquery

In the above command, jquery is our package name.

In new angular, you will find different, different modules.

Angular CLI

Angular CLi is a command-line interface which will get everything you required to run angular. So how to install angular cli on machine use below command.

C:\Users\Sagar>npm install @angular/cli –g

Here g stands for install globally and you will get run angular anywhere from your machine.

angular cli install globally

Here current version of angular cli is 9.1.0

To learn angular cli commands visit this site https://cli.angular.io/ you will get all commands from here.

Now its time to create our application so we can use the below command to create the application.

D:\Target Dec-2019\angular 9.x>ng new customerapplication
angular ng new command

Go to command prompt and run below command

ng serve –o
angular 9 output window

Default port run application is 4200 and our application runs on URL localhost:4200/

TypeScript

It is Open-source language and it is Typed SuperSet of JavaScript. For more learning on typescript, you can visit below link:- https://sagarjaybhay.net/category/typescript/

The folder structure of CLI projects:

  1. Tslint.json:  In this linting is the process of running a program that will analyze code for potential errors. The linting process is done by tslint.json file
  2. Readme.md: this is a file where all commands are listed here you can delete these files.
  3. Package.json: it is a node configuration file where you can register your dependencies here and when you use npm install command it will install all packages mentioned in a package JSON file. This package.json file belongs to a node and it is the node configuration file.
  "name": "customerapplication",
  "version": "0.0.0",
  "scripts": 
    "ng": "ng",
    "start": "ng serve",
    "build": "ng build",
    "test": "ng test",
    "lint": "ng lint",
    "e2e": "ng e2e"
  ,
  "private": true,
  "dependencies": 
    "@angular/animations": "~9.1.0",
    "@angular/common": "~9.1.0",
    "@angular/compiler": "~9.1.0",
    "@angular/core": "~9.1.0",
    "@angular/forms": "~9.1.0",
    "@angular/platform-browser": "~9.1.0",
    "@angular/platform-browser-dynamic": "~9.1.0",
    "@angular/router": "~9.1.0",
    "rxjs": "~6.5.4",
    "tslib": "^1.10.0",
    "zone.js": "~0.10.2"
  ,
  "devDependencies": 
    "@angular-devkit/build-angular": "~0.901.0",
    "@angular/cli": "~9.1.0",
    "@angular/compiler-cli": "~9.1.0",
    "@angular/language-service": "~9.1.0",
    "@types/node": "^12.11.1",
    "@types/jasmine": "~3.5.0",
    "@types/jasminewd2": "~2.0.3",
    "codelyzer": "^5.1.2",
    "jasmine-core": "~3.5.0",
    "jasmine-spec-reporter": "~4.2.1",
    "karma": "~4.4.1",
    "karma-chrome-launcher": "~3.1.0",
    "karma-coverage-istanbul-reporter": "~2.1.0",
    "karma-jasmine": "~3.0.1",
    "karma-jasmine-html-reporter": "^1.4.2",
    "protractor": "~5.4.3",
    "ts-node": "~8.3.0",
    "tslint": "~6.1.0",
    "typescript": "~3.8.3"
  

Above is the package.json file if you see the above file it has dependencies and dev dependencies section. These dev dependencies are the things that are not going in production and it will use only the development phase.

  1. Angular.json: this is a file used for the angular purpose
  2. Tsconfig.json:- these are 2 files present inside the root which are tsconfig.json and other is tsconfig.app.json file. The second file tsconfig.app.json override the root config file
  3. Polyfills.ts: this is not directly coming from angular and it is third party lib. It helps you to run a new ES version of Javascripts on an old browser which does not support new versions of JavaScipt.


GitHub Project Link: – https://github.com/Sagar-Jaybhay/angular9


What is the purpose of Html helpers in MVC?

In this article we will understand What is the purpose of Html helpers in MVC? and How to generate DropDownList using Html helpers in Asp.net MVC by Sagar Jaybhay.

Html helpers

What is an Html Helper?

Html helper is a method that is used to render HTML content in a view. Html helpers are implemented using an extension method.

If you want to create an input text box with id=email and name is email

<input type=text id=email name=email value=’’/>

This is all Html we need to write by using the helper method it becomes so easy

@Html.TextBox(‘email’)

It will generate textbox control whose name is email.

If we want to assign the value of the textbox with some initial value then use below method.

@Html.TextBox(‘email’,’sagar@gmail.com’)

If I want to set an initial style for textbox we can achieve this by using below way.

@Html.TextBox(‘email’,’sagar@gmail.com’,new style=’your style here’ , title=’your title here’);

Here style we pass is an anonymous type.

If we have reserved keyword like class readonly like that and we want to use this as an attribute how we will do this is doing below way means append with @ symbol with the reserved word.

@Html.TextBox(‘email’,’sagar@gmail.com’,new @class=’class name’, @readonly=true);

If we want to generate label

@Html.Label(‘firstname’,’sagar’)

For password use below Html helper method to create password box

@Html.Password(“password”)

If I want to generate textarea then for this also we have a method

@Html.TextArea(“comments”,”,4,12,null)

In above code 4 is the number of rows and 12 is the number of columns

To generate a hidden box

@Html.Hidden(“EmpID”)

Hidden textboxes are not displayed on the web page but used for storing data and when we need to pass data to action method then we can use that.

Is it possible to create our Html helpers in asp.net MVC?

No, we can use plain Html for that but Html helpers reduce a significant amount of Html code to write that view.

Also, your code is simple and maintainable and if you required some complicated logic to generate view then this is also possible.

How to generate DropDownList from database values in Asp.Net MVC?

DropDown List Using Html Helpers In MVC

To achieve the above functionality we need to Create one controller in our project so we created the DropDownListDemo controller in our application. By creating this Index method is created by default. In our Business class we GetDepartments method present so we call this method from our controller.

public ActionResult Index()
        
            
            ViewBag.Departments =new SelectList(this.business.GetDepartments(), "DepartmentID", "DepartmentName");

            return View();
        

See the above method in this we Create SelectList object and assign this ViewBag property. Focus on name of ViewBag property and what name given in our Html code is same. So by convention, it’s bind this property value directly to our DropDownList.

Below is code for Full Controller which we Created

public class DropDownListDemoController : Controller
    
        private BusinessLogic.Business business = null;
        public DropDownListDemoController()
        
            this.business = new BusinessLogic.Business();

        
        // GET: DropDownListDemo
        public ActionResult Index()
        
            
            ViewBag.Departments =new SelectList(this.business.GetDepartments(), "DepartmentID", "DepartmentName");

            return View();
        
    

Below is code for Index.cshtml file

@
    ViewBag.Title = "Index";




<h2>Department List From Database</h2>


@Html.DropDownList("Departments","Select Department")

Now in Business Class, we have GetDepartments method already present which is shown below

public List<Department> GetDepartments()
        
            List<Department> departments = new List<Department>();
            string query = "select * from Department order by DepartmentID";
            var data = this.dataAccess.GetTable(query);
            if(data!=null&&data.Rows.Count>0)
            

                foreach(DataRow dataRow in data.Rows)
                
                    var dept = new Department()
                    
                        DepartmentID = Convert.ToInt32(dataRow["DepartmentID"].ToString()),
                        DepartmentName = dataRow["DepartmentName"].ToString()
                    ;
                    departments.Add(dept);
                

            
            return departments;

        

If you want  to see data from our Database table which is shown below image

DropDownList from DataBase fields

To summarize all if we want to generate DropDownList we have Html.DropDownList Html helper method. In this DropDownList in MVC is a collection of SelectListItem objects.

Now we can Create DropDownList then how we set Item selected in that DropDownList when all options are loaded. If you see above code no department is loaded by default it only shows Select Department but we want Engineering Department loaded by default how we create this.

DropDown List Default Selected

To create this first we need to alter our department table and add one column isSelected or not. To alter our table use below code

alter table [Department]
  add isSelected bit

Default Selection Of DropDown List

After this, we need to add the isSelected field in our department model so our department class will change like this

public class Department
    
        public int DepartmentID  get; set; 
        public string DepartmentName  get; set; 

        public bool? isSelected  get; set; 
    

After this, we need to Set the Engineering Department by default selected so we set the value to 1 in the Engineering department for that below query is used.

  update [Department] set isSelected=1 where DepartmentID=2

Now we need to add method in our business class from which we will retrieve all the values like DepartmentID, DepartmentName, isSelected column.

public List<Department> GetDepartment()
        
            List<Department> departments = new List<Department>();
            string query = "select * from Department order by DepartmentID";
            var data = this.dataAccess.GetTable(query);
            if (data != null && data.Rows.Count > 0)
            

                foreach (DataRow dataRow in data.Rows)
                
                    var dept = new Department()
                    
                        DepartmentID = Convert.ToInt32(dataRow["DepartmentID"].ToString()),
                        DepartmentName = dataRow["DepartmentName"].ToString(),
                        isSelected = (dataRow["isSelected"] !=DBNull.Value ? Convert.ToBoolean(dataRow["isSelected"]) : false)

                    ;
                    departments.Add(dept);
                

            
            return departments;

        

Below is the method in our controller where we created a SelectListItem list and add data from our database table. In below we use bool to cast because we added Nullable field in DepartmentModel

public ActionResult DefaultSelectedDrop()
        
            List<SelectListItem> selectLists = new List<SelectListItem>();
            foreach(var items in this.business.GetDepartment())
            
                var p = new SelectListItem()
                
                    Text=items.DepartmentName,
                    Value=items.DepartmentID.ToString(),
                    Selected=(bool)items.isSelected
                        
                ;
                selectLists.Add(p);
            

            ViewBag.Departments = selectLists;
            return View();
        

And our view code is shown below

@
    ViewBag.Title = "DefaultSelectedDrop";


<h2>Default Selected DropDownList</h2>


@Html.DropDownList("Departments", "Select Department")

GitHub :- https://github.com/Sagar-Jaybhay/MVC5