Development of Automation Program of work of Manager.

In the work is described step by step process of creating of application, which realizes automation of work of dispatcher cash desks in the sale of tickets at the bus station.

The application must be realized in Microsoft Visual Studio at the C#. Database is realised in Microsoft Access.

Search other websites:

The task

In the term paper we need to develop the system, which realizes automation of work in dispatcher cash desks at bus station.

The input data to perform the work are the following:

information about transportation route (routing number, point of destination, district, province, distance, weight, departure time, arrival time);
information about the tickets sold on a given flight (place, cost, time of purchase, passenger’s name, passport data of passenger, the presence or absence of privileges);
information about dispatcher, who serves the client (name of dispatcher, date of birth, address);
information about the driver who transports for a given route (the driver’s name, date of birth, passport details);
information about the bus on which the transporting is realized (bus number, model, license plate number, the maximum number of places);
number of carriage.

In the process of work with application the database of transportations is formed. These transportations include:

data about route;
data about tickets sold;
data about dispatcher, who inputs changes to database;
data about the bus, on which the transporting is realized;
data about the driver who carries out transportation.

⇑

Instructions

1. Developing the database

We develop the database in the Microsoft Access. The name of database file is “02_02_00_009_Base_en.mdb”. The completed database you can download here.

According to problem condition, we have 6 tables. Five tables contain data about:

routes of transportation;
tickets which were sold;
dispatcher, who inputs the information to database;
vehicle (bus, taxi), by which the shipment;
a driver who transports.

We call these table by the names: “Route“, “Ticket“, “Dispatcher“, “Bus“, “Driver“.

Sixth table is the result of executing application with interconnection to database. In this table directly transportation is fixed. We call it the “Transportation“.

The tables “Route“, “Ticket“, “Dispatcher“, “Bus“, “Driver” is primary in relation to the table “Transportation“.

The structure of tables is the following.

Table “Route“.

Table “Ticket“.

Table “Dispatcher“.

Table “Bus“.

Table “Driver“.

Table “Transportation“.

The interconnection scheme between tables of database is shown in Figure 1.

Fig. 1. The interconnection scheme between tables of database

⇑

2. Building the UML-diagramm

The model of database was based on using of unified modeling language UML.

In order not to go into the details of developing a database in UML language in this article we describe basic UML-diagrams.

⇑

2.1. UML-diagram of use

Diagram of using the UML-language (Fig. 2) gives the answer on the appointment of system in the outside world.

Fig. 2. Diagram of using

In general, the diagram of activity shows the decomposition of some activity in its component parts. Activity diagrams are used for modeling business processes, manufacturing processes, serial and parallel computing.

Fig. 3. UML-diagram of activity

⇑

2.3. UML-diagram of interaction

In the interaction diagram shows only those objects that are directly involved in the interaction. The key point for interaction diagrams (or sequence) is the dynamics of the interaction of objects in time.

Fig. 4. UML-diagram of interaction

⇑

2.4. UML-diagram of classes

In the construction of UML models, is distinguished the concept of class. The class is called the description of sets of objects with common attributes. Graphically, a class is displayed in the form of a rectangle.

The software system is developed from the viewpoint of the database model and uses six classes, each of which is the object that corresponds to this or another database table.

Fig. 5. The dependencies between classes of application

Next steps describe the developing of application.

⇑

3. Developing the application

Run MS Visual Studio 2010. Create the project as Windows Forms Application. A detailed example of project creating as Windows Forms Application is described here.

Set the template “Visual C#“, type of project Windows Forms Application. Save the project in some folder. For example, select the folder

C:\Programs\C_SHARP\Program_02_02_00_009e

The name of application is set by default “WindowsFormsApplication1“.

As a result, the empty form of application will be created. This form shown in Figure 6.

Fig. 6. The initial form of application

⇑

4. Connecting to database

On this step we will connect the database “02_02_00_009_Baza_en.mdb” to our application.

The process of connecting database to application, in details, is described here. On this step, the wizard of connecting the database to application is called. This process contains several steps.

⇑

4.1. Connecting the database file to application

To call the process of connecting to database, you need to call the command “Add New Data Source…” from menu “Data“.

In the window of selection of type of database source we select “Database“. In the window of model of connecting to database we select Dataset (model based on the dataset).

In the window of selection of connecting with database we select “New Connection…” and in the following window we select the type of connection “Microsoft Access Database File (OLE DB)“.

It is recommended the database file save to the same folder, that contains files of application. In our case, path to the database file in the window of selection of connecting is following:

C:\Programs\C_SHARP\Program_02_02_00_009e\WindowsFormsApplication1\WindowsFormsApplication1\02_02_00_009_Baza_en.mdb

In the next windows leave all as is. At the question

Save the Connection String to the Application Configuration File we give the answer “Yes” and connection string we don not change.

In the window “Choose Your Database Objects” we select all tables and finish the work of wizard by clicking on the button “Finish“.

After executed actions, with the help of wizard of connection to database, the window of “Server Explorer” will have view, as shown in Figure 7.

Fig. 7. Result of connection to database

⇑

4.2. Connecting the methods of database operating

In order to programmatically control the database and get access to methods, which operate with database, you need to connect namespace System.Data.OleDb.

To do this, in the main form “Form1.cs” in Solution Explorer we select the mode “View code” from the context menu (Fig. 8). At the beginning of file we add the following string:

using System.Data.OleDb;

Fig. 8. Calling the mode of program code of form “Form1.cs“

The general view of top part of file Form1.cs is the following:

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;
using System.Data.OleDb;

⇑

5. Developing the application form

To develop the form you need to use controls from panel “Toolbox” (Fig. 9).

Fig. 9. Panel “Toolbox“

⇑

5.1. Developing the menu

To create menu, you need to use control “menuStrip” from tab “Menus & Toolbars” of panel “Toolbox” (Fig. 10).

Fig. 10. Control “MenuStrip“

Developing the menu using control “MenuStrip” is in detail described here. We develop menu as shown in Figure 11.

Fig. 11. Developing menu “Transportation” and “View“

⇑

5.2. Controls DataGridView

We are changing the form size and we place on the form two controls “DataGridView” from tab Data of panel Toolbox (Fig. 12).

Fig. 12. Controls “DataGridView“

⇑

5.3. Controls of type “GroupBox“

We activate tab “Containers” from panel “Toolbox“.

Place on the form 4 controls of type GroupBox (Fig. 13). As a result we get four objects with names groupBox1, groupBox2, groupBox3, groupBox4. Using these names, you can get access to the properties and methods of these objects.

Fig. 13. Controls of type GroupBox

Set the caption for groupBox1 object. It is carried out with the help of property Text as shown in Figure 14. The value of property “Text” is equal “Filter”. In this group will be placed objects, which realizes a filtering of data to database.

Thus, we set the values of three objects:

in object groupBox2 property Text = “Transportation”;
in object groupBox3 property Text = “View”;
in object groupBox4 property Text = “Commands”.

Fig. 14. Property Text of object groupBox1

After the changes, the application form will have view as shown in Figure 15.

Fig. 15. Application form after forming the objects groupBox1, groupBox2, groupBox3, groupBox4

⇑

5.4. Placement of controls Label, Button, TextBox and ComboBox

Controls of type “Button” and “TextBox” are located on tab “Common Controls” of panel “Toolbox” (Fig. 16).

Fig. 16. Controls Label, Button, TextBox and ComboBox of tool palette Toolbox

⇑

5.5. Placement of controls from group “Filter“

In the area of object groupBox1 (“Filter“) we place the one control of type “ComboBox”, two controls “Label”, one control “Button” and one control “TextBox” (Fig. 17).

Fig. 17. Controls label1, label2, button1, comboBox1, textBox1 in group “Filter“

Set the properties of controls:

in the control label1 property Text = “Field“;
in the control label2 property Text = “Mask“;
in the button1 property Text = “Apply“.

In additional, we change the size of controls button1 and textBox1 for more demonstrative view.

After the changes, window of form is looks like (Fig. 18).

Fig. 18. The form window after forming the controls in groupBox1

⇑

5.6. Designing controls, which are in the group “Transportation“

Thus (p. 5.5), we form the group “Transportation” (groupBox2). From the panel Toolbox we place two controls of type “Button” and we form their properties “Text” as shown in Figure 19.

Two objects of type “Button”, which are named “button2” and “button3”, will be created.

