Screen Record

 Windows Screen Record

WindowsKey+ Alt + R

Recording Starts.

Check Database Session Queries

 SQL SERVER: Check Database Queries which are taking time to execute. 

--https://blog.sqlauthority.com/2021/09/20/sql-server-troubleshooting-high-cpu/

SELECT TOP 50 s.session_id,

           r.status,

           r.cpu_time,

           r.logical_reads,

           r.reads,

           r.writes,

           r.total_elapsed_time / (1000 * 60) 'Elaps M',

           SUBSTRING(st.TEXT, (r.statement_start_offset / 2) + 1,

           ((CASE r.statement_end_offset

                WHEN -1 THEN DATALENGTH(st.TEXT)

                ELSE r.statement_end_offset

            END - r.statement_start_offset) / 2) + 1) AS statement_text,

           COALESCE(QUOTENAME(DB_NAME(st.dbid)) + N'.' + QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid)) 

           + N'.' + QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), '') AS command_text,

           r.command,

           s.login_name,

           s.host_name,

           s.program_name,

           s.last_request_end_time,

           s.login_time,

           r.open_transaction_count

FROM sys.dm_exec_sessions AS s

JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st

WHERE r.session_id != @@SPID

ORDER BY r.cpu_time DESC

Clean Code 𝗖𝗹𝗲𝗮𝗻 𝗖𝗼𝗱𝗲 𝗧𝗶𝗽s - 𝗢𝘃𝗲𝗿𝗰𝗼𝗺𝗶𝗻𝗴 𝗕𝗼𝗼𝗹𝗲𝗮𝗻 𝗕𝗹𝗶𝗻𝗱𝗻𝗲𝘀𝘀

 public void ProcessOrder(int orderId, bool applyDiscount)

{

    if(applyDiscount)

    {

            //Apply Discount to the order

    }

        // Process the order here

}

ProcessOrder(1000, applyDiscount: true);

Clean Code:

public enum DiscountOption

{

    None,

    ApplyDiscount

}

public void ProcessOrder( int orderId, DiscountOption discountOption)

{

    if(discountOption == DiscountOption.ApplyDiscount)

        {

                // Apply discount to the order

        }

// Process the order

}

ProcessOrder(1000, DiscountOption.ApplyDiscount);

Data-Table to List

 Auto increment in DataTable to List

public JsonResult GetProductCategoryList()

        {

            try

            {

                List<ProductCategoryViewModel> List_ProdCatViewModel = new List<ProductCategoryViewModel>();

                //var param = new { CompanyId = LoggedInCompId };

                //var CategoryList = productCategoryService.GetAll().Where(x => x.IsRemoved == false);

                var CategoryList = spQueryService.GetDataWithoutParameter("SP_Get_ProductCategoryListWithType");

                var count = 0;

                List_ProdCatViewModel = CategoryList.Tables[0].AsEnumerable()

                .Select(row => new ProductCategoryViewModel

                {

                    rowSl = (++count).ToString(),

                    ProdCatId = row.Field<int>("ProdCatId"),

                    ProdTypeName = row.Field<string>("ProdTypeName"),

                    ProdCatName = row.Field<string>("ProdCatName"),

                    ProdCatNameOther = row.Field<string>("ProdCatNameOther"),

                    DepriciateValue = row.Field<decimal?>("DepriciateValue"),

                }).ToList();

                return Json(List_ProdCatViewModel, JsonRequestBehavior.AllowGet);

            }

            catch (Exception ex)

            {

                return Json(new { Result = "ERROR", Message = ex.Message }, JsonRequestBehavior.AllowGet);

            }

        }

Remove First 3 character

 Declare @str nvarchar(23) = '04.99991.03.001.00101'

SELECT RIGHT(@str, LEN(@str)-3)

Result: 99991.03.001.00101

split string to TABLE

 

ALTER FUNCTION [pksf].[fn_SplitString]

(    

      @Input NVARCHAR(MAX),

      @Character CHAR(1)

)

RETURNS @Output TABLE (

      Item NVARCHAR(1000)

)

AS

BEGIN

      DECLARE @StartIndex INT, @EndIndex INT

 

      SET @StartIndex = 1

      IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character

      BEGIN

            SET @Input = @Input + @Character

      END

 

      WHILE CHARINDEX(@Character, @Input) > 0

      BEGIN

            SET @EndIndex = CHARINDEX(@Character, @Input)

           

            INSERT INTO @Output(Item)

            SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)

           

            SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))

      END

 

      RETURN

END

Salary Second Highest

Second Highest Salary Department Wise. Group by 

select * from

(

select EmpId, EmpName, Salary, Department,

ROW_NUMBER() Over (Partition by Department Order by Salary Desc) AS SalaryRank

from Emp

) dd

where SalaryRank = 2