Search Blog

Author: Joe Meyer
Date: January 2, 2014

Goal: Find records which has associated data in both Table A (Names) and Table B (Phone Numbers)

Difficulty: Easy

Prerequisites: 2 Tables with relational data, Ability to run SQL Queries

Many database designs incorporate relationships between tables. One of the most useful things with the SQL syntax is the ability to link items found in both tables using an INNER JOIN query. Below are my sample tables:

Names

ID  Name
-- ----
0  Joe
1  Chris
2  Mason

Phones

ID  Extension
-- ---------
0  x0001
0  x0101
2  x3121

Now in this example "Joe" (user ID 0) has 2 phone extensions, Chris has no extensions, and Mason has 1 extension. So lets say we want to get all of that in a table so that we can use it later to display it on our webpage. The following query will pull all users who have an extension and their extensions, and leave off any user who does not.

SELECT * 
FROM Names 
    INNER JOIN Phones ON Names.ID=Phones.ID

And the result of that query would look something like this:

ID Name  Extension
-- ----- --------
0  Joe   x0001
0  Joe   x0101
2  Mason x3121

As you can see it left chris out of this altogether because he did not have any matching criteria in both tables for what we did our INNER JOIN on. If we wanted to list chris in this we would simply need to use an outer join instead of an inner join and then in the Phone Extension it would just be null.

Author: Joe Meyer
Date: January 2, 2014

Awhile back I tackled writing a script that came out to be a lot more complicated than I had originally intended it to be. However, I managed to come up with an accurate way to score a game of bowling using a C# Console Application (as it was the only application type I knew how to make at the time, I may amend this to include a form application a little later on). Doing such involves keeping track of quite a few special cases, such as spares, strikes, what frame you got those in, and whether or not you are in the tenth frame. For anyone who is looking for some incite on how to go about getting started or at least one way to do this problem I have listed the source code below.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
 
namespace ConsoleApplication1
{
	class Program
	{
		//code by www.exchangecore.com
		static int intLength(int integer)
		{
			int length = 1;
			if (integer / 10 > 0)
				length = 2;
			if (integer / 100 > 0)
				length = 3;
			return length;			
		}
 
