Tag Archives: Visual Studio

How to create a console application in C# that will solve crosswords

This tutorial will cover the following tasks in C#:

  • How to count the characters in a string
  • How to assign a file’s directory location to a variable
  • How to create a list variable
  • How to pull/read a CSV file column into a list variable
  • How to clean strings using Regex to remove non alpha numeric characters as the strings are being read into a list
  • How to remove duplicate word entries from a list
  • How to order a list
  • How to write variables to the console, including a list’s elements

Assumptions:

You already know how to create projects in Visual Studio.

If you do not how to do this search online using the following term “how to create C# console applications in visual studio”.

Prerequisites:

First you will need to generate a CSV file with random words using this site:

https://onlinerandomtools.com/generate-random-csv

For the option “how many columns to generate” set the value to 1.

For testing purposes create 1000 rows.

Download the csv file generated and save it using the name “words”.

Summary of how the code works:

The code works by reducing the initial list (i.e. the supplied CSV file of random words) down to only words that match the number of characters of the user word, typically referred to as “string length”.

Once that subset of words has been created the code will then compare the user word’s letters against each letter, referencing the relative position, in each word in the subset.

Note: there is still significant room for optimization but the code is functional and works well as an accessible, human readable tutorial.

Use case example:

If the user enters the word “apple” the dictionary subset will be reduced down to 5 letter words only. These five letter words are then compared to the user word, each word and letter at a time. So if the first word in the list was “cabin” the comparison would jump to the next word in the list as the “a” in “apple” does not match the “c” in “cabin”. If the next word in the dictionary was “acorn” the first letters would match but the comparison would jump to the next word when the “c” and “p” did not match.

Instructions:

Create a C# console application called CrosswordSolver in Visual Studio.

Move the CSV file called “Words” into the bin directory of the project folder, i.e. CrosswordSolver\CrosswordSolver\bin

Open the project CrosswordSolver and paste the C# code below into the default window replacing the default cs page code.

The hardcoded example of a user word is:

string userWord = “a****”;

The user can use * to represent characters unknown, for example ap*le.

Note: The CSV file you randomly generated may have no examples of 5 letter words begining with the letter “a” so experiment with other characters.

You can test the letter comparison functionality by uncommenting the two sections of code immediately following the comments “Test letter comparison”.

To test your CSV file has been read into memory you can uncomment the section of code immediately following “Test that dictionary has been read into memory”.

The C# code:

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

namespace CrosswordSolver
{
    class Program
    {
        static void Main(string[] args)
        {
            int c = 0;
            //User input
            //NOTE: Use * to represent characters unknown 
            string userWord = "a****";
            int wordLength = userWord.Length;

            //Assign directory location of the csv file containing the collection of words to a variable
            string projectFolder = Directory.GetParent(Directory.GetCurrentDirectory()).Parent.FullName;
            string file = Path.Combine(projectFolder, "words.csv");

            //Display dictionary location in console
            Console.WriteLine("Dictionary location: " + file);

            var dictionary = new List<string>();
            using (var rd = new StreamReader(file))

            //Pull file column into dictionary list without cleaning
            //{
            //    while (!rd.EndOfStream)
            //    {
            //        var splits = rd.ReadLine().Split(',');
            //        dictionary.Add(splits[0]);
            //    }
            //}

            //Pull file column into dictionary list while cleaning
            {
                while (!rd.EndOfStream)
                {
                    var splits = rd.ReadLine().Split(',');
                    //string clean is done with Regex
                    dictionary.Add(Regex.Replace(splits[0], "[^A-Za-z0-9 ]", ""));
                }
            }

            //Test that dictionary has been read into memory
            //Console.WriteLine("The dictionary contains the following words:");
            //foreach (var element in dictionary)
            //Console.WriteLine(element);

            //Remove duplicate word entries
            //c = dictionary.Count;
            //Console.WriteLine("The dictionary contains " + c + " words");
            dictionary = dictionary.Distinct().ToList();
            //c = dictionary.Count;
            //Console.WriteLine("The dictionary contains " + c + " words");

            // Count the elements in the List and display test parameters
            c = dictionary.Count;
            Console.WriteLine("The dictionary contains " + c + " words");
            Console.WriteLine("User entered the string: " + userWord);
            Console.WriteLine(userWord + " has " + wordLength + " characters");
            userWord = userWord.ToLower();

            //Reduce the dataset size based on number of characters in string
            IEnumerable<string> query =
                dictionary.Where(word => word.Length == wordLength);

            var subSet = new List<string>();
            foreach (var word in query)
                subSet.Add(word);

            //Order List
            subSet = subSet.OrderBy(x => x).ToList();

            c = subSet.Count;
            if (c != 0)
            {
                Console.WriteLine("The dictionary contains " + c + " words that are " + wordLength + " characters in length");

                //Begin character and position match check
                var result = new List<string>();
                foreach (var word in subSet)

                {
                    for (int i = 0; i <= wordLength - 1; i++)
                    {

                        if ((word.ToLower()[i] == userWord[i]) | (userWord[i] == '*'))
                        {

                            //Test letter comparison (Letters match)
                            //Console.WriteLine(
                            //"Letter " + i + ", which is " + "\"" + word[i] + "\"" + ", of the word " + "\"" + word + "\"" +
                            //" matches letter " + i + ", which is " + "\"" + userWord[i] + "\"" + ", of the user input " + "\"" + userWord + "\""
                            //);

                            if (i == wordLength - 1)
                            { result.Add(word); }

                        }
                        else
                        {
                            //Test letter comparison (Letters do not match)
                            //Console.WriteLine(
                            //"Letter " + i + ", which is " + "\"" + word[i] + "\"" + ", of the word " + "\"" + word + "\"" +
                            //" does not match letter " + i + ", which is " + "\"" + userWord[i] + "\"" + ", of the user input " + "\"" + userWord + "\""
                            //);

                            break;
                        }
                    }
                }

                //Test words that do not match
                //foreach (var word in subSetToRemove)
                //Console.WriteLine(word);

                bool isEmpty = !result.Any();
                if (isEmpty)
                {
                    Console.WriteLine("No matches found");
                }
                else
                {
                    c = result.Count();
                    Console.WriteLine("Potential matches found: " + c);
                    foreach (var word in result)
                        Console.WriteLine(word);
                }
            }
            else
            {
                Console.WriteLine("No words of " + wordLength + " characters long found");
            }
            Console.ReadKey();
        }
    }
}

 

