Developing the program of demonstration of the queries at the LINQ language

Developing the program of demonstration of the queries at the LINQ language

In the topic is described the step by step process of solving the task, that uses a queries at the LINQ language. Using this example you will learn to solve the similar tasks. In the topic is shown:

  • reading data from file by using the StreamReader class;
  • data representation as a generalized dynamic array List<T>;
  • examples some queries at the LINQ, which solve the task.

The given topic shows the step by step of process of performing laboratory work in one of the educational institutions of our planet.

 

The task

File “Workers.txt” consists from the following information about workers:

  • identification code;
  • name of worker;
  • the kind of education;
  • specialty;
  • year of birth.

File “Salary.txt” contains:

  • identification code;
  • the salary for the first half;
  • the salary for the second half.

You can download the file “Workers.txthere. Download the file “Salary.txthere.

Solve the following tasks:

  1. Print the names and initials of workers above 35 years old.
  2. Print the identification code of worker with maximum salary in second half-year.
  3. Print the name, initials and type of education of those workers whose salary is lower than the average salary for the year.

The tasks must be executed using LINQ queries.

 

Additional considerations

As can be seen from the file structures “Workers.txt” and “Salary.txt“, these files have the same field “identification code“. This means that when entering data into files, you must be careful. The identification codes must be unique in the whole file and they must be repeated in the different files.

The content of “Workers.txt” file:

1; Ivanov I.I.; Bachelor; Programmer Engineer; 1900
2; Petrov P.P.; Master; Team Lead; 1950
3; Sidorov S.S.; Super Master; Software Architect; 1990
4; Johnson J.J.; Super Bachelor; HTML-coder; 1997
5; Nicolson J.J.; Bachelor; DevOps engineer; 1992

The content of “Salary.txt” file:

1; 23550; 26580
2; 26800; 28390
3; 24660; 27777
4; 35880; 44444
5; 55555; 39938

 

Performing

1. Creating the project as Windows Forms Application.

Create the project as Windows Forms Application. The example of creating a new project as Windows Forms Application is described here.

Save the files of project in any folder. Copy the files “Workers.txt” and “Salary.txt” in the same folder where is located the execution file (*.exe) of program.

 

2. Create the main form of application.

Create the form as shown in Figure 1. The following controls are placed on the form:

  • three controls of Label type. Automatically the three objects (class instance) are created with names label1, label2, label3;
  • three controls of Button type. Automatically the three objects are created with names button1, button2, button3;
  • three controls of ListBox type. Automatically the three objects are created with names listBox1, listBox2, listBox3.

02_02_00_018_01_

Figure 1. The main form of program

 

3. Setting up of controls of the form.

Set up the following controls on the form:

  • – select the control label1 (by using mouse). In the control label1 property Text = “Workers.txt“;
  • – in the control label2 property Text = “Salary.txt“;
  • – in the control label3 property Text = “Result“;
  • – in the control button1 property Text = “Task 1“;
  • – in the control button2 property Text = “Task 2“;
  • – in the control button3 property Text = “Task 3“.

After setting, form has the view as shown in Figure 2.

02_02_00_018_02e

Figure. 2. The form after setting the controls

 

4. Connecting of the namespace System.Linq.

To use the LINQ-queries, you need to include the namespace System.Linq. As a rule, the namespase System.Linq is included automatically, when application is created as Windows Forms Application.

In the file Form1.cs the connection string is following:

using System.Linq;

 

5. Developing the internal data structures, that correspond to the files “Workers.txt” and “Salary.txt“.

Data that correspond to the one string of file “Workers.txt” is advisable to represent as “Workers” structure:

struct Workers
{
    public string code;       // identification code
    public string name;       // name of worker
    public string education;  // type of eduction
    public string profession; // speciality
    public int year;         // year of birth
}

Also, data that correspond to the one string of file “Salary.txt” is advisable to represent as “Salary” structure:

