Configure fiddler to not capture requests for certain domains

When you are debugging using fiddler, fiddler generally captures requests from all the domains. An easy way to not capture requests from certain domains is to use the build in “filters” tab in fiddler. Use the “Hide the following hosts” dropdown and key in the list of all the domains that you want to hide.

If you want more granular control then you can override the OnBeforeRequest method in Fiddler’s script using Fiddler’s Script Editor.

static function OnBeforeRequest(oSession: Session) {        
        if (
        	oSession.HostnameIs("www.google.com") || 
        	oSession.HostnameIs("inbox.google.com") || 
        	oSession.HostnameIs("13.client-channel.google.com") || 
        	oSession.HostnameIs("d.docs.live.net") || 
        	oSession.HostnameIs("slack.com") || 
        	oSession.HostnameIs("notifications.google.com") || 
        	oSession.HostnameIs("play.google.com") || 
        	oSession.HostnameIs("cello.client-channel.google.com") || 
        	oSession.HostnameIs("lp-push-server-777.lastpass.com") || 
        	oSession.HostnameIs("ssl.gstatic.com") || 
        	oSession.HostnameIs("clients4.google.com") || 
        	oSession.HostnameIs("clients1.google.com")
        )
        {  
            oSession["ui-hide"] = "hidden";    
        }
        //code omitted for brevity
}
Continue Reading

Access Team Foundation Server Source Control from Windows Explorer

I lot of times when I search for a file in Agent Ransack and I want to check who made the last change in TFS source control, I have to switch to Visual studio and check the history for that file in TFS source control. An easier way to do this is to install Microsoft Visual Studio Team Foundation Server Power tools. Microsoft Visual Studio Team Foundation Server Power tools provides integration between windows explorer and TFS source control. Please see the screenshots below.

 

 

 

 

Continue Reading

Fiddler not capturing traffic from browsers

Sometimes when you are trying to capture browser traffic for debugging, Fiddler does not capture the same.
One of the options to try is to make sure that the “Use Filters” checkbox is unchecked in the “Filters” tab in Fiddler.

You can also troubleshoot filters to see which filters are hiding your traffic.

If this does not work then you might have an extension (Like ZenMate or TunnelBear) installed that might have messed with your traffic. You might need to disable those extensions to ensure that Fiddler captures all the traffic from the browser.

Continue Reading

Improve performance of T-sql statements containing encrypted columns.

We can use database level encryption to encrypt sensitive information. A commonly used level of encryption is to only encrypt columns that contain sensitive information like credit card numbers, date of birth etc. Encrypting and decrypting database level information is very CPU resource intensive. If a column is used as a primary key or used in comparison clauses (WHERE clauses, JOIN conditions) the database will have to decrypt the whole column to perform operations involving those columns.

We faced a similar performance issue in one of the previous companies. Database selects based on SSN were very slow as the database had to decrypt the whole column to perform comparison. If you look at the information below(under Current Problem) the database performed 4413 logical reads, 4406 read-ahead reads and took 302 milli seconds to perform the operation.

To improve the performance of these kinds of queries, we decided to store the last four digits of the SSN in clear text and index the column as a non-clustered index. First filtering the rows based on last four digits of the SSN and then filtering the rows based on SSN lead to a dramatic improvement in performance. f you look at the information below(under Improved Solution) the database performed 27 logical reads, 10 physical reads 10, 8 read-ahead reads and took 71 milli seconds to perform the operation.
All in all a huge performance improvement.

Current Problem:

begin
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
SET STATISTICS TIME ON;
SET STATISTICS IO ON;

declare @ssn varchar(max) = '123123123'
with PersonalIdentifierInformationCTE as
(
select CONVERT (CHAR(32), DECRYPTBYKEYAUTOCERT(CERT_ID('CertificateName'), NULL, Identifier)) as Identifier
from PersonalIdentifierInformation
)
select *
from PersonalIdentifierInformationCTE where Identifier is not null
and Identifier=@ssn
end
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
(8 row(s) affected)
Table 'PersonalIdentifierInformation'. Scan count 1, logical reads 4413, 
physical reads 3, read-ahead reads 4406, lob logical reads 0, 
lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 250 ms, elapsed time = 302 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

Improved Solution:

begin
declare @ssn varchar(max) = '123123123'

DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
SET STATISTICS TIME ON;
SET STATISTICS IO ON;

with PersonalIdentifierInformationCTE as 
(
select CONVERT (CHAR(32), DECRYPTBYKEYAUTOCERT(CERT_ID('CertificateName'), NULL, Identifier)) as Identifier, SSNLast4
from PersonalIdentifierInformation
)
select *
from PersonalIdentifierInformationCTE 
where SSNLast4<> 0 and SSNLast4 = substring(@ssn, LEN(@ssn)-4+1, LEN(@ssn)) 
and Identifier is not null
and  Identifier=@ssn
end
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 13 ms.
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 2 ms.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
 SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 16 ms.
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
    (8 row(s) affected)
