Immortal Coder: 2017

Sunday, October 8, 2017

Sample program to sort an array of object by custom predefined sort order using JavaScript

Assume we have an object and we want to sort it based on custom predefined sort order value. We can utilize the JavaScript sort method to achieve this.

var items = [
    { firstname: 'Lazslo', lastname: 'Jamf',  accountType: 'Gold'},
    { firstname: 'Barack', lastname: 'Obamo',  accountType: 'Platinum' },
    { firstname: 'Pig',    lastname: 'Bodine',  accountType: 'Silver'},
    { firstname: 'Pirate', lastname: 'Prentice',  accountType: 'Platinum' }
];

Here, items is an object we need to sort by it's property 'accountType' and the sortOrder array is the predefined order it should follow. The order will be, first Platinum then Gold and then Silver.

Solution:

items.sort(function(a, b) {
    return sortOrder.indexOf(a.accountType) - sortOrder.indexOf(b.accountType);
});

So the final result will be,

var items = [
    { firstname: 'Barack', lastname: 'Obamo',  accountType: 'Platinum' },
    { firstname: 'Pirate', lastname: 'Prentice',  accountType: 'Platinum' }
    { firstname: 'Lazslo', lastname: 'Jamf',  accountType: 'Gold'},
    { firstname: 'Pig',    lastname: 'Bodine',  accountType: 'Silver'}
];

Sample program to sort an array of objects by its property value using JavaScript

Assume we have an object and we want to sort it based on it's property. We can utilize the JavaScript sort method to achieve this. The property value can be string or integer.

var items = [
    { firstname: 'Lazslo', lastname: 'Jamf',  accountType: 'Gold', age: 25},
    { firstname: 'Barack', lastname: 'Obamo',  accountType: 'Platinum', age: 45 },
    { firstname: 'Pig',    lastname: 'Bodine',  accountType: 'Silver', age: 36},
    { firstname: 'Pirate', lastname: 'Prentice',  accountType: 'Platinum', age: 29 }
];

Using the below function it it will be sorted by the 'accountType' string property.

items.sort(function(a, b) {
    var x = a.accountType.toLowerCase(), y = b.accountType.toLowerCase();
    return x < y ? -1 : x > y ? 1 : 0;
});

If we want to sort by the integer property 'age' means use the below method,

items.sort(function(a, b) {
     return a.age - b.age;
});

Monday, May 22, 2017

Sample SQL Server function to Strip HTML tags from a HTML string

If you want to remove the HTML tags from a HTML string and retrieve only plain text, the below SQL Server function can be used. It is just removing all the HTML tags by identifying '<' and '>'.

SQL Server Function :

create function [dbo].[StripHTML] 
(
 @HTMLText varchar(max)
)
returns varchar(max) 
as begin
 declare @Start int
    declare @end int
    declare @Length int
    set @Start = charindex('<',@HTMLText)
    set @end = charindex('>',@HTMLText,charindex('<',@HTMLText))
    set @Length = (@end - @Start) + 1
    while @Start > 0 and @end > 0 and @Length > 0
    begin
        set @HTMLText = stuff(@HTMLText,@Start,@Length,'')
        set @Start = charindex('<',@HTMLText)
        set @end = charindex('>',@HTMLText,charindex('<',@HTMLText))
        set @Length = (@end - @Start) + 1
    end
    return ltrim(rtrim(@HTMLText))
end

Sample Input:

