Run Store Procedure multiple Times With Different parameter

To run Run Store Procedure multiple Times With Different parameter we have To pass dynamic parameter with store procedure

As the parameter  the value will also change .

Lets see eg.

 Create Procedure SP_UpdateRecord --10,2
  (
  @numericvalue1 int,
  @numericvalue2 int
  )
  As
  Begin

  select (@numericvalue1-@numericvalue2) as SubtractionResult,
  (@numericvalue1+@numericvalue2) as AdditionResult,
  (@numericvalue1*@numericvalue2) as  MultiplicationResult,
  (@numericvalue1/@numericvalue2) as  DivisonResult

   end

As the value Change In parameter the result will also Change Accordingly.Here we can use any dynamic value to fetch the result in the addition ,subtraction ,multiplication and division form.

Add, Subtract,multiply,Divide in Store Procedure Sql Server

To Add,Subtract,Divide and Multiply We will use Following operator

Add (+) ,Subtract( -), Multiply (*), Divide (/)

Lets See how we can use it in Store Procedure

 Create Procedure SP_UpdateRecord --10,2
  (
  @numericvalue1 int,
  @numericvalue2 int
  )
  As
  Begin

  select (@numericvalue1-@numericvalue2) as SubtractionResult,
  (@numericvalue1+@numericvalue2) as AdditionResult,
  (@numericvalue1*@numericvalue2) as  MultiplicationResult,
  (@numericvalue1/@numericvalue2) as  DivisonResult

   end

Here We are Using two dynamic value in @numericvalue1 =10 and @numericvalue2=2
that will give us following Record

 SubtractionResult  AdditionResult MultiplicationResult      DivisonResult
           8                         12                             20                          5

Update Two table with Single Store Procedure

To update Two or more Table We can use Single Store Procedure.It can be use as Same as we use a query .the thing only we need to do is to use in Store Procedure ,

Here we are updating two table at a time using Store Procedure.

Lets see how.In the normal update Query in Sql Server Looks like this

  
update Department set DepartmentName='IT ' where DepartmentID=5

  update Designation set DesignationName='Admin' where   DesignationID=3

To use this in Store Procedure we will convert this query in Store procedure Syntax

 Create Procedure  SP_UpdateRecord 

 As
 Begin

 update Department set DepartmentName='IT' where DepartmentID=5
 update Designation set DesignationName='Admin' where DesignationID=3
 end

It will Work as same the single update Query Works.We can also update the Table by passing parameter to it .
Lets See how

Create Procedure  SP_UpdateRecord 
( @DepartmentID int,
@DesignationID int,
@DepartmentName nvarchar(32),
@ DesignationName nvarchar(32))
 As
 Begin

 update Department set DepartmentName=@DepartmentName where DepartmentID=@DepartmentID
 update Designation set DesignationName=@DesignationNamewhere DesignationID=@DesignationID
 end

Store Data in Dynamic Array C#

         

To Store Multiple Array In a New Array using C# . We have to use ArrayList for it

Lets See How 

         

dynamic arrayList = new ArrayList();

 dynamic FirstArrayRecord = _context.ArrayClass.FromSqlRaw("[dbo].[Sp_getCalenderAttendanceJan] @EmployeeID=" + EmployeeID + ",@year=" + year + " ").AsNoTracking().ToList();


            dynamic SecondArrayRecord = _context.ArrayClass.FromSqlRaw("[dbo].[Sp_getCalenderAttendanceFeb] @EmployeeID=" + EmployeeID + ",@year=" + year + " ").AsNoTracking().ToList();

Above We Have Two Records  i.e FirstArrayRecord  and  SecondArrayRecord  we have to add this record in a new arraylist.

            arrayList.Add(FirstArrayRecord );
           arrayList.Add(SecondArrayRecord );

         return arrayList 

In the Last we will return arrayList .

Convert Row data in column using Sql Server

What is pivot ?

Pivot  is   Relational   operator in SQL server  which is used to convert data from Row to Column view. To convert data from Column to Row we will use Unpivot Operator .Unpivot is opposite to Pivot

