Term paper. Development of an environmental pollution monitoring program

Term paper. Development of an environmental pollution monitoring program

This topic describes a detailed step-by-step program development process that manages the local Microsoft SQL Server database located in the “* .mdf” file.

The program is implemented in the programming language C# in Microsoft Visual Studio 2010.

After completing all the stages of the course work you will gain experience in developing applications that interact with databases such as Microsoft SQL Server. 


Content

Task

Develop a program that operates a database such as Microsoft SQL Server. The database is located in *.mdf-file. The program is to be developed in Microsoft Visual Studio.

In the work you need to perform the following tasks:

  • design a local database such as Microsoft SQL Server, which is located in a separate file. The name of the database is “MyDataBase.mdf”;
  • create in the database 2 tables with the names “Source” and “Emission”. Each of the tables must have the specified fields, which are described below. The tables of the database must be linked to each other over a certain field;
  • develop an application that uses the MyDataBase.mdf database. The application must be implemented in C# using the Windows Forms Application template;
  • in the application you need to implement the basic commands for managing records (data) in the database: adding, editing, deleting, viewing;
  • realize the following calculations: finding the minimum, maximum, average emissions for any source.

The database contains 2 tables with the following structure.

Table ‘Source’.

Field name Data type Description
1 ID_Source int Unique identifier of source, the counter, key field
2 Name nchar(50) Source name
3 Address nchar(100) Source address

 

Talbe ‘Emission’

Field name Data type Description
1 ID_Emission int Unique identifier of emission, the counter, key field
2 ID_Source int The field that defines the identifier in the Source table. Used to link the tables
3 count float Number of emissions
4 Text nchar(100) Comment
5 date datetime The date of emissions in the format “##. ##. ####”

The tables are linked to each other by the ID_Source field.

 

Instruction

1. Choosing a Database Model

To organize data management when creating projects (programs), the Microsoft Visual Studio system offers various kinds of data sources. For example:

  • a local Microsoft SQL Server database that is located in a separate “* .mdf” file. An example of working with such a database is described in detail here;
  • local database of Microsoft SQL Server. In this case, a local server can be installed, for example SQLEXPRESS. An example of working with such a database is described in detail here;
  • a local database Microsoft Access. In this case, a “*.mdb” database file is created;
  • a database created using the ODBC driver;
  • Oracle database.

You can also create your own database in the form of structures or classes. Then, for this database, you can create software functionality that will process database records, carry out convenient output, convert to known formats, etc. This is the topic of another term paper.

To use the program, we use a local database, which is located in a separate “* .mdb” file and is designed to work under the management system of relational databases of Microsoft SQL Server.

 

2. Designing (connecting) the database “MyDataBase.mdf”. Creating a New Project in Microsoft Visual Studio
2.1. Preparing files for project files and databases

Prepare a folder for the program. In this case, install the project folder and database files:

D:\Programs\C_SHARP\TermPaper01

 

2.2. Run Microsoft Visual Studio. Creating an application using the Windows Forms Application template. Saving Project Files

Run Microsoft Visual Studio. Create a new project in C# using the Windows Forms Application template.

A new project is created using the command

File->New Project

In our case, the project is created in the folder

D:\Programs\C_SHARP\TermPaper01

A more detailed example of creating a new project is described in the article:

In the ‘New Project’ window, set the following settings:

  • project name (field ‘Name’) TermPaper;
  • folder (Location) “D:\Programs\C_SHARP\TermPaper1\”;
  • the name of solution (Solution name) – TermPaper.

After creating a new project, the main form of the program is as shown in Figure 2.1.

Figure 2.1. Main form

 

2.3. Creating a new or connecting a previously created database “MyDataBase.mdf”

After creating a folder for the database, you can create a new database or connect an existing one. A detailed example of creating/connecting a database, which is located in a separate “*.mdf” file, is described in the articles:

In order not to waste time creating a new database, you can download the archive with the finished database, which was created earlier.

The database is located in two files:

  • “MyDataBase.mdf”;
  • “MyDataBase.ldf”.

After unpacking the archive, copy the database to the folder with the future program:

D:\Programs\C_SHARP\TermPaper01

Connection of the finished, previously created, database to the project is carried out by one of the following commands:

  • in the ‘Tools’ menu, select the command “Connect to Database”;
  • in the utility ‘Server Explorer’, select the button “Connect to Database”.

As a result, the ‘Add Connection’ window opens, in which the following settings should be selected:

  • the field “Data Source” = “Microsoft SQL Server Database File”;
  • the field “Database File name (new of existing)” = “D:\Programs\C_SHARP\TermPaper1\MyDataBase.mdf”. Here, using the “Browse” button, you select the path to our database “MyDataBase.mdf”;
  • option “Log on to the server” = “Use Windows Authentication”.

A detailed example of connecting a finished database to a project is described in the topic:

After, the database “MyDataBase.mdf” will be displayed in the “Server Explorer” window (Figure 2.2).

Figure 2.2. Database “MyDataBase.mdf” in the “Server Explorer” window

 

2.4. Information about the database “MyDataBase.mdf”

The database “MyDataBase.mdf” contains:

  • the ‘Source’ table (Figure 2.3);
  • the ‘Emission’ table (Figure 2.4);
  • the diagram “Diagram1”, which contains information about the relationships between the “Source” and “Emission” tables (see Figure 2.5).

Figure 2.3. Tables “Source”, “Emission” and the Diagram of relationships

