An example of demo application that manipulates data in the table of database MS SQL Server (SQLEXPRESS)

Search other websites:

Task

Develop the application, which demonstrates a basic operations on the data in the database of MS SQL Server type, as follows:

connecting the database to the application;
viewing the database tables on the form;
adding a new record to the database;
editing the record;
deleting the record.

As base is taken the database “Education.dbo“. The process of creating “Education.dbo” database is described here in details.

At the Figure 1 is displayed the structure of “Education.dbo” database. As you can see, database is called:

sasha-pc\sqlexpress.Education.dbo

Here “sasha-pc” – identifier for computer in the network, “sqlexpress” – name of the database server, “Education.dbo” – database name.

Figure 1. Database “Education.dbo“

Database consists two tables: Student and Session. The structure of tables is the following.

Table Student

Table Session

Performing

1. Creating a new project as Windows Forms in MS Visual Studio.

Create a new project of Windows Forms Application type. An example of new project creating is described here in details.

⇑

2. Creating a new view for data displaying the table Student.

To show data in the database tables, you can use different ways. One of them creates the views, which are formed by using MS Visual Studio.

In our case the data of tables will be shown on the form in the control of DataGridView type. After creating views is very convenient link them with controls DataGridView.

To create new view, you need to call “Add New View” command from a context menu, which is called by clicking right mouse button on the item “Views” of database “Education.dbo” (Figure 2).

Figure 2. Calling the command of adding a new view

As a result, the window “Add Table” (Figure 3) will be opened. In the window you need to select tables which are added to the view.

Figure. 3. Selecting the tables, on which will be based new view

In our case you need select table Student and confirm your selection by clicking on the button Add. The next step, you need close the window by selecting button Close.

After performing steps will be formed a window, in which you need to select fields. These fields must be displayed in the view on the form. Select all fields (Figure 4).

After selecting fields, in the top part of working area the fields of table Student are displayed. Using a mouse, you can select the fields for their viewing.

Accordinly, in the middle area of window are displayed the titles of fields, the table in which they are placed (see Figure 4), possibility of sorting, filtering and etc.

In the bottom part of area is displayed the text of corresponding SQL-query, which is used to create view.

Figure 4. Selecting the fields of table Student to display them in the view

When you select command

File->Save All

File->Save View1

the window will be opened. In this window you need to set the name for the view. Set name to “View Student” (Figure 5).

Figure 5. Setting the name for the view

After performing steps the window of view will be as shown in Figure 6.

Figure 6. The view “View Student” in database

Now, you can place on form the control DataGridView and link it with view.

⇑

3. Placing the control DataGridView and setting up connection with database.

Before placement the control DataGridView, you need switch to the design mode of form “Form1.cs [Design]“.

The DataGridView control is a table, that can display data. The control is placed on the ToolBox pane. First, you need a bit adjust size of the form, and then place the DataGridView control (Figure 7). As a result the instance of object named dataGridView1 by default will be created.

Figure 7. The control DataGridView and window of selecting datasource

After placing on the form the control of type DataGridView, in the right top area you can select the setting of datasource. Accordingly the window “DataGridView Tasks” will be opened. In this window you need to select the pop-up menu “Choose Data Source“.

In the opened menu is selected the command “Add Project Data Source” (Figure 7). After that is opened the window of wizard, where sequentially is selected the data source.

At the figure 8 the window “Data Source Configuration Wizard” is shown. In this window is selected the type of data source. In our case select the item “Database“.

Figure 8. Choosing the data source

In the next window (Figure 9) is selected the model of data source. You need choose DataSet.

Figure 9. Choosing the model of data source

In the window at Figure 10, you need set the data connection, which you need use for connecting to database. In our case you need select the database “sasha-pc\sqlexpress\Education.dbo“.

Figure 10. Choosing the data connection

In the next window (Figure 11) is allowed to save the string of connection “Connection String” into the configuration file of application. Leave all as is and going to the next window.

Figure 11. Proposal save the connection string to the database in the configuration file of application

After creating the connection with database the set of objects are displayed (Figure 12). In our case you need select the view “View Student” and select all fields from it. The selected fields are displayed in the control of DataGridView type.

Figure 12. Selection of database objects, which you need to show in DataGridView

After selecting button Finish, the selected objects will be displayed in database “Education.dbo” (Figure 13).