		static String scoreTwo(int totalScore, String score2)
		{
			if (intLength(totalScore) == 1)
				score2 += totalScore + "	 ";
			else if (intLength(totalScore) == 2)
				score2 += totalScore + "	";
			if (intLength(totalScore) == 3)
				score2 += totalScore + "   ";
			return score2;
		}
static void Main(string[] args) { int frameScore = 0, prevFrame = 0, prevFrameTwo = 0, bowlOne, bowlTwo = 0, frame = 1, totalScore = 0, extraFrame; bool strike = false, strikeTwo = false, spare = false; String score1 = "", score2 = "", LastFrameTwo = "", LastFrameThree = "", frameNum = "", line = ""; for (; frame <= 10; frame++) { Console.WriteLine("Please Enter your Scores for Frame {0}:", frame); do //bowlOne loop { Console.Write("Bowl 1:"); bowlOne = int.Parse(Console.ReadLine()); } while (bowlOne > 10 || bowlOne < 0); //checks for valid bowlOne input if (spare == true)// if previous frame was a spare add in the extra points now { prevFrame = 10 + bowlOne; spare = false; totalScore = prevFrame + totalScore; score2 = scoreTwo(totalScore, score2); } if (strikeTwo == true && bowlOne == 10) { prevFrameTwo = 30; totalScore = prevFrameTwo + totalScore; score2 = scoreTwo(totalScore, score2); } if (strikeTwo == true && bowlOne != 10) { strikeTwo = false; prevFrameTwo = 10 + 10 + bowlOne; totalScore = prevFrameTwo + totalScore; score2 = scoreTwo(totalScore, score2); } if (strike == true && bowlOne == 10) { strikeTwo = true; prevFrameTwo = 20; } if (bowlOne < 10) //check to make sure there wasn't a strike on first bowl { do //bowlTwo loop { Console.Write("Bowl 2:"); bowlTwo = int.Parse(Console.ReadLine()); } while (bowlTwo > (10 - bowlOne) || bowlTwo < 0); if (bowlOne + bowlTwo == 10) { spare = true; score1 += bowlOne + "-/ | "; } if (strikeTwo == true && frame == 10) { prevFrameTwo = 10 + 10 + bowlTwo; totalScore = prevFrameTwo + totalScore; score2 = scoreTwo(totalScore, score2); strikeTwo = false; } if (strike == true && bowlOne != 10) { strike = false; prevFrame = 10 + bowlOne + bowlTwo; totalScore = totalScore + prevFrame; score2 = scoreTwo(totalScore, score2); }
if (spare != true && strike != true && strikeTwo != true) { frameScore = bowlOne + bowlTwo; totalScore = totalScore + frameScore; score2 = scoreTwo(totalScore, score2); if (frame != 10) score1 += " " + bowlOne + "-" + bowlTwo + " |"; else score1 += " " + bowlOne + "-" + bowlTwo; } } else { strike = true; prevFrame = 10; if(frame != 10) score1 += " X- |"; } if (frame == 10 && strike == true) { do bowlTwo = int.Parse(Console.ReadLine()); while(bowlTwo < 0 || bowlTwo > 10); if (strikeTwo == true) { prevFrameTwo = 10 + 10 + bowlTwo; totalScore = prevFrameTwo + totalScore; score2 = scoreTwo(totalScore, score2); strikeTwo = false; } } if (frame == 10 && (spare == true || strike == true)) { do extraFrame = int.Parse(Console.ReadLine()); while (extraFrame < 0 || extraFrame > 10); if (strike == true) { prevFrame = 10 + bowlTwo + extraFrame; totalScore = totalScore + prevFrame; score2 = scoreTwo(totalScore, score2); if (bowlTwo == 10) LastFrameTwo = "-X"; else LastFrameTwo += bowlTwo; if (extraFrame == 10) LastFrameThree = "-X"; else LastFrameThree += extraFrame; score1 += " X" + LastFrameTwo + LastFrameThree; } else { if (extraFrame == 10) LastFrameThree = "-X"; else LastFrameThree += extraFrame; if (bowlTwo + extraFrame == 10 && extraFrame != 10) LastFrameThree = "-/"; else LastFrameThree += extraFrame; totalScore = totalScore + 10 + extraFrame; score2 = scoreTwo(totalScore, score2); score1 += bowlOne + "-/" + LastFrameThree; } } frameNum += frame + " "; line += "------"; } Console.WriteLine(frameNum); Console.WriteLine(line); Console.WriteLine(score1); Console.WriteLine(score2); } } }

Author: Joe Meyer
Date: January 2, 2014