If you expand the connection diagram, the connection between the Source and Emission tables will be displayed in the ID_Source field (Figure 2.5).

To display the connection diagram, use the “Design Database Diagrams” command from the context menu of the diagram (Figure 2.4). Also, the connecting diagram is displayed by double-clicking on Diagram1.

Figure 2.4. Calling the command to display a diagram of the relationships between tables

Figure 2.5. Diagram of connections between tables

 

2.5. Connecting a ‘Connection String’ to database

In the program, to access the database, the the ‘Connection String’ with database will be used. This string contains all the necessary information about the database.

A detailed description of connecting the ‘Connection String’ to the program is described in the topic:

To connect a connection string to a database, you need to perform the following sequence of steps:

  1. Go to the ‘Server Explorer’ utility (Figure 2.6).
  2. Select the file “MyDataBase.mdf” (Figure 2.6).
  3. In the “Properties” window, select the “Connection Strings” and copy it to the Clipboard (Figure 2.6).
  4. Go to the text part of the file “Form1.cs”.

Create a variable in the form class Form1 of type string.

Let the name of the variable ConnStr. Insert the string “Connection String” in the initial value string of the ConnStr variable as follows:

// connection string with database
string ConnStr = @"Data Source=.\SQLEXPRESS;AttachDbFilename=D:\Programs\C_SHARP\TermPaper1\TermPaper1\MyDataBase.mdf;Integrated Security=True;User Instance=True";

Figure 2.6. Copy the connection string to the database

At the moment the approximate form of the file “Form1.cs” is as follows:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace TermPaper1
{
    public partial class Form1 : Form
    {
        // connection string with database
        string ConnStr = @"Data Source=.\SQLEXPRESS;AttachDbFilename=D:\Programs\C_SHARP\TermPaper1\TermPaper1\MyDataBase.mdf;Integrated Security=True;User Instance=True";

        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {

        }
    }
}

After this, you can design the program interface and program the methods of processing the database.

 

3. Designing the main form of the program. Placing controls on the form

Using the tools on the ToolBox on the main form of the program, you need to place the following controls (Figure 3.1):

  • label1 of the Label type. Contains the text “Emission source”;
  • label2 of the Label type. Contains the text “Emission”;
  • dataGridView1 of the DataGridView type. This control displays the database table “Source”;
  • dataGridView2 of the DataGridView type – displays the database table “Emisison”;
  • button1 – a button that contains the text “Add source …” (the Text property);
  • button2 – a button that contains the text “Delete source”;
  • button3 – a button that contains the text “Edit source …”;
  • button4 – button that contains the text “Add emissions …”;
  • button5 – button that contains the text “Delete emissions”;
  • button6 – button with the text “Edit Emissions …”;
  • button7 – button with the text “Minimum emissions”;
  • button8 – button with the text “Maximum emissions”;
  • button9 – button with the text “Average emissions”;
  • control menuStrip1 of the MenuStrip type.

For the controls label1, label2, button1, button2, button3, button4, button5, button6, button7, button8, button9, the Text property is set to the appropriate value. Working with these controls is described in more detail in a practical example:

In controls dataGridView1, dataGridView2, a property is set up

EditMode = EditProgrammatically

This means that cells with displayed data will not be able to be edited using the keyboard.

The menuStrip1 control is used to create menu in the program. In this work, we create a menu, as shown in Figure 3.2. Working with the menuStrip1 control is described in detail in the article:

You also need to configure the following form properties (Form1 control):

  • property Text = “Program for Environmental Pollution Monitoring”;
  • property MaximizeBox = False.

Figure 3.1. The main form of the program after design

Figure 3.2. The main menu of program. Sub-menu “Source”, “Emissions”, “Calculation”

 

4. Development of secondary forms (dialogs)
4.1. Designing the form of the “Add source …” window

Adding a new source of emissions to the database is done after clicking on the “Add source …” button. A set of new information about the source can be carried out in a separate form.

To create a new form, you need to call the command

Project -> Add Windows Form...

In the window that opens, select the Windows Form template.

A more detailed example of creating a new form and calling a form is described in the article:

Using the toolbox toolbox Toolbox, create a new form, as shown in Figure 4.1.

The form is named Form2. The name of the form file is “Form2.cs”.

Figure 4.1. Form for adding a new emission source

In the Form2 form, the following controls are placed:

  • two controls of Label type with the names label1, label2. Designed to output information messages;
  • two controls of type Button with the names button1, button2;
  • two controls of type TextBox with the names textBox1, textBox2;

We configure the following properties:

  • in the Form2, property Text = “Add source”;
  • in the Form2, property StartPosition = CenterScreen. This means that the form will be opened at the center of the screen;
  • in the label1 control, property Text = “Source name”;
  • in the label2 control, property Text = “Address”;
  • in button1, property Text = “Add”;
  • in button1 property DialogResult = “OK”. This means that when the button button1 is clicked, the form will be closed with the OK return code;
  • in button2 property Text = “Cancel”;
  • in button2 property DialogResult = “No”.
  • in the textBox1 control, property Modifiers = Public. This means that the control has a public access modifier. After that, you can access to textBox1 from other forms (modules, files);
  • in the textBox2 control, property Modifiers = Public (Visible from the outside).

 

4.2. Designing the window “Edit source”

Following the example of the previous paragraph (paragraph 4.1), a new form is created, “Edit source”. The form is named Form3. The form file is named “Form3.cs”.

After placing and adjusting the controls, the form window will be as shown in Figure 4.2.

