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
- The task
- Instructions
- 1. Run MS Visual Studio.
- 2. Activate the window “Server Explorer”.
- 3. Command “Add Connection…”.
- 4. The window “Add Connection”.
- 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.
- Related topics
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”.
The structure of second 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
Figure 1. Call of “Server Explorer”
⇑
3. Command “Add 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).
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).
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…”.
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.
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.
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”.
Figure 7. The proposal to create the file “Education.mdf”
After confirming, the database “Education.mdf” will created (Figure 8).
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).
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
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.
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.
Figure 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.
Figure 13. Specifying the key field
After setting the primary key the window of table will have the following view (Figure 14).
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).
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“.
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
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.
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).
Figure 19. 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.
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).
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.
Figure 23. The window “Tables and Columns”
Figure 24. Window of setting the properties of relation
After that the relationship between tables (Figure 25) will be set.
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).
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).
Figure 27. Command “Show Table Data”
The window, where need to type data, will be opened (Figure 28).
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).
Figure 29. A message about error of entering data in tables Student and Session
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
- 001 – An example of creating/connecting local Microsoft SQL Server database located in the “*.mdf” file
- 002 – An example of creating a table in the local Microsoft SQL Server database located in the “*.mdf” file
- 003 – Creating an autoincrement field (counter) in the MS SQL Server database table, which is located in the “*.mdf” file
- 004 – Creating a “one-to-many” relationship between tables in Microsoft SQL Server database
- 001 – An Example of creating the local Microsoft SQL Server (SQLEXPRESS) database in Microsoft Visual Studio