Friday, April 12, 2019

WPF: Style definition and use


WPF : Style Introduction :

Style are way to set common property of a control at one place to reuse it.
Style are similar to CSS classes in HTML/Web Applications.
We can declare Style at multiple places like :
Resource Section of Control or it's Parent.(Scope :Control it self and it's child can use this Style)
Resource Section of Window.(Scope =containing form only)
Inside of Resource Dictionary.(Scope = Application level).


Below are some common attributes of Style:

TargetType :if we are setting only TargetType then Style will be application to all controls mention as Target type in applicableScope.

x:Key : used to declare a key, if we declare a Key then style will not be applied on controls automatically, we need to set explicitly.

BasedOn : used to create a new Style by inheriting from an existing Style.

    


Note : Control level Properties have higher precedence than Style. i.e. if you have applied style which is setting background color of control and you also have set background color at Control level, Color set at control will be applicable and system will ignore color what you mentioned in Style.




Thursday, April 11, 2019

WPF : How to use POCO object in XAML file and use converter in WPF


I am trying to use CLR object (Person) in XAML file and also demonstrating use of converter.
This example is just to demonstrate how to use above mentioned functionality, it's doesn't perform any meaningful functionality.

I have imported namespace for Person and Converter classes and created resource in Window for both classes (Note : We can initialize classes via resource only if class has a default constructor).

Definition of person and converter classes are as follows :


Wednesday, April 10, 2019

SQL Server : Some common query optimization techniques

  • Try to fetch relevant data as per your needs from SQL Server, so server need to consume resources for unwanted data.

  • Always provide column names in SELECT clause and provide only required column list.

  • Try to avoid DISTINCT clause, one work around to eliminate distinct is to add a unique/primary key column in result, so you will be able to decide weather record is duplicate of not based on different value of primary/unique key.

  • Try to avoid cross joins.

  • if you are grouping data and applying having clause try to put criteria in where clause if possible, as group by and having will be applied on result of query so you can limit the no. of rows by applying filter criteria in where clause so you will get lesser record and system will be able to perform grouping operation fast.

  • use Like clause carefully and efficiently as it will be more resource intensive for server and produce more unwanted records in query result. for ex if you want to search city name start from  'MA' use LIKE 'MA%' instead of  LIKE '%MA%'.

  • Avoid Co-related sub queries and use Join instead, for ex :  

SELECT c.Name, c.City,(SELECT CompanyName FROM Company WHERE ID = c.CompanyID) AS CompanyName
FROM Customer c        

you can achieve same result by using JOIN which will be faster than this. 

  • If you want to check if a record exists, use EXISTS() instead of COUNT() because count will scan entire table while Exist will return when matching record found. for ex: IF( SELECT COUNT(NAME) FROM PERSON WHERE AGE>100) >0) do something...instead use IF(EXISTS(SELECT NAME FROM PERSON WHERE AGE>100)

Monday, April 8, 2019

SQL Server : Generating xml from Query

Below query will generate xml for employee records

SELECT [EMP_ID]
      ,[FIRST_NAME]
      ,[LAST_NAME]
      ,[DEPARTMENT_ID]
      ,[SALARY]
  FROM [ABCD].[dbo].[Employee]
FOR XML RAW('Employee'), ROOT('Employees'), ELEMENTS XSINIL;

SQL Server : Delete duplicate rows from table


Delete duplicate rows from table , we are considering duplicate rows based on Name and City column Values.

--Works well if Table Desn't have primary key

WITH TEMP AS (
SELECT ROW_NUMBER() OVER ( PARTITION BY NAME,CITY ORDER BY NAME,CITY) as RN,NAME,CITY 
FROM TAbleDuplicate
)  
DELETE FROM TEMP WHERE
RN>1

Another way to delete duplicate from table (if primary key is present in table):

DELETE FROM EMPLOYEE WHERE EMP_ID NOT IN (
SELECT Min(EMP_ID) FROM EMPLOYEE
GROUP BY FIRST_NAME,LAST_NAME,SALARY ) 

SQL Server : Some common and useful queries


Find all the employees and their department name

select emp_id,first_name,Last_Name,dept_id,dept_name
from employee e
inner join 
department d on e.department_id = d.DEPT_ID

we can also apply where clause to add filter for emp_id or first_name and find department and other details for specific employee.

Find all the employees who is not part of any department

select emp_id,first_name,Last_Name,dept_id,dept_name from employee e
left join department d on e.department_id = d.DEPT_ID

where e.department_id is null

Find no. of employees in each department and sum of their salary (i.e. total salary distributed for each department)

select count(emp_id) as empCount,sum(salary),department_id from employee

group by department_id

Find the no.of employee and department where salary distributed is greater than 100000

select count(emp_id) as empCount,sum(salary) as totalSalary, department_id from employee

group by department_id having sum(salary) >100000

ROW_NUMBER(), RANK(), DENSE_RANK()


SELECT  
--ROW_NUMBER() OVER (ORDER BY SALARY desc)AS RW_COUNTER,
 ROW_NUMBER() OVER ( PARTITION BY DEPARTMENT_ID ORDER BY SALARY desc)AS ROW_NUM,
EMP_ID,DEPARTMENT_ID,FIRST_NAME,SALARY 
FROM EMPLOYEE where salary is not null Order by DEPARTMENT_ID

SELECT  
--RANK() OVER (ORDER BY SALARY desc)AS RW_COUNTER,
RANK() OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY desc)AS ROW_NUM,
EMP_ID,FIRST_NAME,SALARY ,DEPARTMENT_ID
FROM EMPLOYEE  where salary is not null Order by DEPARTMENT_ID

