Creation of database applications. Database Application Development

Let's create a simple database application that displays information from the "Tourists" table and a record of the "Tourist Information" table from the database associated with the current record of the "Tourists" table. Microsoft Access.

To do this, let's create an empty Windows application. Appearance of the environment

development is shown in Figure 39.

Rice. 39. Empty application

In Figure 39, the "Data" component group is highlighted, which contains components for accessing and manipulating data.

The binding of the 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. Component Binding Source 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 form its value. When this property is selected, the following window appears in the properties window (Fig. 41).

Rice. 41. List of data sources

The list is currently empty, so you need to create a new data source by choosing 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 selection of data sources:

Database - Database;

Service - A service is some kind of service that provides data. Most often this is a Web service;

Object - An object for selecting an object that will generate data and objects to work with.

In our case, you need to select the "Database" item. A window for selecting a data connection appears (Fig. 43).

Rice. 43. Choosing 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 type of database, its location, usernames, security features, etc.

The drop-down list of the dialog 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 leads to the appearance of the following dialog (Fig. 44).

In this dialog, you select the type of data source (in this case Microsoft Access), the name of the database (in this case, the name and location of the database file), 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 mechanism. Using the "Test Connection" button will make sure that the entered parameters are correct and that the connection is working.

Rice. 44. Creating a new connection

The last step of the dialog is to select those tables or other database objects that are needed in this source data. The selection window is shown in Figure 45.

Rice. 45. Selecting the required tables

In this window, the tables "Tourists" and "Tourist Information" 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. When you click Finish, a DataSet appears next to the BindingSource on the form.

Now the data connected above must 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. Component DataGridView

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 Column Reordering"; and also the ability to dock in the parent container.

In order for the component to display data, you must select a data source in the drop-down list. Choosing the drop-down list leads to the appearance of the following dialog (Fig. 47).

Rice. 47. Selecting a data source for DataGridView

In this case, we have selected the "Tourists" table as the data source. This selection changes the display as follows (Fig. 48).

Rice. 48. Component DataGridView displays the structure of the table

In the figure, you can see that there is another BindingSource component and a TableAdapter component that works with the Tourists table. Please note that at design-time or during development, the data from the table is not displayed.

Now you need to display the data from the linked Tourist Information table. 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 Binding Source between the "Tourists" and "Tourist Information" tables. This selection ensures that only those rows are selected from the Tourist Information table that are associated with the current row in the Tourists table. This choice also ensures that the 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 the data using the arrow keys is inconvenient. There is a BindingNavigator component to simplify navigation through the data. 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 bar.

The property that defines the table to be navigated through is the BindingSource property. Set the value of this property to TouristBindingSource. In operation, the component looks like this (Fig. 52).

Rice. 52. The BindingNavigator component at work

Editing data in the cells of the DataGridView component with appropriate settings is possible, but 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 displaying data in the TextBox components and editing them. To do this, place a Panel type container on the form, and on it three TextBox components as follows (Fig. 53).

Rice. 53. Screen panel for editing records of the table "Tourists"

Now it is necessary to bind the TextBox components to the corresponding fields of the "Tourists" table. To do this, use the property from the DataBindings group - Advanced, 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 to not only bind data, but also set an event within which the data will be updated, as well as formatting the data when it is displayed.

For the top TextBox component in the Binding drop-down list, select the data source "touristsBmdmgSource" and the source field - "Lastname". 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 as follows (Fig. 56).

Rice. 55. Dialog box for the "DataBindings - Advanced" property

Rice. 56. Binding data to visual components

However, when changes are made, all new data remains only on the form. They are not stored in the database, and of course they will not be present when the application is called again. This is because the data has been loaded into a DataSet, which is an in-memory copy of the table. All actions are performed with this copy. In order for the changes to be reflected in the database, you must execute the Update method of the TableAdapter class. Thus, in the developed application it is necessary to place the Update button and write the following program code to the Click event handler:

touristsTableAdapteгUpdate (bDTur_firmDataSet); info_about_touristsTableAdapter.Update (bDTur_firmDataSet);

This code updates the 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 a single row of a table and a group of rows.

To do this, let's create an empty Windows application. Appearance of the environment

development is shown in Figure 39.

