001 – An example of creating the local MS SQL Server database in MS Visual Studio

An example of creating the local MS SQL Server database in MS Visual Studio

In the theme are is shown a task solving of creating the MS SQL Server database using MS Visual Studio. The following questions are considered:

– working with Server Explorer in MS Visual Studio;

– creating a local database of SQL Server Database type;

– creating the tables in database;

– editing of the tables structure;

– linking of database tables between themselves;

– entering data to the tables using MS Visual Studio.

 

Content

The task

1. Run MS Visual Studio.
2. Activate the Server Explorer window.
3. Creating the database “Education”.
4. Objects of database Education.
5. Creating the table Student.
6. Creating the table Session.
7. Editing the structure of tables.
8. Making connections between the tables.
9. Entering data into the tables.

 


The task

Using MS Visual Studio create a MS SQL Sever database named “Education”. Database includes two tables Student and Session. Tables are linked by some field.

 Structure of  “Student” table.

02_02_00_014_table01_e

Structure of “Session” table.

02_02_00_014_table02_e


Progress

  1. Run MS Visual Studio.

 

  1. Activate the Server Explorer window.

 To work with databases Microsoft provides the lite database server Microsoft SQL Server. There are different versions MS SQL Server, for example: Microsoft SQL Server 2005, Microsoft SQL Server 2008, Microsoft SQL Server 2014 and others.

 You can load these versions from the site Microsoft www.msdn.com.

 This server is excellent for working with databases. It is free and has a graphical interface for creating and managing databases using SQL Server Management Tool.

First of all, before creating a database, you need to activate the utility “Server Explorer”. To do this in MS Visual Studio you need to call (figure 1)

View -> Server Explorer

02_02_00_014_01_Figure 1. Calling the Server Explorer

 After calling, the “Server Explorer” window will have the approximate view as shown in Figure 2.

02_02_00_014_02_Figure 2. Window “Server Explorer

 

  1. Creating the database “Education”.

 To create a new database, that is based on the provider Microsoft SQL Server, you need to click on the “Data Connections” node. Then you need to select “Create New SQL Server Database …” (Figure 3).

02_02_00_014_03_Figure 3. Calling the command of creating of database SQL Server

 As a result, the window “Create New SQL Server Database” will be opened (Figure 4).

 In the window (in the «Server Name» field) is indicated the local name server installed on your computer. In our case this name is “SQLEXPRESS”.

 In the field “New database name:” you need to specify the name of database, which will be created. In our case this name is “Education”.

 “Use Windows Authentication” option must be left unchanged.

02_02_00_014_04_Figure 4. Creating a new database MS SQL Server

 After completing the steps, the “Server Explorer” window will be as shown in Figure 5. As see in Figure 5, in the list of databases, is added the database “Education” which is named as:

sasha-pc\sqlexpress.Education.dbo

02_02_00_014_05_Figure 5. The “Server Explorer” window after adding the database Education

 

  1. Objects of database Education.

 If you expand the Education database (“+” sign), you can see a list of the following major objects:

Database Diagrams – they show relationships between tables of database;

Tables – tables, where data is stored;

Views. The difference between the views and tables is that the database tables contain data but views data does not contain and views can be chosen as the content of the other tables or views;

Stored procedures. They represent a group of related operators in the SQL language, which provides additional flexibility when working with the database.

 

  1. Creating the table Student.

  At present, database “Education” is empty and doesn’t contain any objects (tables, stored procedures, views and so on.).

 To create a table, you need to call the context menu (click of right button mouse) and  select the command “Add New Table” (Figure 6).

02_02_00_014_06_Figure 6. Command of adding a new table

 There is and other case of adding a table to database by using a commands of menu “Data”:

Data ->  Add New -> Table

02_02_00_014_07_Figure 7. Alternative adding a new table.

 As a result, the window of adding a new table will be opened. This window contains three columns (Figure 8). In the first column “Column Name” you need to enter the title of corresponding field of database table. In the second column “Data Type” you need to enter the data type of this field. Third column “Allow Nulls” indicates the option of the possibility of a lack of data in the field.

02_02_00_014_08_Figure 8. The window of creating a new table

 By using the table editor you need to form the table Student as shown in Figure 9. The table name must be set when it is closing.

 In the editor table you can set the properties of fields in the “Column Properties” window. To set length of string (nvchar) in the symbols, in the window Column Properties need to use property Length. By default the value of this property is 10.

02_02_00_014_09_Figure 9. Table Student

 Next step you need set the key field. It is realized by calling command “Set Primary Key” from the context menu of field Num_book. By using the key field will be set the relationships between tables. In our case the key field is the number of test book.

