SQL STUFF Function and Get common grid data with pagination, filteration, sorting by sp & functions

=========================================================================

STUFF FUNCTION

=========================================================================

Select STUFF((SELECT ','+ name from sys.columns WHERE object_id = OBJECT_ID('dbo.customers') FOR XML PATH('')),1,1,'')

select STUFF((select ',' + FirstName From Customers FOR XML PATH('')),1,1,'')

========================================================================

 /****** Object:  UserDefinedFunction [dbo].[fn_GetColumnNameByTableName]    Script Date: 21-12-2020 12:08:57 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

--SELECT dbo.fn_GetColumnNameByTableName('Customers')

ALTER FUNCTION [dbo].[fn_GetColumnNameByTableName]

(@TableName nvarchar(MAX))

RETURNS @ReturnTable TABLE (

      TableJoinList NVARCHAR(MAX),

  ColumnList NVARCHAR(MAX)

)

AS

BEGIN

DECLARE @TableJoinList NVARCHAR(MAX) = '';

DECLARE @ColumnList NVARCHAR(MAX) = '';

IF (@TableName = 'Customers')

BEGIN

SELECT @TableJoinList = 'Customers CU  WITH(NOLOCK) LEFT JOIN AspNetUsers AU WITH(NOLOCK) ON AU.Id = CU.UserId',

   @ColumnList = 'CustomerId,CustomerTypeId,UserId,FullName,AU.Email'

END

ELSE IF (@TableName = 'AspNetUsers')

BEGIN

SELECT @TableJoinList = '', @ColumnList = 'Id,FirstName,MiddleName,LastName,UserName'

END

ELSE IF (@TableName = 'AspNetRoles')

BEGIN

SELECT @TableJoinList = '',

@ColumnList = 'Id,Name,ColorCode,UserTypeId,PartnerAccountId'

END

INSERT INTO @ReturnTable (TableJoinList,ColumnList) VALUES (@TableJoinList,@ColumnList)

RETURN

End

==================================================================================================================================================

USE RevampV2_Dev

GO

ALTER PROC GetListData

@TableName varchar(1000) = 'AspNetRoles'

,@WhereClause varchar(1000) = '1=1'

,@PageNumber INT = 3

,@NumberOfRecords INT = 10

,@OrderBy varchar(75) = 'ASC'

,@SortOrderColumn  varchar(75) = 'NAME'

AS

BEGIN

DECLARE @SqlCommand varchar(1000) = ''

DECLARE @ColumnList varchar(1000) = ''

DECLARE @TableJoinList varchar(1000) = ''

SELECT @TableJoinList = TableJoinList, @ColumnList = ColumnList FROM [dbo].[fn_GetColumnNameByTableName](@TableName)

SET @TableName = CASE WHEN ISNULL(@TableJoinList,'') <> '' THEN @TableJoinList ELSE @TableName END 

SET @SqlCommand = 'SELECT ' + @ColumnList + ' FROM '+ @TableName + ' WHERE ' + @WhereClause  + 

' ORDER BY '+CAST(@SortOrderColumn AS VARCHAR) + ' ' +  CAST(@OrderBy AS varchar) +

' OFFSET ' + CAST((@PageNumber - 1) * @NumberOfRecords AS varchar) +' ROWS

FETCH NEXT '+CAST(@NumberOfRecords AS varchar) +' ROWS ONLY'

PRINT @SqlCommand

EXEC (@SqlCommand)

END

Forms in Angular

There are 2 ways to create forms in Angular 

Template Driven Forms

1).Template Driven Forms are created  completely in HTML.
2).easy to build and understand.
3).great for creating simple forms
4).creating complex forms using template driven approach is not recommended
    as the HTML can get very complicated and messy
5).not easy to unit test template forms as the logic is in the HTML.


Reactive Forms (Also called Model Driven Forms)

1).allow us to build the form completely in code.
2).more flexible and has many benefits over template forms.
3).easy to add form input elements dynamically and adjust validation at run-time based on the decisions made in code.
4).easy to unit test as most of the logic and validation is in the component class.
5). only downside of reactive forms is that they require more code than template forms.

Note:- Two classes that we commonly use to create a form control tree is 
1).FormGroup and
2).FormControl.
which creates a form with a group of controls,

Note:- 
error :- Can't bind to 'formGroup' since it isn't a known property of 'form' 
solution:- formGroup and formControlName) are in ReactiveForms module, 
                 import { ReactiveFormsModule } from '@angular/forms'; your module.
 Reference From
=======================Example.ts================================

import { Component, OnInit } from '@angular/core';

// Import FormGroup and FormControl classes

import { FormGroup, FormControl } from '@angular/forms';

@Component({

  selector: 'app-create-employee',

  templateUrl: './create-employee.component.html',

  styleUrls: ['./create-employee.component.css']

})

export class CreateEmployeeComponent implements OnInit {

  // This FormGroup contains fullName and Email form controls

  employeeForm: FormGroup;

  constructor() { }

  // Initialise the FormGroup with the 2 FormControls we need.

  // ngOnInit ensures the FormGroup and it's form controls are

  // created when the component is initialised

  ngOnInit() {

    this.employeeForm = new FormGroup({

      fullName: new FormControl(),

      email: new FormControl()

    });

  }

  onSubmit(): void {

     console.log(this.employeeForm.value);

  }

}
//Both FormControl and FormGroup classes inherit from AbstractControl class.
==========================Example.html===============================  

<form class="form-horizontal" [formGroup]="employeeForm" (ngSubmit)="onSubmit()">

          <label class="col-sm-2 control-label" for="fullName">Full Name</label>

          <input id="fullName" type="text" class="form-control"formControlName="fullName">

          <label class="col-sm-2 control-label" for="email">Email</label>

          <input id="email" type="text" class="form-control" formControlName="email">

 </form>

                                                                                                            

2). FormBuilder   :- import { FormBuilder } from '@angular/forms';

"FormBuilder class provides syntactic sugar that shortens creating instances     of  FormControl, FormGroup, or FormArray." 

-FormBuilder class is provided as a service, so first let's import the service.

The FormBuilder service has three methods:  

• control() - Construct a new FormControl instance
• group() - Construct a new FormGroup instance
• array() - Construct a new FormArray instance

  =======================Example.ts using FormBuilder =========================

import { Component, OnInit } from '@angular/core';

// Import FormGroup and FormControl classes

import { FormGroup, FormControl } from '@angular/forms';

@Component({

  selector: 'app-create-employee',

  templateUrl: './create-employee.component.html',

  styleUrls: ['./create-employee.component.css']

})

export class CreateEmployeeComponent implements OnInit {

    employeeForm: FormGroup;

  constructor(private fb: FormBuilder) { 

     this.employeeForm = this.fb.group({

      fullName: [''],

      email: [''],

      skills: this.fb.group({

      skillName: [''],

      experienceInYears: [''],

      proficiency: ['beginner']

  }),

});

  onSubmit(): void {

     console.log(this.employeeForm.value);

  }

}                                 

Important Notes Related To Import , Export, Declarations, Providers, In @NgModule In Angular - 6

Important Notes Related To Import , Export, Declarations, Providers, In @NgModule In Angular - 6

Types Of Module
1). app.module
2). child.module (Under the app folder is known as child module)

Notes :-
1) app.module (Main module of our application)
. - If we want to use any child module throuout the app than we nee to import it in app.module
  - If we want any inbuilt moules (FormsModule,ReactiveFormsModule) or third party module(BS-  Datepicek module) we nee to import it into app.module
  -If we want to use any component of any child.module than we have to export it from that  child.module and import it in app.module
ex:- we are having a child account module with  RegisterComponent and want to render it on app.component.html
================account.module.ts==============================
import { NgModule } from '@angular/core';
import { LoginComponent } from './login/login.component';
import { RegisterComponent } from './register/register.component';
import { FormsModule, ReactiveFormsModule } from '@angular/forms';

const appRoutes: Routes = [
  { path: 'login', component: LoginComponent },
  { path: 'register', component: RegisterComponent },
];
const component = [LoginComponent, RegisterComponent];

@NgModule({
  imports: [
    CommonModule,
    FormsModule,
    ReactiveFormsModule,
    RouterModule.forRoot(appRoutes)
  ],
  providers: [UserService],
  declarations: [...component],
  //If you not export components using this  and try to render it in app.component.html than you will get error that
  // app-component is not part of module.
  exports: [...component],
})
export class AccountModule { }
================app.component.html==============================
<app-login></app-login>

<app-layout>
</app-layout>

<router-outlet></router-outlet>
=====================================================================
2).For to use FormsModule Component of app folder we need to import it in app.module.ts

3). Use the CommonModule to get access to the *ngIf, *ngFor...etc directives:

4).Angular compilation : -    More...

5).HttpClient  & HttpClientModule (Used id angular 4.3.x and above)
    Must import in app.module for to make api (service) call More ...

.Netcore Api + angular 6 + EFCore Project Part - 3

Open vs code
1).Open integrated terminal (Ctrl  + `)
2). CD D:\
3). ng new DntApp

Note :- This Will Create DntApp With Required Packages ,Libraries And Files

4).cd \src\app

==============================================================
=========================== Environment.ts=======================
==============================================================

export const environment = {
  production: false,
  //Set Api Url Over Here
  apiUrl: 'https://localhost:44343/api/'
};

==============================================================
=========================== Model.ts=======================
==============================================================
Add a folder in app folder and add a file Model.ts
export class Product {
Productid: number = 0;
Name: string = '';
Quantity: number = 0;
Price: number = 0;
}

==============================================================
======================5).Add AppRoutingModule===================
==============================================================

ng generate module app-routing --flat --module=app

import { NgModule } from '@angular/core';
import { RouterModule } from '@angular/router';
// ng generate module app-routing --flat --module=app
@NgModule({
exports: [ RouterModule ]
})
export class AppRoutingModule {}
==============================================================
=================6).Add Accout Module  To App Folder================
==============================================================
ng generate module account

import { NgModule } from '@angular/core';
import { CommonModule } from '@angular/common';
import { RegisterComponent } from './register/register.component';
import { RouterModule, Routes } from '@angular/router';
import { FormsModule } from '@angular/forms';

const routes: Routes = [
// path ='' => use to render the Default Component at page load.
{ path: '', component: RegisterComponent },
{ path: 'register', component: RegisterComponent }
];

@NgModule({
imports: [ CommonModule, FormsModule, RouterModule.forRoot(routes)],
declarations: [ RegisterComponent]
})

export class AccountModule { }

==============================================================
=========================7).register.component.ts===================
==============================================================

1).Create  register in account module.
CD: D:\DntApp\Src\App\Accout  ng g c register -s --spec false

import { Component, OnInit } from '@angular/core';
import { TestService } from 'src/app/Services/test.service';
import { Product } from 'src/app/Models/Model';

@Component({
selector: 'app-register',
templateUrl: './register.component.html',
styles: [],
providers: [TestService]
})

export class RegisterComponent implements OnInit {
data: any;
title = 'Simple CRUD';
public TableData: any;
public product = new Product;
public obj = new Product;
public divTableShow;
constructor(private testService: TestService) {
this.product = new Product();
this.divTableShow = true;
}

ngOnInit() {
this.fnGetData();
}

fnGetData() {
this.testService.getData().pipe().subscribe(data => {
this.TableData = data;
console.log(this.data);
});
}

fnAdd() {
this.product = new Product;
this.divTableShow = false;
}

fnClose() {
this.divTableShow = true;
}

fnSave() {
if (this.product.Productid === 0) {
this.testService.postData(this.product).pipe().subscribe(data => {
console.log(this.data);
this.fnGetData();
alert('Record inserted successfully.');
});
} else {
this.testService.putData(this.product).pipe().subscribe(data => {
console.log(this.data);
this.divTableShow = true;
this.fnGetData();
alert('Record updated successfully.');
});
}
this.divTableShow = true;
}

fnEdit(editObj: any) {
this.product.Productid = editObj.productid;
this.product.Name = editObj.name;
this.product.Quantity = editObj.quantity;
this.product.Price = editObj.price;
this.divTableShow = false;
}

fnDelete(id: number) {
if (confirm('Are you sure to delete?')) {
this.testService.deleteData(id).pipe().subscribe(data => {
console.log(this.data);
alert('Record deleted successfully.');
this.fnGetData();
});
}
}
}

==============================================================
=========================7).register.component.html===================
==============================================================
<div class="row" [hidden]="divTableShow">
  <div class="container">
    <input class="form-group input-sm" type="hidden" [(ngModel)]="product.Productid">
    <div class="form-group col-lg-9">
      <div class="col-lg-3"><label for="Name">Product Name</label></div>
      <div class="col-lg-6"><input id="Name" class="col-lg-9" type="text" [(ngModel)]="product.Name"></div>
    </div>
    <div class="form-group col-lg-9">
      <div class="col-lg-3"><label for="Qty">Qty</label></div>
      <div class="col-lg-6"><input id="Qty" class="col-lg-9" type="text" [(ngModel)]="product.Quantity"></div>
    </div>
    <div class="form-group col-lg-9">
      <div class="col-lg-3"><label for="Price">Price</label></div>
      <div class="col-lg-6"><input id="Price" class="col-lg-9" type="text" [(ngModel)]="product.Price"></div>
    </div>
    <div class="form-group col-lg-12">
      <button class="btn btn-danger" (click)="fnClose()">Cancel</button> &nbsp;&nbsp;&nbsp;
      <button class="btn btn-success" (click)="fnSave()">Save</button>
    </div>
  </div>
</div>



<div class="row">
  <div class="container">
    <div [hidden]="!divTableShow" class="container">
      <i class="btn btn-info fa fa-plus" (click)="fnAdd()"></i>
      <table class="table table hover">
        <thead>
          <tr>
            <th>Name</th>
            <th>Qty</th>
            <th>Price</th>
            <th>Action</th>
          </tr>
        </thead>
        <tbody>
          <tr *ngFor="let obj of TableData">
            <td>{{obj.name}}</td>
            <td>{{obj.quantity}}</td>
            <td>{{obj.price}}</td>
            <td>
              <i class="btn btn-info fa fa-pencil" (click)="fnEdit(obj)"></i> &nbsp;&nbsp;
              <i class="btn btn-danger fa fa-trash" (click)="fnDelete(obj.productid)"></i>
            </td>
          </tr>
        </tbody>
      </table>
    </div>
  </div>
</div>
==============================================================
============================7).TestService =======================
==============================================================

1).Add A service Folder to app
CD:  D:\DntApp\Src\App\Service ng generate service Test
import { Injectable } from '@angular/core';
import { HttpClient, HttpHeaders } from '@angular/common/http';
import { environment } from 'src/environments/environment';
import { Product } from '../Models/Model';

@Injectable({providedIn: 'root'})

export class TestService {
constructor(private httpClient: HttpClient) {
}

getData() {
return this.httpClient.get( environment.apiUrl + 'Values');
}

postData(obj: Product) {
return this.httpClient.post( environment.apiUrl + 'Values', obj);
}

putData(obj: Product) {
return this.httpClient.put( environment.apiUrl + 'Values/', obj);
}

deleteData(id: number) {
return this.httpClient.delete( environment.apiUrl + 'Values/?Productid=' + id );
}
}

==============================================================
============================App.Module ========================
//Notes :
//1).This FormsModule need to import In Respective Module.ts
//        For (2-way binding) using [(ngModel)] In Component.html
//        If are having child module in app folder than we have to
//        Import FormsModule sepratly in that module.
//2).For All Component of app folder we need to import it in app.module.ts
==============================================================

import { FormsModule } from '@angular/forms';
import { BrowserModule } from '@angular/platform-browser';
import { NgModule } from '@angular/core';
import { AppComponent } from './app.component';
import { TestService } from './Services/test.service';
import { HttpClientModule } from '@angular/common/http';
import { AppRoutingModule } from './app-routing.module';
import { AccountModule } from './account/account.module';

@NgModule({
declarations: [
AppComponent,
],

imports: [
AccountModule,
BrowserModule,
AppRoutingModule,
HttpClientModule,
FormsModule
],

providers: [TestService],
bootstrap: [AppComponent]
})

export class AppModule { }

===============================================================
============================App.Component.html===================
===============================================================
<router-outlet></router-outlet>
<a [routerLink]="['register']">Register</a>
===============================================================
=============================Index.html==========================
===============================================================
<!doctype html>
<html lang="en">
<head>
  <meta charset="utf-8">
  <title>DntApp</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>
  <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/css/bootstrap.min.css">
  <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css">
  <app-root></app-root>
</body>
</html>

==============================================================

Now All Is Done build the Project Fire
ng serve --o command in terminal

.Netcore Api + angular 6 + EFCore Project Part - 2

.Netcore Api + angular 6 + EFCore Project Part - 1

 Important Configuration Need to be Done In AppSettins.json File And Startup file
==============================================================
=============================Startup file=======================
==============================================================
namespace DntAppApi
{
    public class Startup
    {
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
        }

        public IConfiguration Configuration { get; }

        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {
            services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_1);

//Need To Add For Configuration Of  Connection String With DbContext
            services.AddDbContext<MPContext>(options =>
            options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));

//Need To Add Service And implementation To Use In Our Application
            services.AddScoped<IProduct_Repository, Product_Repository>();
     
  // Need To Add For Cross Origin Resourse Sharing Calls
   services.AddCors(
                options => {
                    options.AddPolicy(
                   "MAYUR", builder =>                                             builder.AllowAnyOrigin().AllowAnyMethod().AllowAnyHeader().AllowCredentials());
             });
        }

        // This method gets called by the runtime. Use this method to configure the HTTP request                    //pipeline.
        public void Configure(IApplicationBuilder app, IHostingEnvironment env)
        {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
            }
            else
            {
                app.UseHsts();
            }
            // Need To Add For Cross Origin Resourse Sharing Calls
            app.UseCors("MAYUR");
            app.UseHttpsRedirection();
            app.UseMvc();
        }
    }
}
==============================================================
==========================AppSettins.Json file==================
==============================================================
{
 //Add ConnectionStrings Like this To Use It In Application
  "ConnectionStrings": {
    "DefaultConnection": "data source=DESKTOP-4VG8BV2\\SQLEXPRESS; initial catalog=MP;Integrated Security=true;"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Warning"
    }
  },
  "AllowedHosts": "*"
}
==============================================================
=====================ValuesController==========================
==============================================================

namespace DntAppApi.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class ValuesController : ControllerBase
    {
        // GET api/values
        IProduct_Repository _IProduct_Repository = new Product_Repository(new MPContext());

        [HttpGet]
        public IQueryable<TblProduct> Get()
        {
            return _IProduct_Repository.GetAll();
        }

        [HttpPost]
        public TblProduct Post(TblProduct obj)
        {
            _IProduct_Repository.Save(obj);
            return obj;
        }
        [HttpPut]
        public TblProduct Put(TblProduct obj)
        {
            _IProduct_Repository.Save(obj);
            return obj;
        }
        [HttpDelete]
        public int Delete(int Productid)
        {
            _IProduct_Repository.Remove(Productid);
            return 1;
        }
    }
}

.Netcore Api + angular 6 + EFCore Project Part - 3

.Netcore Api + angular 6 + EFCore Project Part - 1

Create .Net core api project using simple repository pattern Setup

1).  Create .Net Core Project  Name It DntAppApi
   
i). Select .Net core web application
ii).select api project

2).   Craete .Net core class library project Name it (DntAppApi.Core)
         i).  Add Microsoft.EntityFrameworkCore package From Nuget Package Manager
 ii).  Add Microsoft.EntityFrameworkCore.SqlServer package From Nuget Package Manager

3).   Craete Another .Net core class library project Name it (DntAppApi.Infrastructure)
        i).  Add Microsoft.EntityFrameworkCore package From Nuget Package Manager

ii). Open Tools > Nuget package Manager > Package Manager Console.

iii). In Package Manager Console Select  DntAppApi.Infrastructure Project's From Default                        Project Dropdown Selection

       iv). Fire Command :-
      Scaffold-DbContext "data source=DESKTOP-4VG8BV2\SQLEXPRESS; initial catalog=MP;Integrated Security=true" Microsoft.EntityFrameworkCore.SqlServer -OutputDir      Entities  -F

  Note* This will create entity folder containing all table of database as class.

4). Build  DntAppApi.Core Project And Add Reference of this Project To  DntAppApi.Infrastructure       Project

5). Now Build  DntAppApi.Infrastructure Project
      - Add Reference of  DntAppApi.Core And  DntAppApi.Infrastructure To  DntAppApi(Main)                Project

Now add two folders in DntAppApi.Infrastructure Project

1) Abstraction (Service) :- Contains All Interfaces

2).Implementation (Servicecontracts) :-  Contains All Class Which Implements Respective Interfaces.

Add an Interfaces in  Abstraction folder
==============================================================
=====================IProduct_Repository=======================
==============================================================
namespace DntAppApi
{
    public interface IProduct_Repository : IDisposable
    {
        IQueryable<TblProduct> GetAll();
        void Save(TblProduct obj);
        void Remove(int Productid);
    }
}

Add an Class in  Abstraction folder
==============================================================
=====================Product_Repository========================
==============================================================
namespace DntAppApi
{
    public class Product_Repository : IProduct_Repository
    {
        private MPContext context;
        public Product_Repository(MPContext _context)
        {
            context = _context;
        }

        public IQueryable<TblProduct> GetAll()
        {
            return context.TblProduct.AsQueryable();
        }
        public void Save(TblProduct obj)
        {
            if (obj.Productid == 0)
            {
                context.TblProduct.Add(obj);
                context.SaveChanges();
            }
            else
            {
                context.Entry<TblProduct>(obj).State = EntityState.Modified;
                context.SaveChanges();
            }
           
        }
        public void Remove(int Productid)
        {
            var obj = context.TblProduct.Find(Productid);
            context.Entry<TblProduct>(obj).State = EntityState.Deleted;
            context.SaveChanges();
        }
        // Flag: Has Dispose already been called?
        bool disposed = false;

        // Public implementation of Dispose pattern callable by consumers.
        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        // Protected implementation of Dispose pattern.
        protected virtual void Dispose(bool disposing)
        {
            if (disposed)
                return;

            if (disposing)
            {
                context.Dispose();
            }

            // Free any unmanaged objects here.
            disposed = true;
        }
    }
}
Note :- Wherever you get compiler error put cursour over there press (Ctrl + dot) and add
Required Namespace

Till Now Project Setup Is Ready....

.Netcore Api + angular 6 + EFCore Project Part - 2

Basics of angular routing

Basics of angular routing

Cli command :- ng generate module app-routing --flat --module=app

 Notes*

 1).
 imports: [ RouterModule.forRoot(routes) ],
 The method is called forRoot() because you configure the router at the application's root level.
 The forRoot() method supplies the service providers and directives needed for routing,
 and performs the initial navigation based on the current browser URL.


 2). The <router-outlet> tells the router where to display routed views.
        The RouterOutlet is one of the router directives that became available to the AppComponent because AppModule imports AppRoutingModule which exported RouterModule.

3). <a routerLink="/heroes">Heroes</a>
       Here routerLink is the selector for the RouterLink directive that turns user clicks into router navigations. It's another of the public directives in the RouterModule.
 

4). { path: '', redirectTo: '/dashboard', pathMatch: 'full' },