Rice. 39. Empty application

In Figure 39, the "Data" component group is highlighted, which contains components for accessing and manipulating data.

Rice. 40. Component Binding Source on the form

Rice. 41. List of data sources

Rice. 42. List of data sources

This dialog provides the following selection of data sources:

Database - Database;

Service - A service is some kind of service that provides data. Most often this is a Web service;

Object - An object for selecting an object that will generate data and objects to work with.

In our case, you need to select the "Database" item. A window for selecting a data connection appears (Fig. 43).

Rice. 43. Choosing 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 type of database, its location, usernames, security features, etc.

Rice. 44. Creating a new connection

The last step of the dialog 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 required tables

Rice. 46. Component DataGridView

In order for the component to display data, you must select a data source in the drop-down list. Choosing the drop-down list leads to the appearance of the following dialog (Fig. 47).

Rice. 47. Selecting a data source for DataGridView

In this case, we have selected the "Tourists" table as the data source. This selection changes the display as follows (Fig. 48).

Rice. 48. Component DataGridView displays the structure of the table

Now you need to display the data from the linked Tourist Information table. 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

Rice. 50. Database application at work

Navigating through the data using the arrow keys is inconvenient. There is a BindingNavigator component to simplify navigation through the data. 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 is customizable as it is a ToolStripContainer menu bar.

Rice. 52. The BindingNavigator component at work

Rice. 53. Screen panel for editing records of the table "Tourists"

Now it is necessary to bind the TextBox components to the corresponding fields of the "Tourists" table. To do this, use the property from the DataBindings group - Advanced, shown in Figure 54.

Rice. 54. Property "DataBindings - Advanced"

The developed application in operation looks as follows (Fig. 56).

Rice. 55. Dialog box for the "DataBindings - Advanced" property

Rice. 56. Binding data to visual components

touristsTableAdapteгUpdate (bDTur_firmDataSet); info_about_touristsTableAdapter.Update (bDTur_firmDataSet);

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 for accessing databases and the programming interfaces used in these access methods. Integration of Visual C # applications with Microsoft SQL Server 2012 is considered. Development is considered as an example. information system"Tourist 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.

Keywords: 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 set goal. The developed information system will be built using client-server technology. In such systems, information is stored on the server, and the interface of the information system is stored on client computers, through it, users of the information system gain access to data.

When developing an information system, two main tasks have to be solved:

the task of developing a database for storing information;
task of developing graphical interface user of client applications.

The database "Travel agency" is created on Microsoft SQL Server 2012. The database stores information about the clients of this travel agency (tourists), about the tours it offers, about the registration and payment of vouchers. At the design stage of the database, tables "Tourists", "Tours", "Vouchers", "Seasons", "Payment" are created. Links are established between tables.

The travel agency application will be used by the travel agency manager, sales managers, accountant, cashier and travel agency office staff. One of the office employees is appointed by the system administrator. Only he will keep user accounts. In addition to the five main tables, the "Users" table is created, which contains information about the database users. This table is not related to other tables. The structure of this table: user code, surname, position, login and password. Only a system administrator can make changes to this table.

SQL Server security is built on two concepts: authentication and authorization. The SQL Server security system administrator creates a separate login object for each user. This object contains the SQL Server user account name, password, fully qualified name, and other attributes 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. In order 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 gives users certain privileges. The sales manager can make changes to the tables "Tourists", "Tours" and change the column "Number_of_seats" in the table "Tours" after the sale of the next tour. An employee of the travel agency can make changes to the tables "Seasons" and "Tours". Accountant and cashier - in the "Payment" table.

You can grant permissions in SQL Server Management Studio by opening the properties windows for the corresponding user. You can also represent permissions using the GRANT statement. Examples of granting authority to a manager. The following statement grants the Menedger user the right to view, modify the Tourists table, insert new rows, and delete obsolete data.

USE Travel Agency

GRANT SELECT, UPDATE, INSERT, DELETE

ON Tourists

A similar instruction is created for working with the "Tours" table. To give the manager the right to change only one column of the "Tours" table, after the table name, in brackets, the name of the variable column Tours (Number of seats) is indicated. Provided SELECT, UPDATE operations.

