002 – An example of creating the “*.mdf” file of local database Microsoft SQL Server

An example of creating the “*.mdf” file of local database Microsoft SQL Server

In the given topic is considered, how to attach (create) database file to local instance of Microsoft SQL Server Express using the .NET Framework Data Provider for SQL Server.


Contents


Search other websites:




Task

Create a database table with name “Education”. In the database create two tables, which are related between them by a some field.

Structure of the first table “Student”.

Databases. The structure of table "Student"

The structure of second table “Session“.

Databases. The structure of table "Session"

As you see, the tables must be related between them by the “Num_book” field.



Instructions

1. Run MS Visual Studio.


2. Activate the window Server Explorer.

The database file with extention “*.mdf” is applied to the server of relational databases Microsoft SQL Server. The file includes database directly.

When “*.mdf” file is created, also is created a file with extention “*.ldf”, which contains a transaction log.

Before the database creating, you need to activate utility “Server Explorer”. To do this in MS Visual Studio, you need to call (Figure 1)

View -> Server Explorer

MS Visual Studio. Command “Server Explorer”Figure 1. Call of “Server Explorer


3. CommandAdd Connection…”.

To show a window of database creating into the format “mdf”, you need to call command “Add Connection”. The “Add Connection” command is called by a two ways. First way – calling the context menu (click by right mouse button) at the element “Data Connection…” (Figure 2).

MS Visual Studio. Server Explorer window. Command “Add Connection”

Figure 2. Calling the command “Add Connection” from the context menu

Second way – calling the command “Connect to Database…” from menu Tools in the main menu Microsoft Visual Studio. Also you can call this command from “Server Explorer” by clicking on the corresponding button (Figure 3).

MS Visual Studio. Window "Server Explorer". Command “Connect to Database”

Figure 3. Command “Connect to Database


4. The window Add Connection”.

As a result of executing previous command, the window “Add Connection” will be opened (Figure 4). In this window, user has the ability to:

  • select a data source (Data source);
  • create a new database file or select an existing database file (Database file name);
  • if it is needed, set a new password to access the database;
  • test the connection with database (button “Test Connection”);
  • set up other parameters by calling of button “Advanced…”.

 

MS Visual Studio. Database project. The window “Add Connection”

Figure 4. The window “Add Connection

As a data source Microsoft Visual Studio proposes Microsoft Access database. To create a “*.mdb” file of MS SQL Server database, you need to change data source by selecting button “Change…”.

As a result, the second window “Change Data Source” will be opened (Figure 5). In this window you can select a data source and data provider.

MS Visual Studio offers the following types of data source:

  • MS Access database, which is contained in the “*.mdb” file;
  • database, which supports the access using ODBC driver;
  • database of type Microsoft SQL Server, including local server SQLEXPRESS;
  • database of type “Microsoft SQL Server Compact 3.5“, which is located in the files with extention “*.sdf”;
  • database “Microsoft SQL Server Database File”, which is located in the files of “*.mdf” format;
  • database Oracle.

To create “*.mdf” file of database Microsoft SQL Server you need to select data source “Microsoft SQL Server Database file” as shown in Figure 5.

MS Visual Studio. Database project. Window “Change Data Source”

Figure 5. Window “Change Data Source

After changing data source in the window “Add Connection” in field “Database file name (new or existing):” you need to type the name of created database. If you need to select “*.mdf” file of existing database then you need select “Browse…” button.

In our case you need to type the database name “Education”, as shown in Figure 6.

MS SQL Server Database project. Creating database “Education”

Figure 6. Creating database “Education”

After confirming on the “OK”, system will show window, as shown in Figure 7. The system folder is proposed:

C:\Users\Sasha\Documents\Education.mdf

To set the other folder, you need to use button “Browse…” from window “Add Connection”.

MS SQL Server Database project. The proposal to create the file “Education.mdf”

Figure 7. The proposal to create the file “Education.mdf”

After confirming, the database “Education.mdf” will created (Figure 8).

MS SQL Server Database. Newly created database “Education.mdf”

Figure 8. Newly created database “Education.mdf


5. 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 9).

MS Visual Studio. Server Explorer window. Command of adding a new table

Figure 9. Command of adding a new table

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

Data ->  Add New -> Table

MS Visual Studio. Command Data\Add New\Table

Figure 10. Alternative adding a new table

As a result, the window of adding a new table will be opened. This window contains three columns (Figure 11). 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.

MS SQL Server Database. The window of creating a new table

Figure 11. The window of creating a new table

By using the table editor you need to form the table Student as shown in Figure 12. 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.

MS SQL Server Database. Table StudentFigure 12. 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 (Figure 13). By using the key field will be set the relationships between tables. In our case the key field is the number of test book.


MS SQL Server Database. Specifying the key fieldFigure 13. Specifying the key field

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

MS SQL Server Database. Table Student after final formation

Figure 14. 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 15).

MS SQL Server Database. Entering the name of the table Student

Figure 15. Entering the name of the table Student


6. Creating the table Session.

Following the model of creating a table Student is created table Session. At the Figure 16 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“.

MS SQL Server Database. Table Session

Figure 16. 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.







7. 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 17. Otherwise, MS Visual Studio will block entering the changes to previosly-created table. The window “Options” (Figure 17) is called from menu Tools in the following sequence:

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

 

MS Visual Studio. Database project. The option “Prevent Saving changes that require table recreation”

Figure 17. 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 18) from the context menu, that is called for selected table.

MS Visual Studio. The Server Explorer window. Command "Open Table Definition"

Figure 18. 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.


8. 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 19):

  • select object Database Diagram;
  • select command Add New Diagram from the context menu (or menu Data);
  • confirm the creating of a new diagram (Figure 20).

 

MS SQL Server Database. "Add a new diagram" command

Figure 19. Calling the command of adding a new diagram

MS Visual Studio. Database project. Calling the command of adding a new diagram

Figure 20. Calling the command of adding a new diagram

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

MS Visual Studio. Add new diagram. The window of adding the table to diagram

Figure 21. The window of adding the table to diagram

As a result, the new object, which contains two tables “Student” and “Session“, will be created (Figure 22).

MS Visual Studio. Database project. Tables Student and Session after adding they to diagram

Figure 22. 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 23) and “Foreign Key Relationship” (Figure 24). 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.

MS Visual Studion. MS SQL Server database. The window “Tables and Columns”

Figure 23. The window “Tables and Columns

MS SQL Server Database project. Window of setting the properties of relation

Figure 24. Window of setting the properties of relation

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

MS Visual Studio. Database project. Relation between tables Student and Session

Figure 25. 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 26).

MS Visual Studio. Databases. Confirming of saving the changes in tables

Figure 26. Confirming of saving the changes in tables


9. 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 23), 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 27).

MS Visual Studio. The "Server Explorer" window. Command “Show Table Data”

Figure 27. Command “Show Table Data

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

MS Visual Studio. Entering data to the “Student” table

Figure 28. 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 “1134”, “1135”, “1221” (see Figure 28), 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 29).

MS Visual Studio. Database project. A message about error of entering data in tables Student and Session

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

MS Visual Studio. Database project. Table Session with the entered data

Figure 30. Table Session with the entered data

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

 


Related topics