Table 'PersonalIdentifierInformation'. Scan count 1, logical reads 27, 
physical reads 10, read-ahead reads 8, lob logical reads 0, 
lob physical reads 0, lob read-ahead reads 0.
    (1 row(s) affected)
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 71 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

Continue Reading

Only step into our files when debugging in Chrome Developer tools.

A lot of times when we are debugging our front end javascript code using Chrome Developer tools,

the system steps into other files. To avoid this, we can ask the debugger to ignore these files by “blackboxing” them. To do this, right click on the file and click “blackbox script”

To get to the blackboxed files, go to the “settings” section by clicking on the symbol as shown below or press F1.

The “blackboxing” tab shows all the files that have been blackboxed.

Continue Reading

Edit videos in Youtube

You can edit your videos very easily in youtube. To start log into your youtube account, select the video you want to edit then click the enhancements button.

2016-10-23-14_45_37-sta-school-renuion-2016-youtube

You can now rotate the videos either left or right. Sometimes the videos that you have shot(perhaps using your phone) are very shaky. In this case, youtube can also stabilize the videos for you. In this case, click the stabilize button and click the save button.

2016-10-23-14_47_01-youtube

Continue Reading

Document database using SchemaSpy and Graphviz

SchemaSpy is a Java-based tool (requires Java 5 or higher) that analyzes the metadata of a schema in a database and generates a visual representation of it in a browser-displayable format. It lets you click through the hierarchy of database tables via child and parent table relationships as represented by both HTML links and entity-relationship diagrams. See here for more information.
Graphviz is open source graph visualization software. Graph visualization is a way of representing structural information as diagrams of abstract graphs and networks. See here for more information.
jTDS is an open source JDBC driver for Microsoft SQL Server and Sybase databases. See here for more information

We will use SchemaSpy, Graphviz and JTDS to document a SQL server database.

Step 1: Download Graphviz from the following location
Step 2: Open command prompt in Administrator mode. Change to the directory where the Graphviz msi file has been downloaded and run the

msiexec /a graphviz-2.38.msi

command. As of 8/4/2016, the latest version of Graphviz is 2.38. Run through the various steps of the Graphviz installer.
Step 3: Add the Graphviz bin folder path to the “Path” “system variables” in “Environment variables”. The path of the bin folder on my machine was “C:\Program Files (x86)\Graphviz2.38\bin”.
2016-08-04_22-16-49

2016-08-04_22-21-50
Step 4: Download Schema Spy from the following location. As of 8/4/2016, the latest version is 5.0.0.
Step 5: Download and unzip the latest JTDS drivers for SQL server from the following location. As of 8/4/2016, the latest version is 1.3.1.
Step 6: For the database being documented, make sure that TCP\IP protocol is enabled and the TCP\IP port points to the port name in step no 7 below(i.e 1433 in this case). This can be configured using “SQL server configuration manager” for SQL server database.

2016-08-05_0-13-07

Step 7: Restart database server using the services snapin.

Step 8: Run the following command to generate the documentation for the database.

java -jar schemaSpy_5.0.0.jar -t mssql05-jtds -db SqlTuning -host localhost 
-port 1433 -u sa1 -p sa1 -o SqlTuningDocumentation 
-dp C:\Downloads\jtds-1.3.1-dist\jtds-1.3.1.jar -s dbo -loglevel fine
Continue Reading

FizzBuzz using C# yield

Yield.
Yield has two great uses,
a. It helps to provide custom iteration without creating temp collections.
b. It helps to do state-full iteration.

[TestClass]
public class FizzBuzzTest
{
    [TestMethod]
    public void Test1()
    {
        var iEnumerableString = new FizzBuzz().GenerateFizzBuzzUpto(100);
        var list = iEnumerableString.ToList();
        Assert.IsTrue(list[0] == "Fizz:3");
        Assert.IsTrue(list[1] == "Buzz:5");
        Assert.IsTrue(list[6] == "FizzBuzz:15");
    }
}
public class FizzBuzz
{
    public IEnumerable<string> GenerateFizzBuzzUpto(int numberUpto)
    {
        for (var counter = 1; counter < numberUpto; counter++)
        {
            if (counter%3 == 0 && counter%5 == 0)
            {
                yield return $"FizzBuzz:{counter}";
            }
            if (counter % 3 == 0)
            {
                yield return $"Fizz:{counter}";
            }
            if (counter % 5 == 0)
            {
                yield return $"Buzz:{counter}";
            }
        }
    }
}
Continue Reading