So lets See how we will use It.We have Data like this in Table

     
    EmployeeID EmployeeName Country State

         14 John D India Karnataka

         15 John R India Bihar

         16 John G India Maharastra

         17 John P India UP

We will use Pivot For State

   select [Karnataka],[Bihar],[Maharastra],[UP]  from(

   select EmployeeName,state from Tablename
   )t
  pivot( max(EmployeeName)  for state in ( [Karnataka],[Bihar],[Maharastra],[UP])  )st

This will convert Row data in Sates to Column with value EmployeeName
So the Result Will be Like

   
    Karnataka   Bihar   Maharastra    UP

    John D          John R    John G           John P

Get month name from week number in SQL Server

To Get Month name from Week Number we have to execute following query

declare @Year char(4), @Week INT, @month int
select @Year = '2020', @Week = 30
SET @month = month(dateadd(wk,@Week,@Year + '/01/01'))
SELECT DATENAME(month, DATEADD(month, @month, -1 ))as Nameofmonth

Here Week =30 and Year is 2020  

Result:-
Nameofmonth
July

Pass NULL value to stored procedure in C#

        

We can Pass null or String as parameter to store procedure

public dynamic GetEmpdata(int EmployeeID)
        {
            dynamic EmployeeIDs = EmployeeID;

            string Department=null;

            var Getdata = _context.Employee.FromSqlRaw("dbo.SP_GetEmpdata    @p0 ,@p1 ",      parameters: new[] { EmployeeIDs , Department}).ToList();
            return Getdata ;
        }

We will take string department parameter as null .it will work as empty String and this will return value if store procedure work fine with null data.

String or binary data would be truncated in SQL

Sometimes I faced this issue like string or binary data would be truncated  in SQL database.

The query that is executed by me was..

 

 drop table if exists #Empattendanceday
 create table #Empattendanceday
  (
 Today datetime,

 DayName nvarchar(8)
 )

 insert into #Empattendanceday
 SELECT GETDATE(), 'Wednesday'

The reason Behind this error is the length of datatype(nvarchar) is dayname is 8 character and we are inserting data more than 8 char i.e 9

That's why it gives us  this error

String or binary data would be truncated.
The statement has been terminated.


So try to keep datatype character more than inserting value or DayName nvarchar(max).

Convert DateTime to a specified Format in C#

To Convert Datetime to  a specific format in C# we need to use ParseExtract.

According To Microsoft

ParseExact(String, String, IFormatProvider) Converts the specified string representation of a date and time to its DateTime equivalent using the specified format and culture-specific format information. The format of the string representation must match the specified format exactly.

Lets see How to use

     

var EventDates = Request.Form["EventDate"];


compEvent.EventDate = DateTime.ParseExact(EventDates, "dd/MM/yyyy", CultureInfo.InvariantCulture ).ToString("MM/dd/yyyy");
           
           

This will convert the Desired DateFormat.

How to convert date in Desired format in angular

To convert date in desired format  like dd-MM-yyyy or  yyyy-MM-dd or other we will use datepipe

Datepipe will provide  date  values in different formats like milliseconds,date object,datetime string etc

Lets see how to use

In component.ts

 import { DatePipe } from '@angular/common';

 constructor( public datepipe: DatePipe) {}

  myDate = new Date();

Gettodaydate = this.datepipe.transform(this.myDate, 'yyyy-MM-dd');

console.log(this.Gettodaydate )

In the above part we have to import datepipe from angular/common and need to define it in constructor and after it we can use easy according to our need.

There are various predefined format

Date Format	Datepipe	Value
M/d/yy, h:mm a	{{todayDate | date:'short'}}	5/19/20, 11:55 PM
MMM d, y, h:mm:ss a	{{todayDate | date:'medium'}}	May 19, 2020, 11:55:20 PM
MMMM d, y, h:mm:ss a z	{{todayDate | date:'long'}}	May 19, 2020 at 11:55:20 PM GMT+5
EEEE, MMMM d, y, h:mm:ss a zzzz	{{todayDate | date:'full'}}	Tuesday, May19, 2020 at 11:55:20 PM GMT+05:30
M/d/yy	{{todayDate | date:'shortDate'}}	5/19/20
MMM d, y	{{todayDate | date:'mediumDate'}}	May 19, 2020
MMMM d, y	{{todayDate | date:'longDate'}}	May 19, 2020
EEEE, MMMM d, y	{{todayDate | date:'fullDate'}}	Tuesday, May 19, 2020
h:mm a	{{todayDate | date:'shortTime'}}	11:55 PM
h:mm:ss a	{{todayDate | date:'mediumTime'}}	11:55:20 PM
h:mm:ss a z	{{todayDate | date:'longTime'}}	11:55:20 PM GMT+5
h:mm:ss a zzzz	{{todayDate | date:'fullTime'}}	11:55:20 PM GMT+05:30