Figure 4.2. The window “Edit source”

The form contains the following controls:

  • two controls of Label type with the names label1, label2;
  • two controls of Button type withe the names button1, button2;
  • two controls of TextBox type with the names textBox1, textBox2.

Configure the following properties of the controls:

  • in Form3, property Text = “Edit Source”;
  • in Form3, property StartPosition = CenterScreen;
  • in button1, property Text = “OK”;
  • in button1, property DialogResult = OK;
  • in button2, property Text = “Cancel”;
  • in button2, property DialogResult = No;
  • in label1, property Text = “Source Name”;
  • in label2, property Text = “Address”;
  • in textBox1, property Modifiers = Public;
  • in textBox2, property Modifiers = Public.

 

4.3. Designing the “Add Emissions …” window

Based on the sample of the previous forms, a new form is designed (Figure 4.3). This form should be activated after clicking on the “Add Emissions …” button. The program name is Form4. Form file “Form4.cs”.

Figure 4.3. The form of adding a new emission for a given source

The form contains the following controls:

  • 4 controls of Label type with names label1, label2, label3, label4;
  • 3 controls of TextBox type with names textBox1, textBox2, textBox3;
  • 2 controls of Button type with names button1, button2.

Configure the following properties of the controls:

  • in Form4, property Text = “Add emissions”;
  • in Form4, property StartPosition = CenterScreen;
  • in label1, property Text = “Number of emissions”;
  • in label2, property Text = “Comment”;
  • in label3, property Text = “Date”;
  • in label4, property Text = “Source”;
  • in label4, property Modifiers = public (Visible from the outside);
  • in button1, property Text = “Add”;
  • in button2, property Text = “Cancel”;
  • in button1, property DialogResult = OK;
  • in button2, property DialogResult = No;
  • in textBox1, property Modifiers = Public;
  • in textBox2, property Modifiers = Public;
  • in textBox3, property Modifiers = Public.

 

4.4. Designing the form of confirmation window “Delete source”

After the user calls the “Delete Source” command, a confirmation window should appear. To do this, create a new form called Form5. The form file is named “Form5.cs”. The Form5 is shown in Figure 4.4.

Figure 4.4. Confirm form “Delete source”

The form contains the following controls:

  • two controls of Label type with names label1, label2;
  • two controls of Button type with names button1, button2.

Configure the following properties of the form and controls:

  • – in Form5, property Text = “Delete the Source of Infection”;
  • – in Form5, property StartPosition = CenterScreen;
  • – in the label1 control, property Text = “Are you sure you want to delete the source:”;
  • – in label2, the property Modifiers = Public;
  • – in button1, property Text = “Yes”;
  • – in button1, property DialogResult = OK;
  • – in button2, property Text = “No”;
  • – in button2, property DialogResult = No.

 

4.5. Designing the form of the confirmation window “Delete Emissions”

Based on the sample in 4.4 a form for removing emissions is developed (Figure 4.5). The name of the form is Form6.cs. Form file “Form6.cs”.

Figure 4.5. The confirmation form “Delete Emissions”

The form contains the following controls:

  • two controls of Label type with names label1, label2;
  • two controls of Button type with names button1, button2.

Configure the following properties of the form and controls:

  • in Form6, property Text = “Delete emissions”;
  • in Form6, property StartPosition = CenterScreen;
  • in control label1, property Text = “Do you really want to delete the emission?”;
  • in control label2, property Modifiers = Public;
  • in button1, property Text = “Yes”;
  • in button1, property DialogResult = OK;
  • in button2, property Text = “No”;
  • in button2, property DialogResult = No.

 

4.6. Designing the window ‘Edit Emissions’

The form of editing the emission string has the form as shown in Figure 4.6. In the program, the form is named Form7 and located in the file “Form7.cs”.

Figure 4.6. The window “Edit Emissions”

The form contains the following controls:

  • 4 controls of Label type with names label1, label2, label3, label4;
  • 3 controls of TextBox type with names textBox1, textBox2, textBox3;
  • 2 controls of Button type with names button1, button2.

Configure the following properties of the form and controls:

  • in Form7, property Text = “Edit Emissions”;
  • in Form7, property StartPosition = CenterScreen;
  • in label1, property Text = “Source”;
  • in label2, property Text = “Number of emissions”;
  • in label3, property Text = “Comment”;
  • in label4, property Text = “Date”;
  • in label1, property Modifiers = Public;
  • in button1, property Text = “OK”;
  • in button2, property Text = “Cancel”;
  • in button1, property DialogResult = OK;
  • in button2, property DialogResult = No;
  • in textBox1, property Modifiers = Public;
  • in textBox2, property Modifiers = Public;
  • in textBox3, property Modifiers = Public.

 

4.7. Designing of the form “Minimum Emissions”

In accordance with the condition of the task, the program calculates the minimum emissions for any source. The result is displayed in a separate window “Minimum Emissions”. To display the result to the project, a corresponding form with the name Form8 is added, which is shown in Figure 4.7. Form file “Form8.cs”.

Figure 4.7. The “Minimum Emissions” window

The form contains the following controls:

  • control of Label type with name label1;
  • control of DataGridView type with name dataGridView1;
  • control of Button type with name button1.

The following properties of the form and controls are set:

  • in Form8, property Text = “Minimum emissions”;
  • in Form8, property StartPosition = CenterScreen;
  • in the control label1, property Text = “Minimum emissions”;
  • in button1, property Text = “OK”;
  • in button1, property DialogResult = OK;
  • in dataGridView1, property Modifiers = Public.

 