If you found this code useful be sure to like the post and comment. ☮

If you would like to know how to create a csv file with C# see this tutorial link.

If you would like to know how to create a console application in Visual Studio that won’t open a command window when it runs see this tutorial link.

 

report cartoon

How to keep an SSRS Report on one page

Using visual studio left click just under design to focus on the report.

Image showing where to click

Now the report properties window should be visible in the bottom right of the screen.

Set the interactive height to 0 as shown below.

Properties windowYou will get the following warning sign below. Limiting the report to one page, by setting the interactive height to zero, means everything needs to be loaded all at once. This will create lag when a user loads the report and interacts with it.

warning message stating can cause performance issuesIf you are basically using SSRS as a means for end users to pull a data dump via excel, heed this warning. If you just have a few aggregated tables and you want them all on the same page you should be fine.

Image with the text 3000 years later in giant letters

How to provide a dynamic parameter drop-down of year options in SSRS

Say you’ve developed a report which returns data based on a inputted year parameter value, e.g. “give me all the sales in 2014”.

How do you provide the year options for the SSRS report?

Well there’s three ways that come to mind.

Select distinct from a date field, e.g. SELECT DISTINCT YEAR(SalesDate) FROM Sales

This would certainly provide you with all the available years but the database could have millions of sales. So it’s not too efficient.

You could manually populate years in the parameter settings in the SSRS report, kind of primitive but it would work.

But for me the robust and efficient way is the solution provided below.

The following SQL query dynamically populates an integer field in a temporary table with years. The query uses a base year variable, which can be set to as far back as when the required data fields and values existed in the database. A loop then provides the years up to and including the current year. This query can be used to generate a dataset for an SSRS report and then this dataset can then be used to provide parameter values for the report. The report will then always create a list of years between the base year and the current year. Meaning the years parameter will never need to be adjusted again.

(For a tutorial on how to turn a month name and year into dates for the first and last day of the month see this tutorial)

/*Create temp table populated with the years from a base year to the present year*/
IF OBJECT_ID('tempdb..#availableYear') IS NOT NULL
    DROP TABLE #availableYear

CREATE TABLE #availableYear ([Year] INT)

DECLARE @baseYear AS INT
DECLARE @i AS INT

/*Change the base year to the earliest year the database has the required data available*/
SET @baseYear = 2013
SET @i = 0

WHILE @i <= YEAR(GETDATE()) - @baseYear
BEGIN
    INSERT INTO #availableYear
    SELECT @baseYear + @i

    SET @i = @i + 1
END

SELECT * FROM #availableYear

Within the Stored Procedure that populates the report you can then do something like below to make sure the date range matches the year chosen.

DECLARE @yearChosen AS INT

SET @yearChosen = 2013

DECLARE @startDate date
DECLARE @endDate date

SET @startDate = CONVERT(CHAR(4), @yearChosen) + '0101'
SET @endDate = CONVERT(CHAR(4), @yearChosen) + '1231'

PRINT @startDate
PRINT @endDate    

--OR For example

YEAR(SaleDate) = @yearChosen