There are four instructions in the Data Control Language (DCL): COMMIT, ROLLBACK, GRANT, REVOKE. All of these instructions are related to protecting the database from accidental or intentional damage. Databases are vulnerable exactly when changes are made to them. To protect the database, SQL is limited to operations. 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 lacks concurrency control. There is no transaction conflict if they are executed sequentially.

One of the main tools for maintaining the integrity of a database is a transaction. A transaction encapsulates all SQL statements that can affect the database. The SQL transaction ends with one of two statements: COMMIT or ROLLBACK. If the transaction ends with a ROLLBACK statement, then all its statements are canceled, the database returns to the initial state... A normal transaction can be executed in one of two modes: READ-WRITE (read-write) or READ-ONLY (read only). You can set one of the following isolation levels for a transaction: SERIAIZABLE ( sequential execution), REPEATABLE READ (repeated read), READ UNCOMMITED (read unconfirmed data). The default features are READ-WRITE and SERIAIZABLE. The default SQL transaction characteristics are generally appropriate for most users.

The application is created in Visual Studio 2012 using the C # programming language. Designing a software product begins with developing a 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 clauses contains subclauses. Each function will be executed in its own window. The MenuStrip element is installed on the main application window, and menu options are formed. The PictureBox element is placed on the form window. A picture is loaded into the element area. The drawing should cover the entire area. The SizeMode property sets the scaling of the picture. For this property, StretchImage is selected from the drop-down list, the drawing is scaled so that it occupies the entire surface of the object.

The comboBox control is installed to display a list of users who have the right to work with the Travel Agency database. The comboBox element binds to a data source. The window “ WITHomboBoxTasks", In which the" Use data-bound items "check box is selected, if this check box is selected, the data binding options open. The comboBox item 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, to enter the password textBox2. For textBox1 and textBox2, the UsesSystemPasworChar property is set to true, which specifies whether the text in the text box should be displayed using the default password characters. Two command buttons “Login” and “Change user” are installed.

When you bind a comboBox element to the Users table, the Form1_Load event handler appears in the form's program code.

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 enter the system, you need to enter user data and click the "Login" button. When the form is loaded, the user surnames contained in the Users table are loaded into the comboBox1 control. In this handler, lines are added that make menus inaccessible, the "Change user" button and in the comboBox1 element no item 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 in the "Users" table with the given surname, and whether the login and password are entered correctly. In the description area of the form class, the parameters passed to the sql command are described. 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 "Enter" 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 Surname, Login, Password FROM Users" +

"WHERE (Surname = @fam) and (Password [email protected])";

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 event handler for pressing the "Enter" button.

The connstr string contains the connection string. In the sql line, the text of the generated query is written, starting with the select statement, after which the selectable fields from the tables that are specified 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 for searching the Users table for a user with the given last name, login, and password. The button1_Click handler opens a SqlConnection. The handler then 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. For selection, the SqlDataReader method is used. Read. If the table "Users" does not contain any records with the specified surname, login and password, then the cmReader.Read () method will return false. This means that you entered an incorrect username or password. In this case, a message about invalid data entered is displayed, the cmReader and SqlConnection objects are closed. If the user data is entered correctly, the menu and the "Change user" button become available. The "Login" button becomes unavailable. The elements textBox1 and textBox2 are cleared and made inaccessible. The comboBox1 element also becomes inaccessible (Figure 2)

Figure 2. View of the main window after the user logs in to the system

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 the convenience of viewing and entering new entries, the tables "Seasons", "Tours" and "Tours", "Payment" will be displayed two at a time in one window. Each DataGridView control is associated with a corresponding table in the Travel Agency database. The table "Tourists" is selected in this window (Figure 3). When you complete the connection (click Finish), the DataSet, BindingSource, and TableAdapter components appear on the form. These components are not visual, so they are displayed in the additional panel. DataSet is a specialized object that contains a database image. To implement the interaction between the DataSet and the actual data source, an object of the TableAdapter type is used. The very name of this object - adapter, converter - indicates its nature. The TableAdapter contains the Fill and Update methods that perform forward and backward data transfers between the DataSet and the table stored in the server's SQL database. The Fiil method fills the DataSet with data from the SQL server, and Update 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 the data sources, the Form2_Load event handler appears in the form's program code.