4.8. Designing the form “Maximum Emissions”

The “Maximum Emissions” form is designed to display the maximum emissions for any source (Figure 4.8). In the program this form is named Form9. The form file is named “Form9.cs”.

Рис. 4.8. The “Maximum emissions” window

The form contains the following controls:

  • the control of Label type with name label1;
  • the control of DataGridView type with name dataGridView1;
  • the control of Button type with name button1.

The following properties of the form and controls are set:

  • in the Form9 form, property Text = “Maximum emissions”;
  • in Form9, property StartPosition = CenterScreen;
  • in the control label1, property Text = “Maximum emissions”;
  • in button1, property Text = “OK”;
  • in button1, property DialogResult = OK;
  • in dataGridView1, property Modifiers = Public.

 

4.9. Designing the form “Average emissions”

The “Average Emissions” form is designed to display average emissions for any source (Figure 4.9). The arithmetic mean is calculated. In the program, this form is called Form10. Form file “Form10.cs”.

Figure 4.9. The “Average emissions” window

The form contains the following controls:

  • the control of Label type with name label1;
  • the control of DataGridView type with name dataGridView1;
  • the control of Button type with name button1.

The following properties of the form and controls are set:

  • in the Form9 form, property Text = “Average emissions”;
  • in Form9, property StartPosition = CenterScreen;
  • in the control label1, property Text = “Average emissions”;
  • in button1, property Text = “OK”;
  • in button1, property DialogResult = OK;
  • in dataGridView1, property Modifiers = Public.

 

5. Writing the program code
5.1. Adding the System.Data.SqlClient namespace

In order to work with the “* .mdf” files of the Microsoft SQL Server database at the program level, you need to connect the System.Data.SqlClient namespace at the top of the “Form1.cs” file.

using System.Data.SqlClient;

At the moment, the text of the main form module Form1.cs has approximately the following form:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

// The namespace for working with the MS SQL database
using System.Data.SqlClient;

namespace TermPaper1
{
    public partial class Form1 : Form
    {
        // Connection string with database
        string ConnStr = @"Data Source=.\SQLEXPRESS;AttachDbFilename=D:\Programs\C_SHARP\TermPaper1\TermPaper1\MyDataBase.mdf;Integrated Security=True;User Instance=True";


        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
        }
    }
}

 

5.2. Development of a method of filling dataGridView1 from the Source table

After loading the program, making changes to the tables, etc., the data from the database should be displayed in the Source and Emission tables. Therefore, you need to create methods that read data from these tables based on a simple SQL query.

The FillSource() method that reads data from the Source table is as follows

// Show the Source table
private void FillSource()
{
    string SqlText = "SELECT * FROM [Source]";
    SqlDataAdapter da = new SqlDataAdapter(SqlText,ConnStr);
    DataSet ds = new DataSet();
    da.Fill(ds,"[Source]");
    dataGridView1.DataSource = ds.Tables["[Source]"].DefaultView;
}

This method should be added to the Form1 form text after the Form1_Load() method.

Let’s explain some fragments of the code of the FillSource() method.

The database is accessed using the FillSource() method.

The variable SqlText contains a string in the SQL language for reading data from the Source table

SELECT *
FROM [Source]

The method declares an instance of the SqlDataAdapter class named da. SqlDataAdapter class provides a set of commands on the data and the database connection used to fill System.Data.DataSet class object and SQL Server updates the database.

An instance of the DataSet class is called ds. The DataSet class implements the data cache in memory.

The program code uses the Fill() method of the SqlDataAdapter class. This method adds or updates rows in System.Data.DataSet according to the names in the data source. The Fill() method gets two parameters of type System.Data.DataSet and System.Data.DataTable. The first parameter is needed to fill out the table entries or schema. The second parameter contains the name of the table.

 

5.3. Developing a method for filling the dataGridView2 control from the ‘Emission’ table

To display the data of the Emission table in dataGridView2, we will develop a method based on the sample of the previous paragraph (paragraph 5.2). The method is called FillEmission()

// Show the Emission table
private void FillEmission()
{
    // generate SQL-query string
    string SqlText = "SELECT * FROM [Emission]";
    int index;
    string ID_Source;

    index = dataGridView1.CurrentRow.Index;
    ID_Source = dataGridView1[0, index].Value.ToString();

    SqlText = "SELECT * FROM [Emission],[Source] WHERE (([Emission].ID_Source = ";
    SqlText = SqlText + ID_Source + ") AND ([Source].ID_Source = " + ID_Source + "))";

    SqlDataAdapter da = new SqlDataAdapter(SqlText, ConnStr);
    DataSet ds = new DataSet();
    da.Fill(ds, "[Emission]");
    dataGridView2.DataSource = ds.Tables["[Emission]"].DefaultView;
}

In this method, the following SQL query is programmatically generated in the SqlText variable:

SELECT *
FROM [Emission], [Source]
WHERE ([Emission].ID_ Source = ID_Source) AND
([Source].ID_Source = ID_Source)

where ID_Source is the unique value of the counter in the Emission and Source tables.

To get the ID_Source value, first calculate the row index in the Source table

index = dataGridView1.CurrentRow.Index;

then the ID_Source value is calculated, which in order goes to the 0 position of the Source table:

ID_Source = dataGridView1[0, index].Value.ToString();

The following steps are performed according to the model of p. 5.2. Only the data is output in the dataGridView2 control.

 