select dbo.StripHTML('
<!DOCTYPE html><html><body><h1>My First Heading. </h1><p>My first paragraph.</p></body></html>
')

Output:

My First Heading. My first paragraph.

Tuesday, April 11, 2017

Sample SQL Server Function to Truncate a string

Below is the sample SQL Server function to Truncate a string to the given number of characters.

If the string is smaller than the given limit, it is returned as-is; otherwise, it is truncated to 3 characters less than the given limit, and '...' is placed at its end..

SQL Server Function :

create function [dbo].[TruncateString]
(
    @Str varchar(max),
    @MaxLength int
)
returns varchar(8000)
as begin
    if @MaxLength is null or @MaxLength < 3 set @MaxLength = 3
    if @MaxLength > 8000 set @MaxLength = 8000

return case
    when datalength(@Str) <= @MaxLength then @Str
    else left(@Str, @MaxLength - 3) + '...'
    end
end

Sample SQL Server Function to Trim a string

Below is a sample SQL Server function to Trim a string. It removes leading & trailing whitespace characters from a string.

This function differs from rtrim() and ltrim() in that it removes tab, newline, and carriage return characters in addition to spaces. Like ltrim() and rtrim(), if you pass null in, you get null back.

SQL Server Function :

create function [dbo].[TrimString]
(
    @Str varchar(max)
)
returns varchar(max)
as begin
    declare @First int = 1,
    @Last int = datalength(@Str)

    while ascii(substring(@Str, @First, 1)) in (32, 9, 10, 13)
    set @First += 1

    if @First > @Last begin
    -- the string is all whitespace (or empty)
    return ''
 end

    while ascii(substring(@Str, @Last, 1)) in (32, 9, 10, 13)
    set @Last -= 1

    return substring(@Str, @First, @Last - @First + 1)
end

Monday, April 10, 2017

Sample C# program to generate a random string

You know well where it is required to generate a random string. It might be for a password generation or any other reason. Here is a sample C# program to generate a random string. You might required to pass a parameters length of the random string also a boolean param to say whether to include upper case characters.

/// 
/// Gets a random alphanumeric string of the specified length,
/// optionally using either all lowercase, or mixed lower and upper case.
/// 
/// Length of the string.
/// Whether to include uppercase characters.
public string GetRandomString(int length, bool includeUppercase)
{
    string chars = "abcdefghiklmnopqrstuvwxyz1234567890";
    if (includeUppercase) chars += "ABCDEFGHIJKLMNOPQRSTUVWXYZ";

    StringBuilder sb = new StringBuilder(length);
    for (int chNum = 0; chNum < length; ++chNum)
    {
        int rand = this.GetRandomInt(0, chars.Length);
        sb.Append(chars[rand]);
    }

    return sb.ToString();
}

/// 
/// Gets a random boolean, based on the passed percentage.
/// It will return true in truePercentage percent of calls.
/// 
/// The percentage of the time true should be returned.
public bool GetRandomBoolean(int truePercentage)
{
    int rand = this.GetRandomInt(0, 100);  // [0-100), can be zero, can't be 100
    return (rand < truePercentage);
}

Wednesday, February 15, 2017

Find which stored procedures taking more time to execute using SQL Server DMV Query

It is very important to optimize the SQL Server stored procedure query to improve the performance. Optimizing the query is not a one time task as it needs to be checked frequently based on the developers activity on database related changes. Adding one single column to an existing select query would leads to performance degradation. There are many ways to monitor and identify the query performance. One of which is DMV (Dynamic Management Views).

DMV is nothing but queries returning information about server state that is current at the time the query was run. The information which are returned are actually returned from the cache. Every time a query executes, its information will be stored in the cache.

Below is the DMV query to identify the stored procedure which uses most resources which taking more time to execute. It is also returning the Query Plan in the last column which is very useful to identify the query section and optimize it.

SELECT CASE WHEN database_id = 32767 then 'Resource' ELSE DB_NAME(database_id)END AS DBName
      ,OBJECT_SCHEMA_NAME(object_id,database_id) AS [SCHEMA_NAME]  
      ,OBJECT_NAME(object_id,database_id)AS [OBJECT_NAME]
      ,cached_time
      ,last_execution_time
      ,execution_count
      ,total_worker_time / execution_count AS AVG_CPU
      ,total_elapsed_time / execution_count AS AVG_ELAPSED
      ,total_logical_reads / execution_count AS AVG_LOGICAL_READS
      ,total_logical_writes / execution_count AS AVG_LOGICAL_WRITES
      ,total_physical_reads  / execution_count AS AVG_PHYSICAL_READS,
      queryplan.query_plan
FROM sys.dm_exec_procedure_stats querystatus
CROSS APPLY sys.dm_exec_query_plan(querystatus.plan_handle) queryplan
where DB_NAME(database_id) = 'DBNAME'
ORDER BY AVG_LOGICAL_READS DESC

You have to mention your database name in the where condition to return stored procedure information from that. As I said earlier, the last column shows the Query plan link. Clicking it would open the query plan of the corresponding stored procedure similar to the below diagram.