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();           ...

How To Get IP Address And MAC Address Using c# , Aps.net MVC


How To Get IP Address And MAC Address Using c# , Aps.net MVC


public ActionResult Index()
{
    var IP = GetUser_IP();

            ViewBag.Ip = IP;

            ViewBag.Mac=GetMacAddress(IP);

            return View();

}



[NonAction]

        protected string GetUser_IP()

        {

   string VisitorsIPAddr = string.Empty;

    if (HttpContext.Request.ServerVariables["HTTP_X_FORWARDED_FOR"] != null)

            {

        VisitorsIPAddr =HttpContext.Request.ServerVariables["HTTP_X_FORWARDED_FOR"].ToString();

            }

            else if (HttpContext.Request.UserHostAddress.Length != 0)

            {

                VisitorsIPAddr = HttpContext.Request.UserHostAddress;

            }

           return  VisitorsIPAddr;

        }



     

   [NonAction]

        public string GetMacAddress(string ipAddress)

        {

            NetworkInterface[] nics = NetworkInterface.GetAllNetworkInterfaces();

            String sMacAddress = string.Empty;

            foreach (NetworkInterface adapter in nics)

            {

                if (sMacAddress == String.Empty)// only return MAC Address from first card  

                {

                    IPInterfaceProperties properties = adapter.GetIPProperties();

                    sMacAddress = adapter.GetPhysicalAddress().ToString();

                }

            }

            return sMacAddress;

        }

how to post formdata or http.context.form data from angular 6 to asp.net mvc web api or api using Token based authentication with header

how to post formdata or http.context.form data from angular 6 to asp.net mvc web api or api using Token based authentication with header 


_____________________________________________________________________
Create form data :-

const formdata = new FormData();
    formdata.append('postJson', JSON.stringify(postJson));
_____________________________________________________________________
Attach Form data To Form-data object and call contract service to post data :-

this.PostService.Postdata(formdata).then((res: any) => {
      this.loggerService.info(res.status);
      this.responseStatus = res.status;
      this.responseDraftId = res.result.alfResponse.draftId;
      if (res.status === 200) {
        this.responseFilePath = res.result ? (res.result.FullFilePath ? res.result.FullFilePath : '') : '';
        this.showPopup('success', 'Success!', res.result.Reference + ' File has been created successfully.');
      } else {
        this.showPopup('error', 'Error!', 'Error while submit Contract!');
      }
    }).catch(error => {
      this.loggerService.error(error);
      this.showPopup('error', 'Error!', 'Error while submit Contract!');
    });
  }
______________________________________________________________________________
create Service "Post Service" with Promises :-

PostService(data: any): Promise<any> {
        return new Promise((resolve, reject) => {
            this.apiService.postWithformHeader('Contract/GenerateContract', data).subscribe((res: any) => {
                return resolve(res);
            }, (err) => {


                reject(err);
            });
        });
    }
//note apiservice is class name where this method is write
_________________________________________________________________________________

method for form data header Attach with Token :-

postWithformHeader(URL, data) {
    this.spinner.displaySpinner(true);
    const optionsData: any;
if(localstorage.getkey('mytoken'))
{
optionsData = {
      headers: this.createAuthorizationHeader(),
    };
}
else{
this.router.navigate(['login']);
}
    const response = this.http.post(this.appUrl + URL, data,optionsData).map((res => {
      this.spinner.displaySpinner(false);
      return res;
    })).catch((error: any) => {
      this.spinner.displaySpinner(false);
      return this.httpErrorHandler.handleError('api-service', error, URL);
    });

    return response;
  }
  _________________________________________________________________________________
 
  Method for create header :- 

   createAuthorizationHeader() {
    
    let headers = new HttpHeaders();
      headers = headers.append('authorization', 'Bearer ' + localstorage.getkey('mytoken'));
   
     return headers;
  }
  ________________________________________________________________________________

How to Add "App_Data" asp.net MVC folder (directory) in publish file

How to Add "App_Data"  asp.net MVC folder (directory) in publish file 




open

"Projectpath"\Properties\PublishProfiles

select File 

"MyHealthRecordApi.pubxml" file

it has following code 
_________________________________________________
<?xml version="1.0" encoding="utf-8"?>
<!--
This file is used by the publish/package process of your Web project. You can customize the behavior of this process
by editing this MSBuild file. In order to learn more about this please visit https://go.microsoft.com/fwlink/?LinkID=208121.
-->
<Project ToolsVersion="4.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <WebPublishMethod>FileSystem</WebPublishMethod>
    <LastUsedBuildConfiguration>Release</LastUsedBuildConfiguration>
    <LastUsedPlatform>Any CPU</LastUsedPlatform>
    <SiteUrlToLaunchAfterPublish />
    <LaunchSiteAfterPublish>True</LaunchSiteAfterPublish>
    <ExcludeApp_Data>True</ExcludeApp_Data>
    <publishUrl>D:\WebSite_Publish\MyHealthRecrodAPI_Publish</publishUrl>
    <DeleteExistingFiles>True</DeleteExistingFiles>
    <PrecompileBeforePublish>True</PrecompileBeforePublish>
    <EnableUpdateable>True</EnableUpdateable>
    <DebugSymbols>False</DebugSymbols>
    <WDPMergeOption>DonotMerge</WDPMergeOption>
  </PropertyGroup>
</Project>

update code 

<?xml version="1.0" encoding="utf-8"?>
<!--
This file is used by the publish/package process of your Web project. You can customize the behavior of this process
by editing this MSBuild file. In order to learn more about this please visit https://go.microsoft.com/fwlink/?LinkID=208121.
-->
<Project ToolsVersion="4.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <WebPublishMethod>FileSystem</WebPublishMethod>
    <LastUsedBuildConfiguration>Release</LastUsedBuildConfiguration>
    <LastUsedPlatform>Any CPU</LastUsedPlatform>
    <SiteUrlToLaunchAfterPublish />
    <LaunchSiteAfterPublish>True</LaunchSiteAfterPublish>
    <ExcludeApp_Data>False</ExcludeApp_Data>
    <publishUrl>D:\WebSite_Publish\MyHealthRecrodAPI_Publish</publishUrl>
    <DeleteExistingFiles>True</DeleteExistingFiles>
    <PrecompileBeforePublish>True</PrecompileBeforePublish>
    <EnableUpdateable>True</EnableUpdateable>
    <DebugSymbols>False</DebugSymbols>
    <WDPMergeOption>DonotMerge</WDPMergeOption>
  </PropertyGroup>
</Project>

Featured Post

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 va...

Popular Posts