5.4. Modification of the Form_Load() method of the main form Form1

In the main form Form1 class, there is the Form_Load() method, which is called immediately after the program is run. Therefore, here it is necessary to enter a call of methods FillSource() and FillEmission().

General view of the Form_Load() method:

private void Form1_Load(object sender, EventArgs e)
{
    FillSource();
    FillEmission();
}

 

5.5. Developing the MyExecuteNonQuery() method for changing data in tables

The program is supposed to edit (change) data in tables using SQL-commands INSERT, UPDATE, DELETE. Therefore, you need to implement a common method that will be called for any of these commands. The method is called MyExecuteNonQuery(). The code of method is as follows:

// Method, for convenient processing of INSERT, UPDATE, DELETE commands
// Method gets the SQL-query
public void MyExecuteNonQuery(string SqlText)
{
    SqlConnection cn; // an instance of a class of type SqlConnection
    SqlCommand cmd;

    // memory allocation with the initialization of the connection string with the database
    cn = new SqlConnection(ConnStr);
    cn.Open(); // open the data source
    cmd = cn.CreateCommand(); // set the SQL command
    cmd.CommandText = SqlText; // set the command line
    cmd.ExecuteNonQuery(); // execute the SQL command
    cn.Close(); // close the data source
}

Let’s explain the work of the method. The method takes the text of the SQL-command in the variable SqlText. This can be one of the INSERT, UPDATE, DELETE commands.

After that, an instance of the SqlConnection class is created, which symbolizes the connection to the database. The class object is called cn.

Another class SqlCommand is designed to correctly store the SQL command in the CommandText variable. Accordingly, an object of this class with the name cmd is created. The SqlCommand class has an ExecuteNonQuery() method that executes the SQL command.

Before making changes to the data source, open it with the Open() command. After making changes, the data source (database) is closed using the Close() method.

The MyExecuteNonQuery() method can be called from other methods using the string:

// execute an SQL command
MyExecuteNonQuery(SqlText);

where SqlText is the text of the corresponding SQL command (INSERT, UPDATE, DELETE).

 

5.6. Programming the event of click on the button1 (“Add source …”)

To call the window of adding a new source, you need to click on button1 (“Add source …”). As a result, the window for adding a new source will open (see section 4.1).

Listing the click event handler on the button button1 looks like this:

// The "Add source ..." button
private void button1_Click(object sender, EventArgs e)
{
    //
    string SqlText = "INSERT INTO [Source] ([ID_Source],[Name],[Address]) VALUES (1, 'Source-01','Address-01') ";
    Form2 f = new Form2(); // create an instance of window

    if (f.ShowDialog() == DialogResult.OK)
    {
        // Generate the SQL command
        SqlText = "INSERT INTO [Source] ([Name], [Address]) VALUES (";
        SqlText = SqlText + "\'" + f.textBox1.Text + "\', ";
        SqlText = SqlText + "\'" + f.textBox2.Text + "\')";

        // execute the SQL-command
        MyExecuteNonQuery(SqlText);
        // Display the 'Source' table
        FillSource();
    }
}

Let’s explain some fragments of the code.

Adding the source is based on the SQL-command:

INSERT INTO [Source] ([Name], [Address])
VALUES (name, address)

where name, address is the value of the textBox1, textBox2 fields of the Form2 form. In these fields, the user enters the name and address of the new source.

The SQL command is programmed in the SqlText variable. The command is executed in the MyExecuteNonQuery() method, which receives the parameter value of the SqlText variable (see section 5.5).

The creation of a new form is carried out in standard way for C# .NET:

Form2 f = new Form2();

The window of form is called by the ShowDialog() method of Form2:

f.ShowDialog()

After the source is added to the database, the ‘dataGridView1’ control of the main form of the Form1 program is redrawn using the FillSource() method.

 

5.7. Programming the click event on ‘button3’ (Edit source …)

Based on the example of the previous paragraph, a click event is programmed on button3. The event handler is as follows:

// The command "Edit source..."
private void button3_Click(object sender, EventArgs e)
{
    int index, n;
    string SqlText = "UPDATE [Source] SET ";
    string ID_Source, name, address;

    // checking, if there are any entries in the Source table
    n = dataGridView1.Rows.Count;
    if (n == 1) return;

    Form3 f = new Form3();

    // fill out the form with data before opening
    index = dataGridView1.CurrentRow.Index;
    ID_Source = dataGridView1[0, index].Value.ToString();
    name = dataGridView1[1, index].Value.ToString();
    address = dataGridView1[2, index].Value.ToString();

    f.textBox1.Text = name;
    f.textBox2.Text = address;

    if (f.ShowDialog() == DialogResult.OK)
    {
        name = f.textBox1.Text;
        address = f.textBox2.Text;
        SqlText += "Name = \'" + name + "\', Address = '" + address + "\' ";
        SqlText += "WHERE [Source].ID_Source = " + ID_Source;

        MyExecuteNonQuery(SqlText);
        FillSource();
    }
}

The event handler opens the Form3 form (“Edit source”), which was designed in section 4.2.

Before opening the form, the index, ID_Source, name, address variables are filled. Then the value of the variables is written to textBox1, textBox2 of Form3. All other commands are performed following the pattern of the preceding paragraph (§ 5.6).

The text of the SQL command is as follows:

UPDATE [Source]
SET Name = name, Address = address
WHERE [Source].ID_Source = ID_Source

where name, address – source name and source address.

 