SELECT  --DENSE_RANK() OVER (ORDER BY SALARY desc)AS RW_COUNTER,
DENSE_RANK() OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY desc)AS ROW_NUM,EMP_ID,FIRST_NAME,SALARY ,DEPARTMENT_ID
FROM EMPLOYEE where salary is not null and DEPARTMENT_ID IS NOT NULL
Order by DEPARTMENT_ID

Note: 
ROW_NUMBER will generate sequential row number based on given partition and order by columns.

RANK () will generate rank based on given partition and order by columns. for example :
if  you have given department_Id in partition and order by salary and lets consider that there are two employee who has same salary so for first employee rank would be 1 and second and third employee rank would be 2 and for fourth employee rank would be 4 as row count for 4th employee would be 4th.

DENSE_RANK() is same as rank but it will give rank 3rd for fourth employee instead of 4th.
i.e. rank assignment will not be depend on row number of particular result.
run above queries to see result.

Create Table :

CREATE TABLE [dbo].TEST (
    [Id] int IDENTITY(1,1) primary Key,
    [Name] nchar(50)  NOT NULL

);
creating table with primary key.

Primary key creation with clustered index :

ALTER TABLE [dbo].[Countries]
ADD CONSTRAINT [PK_Countries]
    PRIMARY KEY CLUSTERED ([CountryId] ASC);
GO

Renaming TABLE Column:

EXEC dbo.sp_rename @objname=N'[dbo].[Cities].[ref]', @newname=N're', @objtype=N'COLUMN'

Cursor Example :

ALTER PROCEDURE SP_TEST_DATETIME
 AS
 BEGIN

  DECLARE @WorkingStatrt DateTime
  DECLARE @WorkingEnd DateTime
  DECLARE @TempTime DateTime
  DECLARE @TotalTime datetime
  DECLARE @CaseKey int
  DECLARE @createTimeStamp datetime
  DECLARE @Action varchar(50)
  
DECLARE WorkingHorsCalculation CURSOR FOR
SELECT CASE_KEY,CREATE_TIMESTAMP,ACTION
FROM AUDIT_LOG_CASE 

-- Open cursor
OPEN WorkingHorsCalculation
-- Fetch data from cursor in to variable
FETCH NEXT FROM WorkingHorsCalculation
INTO @CaseKey,@createTimeStamp,@Action
WHILE @@FETCH_STATUS = 0
BEGIN
-- Do operation according to row value
if @Action='Working Case'
begin
PRINT @Action
SET @WorkingStatrt=@createTimeStamp;
PRINT @WorkingStatrt

end
-- Fetch the next cursor
FETCH NEXT FROM WorkingHorsCalculation
INTO @CaseKey,@createTimeStamp,@Action
END
-- Finally do not forget to close and deallocate the cursor
CLOSE WorkingHorsCalculation
DEALLOCATE WorkingHorsCalculation


END

--*******************************************************************----







Interview Questions and Answers on Exception handling in C#  ( Part-3 )

Question: Can we use try catch block inside of Catch block?
Answer: Yes.






C# Record type: Something to remember while using record types

  Record in c# provide a concise and expressive way to create immutable data types, record is a keyword in c#, we can use this keyword with ...