struct Salary
{
    public string code;   // identification code
    public float salary1; // salary in the first half-year
    public float salary2; // salary in the second half-year
}

Since strings in the files can be a lot, all the data can be placed in the form of generalized dynamic array of type List<T>.

For the “Workers” and “Salary” structures, the dynamic arrays has the following definition:

List<Workers> lw = null; // the list of structures of "Workers" type
List<Salary> ls = null; // the list of structures of "Salary" type

After inputting the structures the Form1 clas has the following view:

...

public partial class Form1 : Form
{
    struct Workers
    {
        public string code; // identification code
        public string name; // name
        public string education; // type of education
        public string profession; // speciality
        public int year; // year of birth
    }

    struct Salary
    {
        public string code; // identification code
        public float salary1; // salary in the first half-year
        public float salary2; // salary in the second half-year
    }

    List<Workers> lw = null; // the list of structures of Workers type
    List<Salary> ls = null; // the list of structures of Salary type

    public Form1()
    {
        InitializeComponent();
    }
}

...

 

6. Connecting the System.IO namespace.

To read data from files, in the program are used the possibilities of StreamReader class, that included in the .NET Framework library. Therefore, to use the methods of these classes, you need to add the following string at the beginning of “Form1.cs” file:

using System.IO;

 

 

7. Creating the methods Read_Workers() and Read_Salary() to read data from files “Workers.txt” and “Salary.txt“.

To read data from files “Workers.txt” and “Salary.txt” you need to input the two methods in the class Form1:

  • Read_Workers();
  • Read_Salary().

Listing of method Read_Workers() is following:

// reading data from file "Workers.txt"
public void Read_Workers()
{
    // create the object of StreamReader class, that corresponds to the file "Workers.txt"
    StreamReader sr = File.OpenText("Workers.txt");
    string[] fields; // the variable, that corresponds to the fields of Workers structure
    string line = null;
    Workers w;

    // reading the string
    line = sr.ReadLine();

    while (line != null)
    {
        // split the string by substrings - delimiter is the symbol ';'
        fields = line.Split(';');

        // creating the structure of Workers type
        w.code = fields[0];
        w.name = fields[1];
        w.education = fields[2];
        w.profession = fields[3];
        w.year = Int32.Parse(fields[4]);

        // adding the structure of Workers type in the list List<Workers>
        lw.Add(w);

        // adding the string in listBox1
        listBox1.Items.Add(line);

        // read the next string
        line = sr.ReadLine();
    }
}

Method Read_Workers() reads data from file “Workers.txt” and writes them in:

  • dynamic array lw of List<Workers> type;
  • control listBox1 to display on the form.

Listing of method Read_Salary() is following:

// read the data from file "Salary.txt"
public void Read_Salary()
{
    // create the object of StreamReader class, that corresponds to file "Salary.txt"
    StreamReader sr = File.OpenText("Salary.txt");
    string[] fields; // the variable, that corresponds to the fields of structure Workers
    string line = null;
    Salary s;

    // read the string
    line = sr.ReadLine();

    while (line != null)
    {
        // split the string by substrings - delimiter is the symbol ';'
        fields = line.Split(';');

        // creating the structure of Salary type
        s.code = fields[0];
        s.salary1 = (float)Convert.ToDouble(fields[1]);
        s.salary2 = (float)Double.Parse(fields[2]);

        // adding the structure of Salary type in the list List<Salary>
        ls.Add(s);

        // adding the string in listBox2
        listBox2.Items.Add(line);

        // read the next string
        line = sr.ReadLine();
    }
}

Method Read_Salary() reads data from file “Salary.txt” and writes them in:

  • dynamic array ls of type List<Salary>;
  • control listBox2 to display on the form.

 

8. Programming the Form1() constructor of main form. Reading data from files.

After running the program, data from files must be loaded automatically into the controls “listBox1” and “listBox2”.