“Develop something useful in under 100 lines of code” contest-Improved version

My article here was well received. Various people pointing out several flaws in the application. Some of these points that were discussed were:

a. Should the application use decimal instead of double since it is a financial application?
Answer: The application has been changed to use decimals to calculate the monthly payment amount, Interest amount, total interest amount. However based on the article here, “the results when computed in doubles are going to be off by a few billionths of a penny one way or the other”. I therefore believe that I need to find a actual use case where using a decimal produced a result that was more correct than using a decimal.

b. The application will not behave correctly when the “loan Date” is passed with a “DateTime.MaxValue” value.
Answer: This has been fixed and a unit test case added for the same.

c. The application needs to consider business rules related to “loan date” and “Loan Period in years”
Answer: This has been fixed and two unit test cases added for the same.

d. What advantages will the system get if the return type of the Get function is a “IList” instead of a “List”?
Answer: This has been fixed in the application. The advantages that we get is that the AmortizationSchedule.Get and IAmortizationSchedule.Get signature does not need to change when the internals of the AmortizationSchedule.Get method is changed to return a collection that implements a IList (example return a Array instead of a List collection).

e. How will you optimize the creation of a the List class on line 30? Will it make sense to pass the created object from the client application if the creation of the list instance is expensive?
Answer: If the List creation is an expensive operation then we can consider using the Object pooling design pattern in the application.
The Object pool design pattern is a creational design pattern that can improve performance when working with classes that are slow to instantiate. Rather than constructing new objects, reusable objects are retrieved from, and released to, a pool as required.
An example implementation is listed here.

f. Will the application need to take care of multiple processes creating “Amortization Schedule”? If yes then how will you change the application?
Answer: It was decided that the application does not need to change to take care of multiple processes creating the “Amortization Schedule”.

g. Why was the “IAmortizationSchedule” interface created? It is not used anywhere in the application
Answer: This is true, the “IAmortizationSchedule” interface is not used in the application but I always create an interface because it helps later down the road when I need to write unit tests by mock the various parts of the application.

h. Is there a possibility that the “toThePowerOfVariable” variable on line 22 will become 1? What happens on line 23 if the “toThePowerOfVariable” variable becomes 1?
Answer: It was decided that there is no possibility of the “toThePowerOfVariable” variable on line 22 becoming 1. If the “toThePowerOfVariable” variable becomes 1, no “DivideByZeroException or other exception will occur.

Here is the changed application:

public class AmortizationSchedule: IAmortizationSchedule
{
    public IList<AmortizationScheduleRow> Get(double annualPercentageRate, 
        double loanAmount, int loanPeriodInYears, DateTime loanStartDate)
    {
        if (annualPercentageRate <= 0 || 
         loanAmount <= 0 || 
         loanPeriodInYears <= 0 || 
         loanStartDate <= DateTime.MinValue || 
         loanStartDate == DateTime.MaxValue)
        {
            throw new ArgumentException("Invalid arguments");
        }
        //If the code below does not throw an exception then 
        //the "loanStartDate" has been validated against the "loanPeriodInYears"
        DateTime addYears = loanStartDate.AddYears(loanPeriodInYears);
        
        #region Calculate monthly payment rounded to 2 digits
        const int NoOfMonthsInYear = 12;
        var loanPeriodInMonths = loanPeriodInYears * NoOfMonthsInYear;
        var effectiveInterestRateInDouble = annualPercentageRate / (NoOfMonthsInYear * 100);
        var toThePowerOfVariable = Math.Pow(1 + effectiveInterestRateInDouble, -loanPeriodInMonths);
        var monthlyPayment = (decimal) (loanAmount * (effectiveInterestRateInDouble / (1 - toThePowerOfVariable)));
        #endregion

        #region Calculate payment schedule based on monthly payment and loan start date
        //Initialize the collection(for performance) based on the 
        //no of elements that it is supposed to hold
        var amortizationSchedule = 
        new List<AmortizationScheduleRow>(loanPeriodInMonths);
        var currentMonthCounter = loanStartDate.AddMonths(1);
        var currentBalance = (decimal) loanAmount;
        decimal totalInterestAmount = 0;
        for (var counter = 0; counter < loanPeriodInMonths; counter++)
        {
            var interestAmount = (decimal) (effectiveInterestRateInDouble * (double) currentBalance);
            totalInterestAmount = (totalInterestAmount + interestAmount);
            var amortizationScheduleRow = new AmortizationScheduleRow
            {
                Date = currentMonthCounter,
                MonthlyPaymentAmount = monthlyPayment,
                InterestAmount = interestAmount,
                TotalInterestAmount = totalInterestAmount
            };
            amortizationSchedule.Add(amortizationScheduleRow);

            currentBalance = currentBalance - amortizationScheduleRow.GetPrincipalAmount();
            currentMonthCounter = currentMonthCounter.AddMonths(1);
        }
        #endregion
        return amortizationSchedule;
    }
}
public interface IAmortizationSchedule
{
    IList<AmortizationScheduleRow> Get(double annualPercentageRate, double loanAmount, 
        int loanPeriodInYears, DateTime loanStartDate);
}
public class AmortizationScheduleRow
{
    public decimal GetMonthlyPaymentAmount()
    {
        return Math.Round(MonthlyPaymentAmount, 2, MidpointRounding.AwayFromZero);
    }
    public decimal MonthlyPaymentAmount { private get; set; }
    public decimal GetInterestAmount()
    {
        return Math.Round(InterestAmount, 2, MidpointRounding.AwayFromZero);
    }
    public decimal InterestAmount { private get; set; }
    public decimal GetTotalInterestAmount()
    {
        return Math.Round(TotalInterestAmount, 2, MidpointRounding.AwayFromZero);
    }
    public decimal TotalInterestAmount { private get; set; }
    public decimal GetPrincipalAmount()
    {
        var principalAmount = MonthlyPaymentAmount - InterestAmount;
        return Math.Round(principalAmount, 2, MidpointRounding.AwayFromZero);
    }
    public string GetDate()
    {
        return Date.ToString("MMM yyyy");
    }
    public DateTime Date { private get; set; }
}
 [TestClass]