5.8. Setting the DeleteRule property in the FK_Emission_Source object (link between diagrams) to correctly remove the source

In order to remove a source from the [Source] table, you need to execute the following SQL query:

DELETE FROM [Source]
WHERE [Source].ID_Source = ID_Source

where ID_Source is the unique source identifier that is evaluated programmatically.

If you remove a source from the Source table, you may experience a problem. The problem is that when you remove a source from the Source table, you must remove all entries from the Emission table. Therefore, the above SQL query will not work so far: the database will give an error that the data in the Emission table is associated with the Source table.

To avoid this problem, you need to properly configure the relationship between the diagrams. In the program, the object that is responsible for the connection between the diagrams is called FK_Emission_Source. To properly configure the connection, you need to do the following:

  1. Go to the ‘Server Explorer’ utility. For the database “MyDataBase.mdf” open the “Database Diagrams” tab (Figure 5.1).
  2. Call the context menu by right-clicking on the Diagram1 element. Select the “Design Database Diagram” command in the context menu (Figure 5.1). As a result, a window opens with the diagram dbo.Diagram1, which links the Source and Emission tables.

Figure 5.1. Calling a connection diagram between tables

  1. In the dbo.Diagram1 window, select the connection between the FK_Emission_Source diagrams (Figure 5.2)
  2. In the properties window on the “INSERT AND UPDATE” tab, set the property: Delete Rule = Cascade.

After that, you can program the click event on button2“Delete source” (see section 5.9).

Figure 5.2. Setting the DeleteRule property to Cascade

 

5.9. Programming the click event on button2 (“Delete source”)

Now all the preparatory operations for implementing the source deletion command from the [Source] table have been performed (see 5.8).

The command to delete the source is based on the SQL query:

DELETE FROM [Source]
WHERE [Source].ID_Source = ID_Source

Where ID_Source is a unique source identifier that is programmed in the following lines:

index = dataGridView1.CurrentRow.Index;
ID_Source = Convert.ToString(dataGridView1[0, index].Value);

Listing the click event handler on the “Delete source” button is as follows:

// Button "Delete Source"
private void button2_Click(object sender, EventArgs e)
{
    int index, n;
    string ID_Source;
    string name, address;
    string SqlText = "DELETE FROM [Source] WHERE [Source].ID_Source = ";

    // check if there are any entries in the Source table
    n = dataGridView1.Rows.Count;
    if (n == 1) return;

    Form5 f = new Form5();

    index = dataGridView1.CurrentRow.Index;
    ID_Source = Convert.ToString(dataGridView1[0, index].Value);

    // generate an SQL command
    SqlText = SqlText + ID_Source;

    // Fill out the information in the Form5 window
    name = Convert.ToString(dataGridView1[1, index].Value);
    address = Convert.ToString(dataGridView1[2, index].Value);

    f.label2.Text = ID_Source + " - " + name + " - " + address;

    if (f.ShowDialog() == DialogResult.OK) // display the form
    {
        // execute SQL-command
        MyExecuteNonQuery(SqlText);
        // display the Source table
        FillSource();
    }
}

Before deleting a source, a confirmation window for the command (form Form5) is called. The form Form5 of confirmation the delete command the source was designed in paragraph 4.4. Previously, the information is formed about the source, which is deleted.

 

5.10. Programming the click event on the button button4 (“Add Emissions …”)

After clicking on the “Add Emissions …” button, the ‘Form4’ of adding the emissions is displayed (see section 4.3).

The listing of the event handler is as follows:

// The command "Add Emissions"
private void button4_Click(object sender, EventArgs e)
{
    string SqlText = "";
    int index; // The number of the selected row in the Source table
    string ID_Source;
    string name;

    Form4 f = new Form4();

    // 1.1. Find the active row in Source and take from it ID_Source
    index = dataGridView1.CurrentRow.Index;
    ID_Source = Convert.ToString(dataGridView1[0, index].Value);
    name = Convert.ToString(dataGridView1[1, index].Value);

    if (f.ShowDialog() == DialogResult.OK)
    {
        // Add data to the table
        // Generate an SQL string
        SqlText = "INSERT INTO [Emission] ([ID_Source], [count], [Text], [date]) VALUES (";
        // Generate the values of the variable SqlText
        SqlText = SqlText + ID_Source + ", "; // ID_Source
        SqlText = SqlText + f.textBox1.Text + ", ";     // count
        SqlText = SqlText + "\'" + f.textBox2.Text + "\', ";   // Text
        SqlText = SqlText + "\'" + f.textBox3.Text + "\')";       // date

        // execute the SQL-command
        MyExecuteNonQuery(SqlText);
        // display the Emission table
        FillEmission();
    }
}

Adding an emission string to the Emission table is done using the SQL command:

INSERT INTO [Emission] ([ID_Source], [count], [Text], [date])
VALUES (ID_Source, f.textBox1.Text, f.textBox2.Text, f.textBox3.Text)

where

  • ID_Source – unique identifier of the source of emissions (calculated programmatically);
  • f.textBox1.Text is the value entered by the user in the textBox1 field of the Form4 form. This value corresponds to the count field of the Emission table;
  • f.textBox2.Text – is the value entered by the user in the textBox2 field of the Form4 form. This value corresponds to the Text field of the Emission table;
  • f.textBox3.Text – is the value entered by the user in the textBox3 field of the Form4 form. This value corresponds to the date field of the Emission table.

 

5.11. Programming the click event on the button6 (Edit Emissions …)

