Creating a new table in the local MS SQL Server database located in the “*.mdf” file
This topic provides an example of creating a new table in the local database, which is implemented in the “*.mdf” file. The database is designed to work in the management system of relational databases (RDBMS) Microsoft SQL Server.
- Related topics
The database “MyDataBase.mdf” is given. Create in this database a table with the name Source, which has the following structure.
1. Run MS Visual Studio. Preparing the database
Run MS Visual Studio.
Using the Server Explorer utility, connect the “MyDataBase.mdf” database to the MS Visual Studio system. Download the archive of the finished (previously created) database here.
The process of connecting the database to future projects in MS Visual Studio is described in more detail in the article:
2. Creating the ‘Source’ table
So far, the database does not contain any elements: tables, views, diagrams, etc. (figure 1).
Figure 1. Database “MyDataBase.mdf” – initial state
To create a table, use the command “Add New Table” from the context menu, which is called by clicking the right mouse button on the “Tables” element of the database “MyDataBase.mdf” (Figure 2).
This will open the window for creating table fields (Figure 3).
The purpose of the fields in the window is as follows:
- field Column Name – contains the names of the fields of the table that is created. In our case, this field can contain the names “ID_Source”, “Name” and “Address”;
- “Data type” – contains the data type, which is specified for any field;
- “Allow Nulls” specifies whether cells can contain NULL values. The NULL value for the cell means that the data was not entered (data not available).
After filling in the names of the fields, the types of the table entry window will have the view, as shown in Figure 4.
Field names and their types are specified in accordance with the condition of the task.
The ID_Column field does not contain the “Allow Nulls” option, since this field can be a unique counter in the future. In this regard, the type of the field ID_Column is selected as an integer (int).
After clicking on the close button of the table input window (or saving), you must specify the Source name of the newly created table (Figure 5).
After the performed actions, a table will be created with the name Source (Figure 6).
3. The work with the ‘Source’ table
After creating the table, the system offers several commands for operating the table data and editing the table structure.
3.1. The command to edit the structure of the table “Open Table Definition”
If you need to rebuild the table structure (number of fields, field names, field types, etc.), you need to do the following:
- open the ‘Tables’ tab in the ‘MyDataBase.mdf’ database (Figure 6);
- in the ‘Tables’ tab, right-click with the mouse, and from the context menu call the “Open Table Definition” command (Figure 7). This opens the field definition table (Figure 8).
Figure 8. The ‘Source’ table in the structure editing mode
After editing the structure of the table, it needs to be closed and saved.
3.2. The command to input data into a table
The data in the table can be entered manually or programmatically.
If you need to enter data, you should do the following:
- call the context menu by right-clicking on the ‘Source’ table (Figure 9). As a result, a window opens for entering data into the table (Figure 10);
- use the keyboard to enter data (records) in the ‘Source’ table (Figure 10).
After you finish entering data, the table can be closed. The data will be automatically saved.
If one of the fields allows the input of NULL values (the “Allow Nulls” option was enabled in the table structure), you can avoid enter values to such cells. Otherwise, the system will issue warning messages.
- An example of creating/connecting local Microsoft SQL Server database located in the “*.mdf” file
- An example of creating and working with the “*.mdf” file of local database Microsoft SQL Server in MS Visual Studio. Tables creating, Setting the relationships between tables. Input data into the tables.
- Creating an autoincrement field (counter) in the MS SQL Server database table, which is located in the “*.mdf” file
- Creating a “one-to-many” relationship between tables in Microsoft SQL Server database
- The “Connection String” string of connection with database. An example of using in applications
- An Example of creating the local Microsoft SQL Server (SQLEXPRESS) database in Microsoft Visual Studio