Sql dynamic query With Join And search using like query with multiple column

Sql dynamic query With Join And search using like query with multiple column



declare @keyword varchar(100)
DECLARE @sqlCommand varchar(1000);
SET @keyword='A'

SET @sqlCommand = 'select * from tbl_Employee as E left join tbl_Cities as C on E.CityID='+
' C.Id left join tbl_States as S on E.StateID='+'S.Id left join tbl_Countries as Co on E.CountryID=Co.Id';
set @sqlCommand=@sqlCommand+' where E.Name like ''%'+@keyword+'%'' or'+ 
' E.MiddleName like ''%'+@keyword+'%'' or'+
' E.LastName like ''%'+@keyword+'%'' or'+
' E.FatherName like ''%'+@keyword+'%'' or'+
' E.MiddleName like ''%'+@keyword+'%'' or'+
' E.MotherName like ''%'+@keyword+'%'' or'+
' E.EmailID like ''%'+@keyword+'%'' or'+
' E.AccountNo like ''%'+@keyword+'%'' or'+
' E.AdharCardNo like ''%'+@keyword+'%'' or'+
' E.PanCardNo like ''%'+@keyword+'%'' or'+
' E.Address like ''%'+@keyword+'%'' or'+
' E.PhoneNo like ''%'+@keyword+'%'' or'+
' E.Dob like ''%'+@keyword+'%'' or'+
' E.Gender like ''%'+@keyword+'%'' or'+
' Co.Name like ''%'+@keyword+'%'' or'+
' C.Name like ''%'+@keyword+'%''';

EXEC (@sqlCommand)

How To Generate PDF From Data / Create PDF in Angular 6

How To Generate PDF From Data / Create PDF in Angular 6

Firstly You Need To Install Two Package

1). jsPDF 
2).AutoTable

 1). For Package of jsPDF You Can Run Following Command
npm install jspdf --save

for Reference You Can User Following Links

2) .Now You Need To Install Second Package 

npm install jspdf jspdf-autotable --save

for Reference 

After Installing following Link then You Should Create Service where you can write actual logic 
For Creating PDF


Now I Am Creating AComponent & Service For auto Table ,Before Start We Have Demo Data for That service where we can use it 

1). This Part Is Write Where A Service Will consume (Use)

Means A Call Component Of Service


1) .Complate Method :-(Compnent.ts)

import { Component, OnInit } from '@angular/core';
import { ExcelService } from '../../../src/app/services/excel.service';

@Component({
  selector: 'app-grid-data',
  templateUrl: './grid-data.component.html',
  styleUrls: ['./grid-data.component.css']
})
export class GridDataComponent implements OnInit {
  data = {};
  dataitems: any = {};
  constructor(private excelService: ExcelService) { }

  ngOnInit() {

    this.dataitems.data = [
      [10, 'Demo Text1', 1, 'Finland', 7.632, 'Helsinki'],
      [20, 'Demo Text2', 2, 'Norway', 7.594, 'Oslo'],
      [30, 'Demo Text3', 3, 'Denmark', 7.555, 'Copenhagen'],
      [40, 'Demo Text4', 4, 'Iceland', 7.495, 'Reykjavík'],
      [50, 'Demo Text5', 5, 'Switzerland', 7.487, 'Bern'],
      [60, 'Demo Text6', 9, 'Sweden', 7.314, 'Stockholm'],
      [70, 'Demo Text7', 73, 'Belarus', 5.483, 'Minsk']
    ];
    this.dataitems.text = 'Hellow Demo Test PDF';
  }
  generateExcel() {
    this.excelService.generateExcel(this.data, 'DemoExcel');
  }
  generatePDF() {

    this.excelService.generatePDF(this.dataitems, 'DemoPDF');
  }

}


2). Now You Need To Create Service File 

import { Injectable } from '@angular/core';
import * as jspdf from 'jspdf';
import 'jspdf-autotable';
@Injectable({
  providedIn: 'root'
})
export class ExcelService {
  workbook: ExcelJS.Workbook;
  worksheet: any;
  constructor() { }