The property “Text” of object button2, we set to value “Add…“. The property “Text” of object button3, we set to value “Delete“.

Fig. 19. Design of controls from group “Transportation“

⇑

5.7. Designing controls, which are located to group “View“

From panel “Toolbox“, we place 5 controls of type “Button” in the area of group “View” (object groupbox3). We correct size of these controls.

As a result, the 5 objects named as “button4”, “button5”, “button6”, “button7” and “button8” will be created.

We form the property “Text” of each of these objects:

in object button4 property Text = “Tickets”;
in object button5 property Text = “Routes”;
in object button6 property Text = “Buses”;
in object button7 property Text = “Drivers”;
in object button8 property Text = “Dispatchers”.

After designing, the application form with the formed group “View”, will have the view as shown in Figure 20.

Fig. 20. Controls of group “View“

⇑

5.8. Designing controls, which are placed on the group “Commands“

In group “Commands”, we place two controls of type Button.

Two objects which are named “button9” and “button10”, will be created. Set the property “Text” of these objects to values “Add…” and “Delete“.

As a result, the application form will be as shown in Figure 21.

Fig. 21. The application form after placing of all controls

⇑

6. Forming the list in control comboBox1

Control (object) comboBox1 from group “Filter” must include the fields, for which will be applied the filter with the purpose more convenient output of data from database.

In the future, we will apply the filter to such fields:

number of transportation;
number of route;
point of destination;
passenger;
driver.

To call the process of forming the list, we need to call the editor of strings of control “comboBox1” (Fig. 22). To do it, after activation (selecting) comboBox1 we need to click on the button “>” (right arrow). The menu, in which we select “Edit Items…“, will be opened.

Fig. 22. Calling the menu which forms the list in comboBox1

As a result, the window “String Collection Editor“, will be opened. In this window, we type the fields names, where the filter will be set.

Fig. 23. The window of string editor of control “comboBox1“

After that, when we press on the “OK“, the strings of control “comboBox1” will be formed.

⇑

7. Getting the property “ConnectionString“

In the following program codes, where we will operate with database, the property “ConnectionString” is used. You need to read this property correctly.

The property “ConnectionString” consists information about data provider and where located the file of database on the drive.

To get the text of property “ConnectionString” you need execute such steps (Fig. 24):

in the “Server Explorer” window, select database “02_02_00_009_Baza_en.mdb“;
in the “Properties” window, select the property “ConnectionString” and copy it to the Clipboard.

Fig 24. Reading the property “ConnectionString”

In our case, the value of “ConnectionString” is equal:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Programs\C_SHARP\Program_02_02_00_009e\WindowsFormsApplication1\WindowsFormsApplication1\02_02_00_009_Baza_en.mdb

It is necessary to note the following. When you will use the string “ConnectionString” in the code at C#, you need to replace the symbol “\” to symbol “\\“, according to syntax of C#.

⇑

8. Adding the internal variables of general use in form “Form1.cs“

The class “Form1” corresponds to main form of application. When we writes program code, we will use some internal variables of general use. These variables are “Connection String” and “act_table“.

First variable will contain the string of connection to database “Connection String“.

Second variable contains the number of active table, which will be showed to “dataGridView2“. The correspondence between the numbers and the database tables is the following:

1 – table “Ticket“;
2 – table “Route“;
3 – table “Bus“;
4 – table “Driver“;
5 – table “Dispatcher“.

Let’s add the text of “ConnectionString” to text of class of main form of application.

Using “Solution Explorer” we open the file “Form1.cs” with the help of command “View Code” (Fig. 25).

Fig. 25. Command “View code“

As a result, the tab with program code will be created. This code is described into file “Form1.cs“.

Listing of file of form is following:

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 WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
    }
}

Let’s modify this listing in such a way, that two variables ConnectionString and act_table were added to it. The variable “ConnectionString” is described as “public“, because it will be used in other classes. For convenience of displaing in the design window, we breake the string of variable “ConnectionString” at the parts.

string ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +       "DataSource=C:\\Programs\\C_SHARP\\Program_02_02_00_009e\\" +  "WindowsFormsApplication1\\WindowsFormsApplication1\\02_02_00_009_Baza_en.mdb";

private int act_table = 1; // active table: 1-tickets,2-routes,3-buses,4-drivers,5-dispatchers

Listing of class “Form1” of main form of application has the view:

...
public partial class Form1 : Form
{
  string ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
    "DataSource=C:\\Programs\\C_SHARP\\Program_02_02_00_009e\\" +            "WindowsFormsApplication1\\WindowsFormsApplication1\\02_02_00_009_Baza_en.mdb";
  private int act_table = 1; // active table: 1-tickets,2-routes,3-buses,4-drivers,5-dispatchers
  
  public Form1()
  {
    InitializeComponent();
  }
}
...

⇑

9. The output of data in dataGridView1

Data, which we need to show in the control dataGridView1, will be formed from different tables. To filtering data, we need read the string, that is selected in comboBox1 and read the mask from textBox1. In the control comboBox1, user can select the field, which indicates the field of data filtering.

⇑

9.1. The text of SQL-query

In the next table are displayed fields, which will be shown in dataGridView1.

The fields, which must be displayed in according to syntax of SQL-language are following:

[Transportation].[Number]
[Route].[Number]
[Route].[Destination]
[Route].[Dispatch time]
[Route].[Arrival time]
[Ticket].[Place]
[Ticket].[Name]
[Ticket].[Cost]
[Driver].[Name]

To create new table the SQL-query is following:

SELECT
  [Transportation].[Number]
  [Route].[Number]
  [Route].[Destination]
  [Route].[Dispatch time]
  [Route].[Arrival time]
  [Ticket].[Place]
  [Ticket].[Name]
  [Ticket].[Cost]
  [Driver].[Name]
FROM
  [Transportation],
  [Route],
  [Ticket],
  [Driver]
WHERE
  ([Transportation].[ID_Route]=[Route].[ID_Route]) AND
  ([Transportation].[ID_Ticket] = [Ticket].[ID_Ticket]) AND
  ([Transportation].[ID_Driver] = [Driver].[ID_Driver])

This request ignores filter. To take into account the filter in the query, you need to add the appropriate line. In the application this string will be added programmatically. For example, to add filter by number of transportation you need add the following string:

 AND ([Transportation].[Number] = '" + textBox1.Text + "')

where textbox1.Text is the text of filter in the control textBox1.

⇑

9.2. Programming the event handler of clicking on the button “Apply”

We can call the data output using filter by clicking on the button “Apply”. Process of event programming in MS Visual Studio is described here in details. The output of database in the control dataGridView is described here.

We program the event of click on the button “Apply”.

Event handler of clicking on the button “Apply” is the following.

private void button1_Click(object sender, EventArgs e)
{
  string CommandText = "SELECT " +
                "[Transportation].[Number], " +
                "[Route].[Number], " +
                "[Route].[Destination], " +
                "[Route].[Dispatch time], " +
                "[Route].[Arrival time], " +
                "[Ticket].[Place], " +
                "[Ticket].[Name], " +
                "[Ticket].[Cost], " +
                "[Driver].[Name] " +
              "FROM " +
                "[Transportation], " +
                "[Route], " +
                "[Ticket], " +
                "[Driver] " +
              "WHERE " +
                "([Transportation].[ID_Route] = [Route].[ID_Route]) AND " +
                "([Transportation].[ID_Ticket] = [Ticket].[ID_Ticket]) AND " +
                "([Transportation].[ID_Driver] = [Driver].[ID_Driver]) ";

            if (textBox1.Text != "")  // if you typed the text in the filter field
            {
                if (comboBox1.SelectedIndex == 0) // Number of transportation
                    CommandText = CommandText + " AND ([Transportation].[Number] = '" + textBox1.Text + "')";
                if (comboBox1.SelectedIndex == 1) // Number of route
                    CommandText = CommandText + " AND ([Route].[Number] = '" + textBox1.Text + "') ";
                if (comboBox1.SelectedIndex == 2) // Destination
                    CommandText = CommandText + " AND ([Route].[Destination] LIKE '" + textBox1.Text + "%') ";
                if (comboBox1.SelectedIndex == 3) // Passenger
                    CommandText = CommandText + " AND ([Ticket].[Name] LIKE '" + textBox1.Text + "%') ";
                if (comboBox1.SelectedIndex == 4) // Driver
                    CommandText = CommandText + " AND ([Driver].[Name] LIKE '" + textBox1.Text + "%') ";
            }

            OleDbDataAdapter dataAdapter = new OleDbDataAdapter(CommandText, ConnectionString);
            DataSet ds = new DataSet();
            dataAdapter.Fill(ds, "[Transportation]");
            dataGridView1.DataSource = ds.Tables["[Transportation]"].DefaultView;
}