Add Dynamic class in Angular 8

We can add dynamic class in Angular using NgClass .It is simple to use in HTML side lets see how

We have three class in the css with different colour

  .pending{

Color:orange;

  }

  .approved{

color:green;

  }

  .disapproved{

color:red;

  }

Above three  class  pending approved and disapproved will be used dynamically in NgClass with conditions

<div [ngClass]="{
                  'pending': element.Status =='Pending',
                  'approved': element.Status =='Approved',
                  'disapproved': element.Status =='Disapproved'
           
                  }">{{element.Status}}</div>

According to the Status the will be dynamically applied.

How to select single column using Linq C#

To select Specific Column using Linq in C# we have write following query base on data.

var Resume = _context.Resume.Where(e => e.EmployeeID ==
               EmployeeID && e.UploadDate == _context.Resume.Max(e => e.UploadDate)).FirstOrDefault();

On the basis of where condition we will get the specific record.

How to find years of experience in SQL

                   

To Find Year of experience in SQL we need to use DATEDIFF

Lets see how

 select (DATEDIFF(Year, DateOfJoining,getdate()))  from   Employee where EmployeeID=@EmployeeID

To get data in months

 select (DATEDIFF(month, DateOfJoining,getdate()))  from   Employee where EmployeeID=@EmployeeID

How to get Day or Weekday name from date in Sql Server

How to get Day or Weekday name from date in Sql Server

To Get Day or weekday from date we need to run below query

    SELECT GETDATE() 'Today', DATENAME(weekday,GETDATE()) ' DayName '

The Result will be like;

       Today                                  DayName 
       2020-05-14 13:14:23.503 Thursday

How to create Store Procedure using Code First Entity Framework

                  How to create Store Procedure using Code First Entity Framework

To add Store procedure  using code first Entity Framework .First we have add migration --any
 from Package manager console.

Steps

   
   1. Select to Tools in visual studio

   2.In tools select NuGet Pacakage Manager

   3. In NuGet Pacakage Manager select select Package mange console.

Add a new Migration and in this migration add

 

   using System;
  using Microsoft.EntityFrameworkCore.Migrations;

   namespace LMSApiSol.Migrations
{
    public partial class Data : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.Sql(@"create Proc [dbo].[Sp_Getall Employeeinfo]    

  as      
  Begin      
  select * from Employee
end
  ");
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
       
        }
    }
   }

Now we will run Command update-Database in Package  manager console;
Store procedure will be added successfully

How to Send parameters in Store Procedure using Code First Entity framework

               How to Send  parameters  in Store Procedure using Code First Entity framework

There are different ways to send parameter Store procedure using  in Code first  Entity frame work.


1.When you have only one parameter

   public dynamic Getmonths (int EmployeeID)
        {
      var GetMonth = _context.Sp_GetAllMonths.FromSqlRaw("[dbo].  [Sp_Getallmonth]      @EmployeeID=" + EmployeeID + "").ToList();
           
            return GetMonth;
        }

  2 .When you have two parameter (this is the best way to send  parameter in the store procedure)

     public dynamic GetSlips(string SalaryMonth, int EmployeeID)
        {
            dynamic EmployeeIDs = EmployeeID;
            string SalaryMonths = SalaryMonth.ToString();

            var Getdata = _context.SP_SalarySlip.FromSqlRaw("dbo.SP_SalarySlipbymonths     @p0 ,@p1 ",      parameters: new[] { EmployeeIDs , SalaryMonths }).ToList();
            return Getdata ;
        }