  generatePDF(data, FileName) {

    const header = [
      [
        {
          content: 'Text', colSpan: 2, styles: {
            fontSize: 8,
            lineWidth: 0.25,
            overflow: 'linebreak',
            halign: 'center'
          }
        },
        {
          content: 'ID', rowSpan: 2, styles: {
            fontSize: 8,
            lineWidth: 0.25,
            overflow: 'linebreak',
            halign: 'center'
          }
        },
        {
          content: 'Country', rowSpan: 2, styles: {
            fontSize: 8,
            lineWidth: 0.25,
            overflow: 'linebreak',
            halign: 'center'
          }
        },
        {
          content: 'Rank', rowSpan: 2, styles: {
            fontSize: 8,
            lineWidth: 0.25,
            overflow: 'linebreak',
            halign: 'center'
          }
        },
        {
          content: 'Capital', rowSpan: 2, styles: {
            fontSize: 8,
            lineWidth: 0.25,
            overflow: 'linebreak',
            halign: 'center'
          }
        },
      ],
      [{
        content: 'Text ID', styles: {
          fontSize: 8,
          lineWidth: 0.25,
          overflow: 'linebreak',
          halign: 'center'
        }
      },
      {
        content: 'Text Name', styles: {
          fontSize: 8,
          lineWidth: 0.25,
          overflow: 'linebreak',
          halign: 'center'
        }
      }]
    ];

    const pdf = new jspdf(); // 'landscape'
    const xOffset = (pdf.internal.pageSize.width / 2) - (pdf.getStringUnitWidth(data.text) * pdf.internal.getFontSize() / 2);
    const yOffset = (pdf.internal.pageSize.height / 2) - (pdf.getStringUnitWidth(data.text) * pdf.internal.getFontSize() / 2);
    let LastY = 0;
    pdf.setFont('helvetica');
    /*
    {
        "courier": ["normal", "bold", "italic", "bolditalic"],
        "Courier": ["", "Bold", "Oblique", "BoldOblique"],
        "times": ["normal", "bold", "italic", "bolditalic"],
        "Times": ["Roman", "Bold", "Italic", "BoldItalic"],
        "helvetica": ["normal", "bold", "italic", "bolditalic"],
        "Helvetica": ["", "Bold", "", ""]
    }
    */
    pdf.setFontType('bolditalic');
    pdf.setFontSize(20);
    pdf.text(data.text, yOffset, xOffset);
    for (let i = 0; i <= 20; i++) {

      pdf.autoTable({
        theme: 'grid',
        head: header,
        body: data.data,
        styles: {
          lineWidth: 0,
        },
        margin: { top: i === 0 ? 31 : 10 },
        columnStyles: { 0: { halign: 'center', fillColor: [0, 255, 0] } }, // Cells in first column centered and green
        didDrawCell: data.data
      });

    }
    for (let i = 0; i <= 20; i++) {

      pdf.autoTable({
        theme: 'grid',
        head: header,
        body: data.data,
        styles: {
          lineWidth: 0,
        },
        margin: { top: 10 },
        columnStyles: { 0: { halign: 'center', fillColor: [0, 255, 0] } }, // Cells in first column centered and green
        didDrawCell: data.data
      });

    }

    pdf.autoTable({
      theme: 'grid',
      head: header,
      body: data.data,
      margin: { top: 10 },
      styles: {
        lineWidth: 0,
      },
      columnStyles: { 0: { halign: 'center', fillColor: [0, 255, 0] } }, // Cells in first column centered and green
      didDrawCell: data.data
    });
    pdf.save(FileName + '.pdf');
  }

}



Here 
" import * as jspdf from 'jspdf';
import 'jspdf-autotable'; "

Both Are Declare Part Of API 









Asp.net And HTML Css And Web Development: How To Call Any Api From Sql (sql Sript)

Asp.net And HTML Css And Web Development: How To Call Any Api From Sql (sql Sript): How To Call Any Api From Sql (sql Sript) Declare @Object as Int; Declare @ResponseText as Varchar(8000); --Code Snippet Exec sp_...

How To Call Any Api From Sql (sql Sript)

How To Call Any Api From Sql (sql Sript)




Declare @Object as Int;
Declare @ResponseText as Varchar(8000);
--Code Snippet
Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'get',
'http://www.yoururl.com', --Your Web Service Url (invoked)
'false'
Exec sp_OAMethod @Object, 'send'
Exec sp_OAMethod @Object, 'resp

Asp.net And HTML Css And Web Development: How To Create Encryption and Description of url an...

Asp.net And HTML Css And Web Development: How To Create Encryption and Description of url an...: How To Create Encryption and Description of url and Data using angular 6 import { Injectable } from '@angular/core'; import...

How To Create Encryption and Description of url and Data using angular 6


How To Create Encryption and Description of url and Data using angular 6


import { Injectable } from '@angular/core';
import * as CryptoJS from 'crypto-js';

@Injectable({
    providedIn: 'root'
})

export class EncrDecrService {
    saltKeys: string = 'MyMD5';
    constructor() { }

    // The set method is use for encrypt the value.
    set(value) {
        const key = CryptoJS.enc.Utf8.parse(this.saltKeys);
        const iv = CryptoJS.enc.Utf8.parse(this.saltKeys);
        const encrypted = CryptoJS.AES.encrypt(CryptoJS.enc.Utf8.parse(value.toString()), key,
            {
                keySize: 128 / 8,
                iv: iv,
                mode: CryptoJS.mode.CBC,
                padding: CryptoJS.pad.Pkcs7
            });

        return encrypted.toString();
    }

    // The get method is use for decrypt the value.
    get(value) {
        const key = CryptoJS.enc.Utf8.parse(this.saltKeys);
        const iv = CryptoJS.enc.Utf8.parse(this.saltKeys);
        const decrypted = CryptoJS.AES.decrypt(value, key, {
            keySize: 128 / 8,
            iv: iv,
            mode: CryptoJS.mode.CBC,
            padding: CryptoJS.pad.Pkcs7
        });

        return decrypted.toString(CryptoJS.enc.Utf8);
    }
}

Asp.net And HTML Css And Web Development: How To Get IP Address And MAC Address Using c# , A...

Asp.net And HTML Css And Web Development: How To Get IP Address And MAC Address Using c# , A...: How To Get IP Address And MAC Address Using c# , Aps.net MVC public ActionResult Index() {     var IP = GetUser_IP();           ...

Featured Post

how to find n number of nodes have child or cildren

 how to find n number of nodes have child or cildren for that we use recursive function  const   data = {     'id' : '0' ,...

Popular Posts