First, in the variable CommandText, is added the string of SQL-query, which displays all data from database. Then, according to filter value, in the CommandText is appended corresponding string, which finishes SQL-command.

After building CommandText string, the object of type OleDbDataAdapter is created, which forwards the data sets from the calling process and back. Data adapters contains the set from four internal command objects. It is commands of reading, inserting, changing and deleting information. As we see from the program code, the constructor of object is getting the string as input parameters of query at the SQL-language and string of connecting to database. So, the object of data adapter is connected to our database.

After creating data adapter (OleDbDataAdapter) we create the object of type DataSet (data set):

DataSet ds = new DataSet();

The data set is a kind of intermediate buffer for data that can be displayed. A data set is a convenient mechanism for reading and updating data and encapsulates a set of tables and relationships between them.

The following command – is filling of data set (variable ds) by values of records from database based on SQL-query, which is placed in data adapter ds with the help of method Fill():

dataAdapter.Fill(ds, "[Transportation]");

To display data, which were based on SQL-query, it is needed that the property DataSource of control dataGridView1 referred on the some table of dataset ds. For example, table “Transportation”. The program code of operation of displaying data is the following:

dataGridView1.DataSource = ds.Tables["[Transportation]"].DefaultView;

After that, data from table “Transportation” will showed on the form.

⇑

9.3. Adding the method button1_Click() into method Form1_Load()

Method Form1_Load() is called when form is loading. Append into this method calling of method of event handling button1_Click().

At present, listing of method is the following.

private void Form1_Load(object sender, EventArgs e)
{
  comboBox1.SelectedIndex = 0;
  button1_Click(sender, e);
}

⇑

10. Building the form of adding of transportation and creating a program code

To add a new transportation you need to click “Add” button from “Transportation” group. To create a fields, which are used in the displaying of transportation (control dataGridView1), you need to create corresponding form.

10.1. Creating form “Form2.cs”

The process of creating a new form in MS Visual Studio is described here in details. To create the new form you need run the command:

Project -> Add Windows Form...

In the opened window, you need to select “Windows Form”. The name of form we leave as “Form2.cs”.

As a result, in “Solution Explorer” we get the files “Form2.cs” and “Form2.Designer.cs”.

At the Figure 26 is displayed the form, which shows data for transportation.

Fig. 26. Creating the form “Form2.cs“

Resize the form “Form2.cs” and place on the form controls as shown at the Figure 27. The names of controls, which will be used in program code, are pointed by red color.

Fig. 27. Form “Form2.cs” with the description of controls names.

In general, form “Form2.cs” is shown at Figure 28.

Fig. 28. Form “Form2.cs“

