Review of programs for creating databases. Development of applications for working with databases Description of the Unit2 module
DEVELOPMENT OF AN APPLICATION FOR WORKING WITH DATABASES
Roza Gaynanova
lecturer of the Department of General Educational Disciplines
Kazan national research Technological University
Russia, Kazan
ANNOTATION
The article discusses methods of accessing databases and the program interfaces used in these access methods. We consider the integration of Visual C# applications with the Microsoft SQL Server 2012 DBMS server. As an example, we consider the development information system"Travel agency"
ABSTRACT
The article examines the methods of access to databases and the software interfaces used in these access methods. We consider the integration of Visual C# applications with the Microsoft SQL Server 2012 database server. As an example the development of the "Tourist Agency" information system is considered.
Key words: database, SQL Server, application, users, control, query.
Keywords: database, SQL Server, application, users, control element, query.
An information system is an interconnected set of tools, methods and personnel used to store, process and issue information in order to achieve a given goal. The information system being developed will be built using client-server technology. In such systems, information is stored on the server, and the information system interface is stored on client computers, through which users of the information system gain access to data.
When developing an information system, you have to solve two main problems:
- the task of developing a database designed to store information;
- development task GUI user of client applications.
The “Travel Agency” database is created on Microsoft SQL Server 2012. The database stores information about the clients of this travel agency (tourists), about the tours offered to them, about the registration and payment of vouchers. At the database design stage, the tables “Tourists”, “Tours”, “Vouchers”, “Seasons”, “Payment” are created. Relationships between tables are established.
The travel agency application will be used by the head of the travel agency, sales managers, accountant, cashier and office staff of the travel agency. One of the office employees is appointed as a system administrator. Only he will maintain user accounts. In addition to the five main tables, a “Users” table is created, which contains information about database users. This table is not related to other tables. The structure of this table is: user code, last name, position, login and password. Only the system administrator can make changes to this table.
SQL Server security is built around two concepts: authentication and authorization. System administrator SQL Server security manager creates a separate login object for each user. This object contains the SQL Server user account name, password, full name and other attributes designed to control access to SQL Server databases. By connecting to SQL Server, the user gains access to the databases in which his account is registered. To register an account in a specific database, the system administrator creates a database username in it and associates it with a specific account. The system administrator grants users certain powers. The sales manager can make changes to the tables “Tourists”, “Vouchers” and change the column “Number of seats” in the “Tours” table after selling the next tour. A travel agency employee can make changes to the “Seasons” and “Tours” tables. Accountant and cashier – in the “Payment” table.
You can grant permissions in SQL Server Management Studio by opening the properties windows for the appropriate user. You can also provide permissions using the GRANT statement. Examples of granting authority to a manager. The following statement grants the Menedger user permission to view, modify the Tourists table, insert new rows, and delete outdated data.
USE Travel Agency
GRANT SELECT, UPDATE, INSERT, DELETE
ON Tourists
A similar instruction is created for working with the “Vouchers” table. To give the manager the right to change only one column of the “Tours” table, after the table name, the name of the modified Tours (Number of_seats) column is indicated in parentheses. Provided operations SELECT, UPDATE.
There are four instructions in the Data Control Language (DCL): COMMIT, ROLLBACK, GRANT, and REVOKE. All of these instructions are related to protecting the database from accidental or intentional damage. Databases are vulnerable precisely when changes are made to them. To protect the database, SQL provides operation restrictions. Which can change it, so that they are executed only within transactions. When multiple users try to use the same database table at the same time, a concurrent access situation is created. Concurrency problems arise even in relatively simple applications if the application is installed and running on a multi-user system that does not have sufficient concurrency control. Transaction conflict does not occur if they are executed sequentially.
One of the main tools for maintaining database integrity is a transaction. A transaction encapsulates all SQL statements that can affect the database. An SQL transaction ends with one of two statements: COMMIT (commit) or ROLLBACK (roll back). If a transaction ends with a ROLLBACK statement, all of its statements are canceled and the database is returned to its original state. A normal transaction can execute in one of two modes: READ-WRITE (read-write) or READ-ONLY (read-only). A transaction can be set to one of the following isolation levels: SERIAIZABLE (sequential execution), REPEATABLE READ (repeated read), READ UNCOMMITED (read uncommitted data). The defaults are READ-WRITE and SERIAIZABLE. The default SQL transaction characteristics are generally suitable for most users.
The application is created in the environment Visual Studio 2012 using the C# programming language. Design software product begins with the development of the user interface.
The main application window should call the main functions for working with the application (Figure 1). The menu is used to perform these operations. The menu consists of the following items: “Tables”, “Queries”, “Reports”. Each of these points contains sub-points. Each function will be executed in its own window. A MenuStrip element is installed on the main application window, and menu options are generated. A PictureBox element is placed on the form window. The drawing is loaded into the element area. The drawing should occupy the entire area. The SizeMode property sets the image scaling. For this property, StretchImage is selected from the drop-down list, and the image is scaled so that it occupies the entire surface of the object.
To display a list of users who have the right to work with the Travel Agency database, a comboBox control is installed. The comboBox element is bound to a data source. The window “ WITHomboBoxTasks”, in which the “Use data-bound elements” checkbox is checked; if this checkbox is checked, data binding options open. The comboBox element is bound to the "Users" table, and "Last Name" is selected in the "Display Member" row. To enter the login, the textbox1 control is installed, and textBox2 is installed to enter the password. For the textBox1 and textBox2 elements, the UsesSystemPasworChar property is set to true, which specifies whether the text in the text box should be rendered as password characters by default. Two command buttons are installed: “Login” and “Change user”.
When you bind a comboBox to the Users table, in program code The Form1_Load event handler appears on the form.
private void Form1_Load(object sender, EventArgs e)
this.usersTableAdapter1.Fill(this.travel agencyDataSet10.Users);
When starting the application, the menu is not available. To log in, you need to enter user information and click the “Login” button. When the form is loaded, the user last names contained in the Users table are loaded into the comboBox1 control. Lines are added to this handler that make the menu unavailable, the “Change User” button, and in the comboBox1 element no element is selected:
menuStrip1.Enabled = false; comboBox1.SelectedIndex = -1;
button2.Enabled = false;
Figure 1. View of the main application window
When you click the “Login” button, it is checked whether there is a user with this last name in the “Users” table, and whether the login and password are entered correctly. The form class description area describes the parameters passed to the sql command. These are three parameters: the user's last name, his login and password.
private string parfam, parpasw, parlog;
The line is added to the namespace:
using System.Data.SqlClient;
// Event handler for clicking the “Login” button
string sql = "";
string connstr = @"Data Source= B302CN-8 \TEST_SQL;Initial Catalog=Travel Agency;Integrated Security=True";
SqlDataReader cmReader;
parfam = comboBox1.Text; parlog = textBox1.Text;
SqlConnection conn = new SqlConnection(connstr);
sql = "SELECT Last Name, Login, Password FROM Users " +
" WHERE (Last Name = @fam) and (Password = @pasw)";
SqlCommand cmdkod = new SqlCommand(sql, conn);
cmdkod.Parameters.Add(new SqlParameter("@fam", SqlDbType.NChar, 25));
cmdkod.Parameters["@fam"].Value = parfam;
cmdkod.Parameters.Add(new SqlParameter("@pasw", SqlDbType.NChar, 10));
cmdkod.Parameters["@pasw"].Value = parpasw;
cmdkod.Parameters.Add(new SqlParameter("@log", SqlDbType.NChar, 15));
cmdkod.Parameters["@log"].Value = parlog;
if (!cmReader.Read())
MessageBox.Show("Wrong password!");
cmReader.Close(); conn.Close();
menuStrip1.Enabled = true; comboBox1.SelectedIndex = -1;
button1.Enabled = false; button2.Enabled = true;
textBox1.Text = ""; textBox1.Enabled = false;
textBox2.Text = ""; textBox2.Enabled = false;
comboBox1.Enabled = false;
cmReader.Close();
private void button2_Click(object sender, EventArgs e)
menuStrip1.Enabled = false; comboBox1.Enabled = true;
textBox1.Enabled = true; textBox2.Enabled = true;
button1.Enabled = true; button2.Enabled = false;
Description of the operation of the “Login” button click event handler.
The connstr line contains the connection string. The text of the generated query is written in the sql line, starting with the select statement, after which the selected fields from the tables that are indicated after the word from are listed.
The handler creates a new instance of the SqlConnection object that provides a connection to the SQL server. The SqlCommand object contains a command with three parameters to search the Users table for a user with the given last name, login and password. The button1_Click handler opens a SqlConnection. Next, the handler executes the SQL command stored in the cmdkod object.
cmReader = cmdkod.ExecuteReader();
As a result of executing the ExecuteReader method, an object of the SqlDataReader class is created, which allows you to sequentially read all the lines of the SQL command execution. The SqlDataReader method is used for sampling. Read. If the “Users” table does not contain any records with the given last name, login and password, then the cmReader.Read() method will return false. This means that an incorrect login or password was entered. In this case, a message about invalid data entered is displayed, and the cmReader and SqlConnection objects are closed. If the user information is entered correctly, the menu and the “Change User” button become available. The “Login” button becomes unavailable. The textBox1 and textBox2 elements are cleared and become unavailable. The comboBox1 element also becomes unavailable (Figure 2)
Figure 2. View of the main window after the user logs in
The tables and query results will be displayed on the DataGridView controls. The main purpose of these elements is to link with tables of external data sources, primarily with database tables. For ease of viewing and entering new entries, the “Seasons”, “Tours” and “Vouchers”, “Payment” tables will be displayed two in one window. Each DataGridView control is associated with a corresponding table in the Travel Agency database. In this window, the “Tourists” table is selected (Figure 3). After completing the connection (clicking the "Finish" button), the DataSet, BindingSource and TableAdapter components appear on the form. These components are not visual, so they are displayed in an additional panel. A DataSet is a specialized object that contains a database image. To implement the interaction between the DataSet and the data source itself, an object of the TableAdapter type is used. The very name of this object - adapter, converter - indicates its nature. TableAdapter contains Fill and Update methods, which perform forward and reverse data transfer between the DataSet and the table stored in the SQL server database. The Fiil method populates the DataSet with data from the SQL server, and the Update method updates the server database SQL data from the local DataSet. The BindingSource component makes it easy to bind controls on a form to data. The main property of the BindingSource component is the Data Source property, which points to the data source.
After the tables are connected to data sources, the Form2_Load event handler appears in the form code.
private void Form2_Load(object sender, EventArgs e)
this.touristsTableAdapter.Fill(this.travel agencyDataSet9.Tourists);
When the form is loaded, the data contained in the Tourists table is displayed on the DataGridView control on the Form2 form window. You can make changes to the table and add new records. After making changes, click on the “Save Tourists” button. Event handler for clicking the “Save Tourists” button:
private void button1_Click(object sender, EventArgs e)
seasonsTableAdapter.Update(travel agencyDataSet9);
MessageBox.Show("Data saved");
Figure 3. View of the window with the “Tourists” table
Each request is displayed in a separate window. On the Form1 window, a new item with the name of the request is added to the “Requests” menu. If the query has no parameters, a DataGridView control is installed on the form window to display the results of the query and associated with the appropriate database procedure or function.
This article describes some methods for developing applications that work with databases, a way to organize access to work with the system for a limited number of people, and ways to integrate Visual C# applications with the Microsoft SQL Server 2012 DBMS server. sharing Using the Visual C# programming language with SQL, you can create powerful applications with a wide range of capabilities. The main strength of SQL is in data retrieval. No matter how many rows there are in a table, they can be retrieved using a single SELECT statement. At the same time, the main disadvantage of the SQL language is its underdeveloped user interface. Using procedural languages, you can create convenient interfaces for entering and viewing data. The most common method of combining SQL with procedural languages is called SQL injection. The SQL statement is inserted at the desired location in the procedural program. Information must be passed between a program written in a procedural language and the SQL code. Basic variables are used for this. In order for SQL to recognize these variables, they must be declared. Variables are declared in the form class description area before the program code description. In the program code, a new instance of the SqlConnection object is created to provide a connection to the SQL server. The SqlCommand object enables execution of an embedded SQL command.
References:
- Allen Taylor. SQL for dummies, 8th edition: Transl. from English – M.: LLC “I.D. Williams”, 2014. – 416 p.
- Gainanova R.Sh. Development of applications for working with MS SQL Server 2012 databases // Fundamental and applied sciences today: Proceedings of the XI international practical conference (April 10-11, 2017, Noth Charleston, USA), volume 3 – p. 34-41.
- Frolov A.V., Frolov G.V. Visual design of C# applications. - M.: KUDRITS-OBRAZ, 2003, - 512 p.
Almost every organization has its own database. Well, even websites use them to make working with information easier and simpler. Indeed, they allow you to make calculations without any problems, quickly find the necessary data, and in general, they simply create order in any information.
Often programmers are involved in their creation, because this is a complex process that is taught in higher educational institutions. There are also many lessons, courses and sets of programs for creating software for database development, a really great variety, you can easily get confused. This article will cover some basic database development software.
About SQL
SQL is a programming language that is used to create databases. If you install it on your computer and start creating a database, it will not be entirely convenient. This is due to the fact that SQL itself does not have any graphical shell, and queries to the database must be sent generally via command line. For this reason, various kinds of programs have appeared that simplify the development of databases. However, it is still worth learning the basics of this language. Suddenly you need to make some kind of request, but the program does not work correctly.
Microsoft Access
This database creation program is definitely familiar to many. After all, it comes in the software package Microsoft Office. This program is one of the easiest to learn, because knowledge of the SQL programming language is practically not needed. You can only indicate which query to make, and the program itself will create an SQL query.
Regarding the relevance of the program. Until now, the databases of many organizations have been made using Microsoft Access. Indeed, the program itself is very easy, there is an intuitive interface. Moreover, the basics of working in Access are even taught in school and in junior college courses!
PhpMyAdmin
Access, of course, is a good program, but if you need a database for a website, it won’t cope. Then PhpMyAdmin comes to the rescue. This is very useful program to create databases. Installation on a computer takes some time, and during installation it’s easy to do something wrong and it won’t work. Therefore, when installing this program to create databases, you must strictly follow the instructions. But another advantage of PhpMyAdmin is that you can access it via the Internet as a website! For example, let's say you have a website that runs on WordPress. It will have a database. And if you have a website on some good hosting, then most likely, work with databases will be carried out through PhpMyAdmin, and it can be accessed through the hosting control panel.
Another program for creating databases. It is free, but there is also a paid version with improved features. In this program it is easy to create connections with tables, and in general, it is simply convenient to work with. Another advantage is that you can display the database graphically. Most people prefer this program when working with databases. In principle, PhpMyAdmin is not inferior in capabilities, but still it is more designed for working with website databases.
This article discussed the main programs for creating databases. In fact, there are a huge number of them, so everyone chooses a tool for themselves, but if you are just getting used to it and want to explore this area, then it is recommended to work with MySQL WorkBench. Once you learn the basics of SQL, there will be no significant difference for you where you work, because the queries are the same everywhere. It is also convenient that, having created a database in one program, you can open it through another software, which is also designed to work with the database. When creating software with a database, you cannot do without this knowledge. Moreover, having mastered SQL, you can even create your own software for developing and editing databases.
Let's create a simple database application that displays on the screen information from the "Tourists" table and the "Tourist Information" table record from the database associated with the current record of the "Tourists" table Microsoft data Access.
To do this, let's create an empty Windows application. Environment appearance
development is shown in Figure 39.
Rice. 39. Blank application
Figure 39 highlights the “Data” component group, which contains components for accessing and manipulating data.
The binding of database data to the form is carried out by the “Binding Source” component. Let's transfer it to the form. After placing it on the form, the development environment takes the following form (Fig. 40).
Rice. 40. Binding Source component on the form
The component is not visual, so it is displayed in an additional panel. The main property of the component is the DataSource property, which points to the data source. By default, the property is empty, so you need to configure its value. When you select this property in the properties window, the following window appears (Fig. 41).
Rice. 41. List of data sources
The list is currently empty, so you need to create a new data source by selecting the Add Project Data Source command to create a new data source and connect to it. The following dialog box appears (Fig. 42).
Rice. 42. List of data sources
This dialog provides the following choice of data sources:
Database - Database;
Service - A service is some service that provides data. Most often this is a Web service;
Object - Object for selecting an object that will generate data and objects to work with it.
In our case, you need to select the “Database” item. A window for selecting a data connection appears (Fig. 43).
Rice. 43. Selecting a data connection
The purpose of this dialog is to create a connection string that describes the connection parameters for the ADO engine, such as the database type, its location, user names, security features, etc.
The dialog drop-down list contains all previously created connections. If the required connection is not in the list, then you should use the “New connection” button. Pressing the button causes the following dialog to appear (Fig. 44).
In this dialog, you select the data source type (in this case, Microsoft Access), the database name (in this case, the name and location of the database file), and the username and password used to connect to the database. The "Advanced" button allows you to set a large number of parameters related to various parts of the ADO engine. Using the “Test Connection” button will ensure that the entered parameters are correct and the connection is working.
Rice. 44. Creating a new connection
The last step of the dialogue is to select those tables or other database objects that are needed in this data source. The selection window is shown in Figure 45.
Rice. 45. Selecting the necessary tables
In this window, the “Tourists” and “Tourist Information” tables are selected. Since no objects other than tables were created in the database, only tables are displayed in Figure 45. This completes the creation of the data source. After clicking the “Finish” button, a DataSet component appears on the form next to the BindingSource component.
Now the data connected above needs to be displayed on the form. The simplest way to display data is to use the DataGridView component from the Data component group. The component is visual and looks like this on the form (Fig. 46).
Rice. 46. DataGridView Component
The component settings window immediately appears, which determines its data editing capabilities: “Enable Adding”, “Enable Editing”, “Enable Deleting”; the ability to change the sequence of columns: “Enable the ability to change the order of columns” (“Enable Column Reordering”); as well as the ability to be attached to the parent container.
In order for the component to display data, you must select a data source in the drop-down list. Selecting the drop-down list causes the following dialog to appear (Fig. 47).
Rice. 47. Selecting a data source for DataGridView
In this case, we chose the “Tourists” table as the data source. This selection changes the screen form as follows (Fig. 48).
Rice. 48. The DataGridView component displays the table structure
The figure shows that another BindingSource component and a TableAdapter component have appeared, working with the “Tourists” table. Please note that in design-time or during the development process, the data from the table is not displayed.
Now you need to display the data from the linked table “Tourist Information”. To do this, place another DataGridView component on the form and select the following as a data source (Fig. 49).
Rice. 49. Selecting a data source for the second DataGridView
Here, the data source is not the “Tourist Information” table itself, but the connection (Binding Source) between the “Tourists” and “Tourist Information” tables. This selection ensures that only those rows from the Tourist Information table that are associated with the current row in the Tourists table are selected. This choice also ensures that associated data is updated and deleted correctly. The operation of the resulting application is shown in Figure 50.
Rice. 50. Database application at work
Navigating through data using the arrow keys is awkward. To simplify data navigation, there is a BindingNavigator component. Let's place it on the form (Fig. 51).
Rice. 51. BindingNavigator component on the form
This component allows you to navigate between table records, add and delete table rows. Opportunities and appearance The component is customizable because it is a ToolStripContainer menu strip.
The property that determines the table through which navigation is performed is the BindingSource property. Let's set the value of this property to "touristsBindingSource". In operation, the component looks like this (Fig. 52).
Rice. 52. BindingNavigator component at work
Editing data in the cells of the DataGridView component with appropriate settings is possible, but it is inconvenient and not rational. In particular, it is difficult to check entered values for errors. Therefore, for the “Tourists” table we will make a screen form that allows you to display data in TextBox components and edit them. To do this, place a container of the Panel type on the form, and on it three TextBox components as follows (Fig. 53).
Rice. 53. Screen panel for editing entries in the “Tourists” table
Now you need to bind the TextBox components to the corresponding fields of the “Tourists” table. To do this, we use the property from the DataBindings - Advanced group, shown in Figure 54.
Rice. 54. Property “DataBindings - Advanced”
Selecting this property leads to the appearance of the dialog shown in Figure 55. This dialog allows you not only to bind data, but also to set an event within which the data will be updated, as well as formatting the data when outputting it.
For the top TextBox component, in the Binding drop-down list, select “touristsBmdmgSource” as the data source and the source field as “Last Name”. For the middle and bottom TextBox components, select the same data source and the “Name” and “Patronymic” fields, respectively.
The developed application in operation looks like this (Fig. 56).
Rice. 55. Dialog window for the “DataBindings - Advanced” property
Rice. 56. Data binding to visual components
However, when changes are made, all new data remains only on the form. They are not saved in the database, and when the application is called again, of course, they will be missing. This occurs because the data was loaded into a DataSet object, which is an in-memory copy of the table. All actions are performed with this copy. In order for changes to be reflected in the database, you must execute the Update method of the TableAdapter class. Thus, in the application being developed, it is necessary to place the “Update” button and write the following program code in the Click event handler:
touristsTableAdapterUpdate(bDTur_firmDataSet); information_about_touristsTableAdapter.Update(bDTur_firmDataSet);
This code updates information in the Tourists and Tourist Information tables provided by the data source. Note that this method is overloaded, and its variants allow you to update either an individual table row or a group of rows.
Let's create a simple database application that displays information from the "Tourists" table and the "Tourist Information" table record from the Microsoft Access database associated with the current record of the "Tourists" table.
To do this, let's create an empty Windows application. Environment appearance
development is shown in Figure 39.
Rice. 39. Blank application
Figure 39 highlights the “Data” component group, which contains components for accessing and manipulating data.
The binding of database data to the form is carried out by the “Binding Source” component. Let's transfer it to the form. After placing it on the form, the development environment takes the following form (Fig. 40).
Rice. 40. Binding Source component on the form
The component is not visual, so it is displayed in an additional panel. The main property of the component is the DataSource property, which points to the data source. By default, the property is empty, so you need to configure its value. When you select this property in the properties window, the following window appears (Fig. 41).
Rice. 41. List of data sources
The list is currently empty, so you need to create a new data source by selecting the Add Project Data Source command to create a new data source and connect to it. The following dialog box appears (Fig. 42).
Rice. 42. List of data sources
This dialog provides the following choice of data sources:
Database - Database;
Service - A service is some service that provides data. Most often this is a Web service;
Object - Object for selecting an object that will generate data and objects to work with it.
In our case, you need to select the “Database” item. A window for selecting a data connection appears (Fig. 43).
Rice. 43. Selecting a data connection
The purpose of this dialog is to create a connection string that describes the connection parameters for the ADO engine, such as the database type, its location, user names, security features, etc.
The dialog drop-down list contains all previously created connections. If the required connection is not in the list, then you should use the “New connection” button. Pressing the button causes the following dialog to appear (Fig. 44).
In this dialog, you select the data source type (in this case, Microsoft Access), the database name (in this case, the name and location of the database file), and the username and password used to connect to the database. The "Advanced" button allows you to set a large number of parameters related to various parts of the ADO engine. Using the “Test Connection” button will ensure that the entered parameters are correct and the connection is working.
Rice. 44. Creating a new connection
The last step of the dialogue is to select those tables or other database objects that are needed in this data source. The selection window is shown in Figure 45.
Rice. 45. Selecting the necessary tables
In this window, the “Tourists” and “Tourist Information” tables are selected. Since no objects other than tables were created in the database, only tables are displayed in Figure 45. This completes the creation of the data source. After clicking the “Finish” button, a DataSet component appears on the form next to the BindingSource component.
Now the data connected above needs to be displayed on the form. The simplest way to display data is to use the DataGridView component from the Data component group. The component is visual and looks like this on the form (Fig. 46).
Rice. 46. DataGridView Component
The component settings window immediately appears, which determines its data editing capabilities: “Enable Adding”, “Enable Editing”, “Enable Deleting”; the ability to change the sequence of columns: “Enable the ability to change the order of columns” (“Enable Column Reordering”); as well as the ability to be attached to the parent container.
In order for the component to display data, you must select a data source in the drop-down list. Selecting the drop-down list causes the following dialog to appear (Fig. 47).
Rice. 47. Selecting a data source for DataGridView
In this case, we chose the “Tourists” table as the data source. This selection changes the screen form as follows (Fig. 48).
Rice. 48. The DataGridView component displays the table structure
The figure shows that another BindingSource component and a TableAdapter component have appeared, working with the “Tourists” table. Please note that in design-time or during the development process, the data from the table is not displayed.
Now you need to display the data from the linked table “Tourist Information”. To do this, place another DataGridView component on the form and select the following as a data source (Fig. 49).
Rice. 49. Selecting a data source for the second DataGridView
Here, the data source is not the “Tourist Information” table itself, but the connection (Binding Source) between the “Tourists” and “Tourist Information” tables. This selection ensures that only those rows from the Tourist Information table that are associated with the current row in the Tourists table are selected. This choice also ensures that associated data is updated and deleted correctly. The operation of the resulting application is shown in Figure 50.
Rice. 50. Database application at work
Navigating through data using the arrow keys is awkward. To simplify data navigation, there is a BindingNavigator component. Let's place it on the form (Fig. 51).
Rice. 51. BindingNavigator component on the form
This component allows you to navigate between table records, add and delete table rows. The functionality and appearance of the component can be customized because it is a ToolStripContainer menu strip.
The property that determines the table through which navigation is performed is the BindingSource property. Let's set the value of this property to "touristsBindingSource". In operation, the component looks like this (Fig. 52).
Rice. 52. BindingNavigator component at work
Editing data in the cells of the DataGridView component with appropriate settings is possible, but it is inconvenient and not rational. In particular, it is difficult to check entered values for errors. Therefore, for the “Tourists” table we will make a screen form that allows you to display data in TextBox components and edit them. To do this, place a container of the Panel type on the form, and on it three TextBox components as follows (Fig. 53).
Rice. 53. Screen panel for editing entries in the “Tourists” table
Now you need to bind the TextBox components to the corresponding fields of the “Tourists” table. To do this, we use the property from the DataBindings - Advanced group, shown in Figure 54.
Rice. 54. Property “DataBindings - Advanced”
Selecting this property leads to the appearance of the dialog shown in Figure 55. This dialog allows you not only to bind data, but also to set an event within which the data will be updated, as well as formatting the data when outputting it.
For the top TextBox component, in the Binding drop-down list, select “touristsBmdmgSource” as the data source and the source field as “Last Name”. For the middle and bottom TextBox components, select the same data source and the “Name” and “Patronymic” fields, respectively.
The developed application in operation looks like this (Fig. 56).
Rice. 55. Dialog window for the “DataBindings - Advanced” property
Rice. 56. Data binding to visual components
However, when changes are made, all new data remains only on the form. They are not saved in the database, and when the application is called again, of course, they will be missing. This occurs because the data was loaded into a DataSet object, which is an in-memory copy of the table. All actions are performed with this copy. In order for changes to be reflected in the database, you must execute the Update method of the TableAdapter class. Thus, in the application being developed, it is necessary to place the “Update” button and write the following program code in the Click event handler:
touristsTableAdapterUpdate(bDTur_firmDataSet); information_about_touristsTableAdapter.Update(bDTur_firmDataSet);
This code updates information in the Tourists and Tourist Information tables provided by the data source. Note that this method is overloaded, and its variants allow you to update both an individual table row and a group of rows.
