C# – Make your own query language

Posted on Posted in C Sharp Tricks Tagged with , , , ,

59101739

Well, most probably the answer depends on the query language 🙂

Here is the task:

Problem 3 – A query language over a CSV file

A CSV files (comma separated values) can be represented as a table.

For example this CSV file:

id, name, course
1, Rado, Haskell
2, Ivo, Python

Can be represented by this table:

id name course
1 Rado Haskell

We are going to use CSV files as tables and make a simple query language for fetching data.

In a language of your choice, make a program that:

  • Reads a CSV file.
  • Gives the user an interactive input for queries.
  • Answers the queries with data from the CSV file.

The queries, that should be supported are:

  • SELECT [columns] LIMIT X – where you can SELECT without giving the LIMIT. Then this will fetch all rows.
  • SUM [column] – returns the sum of all integers in the given column.
  • SHOW – returns a list of all column names in your data.
  • FIND X – returns all rows, that has X in some of their cells (Match X with at least one of the columns). If X is a string, search for every string, that contains X as a substring.

Here are examples of all queries.

Consider that we have loaded the following CSV:

id,name,hometown
1,Kiara,Lunel
2,Mona,Henley-on-Thames
3,Kiayada,Villers-aux-Tours
4,Karly,Hillsboro
5,Igor,Oranienburg

Now, let’s make queries:

query>SELECT id
|id|
|--|
|1 |
|2 |
|3 |
|4 |
|5 |

query> SELECT id, name
|id| name   |
|--|--------| 
|1 |Kiara   |
|2 |Mona    |
|3 |Kiayada |
|4 |Karly   |
|5 |Igor    |

query> SELECT id, name LIMIT 1
|id| name   |
|--|--------| 
|1 |Kiara   |

query> SUM id
15

query> SHOW
id, name, hometown

query> FIND "-"
|id| name   | hometown          |
|--|--------| ------------------|
|2 |Mona    | Henley-on-Thames  |
|3 |Kiayada | Villers-aux-Tours |

Features

  • If the query is non-valid – say it. Don’t crash the program.
  • As you see in the examples, the results should be displayed in visual, tabular way. This is up to you. You don’t have to follow the styles of the example.

What I did? Check by yourself:

query2

And my favourite – here comes the code 🙂

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

class CSV_query_language
{
    public static string[] sCommandActivate;
    public static string[] sCommandDetails;

    public const string SHOW = "show";
    public const string SUM = "sum";
    public const string FIND = "find";
    public const string SELECT = "select";
    public const string LIMIT = "limit";

    static void Main()
    {
        try
        {
            string sCommand = Console.ReadLine();
            sCommandActivate = sCommand.Split('>');
            sCommandDetails = sCommandActivate[1].Split(' ');

            if (sCommandDetails.Contains(SHOW))
            {
                ShowHeader();
            }

            if (sCommandDetails.Contains(SUM))
            {
                SumValues();
            }

            if (sCommandDetails.Contains(FIND))
            {
                FindTheResults();
            }
            if (sCommandDetails.Contains(SELECT))
            {
                ShowMeWhatYouGotGeneral();
            }
            Console.WriteLine("\nThank you for using the custom query language!\nPress Y for a new query.");
            string sNewQuery = Console.ReadLine();
            if (sNewQuery == "Y" || sNewQuery == "y")
            {
                Main();
            }
        }

        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);