After creating the form, class of form named as “Form2” will be created. Listing of describing of class of form “Form2” is following:

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 WindowsFormsApplication1
{
    public partial class Form2 : Form
    {
        public Form2()
        {
            InitializeComponent();
        }
}

⇑

10.2. Programming of event handlers of clicking on the buttons “Add” and “Cancel”

Call the event “Click”, which is generated when user clicks on the button “Add” of form “Form2“.

The process of programming of new event is described here.

As a result, in the form class will be generated code. In this code, you need to insert the following text of event handler:

private void button3_Click(object sender, EventArgs e)
{
  this.DialogResult = DialogResult.OK;
}

Listing of event handler of clicking on the button “Cancel”:

private void button4_Click(object sender, EventArgs e)
{
    this.DialogResult = DialogResult.No;
}

⇑

10.3. Programming of methods of data displaying in controls dataGridView1, dataGridView2, dataGridView3, dataGridView4, dataGridView5.

To access to the methods and properties, which are used for work with databases, you need to include namespace “Form2.cs” (see p.4.2):

using System.Data.OleDb;

To display data in controls of DataGridView type, we create additional method Get_Table(), which gets two input parameters. Parameter table_name specifies the table name in database. Parameter num_dG specifies the number of control of type DataGridView – from 1 to 5. For example, if “num_dG=2“, it means that are displayed only data of control dataGridView2.

Listing of method Get_Table() is following:

private void Get_Table(string table_name, int num_dG)
{
      Form1 f1 = new Form1();
      string CommandText = "SELECT * FROM ";
      CommandText += table_name;
      OleDbDataAdapter dataAdapter = new OleDbDataAdapter(CommandText, f1.ConnectionString);
      DataSet ds = new DataSet();
      dataAdapter.Fill(ds, table_name);
      if (num_dG == 1) dataGridView1.DataSource = ds.Tables[table_name].DefaultView;
      if (num_dG == 2) dataGridView2.DataSource = ds.Tables[table_name].DefaultView;
      if (num_dG == 3) dataGridView3.DataSource = ds.Tables[table_name].DefaultView;
      if (num_dG == 4) dataGridView4.DataSource = ds.Tables[table_name].DefaultView;
      if (num_dG == 5) dataGridView5.DataSource = ds.Tables[table_name].DefaultView;
}

Next step, we program the event Load, which is called when form “Form2” is loading. For this event, will be called the corresponding method Form2_Load().

Listing of method Form2_Load() is following:

private void Form2_Load(object sender, EventArgs e)
{
    Get_Table("Route", 1); // fill the table “Route”
    Get_Table("Ticket", 2);   // fill the table “Ticket”
    Get_Table("Driver", 3);
    Get_Table("Bus", 4);
    Get_Table("Dispatcher", 5);
    textBox1.Text = "0";
    comboBox1.SelectedIndex = 0;
    comboBox2.SelectedIndex = 0;
}

In Form2_Load() method will be called one by one Get_Table() method, which fills all 5 controls of DataGridView type.

⇑

10.4. Programming of event handlers of changing of active cell in controls dataGridView1, dataGridView2, dataGridView3, dataGridView4, dataGridView5

When you change active cell in controls of DataGridView type, you need to output the corresponding text in controls (see Figure 27):

label2 (Route);
label3 (Ticket);
label4 (Driver);
label5 (Bus);
label6 (Dispatcher).

When you change the active cell in controls of DataGridView type the event CellEnter is generated (Figure 29).

Fig. 29. The event CellEnter of control dataGridView1

Code listing of event handlers CellEnter for controls dataGridView1, dataGridView2, dataGridView3, dataGridView4, dataGridView5 is the following.

private void dataGridView1_CellEnter(object sender, DataGridViewCellEventArgs e)
{
  int row;
  // We take data from cells of table “Route”
  row = dataGridView1.CurrentCell.RowIndex;
  label2.Text = "Route - " + "#" + Convert.ToString(dataGridView1[1, row].Value) +
       "/" + Convert.ToString(dataGridView1[2, row].Value) +
       "/" + Convert.ToString(dataGridView1[3, row].Value) +
       "/" + Convert.ToString(dataGridView1[4, row].Value) +
       "/" + Convert.ToString(dataGridView1[7, row].Value) +
      " - " + Convert.ToString(dataGridView1[8, row].Value);
}

private void dataGridView2_CellEnter(object sender, DataGridViewCellEventArgs e)
{
  int row;
  // We take data from table “Ticket”
  row = dataGridView2.CurrentCell.RowIndex;
  label3.Text = "Ticket: place # " + Convert.ToString(dataGridView2[1, row].Value) +
      " / " + Convert.ToString(dataGridView2[2, row].Value) +
      " / " + Convert.ToString(dataGridView2[3, row].Value) +
      " / " + Convert.ToString(dataGridView2[4, row].Value) +
      " / " + Convert.ToString(dataGridView2[5, row].Value) +
      " / " + Convert.ToString(dataGridView2[6, row].Value);
}

private void dataGridView3_CellEnter(object sender, DataGridViewCellEventArgs e)
{
  int row;
  // Data about driver
  row = dataGridView3.CurrentCell.RowIndex;
  label4.Text = "Driver: " + Convert.ToString(dataGridView3[1, row].Value);
}

private void dataGridView4_CellEnter(object sender, DataGridViewCellEventArgs e)
{
  int row;
  // Information about the bus
  row = dataGridView4.CurrentCell.RowIndex;
  label5.Text = "Bus: # " + Convert.ToString(dataGridView4[1, row].Value) 
  " / " + Convert.ToString(dataGridView4[2, row].Value) +
  " / " + Convert.ToString(dataGridView4[3, row].Value) +
  " / " + Convert.ToString(dataGridView4[4, row].Value);
}

private void dataGridView5_CellEnter(object sender, DataGridViewCellEventArgs e)
{
  int row;
  // Information about the dispatcher
  row = dataGridView5.CurrentCell.RowIndex;
  label6.Text = "Dispatcher: " + Convert.ToString(dataGridView5[1, row].Value);
}

⇑

10.5. Building the strings for filter in controls comboBox1 and comboBox2

In the control comboBox1, user can select the fields from table “Route”, which will be used to filter data.

The process of forming of strings in the controls of type ComboBox is described in p. 6.

We form such strings in comboBox1:

Route number;
Destination;
District;
Region.

In control comboBox2, we are forming such strings:

Place;
Name of passenger.

⇑

10.6. Programming of filter to table “Route”

In the form “Form2” you can apply the filter to tables “Route” and “Ticket”.

Applying the filter to table “Route” is realized with the help of component comboBox1, textBox2 and button1.

Code listing of event handler method of clicking on the button “Apply” is the following:

private void button1_Click(object sender, EventArgs e)
{
  // Filter to the table "Route"
  string CommandText = "SELECT * FROM [Route]";

  if (textBox2.Text == "")
    CommandText = "SELECT * FROM [Route]";
  else
  if (comboBox1.SelectedIndex == 0) 
    CommandText = "SELECT * FROM [Route] WHERE [Number] = '" + textBox2.Text + "'"; 
  else
  if (comboBox1.SelectedIndex == 1) 
    CommandText = "SELECT * FROM [Route] WHERE [Destination] LIKE '" + textBox2.Text + "%'";
  else
  if (comboBox1.SelectedIndex == 2) 
    CommandText = "SELECT * FROM [Route] WHERE [District] LIKE '" + textBox2.Text + "%'";
  else
  if (comboBox1.SelectedIndex == 3) 
    CommandText = "SELECT * FROM [Route] WHERE [Region] LIKE '" + textBox2.Text + "%'";

  Form1 f = new Form1();

  OleDbDataAdapter dataAdapter = new OleDbDataAdapter(CommandText, f.ConnectionString);
  DataSet ds = new DataSet();
  dataAdapter.Fill(ds, "[Route]");
  dataGridView1.DataSource = ds.Tables[0].DefaultView;
}

⇑

10.7. Programming of filter to table “Ticket”

Filter for table “Ticket” applies when you pressed the button “Apply” in the group “Ticket”.

Control of this button is named “button2”. Also, for the filter you need to use controls comboBox2 and textBox3.

In textBox3 field you need to specify the filter value. In the comboBox2 you need to specify the field from table “Ticket”, for which we are using the filter.

private void button2_Click(object sender, EventArgs e)
{
  // Filter to the table "Ticket"
  string CommandText = "SELECT * FROM [Ticket]";

  if (textBox3.Text == "")
    CommandText = "SELECT * FROM [Ticket]";
  else
  if (comboBox2.SelectedIndex == 0)
    CommandText = "SELECT * FROM [Ticket] WHERE [Place] = " + textBox3.Text;
  else
  if (comboBox2.SelectedIndex == 1) //
    CommandText = "SELECT * FROM [Ticket] WHERE [Name] LIKE '" + textBox3.Text + "%'";

  Form1 f = new Form1();
  OleDbDataAdapter dataAdapter = new OleDbDataAdapter(CommandText, f.ConnectionString);
  DataSet ds = new DataSet();
  dataAdapter.Fill(ds, "[Ticket]");
  dataGridView2.DataSource = ds.Tables[0].DefaultView;
}

⇑

10.8. Setting the access to some fields of Form2

To read from Form1 the values of some controls of Form2, you need to change the directive of visibility from “private” to “public”. To realize this we need to call file “Form2.Designer.cs” (Fig. 30).

Fig. 30. File “Form2.Designer.cs”

As a result, we get the text of file, which is the following:

namespace WindowsFormsApplication1
{
   partial class Form2
    {
        ...
        private System.Windows.Forms.Label label1;
        private System.Windows.Forms.TextBox textBox1;
        private System.Windows.Forms.GroupBox groupBox1;
        private System.Windows.Forms.Button button1;
        private System.Windows.Forms.TextBox textBox2;
        private System.Windows.Forms.ComboBox comboBox1;
        private System.Windows.Forms.GroupBox groupBox2;
        private System.Windows.Forms.Button button2;
        private System.Windows.Forms.TextBox textBox3;
        private System.Windows.Forms.ComboBox comboBox2;
        private System.Windows.Forms.GroupBox groupBox3;
        private System.Windows.Forms.Label label6;
        private System.Windows.Forms.Label label5;
        private System.Windows.Forms.Label label4;
        private System.Windows.Forms.Label label3;
        private System.Windows.Forms.Label label2;
        private System.Windows.Forms.Label label7;
        private System.Windows.Forms.DataGridView dataGridView1;
        private System.Windows.Forms.DataGridView dataGridView2;
        private System.Windows.Forms.Label label8;
        private System.Windows.Forms.Label label9;
        private System.Windows.Forms.DataGridView dataGridView3;
        private System.Windows.Forms.Label label10;
        private System.Windows.Forms.DataGridView dataGridView4;
        private System.Windows.Forms.Label label11;
        private System.Windows.Forms.DataGridView dataGridView5;
        private System.Windows.Forms.Button button3;
        private System.Windows.Forms.Button button4;
    }
}

Changing the access from “private” to “public” for such properties:

textBox1 (Number of transportation);
dataGridView1;
dataGridView2;
dataGridView3;
dataGridView4;
dataGridView5;

Thus, the code snippet of file “Form2.Designer.cs” is following:

namespace WindowsFormsApplication1
{
    ...
    partial class Form2
    {
        private System.Windows.Forms.Label label1;
        public System.Windows.Forms.TextBox textBox1;
        private System.Windows.Forms.GroupBox groupBox1;
        private System.Windows.Forms.ComboBox comboBox1;
        private System.Windows.Forms.Button button1;
        private System.Windows.Forms.TextBox textBox2;
        private System.Windows.Forms.GroupBox groupBox2;
        private System.Windows.Forms.ComboBox comboBox2;
        private System.Windows.Forms.Button button2;
        private System.Windows.Forms.TextBox textBox3;
        private System.Windows.Forms.GroupBox groupBox3;
        private System.Windows.Forms.Label label6;
        private System.Windows.Forms.Label label5;
        private System.Windows.Forms.Label label4;
        private System.Windows.Forms.Label label3;
        private System.Windows.Forms.Label label2;
        public System.Windows.Forms.DataGridView dataGridView1;
        public System.Windows.Forms.DataGridView dataGridView2;
        public System.Windows.Forms.DataGridView dataGridView3;
        public System.Windows.Forms.DataGridView dataGridView4;
        public System.Windows.Forms.DataGridView dataGridView5;
        private System.Windows.Forms.Button button3;
        private System.Windows.Forms.Button button4;
        private System.Windows.Forms.Label label7;
        private System.Windows.Forms.Label label8;
        private System.Windows.Forms.Label label9;
        private System.Windows.Forms.Label label10;
        private System.Windows.Forms.Label label11;
    }
}

This completes the creation the form “Form2.cs“.

⇑

10.9. Listing of file “Form2.cs”

In abbreviated form, the text of “Form2.cs” file 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;
using System.Data.OleDb;

namespace WindowsFormsApplication1
{
    public partial class Form2 : Form
    {
        public Form2()
        {
            InitializeComponent();
        }

        private void button3_Click(object sender, EventArgs e)
        {
          ...
        }

        private void button4_Click(object sender, EventArgs e)
        {
          ...
        }

        private void Get_Table(string table_name, int num_dG)
        {
          ...
        }

        private void Form2_Load(object sender, EventArgs e)
        {
            ...
        }

        private void dataGridView1_CellEnter(object sender, DataGridViewCellEventArgs e)
        {
            ...
        } 

        private void dataGridView2_CellEnter(object sender, DataGridViewCellEventArgs e)
        {
            ...
        }

        private void dataGridView3_CellEnter(object sender, DataGridViewCellEventArgs e)
        {
            ...
        }

        private void dataGridView4_CellEnter(object sender, DataGridViewCellEventArgs e)
        {
            ...
        }

        private void dataGridView5_CellEnter(object sender, DataGridViewCellEventArgs e)
        {
            ...
        }

        private void button1_Click(object sender, EventArgs e)
        {
            ...
        }

        private void button2_Click(object sender, EventArgs e)
        {
            ...
        }
     }
}

⇑

11. Programming of event of calling form “Form2” from “Form1”

The form “Form2” is shown, when user clicks on the button “Add” from group “Transportation” (see Fig. 21). The control of type “button”, which corresponds to command “Add…” is named button2.

Code listing of calling the form “Form2” from “Form1” is following:

private void button2_Click(object sender, EventArgs e)
{
  string CommandText;
  string num_per, ID_M, ID_B, ID_D, ID_A, ID_V;
  int row;


  Form2 f = new Form2(); // creating a new form

  if (f.ShowDialog() == DialogResult.OK)
  {
    // adding data
    if (f.textBox1.Text == "") num_per = "0";
    else num_per = f.textBox1.Text;
    row = f.dataGridView1.CurrentCell.RowIndex; // get the string from dataGridView1
    ID_M = Convert.ToString(f.dataGridView1[0, row].Value);
    row = f.dataGridView2.CurrentCell.RowIndex; // get the string from dataGridView2
    ID_B = Convert.ToString(f.dataGridView2[0, row].Value);
    row = f.dataGridView3.CurrentCell.RowIndex; // get the string from dataGridView3
    ID_D = Convert.ToString(f.dataGridView3[0, row].Value);
    row = f.dataGridView4.CurrentCell.RowIndex; 
    ID_A = Convert.ToString(f.dataGridView4[0, row].Value);
    row = f.dataGridView5.CurrentCell.RowIndex; 
    ID_V = Convert.ToString(f.dataGridView5[0, row].Value);

    // forming CommandText
    CommandText = "INSERT INTO [Transportation] ([Number], ID_Route, ID_Ticket, ID_Dispatcher, ID_Bus, ID_Driver) " +
    "VALUES (" + num_per + ", " + ID_M + ", " + ID_B + ", " +
             ID_D + ", " + ID_A + ", " + ID_V + ")";

    // executing the SQL-command
    My_Execute_Non_Query(CommandText);
    button1_Click(sender, e);
  }
}

In the method button2_Click() is called method My_Execute_Non_Query(), code listing of which is following:

// executing the SQL-query for commands INSERT, UPDATE and DELETE
public void My_Execute_Non_Query(string CommandText)
{
  OleDbConnection conn = new OleDbConnection(ConnectionString);
  conn.Open();
  OleDbCommand myCommand = conn.CreateCommand();
  myCommand.CommandText = CommandText;
  myCommand.ExecuteNonQuery();
  conn.Close();
}

In the method My_Execute_Non_Query() is created the object, which realizes the connection with database. Then the command, which changes database, is executed. To do this you need to use method ExecuteNonQuery().

All commands at the SQL-language is divided on two types:

commands that read data from database;
commands that change data into database.

After that, you can test the application. You can call the “Add…” command from group “Transportation” and you can add the string of transportation in the table, which is displayed in dataGridView1.

⇑

12. Code listing of “Form1.cs” module

At the moment, code listing of “Form1.cs” file in the abbreviated form is the following:

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;
using System.Data.OleDb;

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public string ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
               "Data Source=C:\\Programs\\C_SHARP\\Program_02_02_00_009e\\" +
               "WindowsFormsApplication1\\WindowsFormsApplication1\\02_02_00_009_Baza_en.mdb";
        private int act_table = 1; // active table: 1-tickets,2-routes,3-buses,4-drivers,5-dispatchers

        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
             ...
        }

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

        private void button2_Click(object sender, EventArgs e)
        {
             ...
        }

        // executing the SQL-query for commands INSERT, UPDATE and DELETE
        public void My_Execute_Non_Query(string CommandText)
        {
             ...
        }
    }
}