Therefore, in the constructor Form1() of class you need add methods Read_Workers() and Read_Salary().

Also, in the constructor Form1() of class is added the memory allocation for dynamic arrays lw and ls. Listing of constructor of main form is as follows:

public Form1()
{
    InitializeComponent();

    // memory allocation for lists
    lw = new List<Workers>();
    ls = new List<Salary>();

    // clear the controls of ListBox type
    listBox1.Items.Clear();
    listBox2.Items.Clear();
    listBox3.Items.Clear();

    // read data from file "Workers.txt"
    Read_Workers();
    Read_Salary();
}

 

9. Programming the events of clicking on the button “Task 1“.

The paragraph 1 of task is solved, when user clicks on the button “Task 1“. As a result, the corresponding event handler will be called.

An example of programming the event of clicking on the button in the application is described here.

Listing of event handler of clicking on the button “Task 1“:

// Names and initials of workers older than 35 years
private void button1_Click(object sender, EventArgs e)
{
    // query named "names"
    var names = from nm in lw
                where nm.year < (2016-35)
                select nm.name;

    listBox3.Items.Clear(); // clear the list

    // add the result of query "names" in the listBox3
    foreach (string s in names)
        listBox3.Items.Add(s);
}

In the listing above, the LINQ-query is formed. This query is named as “names“:

var names = from nm in lw
            where nm.year < (2016-35)
            select nm.name;

The query is begun from the “from” operator. In the “from” operator is given the variable of range, that is named as “nm“. The dynamic array lw of type Workers is the data source in the operator from.

The next step is the “where” operator that is a condition. The element in data source should sutisfy this condition, so that it can be obtained on request.

The query ends by the “select” operator. The “select” operator sets, what must be outputed by a query. In the given example is selected the “name” field of “Workers” structure.

To execute the query, you must use the “foreach” loop.

foreach (string s in names)
    listBox3.Items.Add(s);

Since, the result of LINQ-query is the string type, then the variable s of type string is described in the loop.

 

10. Programming the event of clicking on the button “Task 2“.

The event handler of cliciking on the button “Task 2” is following:

// identification code of worker with maximum salary for second half-year.
private void button2_Click(object sender, EventArgs e)
{
    // query max_salary
    var max_salary = (from ms in ls
                      select ms.salary2).Max(); // method Max() returns the maximum value
    listBox3.Items.Clear(); 
    listBox3.Items.Add(max_salary);
}

In this LINQ-query is used the method Max(), that returns a maximum value in the list. This list is formed into the query with name max_salary.

 

11. Programming the event of clicking on the button “Task 3“.

Listing of event handler of clicking on the button “Task 3” is following:

// The names and education kind those workers,
// which have the salary less then average for year
private void button3_Click(object sender, EventArgs e)
{
    var result = from w in lw
                 from sl in ls
                 let avg = (from s in ls
                 select (s.salary1 + s.salary2)).Average()
                 where ((sl.salary1 + sl.salary2) < avg) && (w.code == sl.code)
                 select w.name +" - " + w.profession;

    listBox3.Items.Clear();
    foreach (string s in result)
        listBox3.Items.Add(s);
}

In this handler is formed the query with name result.

In the query you need realize the data fetch from two data sources. In our case these are the following sources:

  • the dynamical array lw of type List<Workers>. The name and kind of education are selected from this array;
  • the dynamical array ls of type List<Salary>. The salary is selected from this array.

To select the data from two sources the query must consist of two nested “from” operators.

To select data that correspond to unique identifier code (in the “where” operator), the string (w.code==sl.code) is used:

where (...) && (w.code == sl.code)

To calculate the average, is used method Average() of .NET Framework environment. The average is saved in the variable avg, which is entered into query by using the “let” operator:

...

let avg = (from s in ls
           select (s.salary1 + s.salary2)).Average()

...

 

12. Run the program.

Now you can run the program.