Over the years there have been a large number of sorting algorithms that have surfaced. Here is some code that does timing for Insertion sorts, Selection Sorts, Bubble Sorts, and Quick Sorts. It also allows you to test various sizes of arrays. This is written as a C# Console Application. The purpose of the program is simply to demonstrate timing in the most general sense. It's also a great guide for getting code to any of the algorithms and learning how they work. There is also a similar javascript and graphical demonstration which can be found at Darryl Nester of Bluffton University's page, which is extremely useful for those who learn better by seeing how things work.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace SortingComparison
{
    class Program
    {
        //Code taken from exchangecore.com
        public delegate void SortMethod(int[] list);

        static void ShowSortingTimes(String methodName, SortMethod method, int[] list)
        {
            double sortTime;
            Console.WriteLine("{0} of {1} items:", methodName, list.Length);
            FillRandom(list, 10000);
            sortTime = GetSortingTime(method, list);
            Console.WriteLine("\t{0} seconds for a scrambled list", sortTime);
            sortTime = GetSortingTime(method, list);
            Console.WriteLine("\t{0} seconds for a sorted list\n", sortTime);
        }

        static double GetSortingTime(SortMethod method, int[] list)
        {
            int startTime, stopTime;
            startTime = Environment.TickCount;
            method(list);
            stopTime = Environment.TickCount;
            return (stopTime - startTime) / 1000.0;
        }


        static Random rnd = new Random();
        static void FillRandom(int[] arr, int max)
        {
            for (int i = 0; i < arr.Length; i++)
                arr[i] = rnd.Next(max + 1);
        }

        static int FindMax(int[] arr, int last)
        {
            // find the index of the largest number in the array.
            int maxIndex = 0;
            for (int i = 1; i <= last; i++)
            {
                // if the number in location i is bigger than the largest #
                // we have seen before, remember i.
                if (arr[i] > arr[maxIndex])
                    maxIndex = i;
            }
            return maxIndex;
        }

        static void swap(int[] arr, int m, int n)
        {
            int tmp = arr[m];
            arr[m] = arr[n];
            arr[n] = tmp;
        }

        static void SelectionSort(int[] list)
        {
            int last = list.Length - 1;
            do
            {
                int biggest = FindMax(list, last);
                swap(list, biggest, last);
                last--;
            } while (last > 0);
            return;
        }

        static void InsertionSort(int[] list)
        {
            for (int i = 1; i < list.Length; i++)
            {
                if (list[i] < list[i - 1])
                {
                    int temp = list[i];
                    int j;
                    for (j = i; j > 0 && list[j - 1] > temp; j--)
                        list[j] = list[j - 1];
                    list[j] = temp;
                }
            }
        }

        static void BubbleSort(int[] list)
        {
            for (int i = list.Length - 1; i > 0; i--)
            {
                for (int j = 0; j < i; j++)
                {
                    if (list[j] > list[j + 1])
                        swap(list, j, j + 1);
                }
            }

        }

        static void QuickSort(int[] a)
        {
            QuickSortRecursive(a, 0, a.Length);
        }

        static void QuickSortRecursive(int[] a, int low, int high)
        {
            if (high - low <= 1) return;
            int pivot = a[high - 1];
            int split = low;
            for (int i = low; i < high - 1; i++)
                if (a[i] < pivot)
                    swap(a, i, split++);
            swap(a, high - 1, split);
            QuickSortRecursive(a, low, split);
            QuickSortRecursive(a, split + 1, high);
            return;
        }

        static int Partition(int[] arr, int x)
        {
            int lowMark = 0, highMark = arr.Length - 1;

            while (true)
            {
                // find the first item out of place from the start
                while (lowMark < arr.Length && arr[lowMark] <= x)
                    lowMark++;
                // first the first out of place from the end
                while (highMark >= 0 && arr[highMark] > x)
                    highMark--;
                if (lowMark > highMark)
                    return highMark;
                // swap those two items
                swap(arr, lowMark, highMark);
            }
        }
        static void Main(string[] args)
        {
            int x = 20000; // list size variable
            int[] list = new int[x];
            int select; // case selection variable
            do
            {
                Console.WriteLine("Make a Selection:");
                Console.WriteLine("\t1: Insertion Sort");
                Console.WriteLine("\t2: Selection Sort");
                Console.WriteLine("\t3: Bubble Sort");
                Console.WriteLine("\t4: Quick Sort");
                Console.WriteLine("\t5: Change Array Size. Currently {0}", x);
                Console.WriteLine("\t0: Quit");
                Console.Write("Selection: "); select = int.Parse(Console.ReadLine());


                FillRandom(list, x);
                switch (select)
                {
                    case 1: ShowSortingTimes("Insertion Sort", InsertionSort, list);
                        break;

                    case 2: ShowSortingTimes("Selection Sort", SelectionSort, list);
                        break;

                    case 3: ShowSortingTimes("Bubble Sort", BubbleSort, list);
                        break;

                    case 4: ShowSortingTimes("Quick Sort", QuickSort, list);
                        break;

                    case 5:
                        do
                        {
                            Console.WriteLine("New Array Size: ");
                            x = int.Parse(Console.ReadLine());
                        } while (x < 0);
                        list = new int[x];
                        break;
                }
                Console.WriteLine();
                Console.WriteLine();
                Console.WriteLine();
            } while (select != 0);
        }
    }
}