⇑

13. Creating the form of deleting the record in dataGridView1

When user selects button “Delete” (button3) from group “Transportation”, you need to program the method of event handler of click on this button (Fig. 31).

Fig. 31. Button “Delete” of main form “Form1.cs”

We create the form by a standard way. The process of creating the form in MS Visual Studio C# is described here in details.

As a result, in “Solution Explorer” we get files “Form3.cs” and “Form3.Designer.cs”.

Previously you need to prepare the form by the pattern, as shown in Figure 32.

When we are creating the form we need to set such properties of form:

property “Text” is set to value “Deleting of transportation”;
property FormBorderStyle = “Fixed3D”;
property StartPosition = “CenterScreen”;
property ControlBox = false.

We are placing on the form following controls:

the control of “Label” type (text “Do you really want to delete this record?”);
two controls of “Button” type (buttons “Yes” and “No”).

Fig. 32. Confirmation form of deleting the transportation

In the “Form3.cs”, we are programming the event handler of clicking on the button “Yes” by a standard way:

private void button1_Click(object sender, EventArgs e)
{
  this.DialogResult = DialogResult.OK;
}

Also, we are programming the event of clicking on the button “No”:

private void button2_Click(object sender, EventArgs e)
{
  this.DialogResult = DialogResult.No;
}

⇑

14. Programming the event of calling of command “Delete” from group “Transportation”

When user calls the command “Delete” from group “Transportation” then must be called the form “Form3.cs”.

Code listing of method of event handler is following:

private void button3_Click(object sender, EventArgs e)
{
  Form3 f = new Form3();

  if (f.ShowDialog() == DialogResult.OK)
  {
    int index, index_old;
    string ID;
    string CommandText = "DELETE FROM ";
    index = dataGridView1.CurrentRow.Index; // number in the view table
    index_old = index;
    ID = Convert.ToString(dataGridView1[0, index].Value); // ID is as string

    // Forming the CommandText
    CommandText = "DELETE FROM [Transportation] WHERE [Transportation].[Number] = '" + ID + "'";

    // executing the SQL-query
    My_Execute_Non_Query(CommandText);

    // repaint dbGridView1
    button1_Click(sender, e);

    if (index_old >= 0)
    {
      dataGridView1.ClearSelection();
      dataGridView1[0, index_old].Selected = true;
    }
  }
}

⇑

15. Programming of commands from group “View”

15.1. Viewing of table “Tickets” in dataGridView2

To display table “Ticket” in dataGridView2, user must click on the button “Tickets” (button4) from group “View” (Fig. 33).

Fig. 33. The command of viewing of purchased tickets

Listing of additional function Get_Tickets() and event handler button4_Click() is the following:

// read all fields from table “Ticket”
private void Get_Tickets() 
{
  string CommandText = "SELECT [ID_Ticket], [Place], [Cost], [Time], [Name], [Passport], [Privileges] FROM [Ticket]";
  OleDbDataAdapter dataAdapter = new OleDbDataAdapter(CommandText, ConnectionString);

  // creating DataSet object
  DataSet ds = new DataSet();
  // fill data from table “Ticket” in dataGridView1
  dataAdapter.Fill(ds, "[Ticket]");
  dataGridView2.DataSource = ds.Tables[0].DefaultView;
  dataGridView2.Columns[0].Visible = false; // Hide the field “ID_Bilets”
}

// Click on the button "Tickets"
private void button4_Click(object sender, EventArgs e)
{
  Get_Tickets();
  act_table = 1;
}

Also, we add the call of function Get_Tickets() into the method Form1_Load(). So, listing of method Form1_Load() is following:

private void Form1_Load(object sender, EventArgs e)
{
  comboBox1.SelectedIndex = 0;
  button1_Click(sender, e);
  Get_Tickets();
}

Now, you can test the application.

⇑

15.2. Viewing of table “Route” in dataGridView2

The output of table “Route” in dataGridView2 is carries out with the help of command “Routes” (button5).

Listing of additional function Get_Routes() and event handler button5_Click() is the following:

private void Get_Routes() 
{
  string CommandText = "SELECT * FROM [Route]";
  OleDbDataAdapter dataAdapter = new OleDbDataAdapter(CommandText, ConnectionString);
  DataSet ds = new DataSet(); 
  dataAdapter.Fill(ds, "[Route]");
  dataGridView2.DataSource = ds.Tables[0].DefaultView;
  dataGridView2.Columns[0].Visible = false;
}

private void button5_Click(object sender, EventArgs e)
{
  Get_Routes();
  act_table = 2;
}

⇑

15.3. Viewing of table “Bus” in dataGridView2

To output table “Bus” in dataGridView2 you need to call the command “Buses” (button6).

The listing of additional method Get_Bus() and event handler of clicking on the button “Buses” is following:

private void Get_Bus() 
{
  string CommandText = "SELECT * FROM [Bus]";
  OleDbDataAdapter dataAdapter = new OleDbDataAdapter(CommandText, ConnectionString);
  DataSet ds = new DataSet(); 
  dataAdapter.Fill(ds, "[Bus]");
  dataGridView2.DataSource = ds.Tables[0].DefaultView;
  dataGridView2.Columns[0].Visible = false; // hide the field ID_Bus
}

private void button6_Click(object sender, EventArgs e)
{
  Get_Bus();
  act_table = 3;
}

⇑

15.4. Viewing of table “Driver” in dataGridView2

Table “Driver” is output in dataGridView2 when user presses on the button “Drivers” (button7).

Listing of additional method Get_Driver() and event handler button7_Click() is the following:

private void Get_Driver() 
{
  string CommandText = "SELECT * FROM [Driver]";
  OleDbDataAdapter dataAdapter = new OleDbDataAdapter(CommandText, ConnectionString);
  DataSet ds = new DataSet();
  dataAdapter.Fill(ds, "[Driver]");
  dataGridView2.DataSource = ds.Tables[0].DefaultView;
  dataGridView2.Columns[0].Visible = false;
}

private void button7_Click(object sender, EventArgs e)
{
  Get_Driver();
  act_table = 4;
}

⇑

15.5. Viewing of table “Dispatcher” in dataGridView2

To output table “Dispatcher” in control dataGridView2 you need to select the command “Dispatchers”.

Listing of additional method Get_Dispatcher() and event handler button8_Click() is the following:

private void Get_Dispatcher()
{
  string CommandText = "SELECT * FROM [Dispatcher]";
  OleDbDataAdapter dataAdapter = new OleDbDataAdapter(CommandText, ConnectionString);
  DataSet ds = new DataSet();
  dataAdapter.Fill(ds, "[Dispatcher]");
  dataGridView2.DataSource = ds.Tables[0].DefaultView;
  dataGridView2.Columns[0].Visible = false;
}

private void button8_Click(object sender, EventArgs e)
{
  Get_Dispatcher();
  act_table = 5;
}

⇑

16. Building of forms, which add data into the tables

16.1. Building the form, which add a new ticket

Creating a new form in MS Visual Studio C# is described here in details.

We add a new form. We create the form, which adds a new ticket using the example that shown in Figure 34.

Files, which correspond this form are “Form4.cs” and “Form4.Designer.cs”.

Set the following properties of form:

property Text = “Tickets”;
property FormBorderStyle = “Fixed3D”;
property ControlBox = “false” (hide the system menu);
property StartPosition = “CenterScreen” (the window is displayed at the center of screen).

Place the controls on the form. Meaningful controls have such names:

textBox1 – field “Place” in the table “Ticket”;
textBox2 – field “Cost”;
textBox3 – “Time”;
textBox4 – “Name”;
textBox5 – “Passport”;
checkBox1 – “Privileges”;
button1 – button “Add”;
button2 – button “Cancel”.

Fig. 34. The form of adding a new ticket

After creating the form, you need to set the visibility outside for some fields of class Form4. To do it we go to the file “Form4.Designer.cs” (see Figure 30 for the form “Form2.cs”).

We set the access public for meaningful fields of form “Form4.cs” – textBox1, textBox2, textBox3, textBox4, textBox5, checkBox1. We can do this by another way simply changing the field “Modifiers” in the window “Properties”.

The snippet of corresponding code of class Form4 is following:

partial class Form4
{
  ...
  #endregion
  private System.Windows.Forms.Label label1;
  public System.Windows.Forms.TextBox textBox1;
  private System.Windows.Forms.Label label2;
  public System.Windows.Forms.TextBox textBox2;
  private System.Windows.Forms.Label label3;
  private System.Windows.Forms.Label label4;
  public System.Windows.Forms.TextBox textBox3;
  private System.Windows.Forms.Label label5;
  public System.Windows.Forms.TextBox textBox4;
  public System.Windows.Forms.TextBox textBox5;
  public System.Windows.Forms.CheckBox checkBox1;
  private System.Windows.Forms.Button button1;
  private System.Windows.Forms.Button button2;
}

We program the event of clicking on the buttons “button1” and “button2” in the same way as described in p. 11.2.

Listing of event handlers is following:

private void button1_Click(object sender, EventArgs e)
{
  this.DialogResult = DialogResult.OK;
}

private void button2_Click(object sender, EventArgs e)
{
  this.DialogResult = DialogResult.No;
}

⇑

16.2. Creating the form which adds data in the table “Route”

Creating a new form in MS Visual Studio is described here in details.

Create the form as shown in Figure 35.

As a result, in Solution Explorer we get the files named “Form5.cs” and “Form5.Designer.cs”.

Set the following properties of form:

property Text = “Adding a route“;
property FormBorderStyle = “Fixed3D”;
property ControlBox = “false”;
property StartPosition = “CenterScreen”.

Meaningful controls are named as:

textBox1 – “Route number“;
textBox2 – “Destination”;
textBox3 – “District”;
textBox4 – “Region”;
textBox5 – “Distance”;
textBox6 – “Weight”.
dateTimePicker1 – dispatch time;
dateTimePicker2 – arrival time;
button1 – button “Add”;
button2 – button “Cancel”.

In controls “dateTimePicker1” and “dateTimePicker2”, we set the property “Format” into value “Time”.

Fig. 35. The form of adding a new route

Set the access “public” for meaningful fields of form “Form5.cs” – textBox1, textBox2, textBox3, textBox4, textBox5, textBox6, dateTimePicker1, dateTimePicker2.

Code snippet of class Form5 is following:

...
partial class Form5
{

  #endregion

  private System.Windows.Forms.Label label1;
  public System.Windows.Forms.TextBox textBox1;
  private System.Windows.Forms.Label label2;
  public System.Windows.Forms.TextBox textBox2;
  public System.Windows.Forms.TextBox textBox3;
  private System.Windows.Forms.Label label3;
  public System.Windows.Forms.TextBox textBox4;
  private System.Windows.Forms.Label label4;
  public System.Windows.Forms.TextBox textBox5;
  private System.Windows.Forms.Label label5;
  public System.Windows.Forms.TextBox textBox6;
  private System.Windows.Forms.Label label6;
  private System.Windows.Forms.Label label7;
  public System.Windows.Forms.DateTimePicker dateTimePicker1;
  private System.Windows.Forms.Label label8;
  public System.Windows.Forms.Button button1;
  private System.Windows.Forms.Button button2;
  public System.Windows.Forms.DateTimePicker dateTimePicker2;
}
...

We program the handler event of clicking on the buttons button1 and button2 in the same way, as described in p. 11.2.

Listing of event handlers is following:

private void button1_Click(object sender, EventArgs e)
{
  this.DialogResult = DialogResult.OK;
}

private void button2_Click(object sender, EventArgs e)
{
  this.DialogResult = DialogResult.No;
}

⇑

16.3. Creating the form which adds data into the table “Bus”

Creating a new form is described here in details.

The form, which needed to add a new vehicle, is shown at Figure 36. The files “Form6.cs” and “Form6.Designer.cs” correspond to this form.

We set such properties of the form:

property Text = “Add a bus“;
property FormBorderStyle = “Fixed3D”;
property ControlBox = “false”;
property StartPosition = “CenterScreen”.

Meaningful controls are named:

textBox1 (Number);
textBox2 (Model);
textBox3 (Plate number);
textBox4 (Seats).

Fig. 36. The form of adding a new bus

Set access “public” for meaningful fields of form “Form6.cs”: textBox1, textBox2, textBox3, textBox4.

...
partial class Form6
{
  ...