After selecting the command “Edit Emissions …”, the corresponding window (form Form7) opens, which was designed in section 4.6.

Listing the click event handler on button6 is as follows:

// Button "Edit emissions..."
private void button6_Click(object sender, EventArgs e)
{
    int index, index_src, n;
    string SqlText = "UPDATE [Emission] SET ";
    string ID_Emission, ID_Source, count, Text, date;
    string Name_Source;

    // check if there are any records in the Emission table
    n = dataGridView2.Rows.Count;
    if (n == 1) return;

    Form7 f = new Form7();

    // fill out the form with data before opening
    index = dataGridView2.CurrentRow.Index;
    ID_Emission = dataGridView2[0, index].Value.ToString();
    ID_Source = dataGridView2[1, index].Value.ToString();
    count = dataGridView2[2, index].Value.ToString();
    Text = dataGridView2[3, index].Value.ToString();
    date = dataGridView2[4, index].Value.ToString();
    index_src = dataGridView1.CurrentRow.Index;
    Name_Source = dataGridView1[1, index_src].Value.ToString();

    //
    f.label1.Text = Name_Source;
    f.textBox1.Text = count;
    f.textBox2.Text = Text;
    f.textBox3.Text = date;

    if (f.ShowDialog() == DialogResult.OK)
    {
        count = f.textBox1.Text;
        Text = f.textBox2.Text;
        date = f.textBox3.Text;

        SqlText += "count = " + count + ", Text = \'" + Text + "\', date = \'" + date + "\' ";
        SqlText += "WHERE [Emission].ID_Emission = " + ID_Emission;
        MyExecuteNonQuery(SqlText);
        FillEmission();
    }
}

The command “Edit Emissions …” is based on the SQL command:

UPDATE [Emission]
SET count = count1, Text = Text1, date = date1
WHERE [Emission].ID_Emission = ID_Emission

where

  • count1 is the value entered by the user in the textBox1 field of the Form7 form. This value corresponds to the [Emission]. [Count] field;
  • Text1 is the value entered by the user in the textBox2 field of the Form7 form. This value corresponds to the [Emission]. [Text] field;
  • date1 is the value entered by the user in the textBox3 field of the Form7 form. This value corresponds to the [Emission].[Date] field;
  • ID_Source is the unique source identifier from the Source table. This value corresponds to the [Emission].ID_Source field.

 

5.12. Programming the click event on the button5 (“Delete Emissions”)

The program code of event handler of delete row from the ‘Emission’ table:

// Button "Delete Emissions"
private void button5_Click(object sender, EventArgs e)
{
    //
    int index, n;
    string ID_Emission;
    string count, text;
    string SqlText = "DELETE FROM [Emission] WHERE [Emission].ID_Emission = ";

    // Check if there are rows in the Emission table
    n = dataGridView2.Rows.Count;
    if (n == 1) return;

    Form6 f = new Form6();

    index = dataGridView2.CurrentRow.Index;
    ID_Emission = Convert.ToString(dataGridView2[0, index].Value);

    // generate the SQL-command
    SqlText += ID_Emission;

    // fill the information in the Form6 window
    count = Convert.ToString(dataGridView2[2, index].Value);
    text = Convert.ToString(dataGridView2[3, index].Value);

    f.label2.Text = ID_Emission + " - " + count + " - " + text;

    if (f.ShowDialog() == DialogResult.OK)
    {
        MyExecuteNonQuery(SqlText); // execute SQL-command
        FillEmission(); // display the Emission table
    }
}

The “Delete Emissions” command is based on the SQL command:

DELETE FROM [Emission]
WHERE [Emission].ID_Emission = ID_Emission

where ID_Emission is the unique identifier of the emission string in the Emission table.

ID_Emission is generated programmatically on the basis of the active row of the dataGridView2 control:

index = dataGridView2.CurrentRow.Index;
ID_Emission = Convert.ToString(dataGridView2[0, index].Value);

 

5.13. Programming the click on the button7 (“Minimum Emissions”)

To calculate the minimum emissions for any source, use the SQL query:

SELECT [Emission].ID_Source, MIN(Emission].count AS 'Minimum emissions'
FROM [Emission]
GROUP BY [Emission].ID_Source

To find the minimum, this query uses the MIN aggregation function from the SQL library.

The program code of click on the button7 is as follows:

// Button "Minimum emissions"
private void button7_Click(object sender, EventArgs e)
{
    // SQL query - defines the minimum emissions for any source
    string SqlText;

    SqlText = "SELECT [Emission].ID_Source, MIN([Emission].count) AS \'Minimum emissions\' ";
    SqlText += " FROM [Emission]";
    SqlText += " GROUP BY [Emission].ID_Source";

    Form8 f = new Form8();

    SqlDataAdapter da = new SqlDataAdapter(SqlText, ConnStr);
    DataSet ds = new DataSet();
    da.Fill(ds, "[Emission]");
    f.dataGridView1.DataSource = ds.Tables["[Emission]"].DefaultView;
    f.ShowDialog();
}

The result is output in the form Form8, which was designed in section 4.7.

 

5.14. Programming the event of clicking on button8 (“Maximum Emissions”)

On the basis of the previous paragraph (clause 5.13), the click event handler on button8 is implemented.

Maximum emissions calculation is based on the SQL query:

SELECT [Emission].ID_Source, MAX(Emission].count AS 'Maximum emissions'
FROM [Emission]
GROUP BY [Emission].ID_Source