Author: Joe Meyer
Date: January 2, 2014

Goal: Ask for user input with C# Console and use that input to compute an average

Difficulty: Easy

Prerequisites: Visual Studios or other C# Compiler

Often times it is useful to be able to take in user input. While Console Applications in C# are by far the simplest development platform you can built on. In fact even when I'm building more complex applications I'll often write methods in a console app to test first and then port them over. But in order to do any of this we need to leverage the Console.ReadLine() function.

The simple example of how to find the average of any given set of numbers is a great way to practice! First lets go over the basics: You can easily store your Console.ReadLine() value into a variable, however it should be noted that Console.ReadLine takes in a string, and so it must be typecasted in order to store it in a different variable type. In the example below I'll take in two integers and writeline them back to the user:

//This code can be used to read console input into an integer variable
int myInt;
myInt = int.Parse(Console.ReadLine());

//and here we output the results of myInt to the screen
Console.WriteLine("Variable myInt = {0}", myInt);

//We can also read into an integer being declared on the fly
int mySecondInt = int.Parse(Console.ReadLine());

//and here we output the results using string concatination instead
Console.WriteLine("Variable mySecondInt = " + mySecondInt);

That's a great start to our mean finder but we want to be a little smarter and dynamic in our code writing so that we don't need to create lots and lots of Console.ReadLine calls or change our code every time we want to change the number of user inputs. To accomplish this we can use a while loop and just continue to store values into the variable we declare until the readline is blank. I am also going to be storing the total of the numbers I take in in a seperate variable, and a total number of numbers taken in. This will give me all the information I need to find the average/mean of the numbers fed to Console.ReadLine(). (Keep in mind this code isn't fool proof, if you enter a string it will not work properly).

string number;
double total = 0;
int numbers = 0;
while ((number = Console.ReadLine()) != "")
{
    total += double.Parse(number);
    numbers++;
}

Console.WriteLine("The average of the numbers entered is {0}", total / numbers);

You can see that by using a while loop with our Console.ReadLine we were able to cut down on the amount of code we had to write, and the number of changes that will need to ever be made to the code directly. Now it can do 100 user inputs just as easily as 1 or 2.

Author: Joe Meyer
Date: January 2, 2014

Goal: Find records from Table A (Students) that do not exist in Table B (Rooms)

Difficulty: Easy

Prerequisites: 2 Tables with relational data, Ability to run SQL Queries

There is often times you wish to find items in one table or query that are not in another table or query. This is commonly referred to amongst the sql community as a problem referred to as "A not in B". In this example I want to find any Student who does not have a room assigned to them. Some other examples that you might think of may be, finding invoices that don't have any payments, or perhaps find customers who don't have any orders. Regardless of the specifics, it is generally very simple to find records in one table who do not have any associated record in another table.

Here are my 2 tables:

Students

uid firstName lastName
--- --------- --------
1   Joe      Blow
2   Nancy    Drew
3   Johnny   Appleseed

Rooms

roomid uid building
------ --- --------
1      1   Monroe
2      3   Washington

Now in order to find student names who do not have a room on campus we can use the query below:

SELECT firstName, lastName
FROM students LEFT JOIN rooms ON students.uid=rooms.uid
WHERE rooms.uid IS NULL

which would return:

firstName lastName
--------- ---------
Nancy     Drew

This is a great query to have handy as these types of situations are extremely common and has a high use case for application developers and reports.

Author: Joe Meyer
Date: January 2, 2014

Goal: Query Active Directory for users' password last changed, password never expires, and other information

Difficulty: Medium

Prerequisites: Windows XP or higher

So you just enforced a password expiration policy. An excellent step in securing your network. But now you want to audit who has changed their password and who just isn't using their account anymore. After all, odds are if it's been a couple weeks and their password is expired and they haven't changed it, they most likely aren't using their account. That is where this relatively simple script comes into play. Visual Basic can be a powerful tool for accessing windows API's one of which lets us query Active Directory via an ADODB connection. Below I'll show you how to leverage this ability to pull back active directory information such as their Account Name, Full Name, Account Creation Date, Last Login Date (note that if you have multiple domain controllers this is the last login date they logged in using the queried domain controller only), Password last Change date, and whether or not their account password is set to expire. Below we'll output this information into a CSV file so that we can easily view it using excel or favorite spreadsheet manipulator.

  1. The first step of this process is to create the VBScript which pulls back this data. Here is a sample code which i'll save as activeDirectoryInfo.vbs. Edit as you see fit and be sure to change the domain query.
    On Error Resume Next
    const ADS_UF_DONT_EXPIRE_PASSWD = &H10000
    Const ADS_SCOPE_SUBTREE = 2
    Set objConnection = CreateObject("ADODB.Connection")
    Set objCommand =   CreateObject("ADODB.Command")
    objConnection.Provider = "ADsDSOObject"
    objConnection.Open "Active Directory Provider"
    Set objCommand.ActiveConnection = objConnection
    objCommand.Properties("Page Size") = 1500
    objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
    objCommand.CommandText = _
        "SELECT * FROM 'LDAP://dc=domain,dc=com' WHERE objectCategory='user'" 
    Set objRecordSet = objCommand.Execute
    objRecordSet.MoveFirst
    Wscript.StdOut.Write """SamAccountName"",""Full Name"",""Created"",""Last Login"",""PasswordChanged"",""Password Never Expires""" & vbCrlf
    Do Until objRecordSet.EOF
        strPath = objRecordSet.Fields("ADsPath").Value
        Set objUser = GetObject(strPath)
     
     IF IsEmpty(objUser.samAccountName) THEN
      'Do Nothing
     ELSE
      Wscript.StdOut.Write """" & objUser.samAccountName & ""","
      IF IsEmpty(objUser.FullName) THEN
       Wscript.StdOut.Write """NONE"","
      ELSE
       Wscript.StdOut.Write """" & objUser.FullName & ""","
      END IF
      IF IsEmpty(objUser.whenCreated) THEN
       Wscript.StdOut.Write """NONE"","
      ELSE
       Wscript.StdOut.Write """" & objUser.whenCreated & ""","
      END IF
      IF IsEmpty(objUser.GET("lastLogon")) THEN
       Wscript.StdOut.Write """1/1/1601"","
      Else
       dim intLogonTime
       Set objLogon = objUser.Get("lastLogon")
       intLogonTime = objLogon.HighPart * (2^32) + objLogon.LowPart
       intLogonTime = intLogonTime / (60 * 10000000)
       intLogonTime = intLogonTime / 1440
       intLogonTime = intLogonTime + #1/1/1601#
       inactiveDays = intLogonTime
       Wscript.StdOut.Write """" & inactiveDays & ""","
      END IF
      IF IsEmpty(objUser.passwordLastChanged) THEN
       Wscript.StdOut.Write """1/1/1900 12:00:00 AM"","
      Else
       Wscript.StdOut.Write """" & objUser.passwordLastChanged & ""","
      END IF
      IF objUser.GET("userAccountControl") AND ADS_UF_DONT_EXPIRE_PASSWD THEN
       Wscript.StdOut.Write """" & "TRUE" & """"
      ELSE
       Wscript.StdOut.Write """" & "FALSE" & """"
      END IF
      Wscript.StdOut.WriteLine
     End If
        objRecordSet.MoveNext
    Loop
  2. Next we actually need to run this script file. In windows this can be done by opening the command prompt and using the cscript command. This in combination with sending the script output to a file will generate our csv file for us. Note: You will be querying Active Directory using the account you run the command as so make sure you are running with elevated permissions if they are required.
    cscript activeDirectoryInfo.vbs > activeDirectoryInfo.csv
  3. Finally, open up excel an load up your csv file. Voila! A great auditing tool for those who are requiring frequent password changes. 

Author: Joe Meyer
Date: January 2, 2014

Goal: Generate a file of prime numbers

Difficulty: Medium

Prerequisites: Visual Studios or other C# Compiler

As many people are aware, prime numbers are one of the hardest things to find. There really are no really great ways to find large primes. This makes prime numbers perfect for security encryption. One of the most popular types of cryptography that is used widely around the world is the RSA Algorithm (though I believe this uses relative primes). 

There are also several different approaches/algorithms for finding primes. My approach listed below uses a method that incorperates "Trial Division". This means that I check to see if it is divisible by any previously found prime. This is not quite as efficient as the sieve methods, which is where you start with a large bucket of numbers and then eliminate the ones that don't belong. However, the advantage that I have found to using this is that I can stop anywhere in the process and easily pick back up without having to have any special knowledge of where I was, just need the list of primes I had already found, which I take advantage of by writing the prime numbers to a text file.

Notes about the program: This program can become very memory intensive and may need to be run in x64 mode in order to find larger primes, to run in x86 mode please lower the arraySize variable to a suitable size. This program is built to use a single thread and will likely use 100% of 1 CPU core whenever it is running as it is a CPU bound process. This program was written as a Console Application in C# using Visual Studios 2010 and tested using the .NET3.0 framework.

using System;
using System.Collections.Generic;
using System.Text;
using System.IO;

namespace Find_Primes
{
    class Program
    {
        static void Main(string[] args)
        {
            // Code taken from www.exchangecore.com written by Joe
            /*
             * This script can be stopped and restarted from where it left off
             * assuming that the text file containing a list of prime numbers
             * exists in a {prime}\r\n format
             * 
             * arraySize^2 is the number of primes that will be found
             * setting arraySize to large will exhaust committed memory 
             * and cause out of memory exceptions
             */
            int arraySize = 40960;
            double primesFoundLimit = Math.Pow(arraySize, 2); 
            ulong[][] primes = new ulong[arraySize][];
            for (int i = 0; i < arraySize; i++)
                primes[i] = new ulong[arraySize];
            primes[0][0] = 2;
            ulong number = 3;
            int primesFound = 1;
            if (File.Exists("./primes.txt"))
            {
                using (StreamReader sr = new StreamReader("./primes.txt"))
                {
                    string line;
                    while ((line = sr.ReadLine()) != null)
                        primes[primesFound / arraySize][primesFound++ % arraySize] = ulong.Parse(line);
                }
                number = primes[primesFound / arraySize - 1][(primesFound - 1) % arraySize] + 2;
            }
            else
            {
                using (StreamWriter sw = new StreamWriter("./primes.txt", true))
                {
                    sw.WriteLine("2");
                }
            }

            for (; number < ulong.MaxValue && primesFound < primesFoundLimit; number += 2)
            {
                bool isPrime = true;
                double sqrt = Math.Sqrt(number);
                for(uint primeCount = 0; primeCount < primesFound && primes[primeCount / arraySize][primeCount % arraySize] <= sqrt ; primeCount++)
                {
                    if (number % primes[primeCount / arraySize][primeCount % arraySize] == 0)
                    {
                        isPrime = false;
                        break;
                    }
                }
                if(isPrime)
                {
                    using (StreamWriter sw = new StreamWriter("./primes.txt", true))
                    {
                        sw.WriteLine(number);
                    }
                    primes[primesFound / arraySize][primesFound++ % arraySize] = number;
                }
            }
        }
    }
}

Attached are all the prime numbers up to one hundred million with each number seperated by a carriage return linefeed. (Each number on a new line) - Primes.zip