            Console.WriteLine("\nAn error has occurred. Try again!");
            Console.WriteLine("Possible queries:\n");
            Console.WriteLine("query> show");
            Console.WriteLine("query> sum id");
            Console.WriteLine("query> select id, name, age limit 5");
            Console.WriteLine("query> select id, experience");
            Console.WriteLine("query> find \"-\"");
            Console.WriteLine("Please, enter a query and enjoy the result:\n");
            Main();
        }
    }
    static void ShowMeWhatYouGotGeneral()
    {

        string file1 = @"New Text Document.csv";
        string[] allLines = File.ReadAllLines(file1);

        if (sCommandDetails.Contains(LIMIT))
        {
            SetALimit();
        }
        else
        {
            ShowMeWhatYouGot();
        }
    }
    static void FindTheResults()
    {
        string file1 = @"New Text Document.csv";
        string[] allLines = File.ReadAllLines(file1);
        int iLastValue = sCommandDetails.Length - 1;

        string sStringToSearch = sCommandDetails[iLastValue];
        sStringToSearch = sStringToSearch.Replace("\"", "");

        string sBeautiful = "";
        Boolean first = true;
        int iItem = 0;
        string[] display;
        string sDisplayResult;
        string[] getColumnName = allLines[0].Split(',');

        sBeautiful = new String('*', 20 * allLines.Length - 1);
        Console.WriteLine(sBeautiful);

        for (int i = 0; i < getColumnName.Length; i++)
        {
            sDisplayResult = getColumnName[i];
            Console.Write(string.Format("| {0,-20}", sDisplayResult));
        }
        Console.WriteLine();
        Console.WriteLine(sBeautiful);
        first = true;
        Boolean newLineNeeded = false;

        foreach (var item in allLines)
        {
            if (first)
            {
                first = false;
                continue;
            }
            if (item.Contains(sStringToSearch))
            {
                iItem = item.Split(',').Length;
                display = item.Split(',');

                for (int i = 0; i < iItem; i++)
                {
                    sDisplayResult = display[i];
                    Console.Write(string.Format("| {0,-20}", sDisplayResult));
                    newLineNeeded = true;
                }
                if (newLineNeeded)
                {
                    Console.WriteLine();
                }
                newLineNeeded = false;
            }
        }
        Console.WriteLine(sBeautiful);
    }

    static void ShowMeWhatYouGot()
    {   

        int iItem = 0;
        string sDisplayResult = "";
        Boolean first = true;
        string sBeautiful = "";
        string[] display;
        string Translator2 = "";

        string file1 = @"New Text Document.csv";
        string[] allLines = File.ReadAllLines(file1);

        string sArrDisplay2 = String.Join(",", sCommandDetails, 1, sCommandDetails.Length - 1);
        string[] sArrDisplay = sArrDisplay2.Split(',');

        int iCountColumns = sCommandDetails.Length - 2;
        string[] getColumnName = allLines[0].Split(',');

        foreach (string item in allLines)
        {
            iItem = item.Split(',').Length;
            display = item.Split(',');
            for (int i = 0; i < iItem; i++)
            {
                if (sArrDisplay.Contains(display[i]))
                {
                    Translator2 = i + "," + Translator2;
                }
            }
        }
        
        //Removing the word SHOW from the Translator2
        Translator2 = Translator2.Remove(Translator2.Length - 1);

        foreach (string item in allLines)
        {
            sBeautiful = new String('*', 20 * Translator2.Length - 1);
            if (first)
            {
                Console.WriteLine(sBeautiful);
            }

            iItem = item.Split(',').Length;
            display = item.Split(',');

            for (int i = 0; i < iItem; i++)
            {
                if (Translator2.Contains(i.ToString()))
                {
                    sDisplayResult = display[i];
                    Console.Write(string.Format("| {0,-20}", sDisplayResult));
                }
            }

            Console.WriteLine();
            if (first)
            {
                Console.WriteLine(sBeautiful);
                first = false;
            }

        }
        Console.WriteLine(sBeautiful);
    }

    static void ShowHeader()
    {
        string file1 = @"New Text Document.csv";
        StreamReader sReader = new StreamReader(file1, Encoding.GetEncoding("Windows-1251"));
        Console.WriteLine(sReader.ReadLine());
    }

    static void SumValues()
    {
        string file1 = @"New Text Document.csv";
        string[] allLines = File.ReadAllLines(file1);

        int iLastValue = sCommandDetails.Length - 1;
        int iResult = -1;
        int iCounter = -1;

        string sColumnToWork = sCommandDetails[iLastValue];
        string[] getColumnName = allLines[0].Split(',');

        foreach (var item in getColumnName)
        {
            iCounter++;
            if (item == sColumnToWork)
            {
                iResult = iCounter;
            }
        }

        int iCountColumns = allLines[0].Split(',').Length;
        string sGetColumnThatWeAreTalkingAboutName = getColumnName[iResult];
        string sFirstLine = "";

        //Setting the header to look like: 0,0,0,0... etc, in order to be excluded from the sum:
        if (iCountColumns > 1)
        {
            for (int i = 0; i < iCountColumns - 1; i++)
            {
                sFirstLine = sFirstLine + "0,";
            }
            sFirstLine = sFirstLine + "0";
        }
        else
        {
            sFirstLine = "0";
        }

        allLines[0] = sFirstLine;

        IEnumerable strs = allLines;
        var columnQuery =
            from line in strs
            let elements = line.Split(',')
            select Convert.ToInt32(elements[iResult]);

        var results = columnQuery.ToList();
        double dSum = results.Sum();
        Console.WriteLine("\nThe sum of column [{0}] is  {1:0.##}!", sGetColumnThatWeAreTalkingAboutName, dSum);
    }

    static void SetALimit()
    {
        Boolean first = true;
        int iItem = 0;
        string sDisplayResult = "";
        string sBeautiful = "";
        string Translator2 = "";
        int z = -2;

        string file1 = @"New Text Document.csv";
        string[] allLines = File.ReadAllLines(file1);
        string sArrDisplay2 = String.Join(",", sCommandDetails, 1, sCommandDetails.Length - 1);
        string[] sArrDisplay = sArrDisplay2.Split(',');

        int Limit = int.Parse(sArrDisplay[sArrDisplay.Length - 1])-1;

        sArrDisplay = sArrDisplay.Reverse().Skip(2).Reverse().ToArray();
        int iCountColumns = sCommandDetails.Length - 2;

        string[] getColumnName = allLines[0].Split(',');
        string[] display;


        foreach (string item in allLines)
        {
            iItem = item.Split(',').Length;
            display = item.Split(',');
            for (int i = 0; i < iItem; i++)
            {
                if (sArrDisplay.Contains(display[i]))
                {
                    Translator2 = i + "," + Translator2;
                }
            }
        }

        //We remove the last comma from the string here:
        Translator2 = Translator2.Remove(Translator2.Length - 1);

        foreach (string item in allLines)
        {
            z++;
            if (z > Limit)
            {
                break;
            }

            sBeautiful = new String('*', 20 * Translator2.Length - 1);
            if (first)
            {
                Console.WriteLine(sBeautiful);
            }

            iItem = item.Split(',').Length;
            display = item.Split(',');

            for (int i = 0; i < iItem; i++)
            {
                if (Translator2.Contains(i.ToString()))
                {
                    sDisplayResult = display[i];
                    Console.Write(string.Format("| {0,-20}", sDisplayResult));
                }
            }
            Console.WriteLine();
            if (first)
            {
                Console.WriteLine(sBeautiful);
                first = false;
            }
        }
        Console.WriteLine(sBeautiful);
    }
}

Enjoy it.

Related posts:

  1. C# – WPF – Dropdown menus & audio combination
  2. C# – Simple Counter of Values in Array – Miss Cat 2011
  3. C# – A simple interface with 3 classes in C#