To find the maximum, the SQL query uses the MAX aggregation function.

Listing the event handler is as follows:

// Button "Maximum emissions"
private void button8_Click(object sender, EventArgs e)
{
    // SQL query - determines the maximum emissions for any source
    string SqlText = "SELECT [Emission].ID_Source, MAX([Emission].count) AS \'Maximum emissions\' ";
    SqlText += " FROM [Emission]";
    SqlText += " GROUP BY [Emission].ID_Source";

    Form9 f = new Form9();

    SqlDataAdapter da = new SqlDataAdapter(SqlText, ConnStr);
    DataSet ds = new DataSet();
    da.Fill(ds, "[Emission]");
    f.dataGridView1.DataSource = ds.Tables["[Emission]"].DefaultView;
    f.ShowDialog();
}

 

5.15. Programming the click event on button9 “Average Emissions”

Average emissions are calculated based on the SQL query:

SELECT [Emission].ID_Source, AVG(Emission].count AS 'Average emissions'
FROM [Emission]
GROUP BY [Emission].ID_Source

 

To find the average arithmetic value in the SQL query, the aggregation function AVG is used.

Listing the event handler is as follows:

// Button "Average emissions"
private void button9_Click(object sender, EventArgs e)
{
    // SQL-query - determines the average emissions for any source
    string SqlText = "SELECT [Emission].ID_Source, AVG([Emission].count) AS \'Average emissions\' ";
    SqlText += " FROM [Emission]";
    SqlText += " GROUP BY [Emission].ID_Source";

    Form10 f = new Form10();

    SqlDataAdapter da = new SqlDataAdapter(SqlText, ConnStr);
    DataSet ds = new DataSet();
    da.Fill(ds, "[Emission]");
    f.dataGridView1.DataSource = ds.Tables["[Emission]"].DefaultView;
    f.ShowDialog();
}

 

5.16. Programming the row selection event in the dataGridView1 (Source table)

If the user selects a row in the Source table (dataGridView1 control), the associated Emission table rows that have the same ID_Source value must be displayed. The Emission table data is output in the dataGridView2 control.

Therefore, it is advisable to program the Click event of the dataGridView1 control (Figure 5.3). The handler for this event is called dataGridView1_Click().

Figure 5.3. The Click event of the dataGridView1 control

Listing the mouse event handler on the dataGridView1 is as follows:

// The click on dataGridView1
private void dataGridView1_Click(object sender, EventArgs e)
{
    // Based on the selected row in the Source table, display the Emission table
    // determine the number of rows in the dataGridView1
    int n = dataGridView1.RowCount;
    int row = dataGridView1.CurrentRow.Index;

    if (n != (row + 1)) // Check if there was a click on the last row
    FillEmission();
}

 

5.17. Programming the change event of the active cell in the dataGridView1 (Source table)

If you change the cell value in the Source table (dataGridView1 control) with a mouse or keyboard, the corresponding Emission table data (dataGridView2 control) should be displayed.

In this case, it is efficient to program the CellEnter event of the dataGridView1 control. This event is generated if any cell in the dataGridView1 table gets input focus. This situation is possible when the user changes the active cells (rows) with the mouse or keyboard.

Figure 5.4. CellEnter event of the dataGridView1 control

The CellEnter event handler is called dataGridView1_CellEnter(). The program code of the event handler is as follows:

// Changing the active cell in dataGridView1
private void dataGridView1_CellEnter(object sender, DataGridViewCellEventArgs e)
{
    int n = dataGridView1.RowCount;
    int row = dataGridView1.CurrentRow.Index;
    if (n != (row + 1)) // Check if there was a click on the last row
    FillEmission();
}

 

6. Setting menu commands menuStrip1

In this step, the execution of commands from the menu menuStrip1 of the main form of the Form1 program is configured.

In this step, the execution of commands from the menu menuStrip1 of the main form of the Form1 is configured. The menuStrip1 menu commands copy the execution of the event handlers following buttons: button1, button2, …, button9. Since there are already programmed click commands on the buttons of the main form, it is enough to redirect the corresponding menu commands to them.

We will redirect the menu command “Source” -> “Add …” to the event handler button1_Click().To do this, perform the following steps.

  1. Activate the command “Source” -> “Add …” from the menu menuStrip1 (Figure 6.1 – 1).
  2. Activate the Events tab in the Properties window (Figure 6.1-2).
  3. Select the Click event (Figure 6.1 – 3).
  4. From the dropdown list, select the name of the event handler button1_Click() (Figure 6.1 – 4).

Figure 6.1. Redirecting the “Source” -> “Add …” command to the event handler button1_Click()

Following the pattern set up all of the other menu commands:

  • the “Source” -> “Delete” command corresponds to the event handler button2_Click ();
  • the “Source” -> “Edit…” command corresponds to the event handler button3_Click ();
  • the “Emissions” -> “Add…” command corresponds to the event handler button4_Click();
  • the “Emissions” -> “Delete” command corresponds to the event handler button5_Click();
  • the “Emissions” -> “Edit…” command corresponds to the event handler button6_Click();
  • the “Calculation” -> “Minimum emissions…” command corresponds to the event handler button7_Click();
  • the “Calculation” -> “Maximum emissions…” command corresponds to the event handler button8_Click();
  • the “Calculation” -> “Average emissions…” command corresponds to the event handler button9_Click().

 

7. Run the program

After the performed actions, you can run the program (Figure 7). 🙂

Figure 7. Executing the program

 


Related topics