private void Form2_Load (object sender, EventArgs e)

this.touristsTableAdapter.Fill (this.travel agencyDataSet9.Tourists);

When the form loads, 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. The 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, the "Requests" menu is added new item with the name of the request. If the query has no parameters, a DataGridView control is installed on the form window to display the results of the query execution and binds to the appropriate procedure or database function.

This article describes some methods of developing applications that work with databases, a way to organize access to work with a system for a limited number of people, ways to integrate Visual C # applications with a Microsoft SQL Server 2012 DBMS. sharing Visual C # programming language with SQL you can create powerful applications with a wide range of capabilities. The main strength of SQL lies in data retrieval. No matter how many rows there are in the table, you can retrieve them with a single SELECT statement. At the same time, the main disadvantage of the SQL language is its underdeveloped user interface. With procedural languages, you can create user-friendly interfaces data entry and viewing. The most common technique for combining SQL with procedural languages is called SQL injection. The SQL statement is inserted at the desired location in the procedural program. Information must flow between a program written in a procedural language and the SQL code. For this, basic variables are used. For SQL to recognize these variables, they must be declared. Variables are declared in the class description area of the form before the code description. In code, the newly created SqlConnection object provides a connection to the SQL server. The SqlCommand object provides execution of the embedded SQL command.

Bibliography:

Allen Taylor. SQL for Dummies, 8th Edition: Per. from English - M .: LLC “I.D. Williams ”, 2014. - 416 p.
Gaynanova R.Sh. Development of applications for working with databases MS SQL Server 2012 // Fundamental and applied sciences today: Materials 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, - 512s.

Almost every organization has its own database. But what is really there, even sites use them to make it easier and easier to work with information. Indeed, they allow you to make calculations without any problems, quickly find the necessary data, and in general, simply create order in any information.

Often, programmers are engaged 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 developing databases, a really great variety, you can easily get confused. This article will discuss some of the basic programs for database development.

About SQL

SQL is a programming language used to create databases. If you install it on your computer and start creating a database, it will not be very convenient. This is due to the fact that SQL itself does not have any graphical shell, and queries to the database must be sent in general through command line... For this reason, various kinds of programs have appeared that simplify the development of databases. However, learning the basics of this language is still worth it. Suddenly you need to make a request, but the program does not work correctly.

Microsoft Access

This program for creating databases is exactly familiar to many. After all, she comes in a package Microsoft programs Office. This program is one of the easiest to learn, because knowledge of the SQL programming language is practically not needed there. You can only designate which query to make, and the program will compose the SQL query itself.

About the relevance of the program. Until now, the databases of many organizations have been made using Microsoft Access. Indeed, the program itself is very light, there is an intuitive interface. What's more, the basics of using Access are even taught in school and early college!

PhpMyAdmin

Access, of course, is a good program, but if you need a database for a website, it won't do it. 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 is easy to do something wrong and will not work. Therefore, when installing this program to create databases, you must strictly follow the instructions. But another plus of PhpMyAdmin is that it can be accessed via the Internet as a website! For example, you have a website that is powered by WordPress. He will have a database. And if you have a site on some good hosting, then, most likely, work with databases will be carried out through PhpMyAdmin, and it will be possible to access it through the hosting control panel.

Another program for creating databases. It's free, but there is also a paid version with improved features. It is easy to create links with tables in this program, and indeed, it is just convenient to work with. Also a plus is that you can show the database in graphically... Most people, when working with databases, prefer this particular program. In principle, PhpMyAdmin is not inferior in its capabilities, but nevertheless it is more intended for working with a database of sites.

This article has covered the basic programs for creating databases. In fact, there are a lot of them, so everyone chooses a tool for themselves, but if you are just getting started and want to study this area, then it is recommended to work with MySQL WorkBench. Once you learn the basics of SQL, it won't make a big difference for you where to 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.

Skyrim: mod for your castle (Honduin) Download mod for skyrim heavenly castle

Amazing grass - Retextures and replayers - Mods and plugins for TES V: Skyrim Special Edition Mod for more grass in Skyrim

PunBB installation and extensions Awesome profile powered by punbb

Oblivion Association: Era of Change - Build Description