  private System.Windows.Forms.Label label1;
  public System.Windows.Forms.TextBox textBox1;
  private System.Windows.Forms.Label label2;
  public System.Windows.Forms.TextBox textBox2;
  public System.Windows.Forms.TextBox textBox3;
  private System.Windows.Forms.Label label3;
  public System.Windows.Forms.TextBox textBox4;
  private System.Windows.Forms.Label label4;
  private System.Windows.Forms.Button button1;
  private System.Windows.Forms.Button button2;
}

...

We program the event of clicking on the buttons “button1” and “button2” in the same way, as described in p. 11.2.

Listing of event handlers is following:

private void button1_Click(object sender, EventArgs e)
{
  this.DialogResult = DialogResult.OK;
}

private void button2_Click(object sender, EventArgs e)
{
  this.DialogResult = DialogResult.No;
}

⇑

16.4. Creating the form which adds a new driver

Form of adding a new driver is shown in Figure 37.

To the newly created form in “Solution Explorer” correspond two files: “Form7.cs”, “Form7.Designer.cs”.

We set properties of form in the same way as described above.

Meaningful controls are named:

textBox1 (Name);
textBox2 (Passport);
dateTimePicker1 (Birth date);
button1 – button “Add”;
button2 – button “Cancel”.

In the control dateTimePicker1 we set property “Format” into value “Short”.

Fig. 37. The form of adding data into table “Driver”

In the “Properties” window we set “public” access in property “Modifiers” (Fig. 38) for such controls:

textBox1 (Name);
dateTimePicker1 (Birth Date);
textBox2 (Passport).

Automatically, the file “Form7.Designer.cs” will be changed.

Fig. 38. Setting the access public to control textBox1

We program the event of clicking on the buttons “button1” and “button2” in the same way as described in p. 11.2.

Listing of event handlers is following:

private void button1_Click(object sender, EventArgs e)
{
  this.DialogResult = DialogResult.OK;
}

private void button2_Click(object sender, EventArgs e)
{
  this.DialogResult = DialogResult.No;
}

⇑

16.5. Creating the form which adds a new dispatcher

The form, in which we add a new dispatcher is shown in Figure 39. The files, which corresponds to the form are named “Form8.cs” and “Form8.Designer.cs”.

We set the properties of form in the same way as we did it in the previously forms.

Meaningful controls are named:

textBox1 (Name);
dateTimePicker1 (Birth Date);
textBox2 (Address).

Fig. 39. The form of adding data into the table “Dispatcher”

In the window “Properties”, we set the access “public” in property “Modifier” for such controls:

textBox1 (Name);
dateTimePicker1 (Birth Date);
textBox2 (Address).

Automatically, file “Form8.Designer.cs” will be changed.

We program the events of clicking on the buttons “button1” and “button2” in the same way as described above.

The program code of events handlers is following:

private void button1_Click(object sender, EventArgs e)
{
  this.DialogResult = DialogResult.OK;
}

private void button2_Click(object sender, EventArgs e)
{
  this.DialogResult = DialogResult.No;
}

⇑

17. Programming the methods of adding data into the tables “Ticket”, “Route”, “Bus”, “Driver”, “Dispatcher”

All of the following steps will be executed on the main form “Form1.cs” of application.

Therefore, first of all we activate the form “Form1.cs”.

17.1. Adding the data to the table “Ticket”

Listing of method Add_Ticket() of adding data to the table “Ticket” is following:

// adding the ticket through ConnectionString and query ExecutoNonQuery()
private void Add_Ticket(string place, string cost, DateTime time, string name, string passport, bool privileges)
{
  string CommandText;
  string s_time;
  string s_cost;

  s_time = Convert.ToString(time); // converting time to string
  s_cost = cost.Replace(',', '.'); // We change the separator '.' on ','
                                   // according to SQL-syntax.
  CommandText = "INSERT INTO [Ticket] ([Place], [Cost], [Time], [Name], [Passport], [Privileges]) " +
                 "VALUES ('" + place + "', " + s_cost + ", '" + s_time + "', '" +
                 name + "', '" + passport + "', " + privileges + ")";
  My_Execute_Non_Query(CommandText);
}

In the listing above, method Add_Ticket() gets such variables as input parameters: place, cost, time, name, passport, privileges. Then the SQL-query is formed. In this query, in the variable CommandText the command

INSERT INTO

is formed, which is executed in the method My_Execute_Non_Query().

⇑

17.2. Adding data into the table “Route”

Code listing of method Add_Route() is following.

private void Add_Route(string num_route, string destination, string district, string region, double distance, double weight, DateTime dispath_time, DateTime arrival_time)
{
  string CommandText;
  string s_disp_time, s_arr_time;
  string s_weight, s_distance;

  s_disp_time = Convert.ToString(dispatch_time); // Convert departure time to string.
  s_arr_time = Convert.ToString(arrival_time); // convert arrival time to string
  s_weight = Convert.ToString(weight);    // convert weight from double to string
  s_weight = s_weight.Replace(',','.'); // change a comma to a point according to SQL syntax
  s_distance = Convert.ToString(distance); // convert distance to string
  s_distance = s_distance.Replace(',', '.'); //

  CommandText = "INSERT INTO [Route] ([Number], [Destination], [District], [Region], [Distance], [Weight], [Dispatch time], [Arrival time])"
     + " VALUES ('" + num_route + "', '" + destination + "', '" + district + "', '" + region + "', "
     + s_distance + ", " + s_weight + ", '" + s_disp_time + "', '" + s_arr_time + "')";

  My_Execute_Non_Query(CommandText);
}

Method Add_Route() gets as input parameters the values, which corresponds to the fields of table “Route”.

⇑

17.3. Adding data into the table “Bus”

Code listing of method Add_Bus() is following.

void Add_Bus(string number, string model, string plate_number, string seats)
{
  string CommandText;
  CommandText = "INSERT INTO [Bus] ([Number], [Model], [Plate number], [Seats])"
  + " VALUES ('" + number + "', '" + model + "', '" + plate_number + "', " + seats + ")";
  My_Execute_Non_Query(CommandText);
}

Method Add_Bus() gets input parameters, which corresponds to the fields of table “Bus”.

⇑

17.4. Adding data into table “Driver”

Code listing of method Add_Driver(), that adds data into table “Driver” is following.

void Add_Driver(string name, string birth_date, string passport)
{
  string CommandText;
  CommandText = "INSERT INTO [Driver] ([Name], [BirthDate], [Passport])"
                + " VALUES ('" + name + "', '" + birth_date + "', '" + passport + "')";
  My_Execute_Non_Query(CommandText);
}

⇑

17.5. Adding data into table “Dispatcher”

Code listing of method Add_Dispatcher() is following.

void Add_Dispatcher(string name, string birth_date, string address)
{
  string CommandText;
  CommandText = "INSERT INTO [Dispatcher] ([Name], [BirthDate], [Address])"
                + " VALUES ('" + name + "', '" + birth_date + "', '" + address + "')";
  My_Execute_Non_Query(CommandText);
}

⇑

18. Programming the event of click on the button “Add” from group “Commands”

In the main form “Form1.cs”, when user selects button “Add…” from group “Commands”, you need show one of the windows, which correspond adding data into the tables “Ticket”, “Route”, “Bus”, “Driver”, “Dispatcher” (Fig. 40).

To do it, using the tools of language C#, you need to call the corresponding form. After inputting data to the form and selecting of confirmation query, data from the form must be added into the corresponding table of database.

Fig. 40. Button “Add…” of main form

Code listing of event handler of clicking on the button “Add…” is following:

private void button9_Click(object sender, EventArgs e)
{
  if (act_table == 1) // table “Ticket”
  {
    Form4 f = new Form4();

    if (f.ShowDialog() == DialogResult.OK)
    {
      // addint data into the table “Ticket”
      Add_Ticket(f.textBox1.Text, f.textBox2.Text, Convert.ToDateTime(f.textBox3.Text),
                 f.textBox4.Text, f.textBox5.Text, f.checkBox1.Checked);
      Get_Tickets();
    }
  }
  else
  if (act_table == 2) // table "Route"
  {
    Form5 f = new Form5();
    if (f.ShowDialog() == DialogResult.OK)
    {
      // adding data into the table "Route"
      Add_Route(f.textBox1.Text, f.textBox2.Text, f.textBox3.Text, f.textBox4.Text,
      Convert.ToDouble(f.textBox5.Text), Convert.ToDouble(f.textBox6.Text),
                       f.dateTimePicker1.Value, f.dateTimePicker2.Value);
      Get_Routes();
    }
  }
  else
  if (act_table == 3) // table "Bus"
  {
    Form6 f = new Form6();
    if (f.ShowDialog() == DialogResult.OK)
    {
      // adding data into the table "Bus"
      Add_Bus(f.textBox1.Text, f.textBox2.Text, f.textBox3.Text, f.textBox4.Text);
      Get_Bus();
    }
  }
  else
  if (act_table == 4) // table "Driver"
  {
    Form7 f = new Form7();
    if (f.ShowDialog() == DialogResult.OK)
    {
      // adding data into the table "Driver"
      Add_Driver(f.textBox1.Text, Convert.ToString(f.dateTimePicker1.Value), f.textBox2.Text);
      Get_Driver();
    }
  }
  else
  if (act_table == 5) // table "Dispatcher"
  {
    Form8 f = new Form8();
    if (f.ShowDialog() == DialogResult.OK)
    {
      // adding data into the table "Dispatcher"
      Add_Dispatcher(f.textBox1.Text, Convert.ToString(f.dateTimePicker1.Value), f.textBox2.Text);
      Get_Dispatcher();
    }
  }
}

As you see from program code, using the variable “act_table”, the corresponding method is called which adds data in database.

⇑

19. Programming of event of clicking on the button “Delete” from group “Commands”

Program code of event of clicking on the button “Delete” from group “Commands”, is following.

private void button10_Click(object sender, EventArgs e)
{
   Form3 f = new Form3();
   f.Text = " ";
   if (f.ShowDialog() == DialogResult.OK)
   {
     int index, index_old;
     string ID;
     string CommandText = "DELETE FROM ";
     index = dataGridView2.CurrentRow.Index; // The order number
     index_old = index;
     ID = Convert.ToString(dataGridView2[0, index].Value); // We set “ID” as string
     // Forming the “CommandText”
     if (act_table == 1) // process the table "Ticket"
       CommandText = "DELETE FROM [Ticket] WHERE [Ticket].[ID_Ticket] = " + ID;
     if (act_table == 2) // process the table "Route"
       CommandText = "DELETE FROM [Route] WHERE [Route].[ID_Route] = " + ID;
     if (act_table == 3) // process the table "Bus"
       CommandText = "DELETE FROM [Bus] WHERE [Bus].[ID_Bus] = " + ID;
     if (act_table == 4) // process the table "Driver"
       CommandText = "DELETE FROM [Driver] WHERE [Driver].[ID_Driver] = " + ID;
     if (act_table == 5) // process the table "Dispatcher"
       CommandText = "DELETE FROM [Dispatcher] WHERE [Dispatcher].[ID_Dispatcher] = " + ID;

     // executing the SQL-query
     My_Execute_Non_Query(CommandText);

     // repaint dbGridView2
     if (act_table == 1) Get_Tickets();
     else
     if (act_table == 2) Get_Routes();
     else
     if (act_table == 3) Get_Bus();
     else
     if (act_table == 4) Get_Driver();
     else
     if (act_table == 5) Get_Dispatcher();

     if (index_old >= 0)
     {
       dataGridView2.ClearSelection();
       dataGridView2[0, index_old].Selected = true;
     }
   }
}

⇑

20. Programming of choosing of menu (see p. 5.1)

Last step, is the appointment to commands of menus (see p. 5.1 and Fig.11) the execution of corresponding event handlers. They are called when user clicks on the buttons, that are placed on the main form.

For example, the command “Add” from menu “Transportation” should perform the same job as the command “Add…” from group “Transportation”, which is placed on the main form (Fig. 41).

Therefore, it is inappropriate to write code twice of handler of adding transportation. Microsoft Visual Studio allows the system to assign the execution method (if already programmed) to other command (see. Fig. 42).

Fig. 41. Corresponding the command “Add…” from menu “Transportation” to the command with the same name from group “Transportation”

Command, that adds the transportation from main form is called by clicking the button “Add…” from group “Transportation”. This button corresponds to the object named button2. The method of event handler of clicking on the button 2 is named “button2_Click()” (see p. 11).

For appointment of event handling method of the command “Add …” from the “Transport” menu, you need to:

activate (select using mouse) command “Add…” from menu “Transportation”;
in the “Properties” window activate the tab “Events”;
in the field “Click” select the method “button2_Click” (from dropdown list).

Fig. 42. Appointment of the method of processing events for command “Add …” from the “Transport”

In the same way we appoint for others commands of menu the following event handlers:

for command “Delete” from menu “Transportation” we appoint the event handler button3_Click;
for command “Tickets” from menu “View” we appoint the event handler button4_Click;
for command “Routes” from menu “View” we appoint the event handler button5_Click;
for command “Buses” from menu “View” we appoint the event handler button6_Click;
for command “Drivers” from menu “View” we appoint the event handler button7_Click;
for command “Dispatchers” from menu “View” we appoint the event handler button8_Click.

⇑

21. Run the application

Now you can run the application and use it.

Development of Automation Program of work of Manager.

Contents

The task

Related topics

Instructions

1. Developing the database

2. Building the UML-diagramm

2.1. UML-diagram of use

2.2. Construction of UML-diagram of activity

2.3. UML-diagram of interaction

2.4. UML-diagram of classes

3. Developing the application

4. Connecting to database

4.1. Connecting the database file to application

4.2. Connecting the methods of database operating

5. Developing the application form

5.1. Developing the menu

5.2. Controls DataGridView

5.3. Controls of type “GroupBox“

5.4. Placement of controls Label, Button, TextBox and ComboBox

5.5. Placement of controls from group “Filter“

5.6. Designing controls, which are in the group “Transportation“

5.7. Designing controls, which are located to group “View“

5.8. Designing controls, which are placed on the group “Commands“

6. Forming the list in control comboBox1

7. Getting the property “ConnectionString“

8. Adding the internal variables of general use in form “Form1.cs“

9. The output of data in dataGridView1

9.1. The text of SQL-query

9.2. Programming the event handler of clicking on the button “Apply”

9.3. Adding the method button1_Click() into method Form1_Load()

10. Building the form of adding of transportation and creating a program code

10.1. Creating form “Form2.cs”

10.2. Programming of event handlers of clicking on the buttons “Add” and “Cancel”

10.3. Programming of methods of data displaying in controls dataGridView1, dataGridView2, dataGridView3, dataGridView4, dataGridView5.

10.4. Programming of event handlers of changing of active cell in controls dataGridView1, dataGridView2, dataGridView3, dataGridView4, dataGridView5

10.5. Building the strings for filter in controls comboBox1 and comboBox2

10.6. Programming of filter to table “Route”

10.7. Programming of filter to table “Ticket”

10.8. Setting the access to some fields of Form2

10.9. Listing of file “Form2.cs”

11. Programming of event of calling form “Form2” from “Form1”

12. Code listing of “Form1.cs” module

13. Creating the form of deleting the record in dataGridView1

14. Programming the event of calling of command “Delete” from group “Transportation”

15. Programming of commands from group “View”

15.1. Viewing of table “Tickets” in dataGridView2

15.2. Viewing of table “Route” in dataGridView2

15.3. Viewing of table “Bus” in dataGridView2

15.4. Viewing of table “Driver” in dataGridView2

15.5. Viewing of table “Dispatcher” in dataGridView2

16. Building of forms, which add data into the tables

16.1. Building the form, which add a new ticket

16.2. Creating the form which adds data in the table “Route”

16.3. Creating the form which adds data into the table “Bus”

16.4. Creating the form which adds a new driver

16.5. Creating the form which adds a new dispatcher

17. Programming the methods of adding data into the tables “Ticket”, “Route”, “Bus”, “Driver”, “Dispatcher”

17.1. Adding the data to the table “Ticket”

17.2. Adding data into the table “Route”

17.3. Adding data into the table “Bus”

17.4. Adding data into table “Driver”

17.5. Adding data into table “Dispatcher”

18. Programming the event of click on the button “Add” from group “Commands”

19. Programming of event of clicking on the button “Delete” from group “Commands”

20. Programming of choosing of menu (see p. 5.1)

21. Run the application

Related topics

Programming: theory and practice