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>

How To Design Certificate using Html css And Format for print landscape and portrait Mode page Design

How To Design Certificate using Html CSS And Format for print landscape and portrait Mode page Design

OUTPUT:-


HTML AND CSS CODE FOR DESIGN CIRTIFICATE



<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Cirtificate</title>
<style type="text/css">
    .Page{

    width:800px; height:600px;background-image: url('Background.png');background-size: 100% 100%;
}
.BorderDiv{
width:759px;margin-left: 1px; height:559px; padding:20px;background-image: url('Border.png');background-size: 100% 100%;
}
.Name{
top: -368px;position: relative;font-size: 26px;left: 21px;color: #1713eadb;width: 404px;
}
.Phone{
  top: -400px; width:686px;position: relative;font-size: 26px;color: #1713eadb;left: 307px;
}
.Email{
  top: -387px;width:400px;position: relative;font-size: 20px;left: 43px;color: #1713eadb;
}
.Header{
  width: 100%;
}
.Footer{
  width: 100%;position: relative;top: -285px;
}
.LogoDivyang{
  position: relative;width: 91px;top: 5px;left: 51px;
}
.Signature{
  position: relative;width: 91px;top: 12px;left: 229px;
}
.PramanText{
  width: 100%;width: 260px;position: relative;top: -78px;
}
.Sveeplogo{
  position: relative;width: 91px;top: 33px;left: 51px;
}

.Electionlogo{
  position: relative;width: 91px;top: 33px;left: 229px;
}
.Text{
  width: 100%; position: relative;    top: -163px; 
}
</style>
<style type="text/css" media="print">
   * {
    -webkit-print-color-adjust: exact !important;   /* Chrome, Safari */
    color-adjust: exact !important;                 /*Firefox*/
}


@page {

 size:A4 portrait;
    margin-left: 0px;
    margin-right: 0px;
    margin-top: 0px;
    margin-bottom: 0px;
    margin: 0;
    width: 100%;
    -webkit-print-color-adjust: exact;
}
@media print {
.Page{
width: 100%;
height: 600px;
}
.BorderDiv{
  width: 95%;
  height: 560px;
}

.Name{
  padding-left: 5px;
left: 21px;width: 49%;
margin-top: -8px;
}
.Phone{
  padding-left: 85px;
 left: 420px;width:42%;
margin-top: -2px;
}
.Email{
  padding-left: 0px;
left: 60px;
}
.Header{
  width: 100%;
}
.Footer{
  top: -315px;
}
.LogoDivyang{
  width: 98px;top: 20px;left: 65px;
}
.Signature{
  width: 98px;top: 12px;left: 240px;
}
.PramanText{
  position: relative;top: -78px;
}
.Sveeplogo{
  top: 33px;left: 51px;
}

.Electionlogo{
  top: 33px;left: 249px;
}
.Text{
   top: -163px; 
}


 .noprint{
display: none;
 }
 }
</style>
</head>

<body>
<div style="" class="Page">
<div style=" " class="BorderDiv">
<div style="" class="Header">
     <div style="float: left;width: 50%">
        <img src="sveeplogo.png" class="Sveeplogo" style="" />
     </div>
     <div style="float: left;width: 50%">
        <img src="electionlogo.png" class="Electionlogo" style=""/>
     </div>
 </div>      
 <div style="width: 100% ;">
        <img src="heading.png" style="width: 100%" />      
</div>
<div style="width: 100% ;text-align: center">
    <img src="PramanText.png" style="" class="PramanText" />
</div>

<div style="width: 100% ;text-align: center">
    <img src="Text.png" style=""  class="Text" />
<div class="Name"><span><?php echo $result1[0]['name']?></span></div>
<div class="Phone"><span ><?php echo $result1[0]['phone'];?></span></div>
<div class="Email"><span ><?php echo $result1[0]['email'];?></span></div>   
</div>
<div style="" class="Footer">
           <div style="float: left;width: 50%;">
            <img src="LogoDivyang.png" style="" class="LogoDivyang" />
           </div>
           <div style="float: left;width: 50%">
            <img src="Signature.png" style="" class="Signature" />
           </div>
</div>    
</div>
</div>
<input type="button" name="print" value="print" onclick="print()" class="noprint" />
</body>
</html>

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