Figure 13. Control of type DataGridView with the selected fields of view “View Student“

Likewise you can set up the views, which includes any fields from any tables of database. Also, the one view can display fields from different tables.

⇑

4. Setting up the control of DataGridView type.

If you run the application then will be got data of view “View Student“, which corresponds to the table Student of database (Figure 14).

Figure 14. Running the application

As you can see from Figure 14, data in the table dataGridView1 are displayed normally, but the design can be corrected.

Control of type DataGridView allows editing of the form fields that are displayed.

To call commands of fields editing, it is enough to call the context menu by clicking right mouse button on the control dataGridView1.

There are many useful commands in the menu. These commands allows to control the view and work to the DataGridView:

command of locking control (Lock Control);
command of editing the fields, which are displayed in the view (Edit Columns…);
command of adding the new fields, for example calculated fields (Add Column).

In our case you need select the command “Edit Columns…” (Figure 15).

Figure 15. Calling the command “Edit Columns…” from the context menu

As a result, the window “Edit Columns” will be opened. In this window you can set the fields view to your liking (Figure 16).

Figure 16. The window of setting of fields view in the view “View Student“

In the window an Figure 16, for any field you can set the title, alignment, width, possibility of data editing and so on.

⇑

5. String “Connection String“.

To change data in the database, you need get the string for connecting with database. This string is named as “Connection String“.

There are different ways of getting the string “Connecting String“. One of them is based on the reading this string in the Properties window of database “Education.dbo” (Figure 17).

Figure 17. Determining the “Connection String“

To save the string in the program is entered internal variable of type string. You need use the Clipboard to copy string “Connection String” in the declared variable of type string. In the text of file “Form1.cs” at the begin of class “Form1” declaration you need to declare variable:

string conn_string = @"Data Source=(local)\SQLEXPRESS;Initial Catalog=Education;Integrated Security=True;Pooling=False";

At present, the text of class Form1 is following:

public partial class Form1 : Form
{
    string conn_string = @"Data Source=(local)\SQLEXPRESS;Initial Catalog=Education;Integrated Security=True;Pooling=False";

    public Form1()
    {
        InitializeComponent();
    }

    private void Form1_Load(object sender, EventArgs e)
    {
        // TODO: This line of code loads data into the 'educationDataSet.View_Student' table. You can move, or remove it, as needed.
        this.view_StudentTableAdapter.Fill(this.educationDataSet.View_Student);
    }
}

⇑

6. Creating a new form for demonstration of data manipulating commands.

To have a possibility to process data of current record you need create a new form. The process of new form creating in MS Visual Studio – C# is described here in details.

Adding a new form is realized by command:

Project -> Add Windows Form...

In the opened window “New Item” you need select the item “Windows Form“. The name of new form file leave “Form2.cs” by default.

At the Figure 18 is shown the view of a new form.

Place on the form controls of following types:

- two controls of Button type (buttons OK and Cancel). Accordingly will be got two objects named “button1” and “button2“;
- four controls of Label type for creating the messages;

four controls of TextBox type for entering data in the fields Num_book, Name, Group, Year.

You need set the following properties of controls:

in the control button1 property Text = “OK“;
in the control button2 property Text = “Cancel“;
in the control button1 property DialogResult = “OK“;
in the control button2 property DialogResult = “Cancel“;
in the control label1 property Text = “Num_book“;
in the control label2 property Text = “Name“;
in the control label3 property Text = “Group“;
in the control label4 property Text = “Year“.

Also, set the visibility of controls of TextBox type. To do this set the property Modifiers = “public” in the all controls textBox1, textBox2, textBox3, textBox4.

Figure 18. The view of a new form

⇑

7. Adding the buttons of calling the commands of data manipulating of table Student.

Next step, you need to switch on the main form Form1.

Add three buttons (Button control) on the main form Form1. By automatically will be created three variables-objects named button1, button2, button3. In any of these buttons set the following settings (Properties window):

in the button button1 property Text = “Insert …” (insert record);
in the button button2 property Text = “Edit …” (edit record);
in the button button3 property Text = “Delete” (delete record).

As a result the main form of application will have following view as shown in Figure 19.

Figure 19. The main form of application

⇑

8. Programming an event of clicking on the button “Insert…“.

Event handler of clicking on the “Insert…” button has view:

private void button1_Click_1(object sender, EventArgs e)
{
    string cmd_text;
    Form2 f2 = new Form2();
    if (f2.ShowDialog() == DialogResult.OK)
    {
        cmd_text = "INSERT INTO Student VALUES (" +
        "'" + f2.textBox1.Text + "' , '" +
        f2.textBox2.Text + "' , '" +
        f2.textBox3.Text + "' , " +
        f2.textBox4.Text + ")";

        // create a connection with database
        SqlConnection sql_conn = new SqlConnection(conn_string);

        // create the SQL-command
        SqlCommand sql_comm = new SqlCommand(cmd_text, sql_conn);

        sql_conn.Open(); // open the connection
        sql_comm.ExecuteNonQuery(); // execute the SQL-command
        sql_conn.Close(); // close the connection
        this.view_StudentTableAdapter.Fill(this.educationDataSet.View_Student);
    }

}

First, the form Form2 is called. After getting the “OK” result, filled fields of TextBox type in the form Form2 are included in the SQL-query. The SQL-query is the following:

INSERT INTO Student
VALUES (value1, value2, value3, value4)

where value1 is a number of test book; value2 – student’s name; value3 – name of group, where student is learning; value4 – year of entrance.

The string of connection with database “Connection String” is declared in the conn_string variable (see p. 5). The object of SqlConnection class realizes connecting of application with data sources. In addition, the SqlConnection class solves tasks of user authentication, working with networks, identification of databases, buffering of connections and transaction processing.

The SQL-command, that adds record to the table, is encapsulated in SqlCommand class. Constructor of SqlCommand class takes two parameters: string of SQL query (variable cmd_text) and object of class SqlConnection.

The ExecuteNonQuery() method is realized in the interface IDBCommand. Method realizes SQL-commands, which don’t return data. These commands include commands INSERT, DELETE, UPDATE and also stored procedures, which don’t return data. The ExecuteNonQuery() method returns the number of involved records.

⇑

9. Programming the event of clicking on the button “Edit…“.

The event handler of clicking on the button “Edit…” is following:

private void button2_Click(object sender, EventArgs e)
{
    string cmd_text;
    Form2 f2 = new Form2();
    int index;
    string num_book;

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

    f2.textBox1.Text = num_book;
    f2.textBox2.Text = Convert.ToString(dataGridView1[1, index].Value);
    f2.textBox3.Text = Convert.ToString(dataGridView1[2, index].Value);
    f2.textBox4.Text = Convert.ToString(dataGridView1[3, index].Value);

    if (f2.ShowDialog() == DialogResult.OK)
    {
        cmd_text = "UPDATE Student SET Num_book = '" + f2.textBox1.Text + "', " +
        "[Name] = '" + f2.textBox2.Text + "', " +
        "[Group] = '" + f2.textBox3.Text + "', " +
        "Year = " + f2.textBox4.Text +
        "WHERE Num_book = '" + num_book + "'";

        SqlConnection sql_conn = new SqlConnection(conn_string);
        SqlCommand sql_comm = new SqlCommand(cmd_text, sql_conn);

        sql_conn.Open();
        sql_comm.ExecuteNonQuery();
        sql_conn.Close();

        this.view_StudentTableAdapter.Fill(this.educationDataSet.View_Student);

    }
}

In this handler, the SQL-command UPDATE is executed. This command changes the current value of active record.

⇑

10. Programming an event of clicking on the button “Delete“.

The event handler of clicking on the button “Delete” is following:

private void button3_Click(object sender, EventArgs e)
{
    string cmd_text = "DELETE FROM Student";
    int index;
    string num_book;

    index = dataGridView1.CurrentRow.Index;
    num_book = Convert.ToString(dataGridView1[0,index].Value);
    cmd_text = "DELETE FROM Student WHERE [Student].[Num_book] = '" + num_book + "'";

    SqlConnection sql_conn = new SqlConnection(conn_string);
    SqlCommand sql_comm = new SqlCommand(cmd_text, sql_conn);

    sql_conn.Open();
    sql_comm.ExecuteNonQuery();
    sql_conn.Close();

    this.view_StudentTableAdapter.Fill(this.educationDataSet.View_Student);
}

In this handler, the SQL-command DELETE is executed.

BestProg

C#. Windows Forms. An example of demo application that manipulates data in the table of database MS SQL Server (SQLEXPRESS)