02_02_00_014_10_Figure 10. Specifying the key field

 After setting the primary key  the window of table will have the following view (Figure 11).

02_02_00_014_11_Figure 11. Table Student after final formation

 Now you can close the table. In the window of saving the table you need set the it’s name – Student (Figure 12).

02_02_00_014_12_Figure 12. Entering the name of the table Student

 

  1. Creating the table Session.

 Following the model of creating a table Student is created table Session. At the Figure 13 the view of table Session is shown after final formation. Primary key is set in field Num_book. The name of table is set as “Session”.

Figure 13. Table Session

 After performing steps, in the “Server Explorer” window will shown two tables Student and Session.

 Thus, in the database you can add any number of tables.

 

  1. Editing the structure of tables.

 There are cases when you need to change the structure of the database table.

 First of all, to do change the tables of database in MS Visual Studio, you need remove the option “Prevent Saving changes that require table re-creation” as shown in Figure 14.  Otherwise, MS Visual Studio will block entering the changes to previosly-created table. The window “Options” (Figure 14) is called from menu Tools in the following sequence:

Tools -> Options -> Database Tools -> Table and Database Designers

02_02_00_014_14_Figure 14. The option “Prevent Saving changes that require table re-creation

 After settings you can change the structure of tables. To do this you can use command “Open Table Definition” (Figure 15) from the context menu, that is called for selected table.

02_02_00_014_15_Figure 15. Calling the command “Open Table Definition

 Also this command is realized in the “Data” menu:

Data -> Open Table Definition

Previously you need select the table.

 

  1. Making connections between the tables.

 According to problem condition there are connections between tables by field Num_book. To create link between tables, first of all you need (Figure 16):

– select object Database Diagram;

– select command Add New Diagram from the context menu (or menu Data).

02_02_00_014_16_Figure 16. Calling the command of adding a new diagram

 As a result, the window of adding new diagram “Add Table” (Figure 17). In this window you need to select two tables “Student” and “Session” and press the “Add” button.

02_02_00_014_17_Figure 17. The window of adding the table to diagram

02_02_00_014_18_Figure 18. Tables Student and Session after adding they to diagram

 To set the relationship between tables, you need click on the field “Num_book” of table Student and then (holding down the mouse button) drag it to the field “Num_book” of table Session.

 As a result, will be opened two windows: “Tables and Columns” (Figure 19) and “Foreign Key Relationship” (Figure 20). In these tables you need leave all as it is and confirm your selection by clicking at “OK” button.

 In the window “Tables and Columns” is set the title of relation (FK_Session_Student) and name of parent table and child table.

02_02_00_014_19_Рис. 19. Window “Tables and Columns

02_02_00_014_20_Figure 20. Window of setting the properties of relation

 After that the relationship between tables (Figure 21) will be set.

02_02_00_014_21_Figure 21. Relation between tables Student and Session

 Saving the diagram is in the same way as saving the table. You need choose the diagram’s name at your discretion (for example “Diagram1”).

 At the next step, the window “Save” will be opened. In this window you need to confirm your choice (Figure 22).

02_02_00_014_22_Figure 22. Confirming of saving the changes in tables

 

  1. Entering data into the tables.

 Microsoft Visual Studio allows you to enter data into the tables directly.

 In our case, when you set the relation (Figure 19), the table “Student” is realized as primary table (“Primary Key Table”). Therefore, first of all you need enter data in cells for the table “Student”. If you, first of all, will try enter data into the table Session, the system will block entering and will shows the corresponding message.

 To access the data entry mode in the Student table, you need to call the command Show Table Data from the context menu (click by right mouse button) or from Data menu (Figure 23).

02_02_00_014_23_Figure 23. Command “Show Table Data

 The window, where need to type data, will be opened (Figure 24).

Figure 24. Entering data to the “Student” table

 After entering data in the Student table you need to enter data in the Session table.

 When you entering data to the field Num_book of Session table you need to enter exactly the same values, which entered in the field Num_book of table Student (because these fields are linked themselves).

 For example, if in the field Num_book in the table Student enter values “101”,102”, “103” (see Figure 24), then you need enter exactly these values in the field Num_book in the table Session. If you try enter other value then system will display approximate the following window (Figure 25).

02_02_00_014_25_

Figure 25. A message about error of entering data in tables Student and Session

 Table Session with entered data is shown in Figure 26.

02_02_00_014_26_Figure 26. Table Session with the entered data

 So, database created. Inputting and processing of data in the tables can be implemented by using a programming.