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 Dynamic Upload multiple files with extra detail of model in asp.net core

 How To Dynamic Upload multiple files with extra detail of model in asp.net core  1). firstly create Model File  namespace MyModel {     pub...

Popular Posts