How To Print Custom Design PDF in Angular 8

To Print Custom Design PDF  in Angular. We need to install Plugin Like

   npm install pdfmake --save


  Then Import these two library in Component.ts

 
   import pdfMake from 'pdfmake/build/pdfmake';

   import pdfFonts from 'pdfmake/build/vfs_fonts';

   pdfMake.vfs = pdfFonts.pdfMake.vfs;

Now On HTML page We will add a button to generate PDF.

<button type="button" (click)="generatepdf()" >Click Me!</button>

Now on Component.ts

   generatepdf() {
    let Todaydate = this.datepipe.transform(this.Payslip.Todaydate, 'MMMM d, y');
    const documentDefinition = {
      template: [
        {
          text: 'Salary Slip',
          bold: true,
          fontSize: 20,
          alignment: 'center',
          margin: [0, 0, 0, 20]
        },
        {
          text: 'PaySlip For the Month '
        },
        {
          columns: [
            [{
              text:'Company Address' + this.Payslip.CompanyAddress
            },
              {
              text: 'EmployeeName : ' + this.Payslip.EmployeeName
              }
            ],
          ]
        }]
    };

     pdfMake.createPdf(documentDefinition).open();

  }

Now you can also add any dynamic data on button click!!

How to print HTML Design In Angular 8

How to print HTML Design In Angular8

It is easily to print HTML Design without any Plugin and Extra Code.

Lets See How?

Eg. HTML

    <div id="divid">Whatever want to print...</div>

    <button(click)="printComponent('divid')" title="Print" />

   Eg. Component.ts

    printComponent(compo) {

     let printContents = document.getElementById(cmpName).innerHTML;

    let originalContents = document.body.innerHTML;

    document.body.innerHTML = printContents;

    window.print();

    document.body.innerHTML = originalContents;

    }

This Will Reflect To Print Option From Where You Can Print Entire Html with Design.

How To Upload File Using Form data

        Upload File Using Form data In Angular 8

How to upload Files through Form data. Below is the my page HTML Code Looks like.

Here we will upload files on change .(change)="uploadFile(file.files)" > Now we will move on ts Component .

The above code is written in ts component  we have taken   const formData = new FormData(); Now we will append the file to this form data.Here we have selected only one files  at a time.Lets Move on Service of this Component.

In this Service we have used GlobalUrl and Post Method for the Api i.e in C#.
Now we will move on Api .To check how file uploads in Database.

In the Api we will make folder for Image Storage.

How To Bind table in Angular

                 

To Bind data in Datatable very Easy in Angular.Lets See How my HTML Code Look Like.

HTML

 
 " <table class="table table-sm" style="margin-top: 35px;">

              <thead>

                <tr>

                  <th scope="col">Name</th>

                  <th scope="col">Relationship</th>

                  <th scope="col">Date of Birth</th>

                  <th scope="col">Phone</th>

                </tr>

              </thead>

              <tbody>

                <tr  *ngFor="let element of Familyinfodata"  >

                  <td>{{element.Name}}</td>

                  <td>{{element.Relationship}}</td>

                  <td>{{element.DatofBirth|date:"dd-MM-yy"}}</td>

                  <td>{{element.Phonenumber}}</td>


                </tr>
              </tbody> "

We bind the data in <tr> using *ngFor to iterate the array data in Table.Here Data come in an array of "Familyinfodata" . Lets See ts Component for More information.

TS 

   
    Familyinfodata:any=[];

  Getfamilyinfo(EmployeeID) {

    this.services.Getfamilyinformation(EmployeeID)

      .subscribe(data => { this.Familyinfodata= data;  });

   }

we store the data in any array  and used this array in <tr> of the table.

Service

     
       Getfamilyinformation(EmployeeID) {

      return       this.http.get(`${Global.Url}/employee/GetEmpFamilyInfo/${EmployeeID}`).pipe(catchError(this.ErrorResponce));


  }

How to Bind DropDown List in Angular 8

Before start How to Bind dropdown in angular 8  using material form.I think  you’ve already set up a basic Angular project to work with select dropdown,

Now we are going to create a material form  drop down.