public class AmortizationScheduleTester
{
    const double annualPercentageRate = 3.5;
    const double loanAmount = 300000;
    const int loanPeriodInYears = 15;
    readonly DateTime loanStartDate = new DateTime(2016, 7, 1);
    [TestMethod]
    public void GetAmortizationScheduleExceptionTest()
    {
        const int NoOfMonthsInYear = 12;
        var monthlyPaymentSchedule = new AmortizationSchedule().Get(annualPercentageRate, loanAmount,
            loanPeriodInYears, loanStartDate);
        Assert.IsFalse(monthlyPaymentSchedule == null);
        Assert.IsFalse(monthlyPaymentSchedule.Count > 1);
        var secondMonthPaymentSchedule = monthlyPaymentSchedule[1];
        Assert.AreEqual(monthlyPaymentSchedule.Count, 15 * NoOfMonthsInYear);
        Assert.AreEqual(secondMonthPaymentSchedule.GetMonthlyPaymentAmount(), 
        (decimal) 2144.65);
        Assert.AreEqual(secondMonthPaymentSchedule.GetDate(), "Sep 2016");
        Assert.AreEqual(secondMonthPaymentSchedule.GetInterestAmount(), 
        (decimal) 871.30);
        Assert.AreEqual(secondMonthPaymentSchedule.GetTotalInterestAmount(), 
        (decimal) 1746.30);
        Assert.AreEqual(secondMonthPaymentSchedule.GetPrincipalAmount(), 
        (decimal) 1273.35);
    }
    [ExpectedException(typeof(ArgumentException))]
    [TestMethod]
    public void GetAmortizationScheduleExceptionTest1()
    {
        new AmortizationSchedule().Get(0, loanAmount,
            loanPeriodInYears, loanStartDate);
    }
    [ExpectedException(typeof(ArgumentException))]
    [TestMethod]
    public void GetAmortizationScheduleExceptionTest2()
    {
        new AmortizationSchedule().Get(annualPercentageRate, 0,
            loanPeriodInYears, loanStartDate);
    }
    [ExpectedException(typeof(ArgumentException))]
    [TestMethod]
    public void GetAmortizationScheduleExceptionTest3()
    {
        new AmortizationSchedule().Get(annualPercentageRate, loanAmount, 0, loanStartDate);
    }
    [ExpectedException(typeof(ArgumentException))]
    [TestMethod]
    public void GetAmortizationScheduleExceptionTest4()
    {
        new AmortizationSchedule().Get(annualPercentageRate, loanAmount,
            loanPeriodInYears, DateTime.MinValue);
    }
    [ExpectedException(typeof(ArgumentException))]
    [TestMethod]
    public void GetAmortizationScheduleExceptionTest5()
    {
        new AmortizationSchedule().Get(annualPercentageRate, loanAmount,
            loanPeriodInYears, DateTime.MaxValue);
    }
    [ExpectedException(typeof(ArgumentOutOfRangeException))]
    [TestMethod]
    public void GetAmortizationScheduleExceptionTest6()
    {
        new AmortizationSchedule().Get(annualPercentageRate, loanAmount,
            loanPeriodInYears, DateTime.MaxValue.AddYears(-1));
    }
}

Unit Test Project-Improved

Continue Reading