Below is the code of My HTML

 In this form data come in a array (months) from Ts component.in mat option *ngFor is us to iterate the array and the value shows in Dropdown. We will get the selected value  from mat select over selection change .The selection change function called on Ts component which get value when the change event fires.

Lets see How we fetch data in array on ts component and bind it to mat-option

GetSalarymonths function is called on page load.Now its time to get value on select change.

(selectionChange)="SalaryslipByMonth($event.value)"

We will call this (SalaryslipByMonth) function on ts component.Lets see how it Looks Like.

 Now we will check value in console.

Validation in Angular 8

                           Angular 8 -Validation in angular 8

  How to set Validation in Angular 8 using form control in simple input Fields. Like Name ,Relationship ,Contact number etc.In Contact number  maximum input is  10 digits and with the format of Number you want. Below is the form that looks like.

In this form there are three input fields .All these fields are require if any of the field not filled by user the same  message will show in the form .

Below is my Code in HTML

 <div class="right-side"> 
<input matInput placeholder="" maxlength="30 [formControl]="PrimaryNameFormControl" [readonly]="emergencyEdit" [(ngModel)]="EmployeeModel.PrimaryName">
                  <mat-error *ngIf="PrimaryNameFormControl.invalid">{{PrimaryNameRequiredErrorMessage()}}</mat-error>

                  <input matInput placeholder="" maxlength="30" [formControl]="PrimaryRelationshipFormControl" [readonly]="emergencyEdit" [(ngModel)]="EmployeeModel.PrimaryRelationship">
                  <mat-error *ngIf="PrimaryRelationshipFormControl.invalid">{{PrimaryRelationshipRequiredErrorMessage()}}</mat-error>

                  <input matInput placeholder="" maxlength="10" [formControl]="PrimaryContactFormControl" [readonly]="emergencyEdit" [(ngModel)]="EmployeeModel.PrimaryContact">
                  <mat-error *ngIf="PrimaryContactFormControl.invalid">{{PrimaryContactRequiredErrorMessage()}}</mat-error>


                 </div>

In this form Angular material is used for the error if input field is invalid.Now its Time to check the  function on page load. before that we will import 
"import { FormControl, Validators, FormGroup } from '@angular/forms';" on ts  Component.



My  Form.component.ts looks Like

  PrimaryNameFormControl = new FormControl('', [
    Validators.required,
  ]);
  PrimaryRelationshipFormControl = new FormControl('', [
    Validators.required,
  ]);
  PrimaryContactFormControl = new FormControl('', [
    Validators.required,
    Validators.required, Validators.pattern("[7-9]{1}[0-9]{9}")
  ]);

  PrimaryNameRequiredErrorMessage() {
    return this.PrimaryNameFormControl.hasError('required') ? 'You must enter a value' : '';
  }

  PrimaryRelationshipRequiredErrorMessage() {
    return this.PrimaryRelationshipFormControl.hasError('required') ? 'You must enter a value' : '';
 }
 PrimaryContactRequiredErrorMessage() {
    return this.PrimaryContactFormControl.hasError('required') ? 'You must enter a value' :
      this.PrimaryContactFormControl.hasError('pattern') ? 'Not Valid  Number' : '';

  }

  On the submit button you can also validate like Below.

 EmployeEmergencycontact ;

  Submit () {

      this.EmployeEmergencycontact = new EmployeeEmergrncyContact (
      this.EmployeeModel.PrimaryName,
      this.EmployeeModel.PrimaryRelationship,
      this.EmployeeModel.PrimaryContact,
    if (this.EmployeeModel.PrimaryName == "") {

      alert("Invalid PrimaryName ")

    } else if (this.EmployeeModel.PrimaryRelationship == "") {

      alert("Invalid PrimaryRelationship ")
    }

    else if (this.EmployeeModel.PrimaryContact == ""   ||  this.PrimaryContactFormControl.hasError('pattern') ? 'Not Valid  Number' : '') {

      alert("Invalid PrimaryContact ")
    }

 else {
       this.services.UpdateEmployeeEmergencyContact(this.EmployeEmergencycontact).subscribe(data   => {

        alert("Added Successfully");

      }, error => console.log(error)

      )
    }
  }