ASP.NET the MVC database
May 12, 2021 ASP.NET
Table of contents
ASP.NET MVC - SQL Database
To learn ASP.NET MVC, we'll build an Internet application.
Part 6: Add a database.
Create a database
Visual Web Developer comes with a free SQL database called SQL Server Compact.
The database required for this tutorial can be created in the following simple steps:
- Right-click the folder in the solution Explorer App_Data window
- Select Add, New Item
- Select SQL Server Compact Local Database
- Name the database Movies .sdf
- Click the Add button
* If sql Server Compact Local Database is not available in the options, you have not installed SQL Server Compac on your computer. Install it by linking: SQL Server Compact
Visual Web Developer automatically creates App_Data database in the folder.
Note: In this tutorial, you need to learn some basics about SQL Database. If you'd like to start with this topic, visit our SQL tutorial.
Add a database table
Double-clicking App_Data Movies file .sdf folder opens the Database Explorer window.
To create a new table in the database, right-click the Tables folder and select Create Table.
Create a column like this:
| List | type | Whether to allow NULL |
|---|---|---|
| ID | int (primary key) | No |
| Title | nvarchar(100) | No |
| Director | nvarchar(100) | No |
| Date | datetime | No |
Explanation of the column:
The ID is the integer (full number) that identifies each record in the table.
Title is a 100-character text column that stores the name of a movie.
Director is a 100-character text column that stores the director's name.
Date is a date column that stores the release date of a movie.
After you have created the above column, you must set the ID column as the primary key (record identifier) of the table. T o do this, click Column Name (ID) and select Primary Key. In the Column Properties window, set the Identity property to True:
When you're creating a table column, save the table and name it MovieDBs.
Comments:
We specifically named the table "MovieDBs" (ending in s). I n the next chapter, you'll see MovieDB for the data model. This may seem strange, but this naming convention ensures that the controller is connected to the database table, which you must use.
Add a database record
You can use Visual Web Developer to add some test records to the movie database.
Double-click App_Data The Movies file .sdf folder.
Right-click the MovieDBs table in the Database Explorer window and select Show Table Data.
Add some records:
| ID | Title | Director | Date |
|---|---|---|---|
| 1 | Psycho | Alfred Hitchcock | 01.01.1960 |
| 2 | La Dolce Vita | Federico Fellini | 01.01.1960 |
Note: The ID column updates automatically, so you don't have to edit it.
Add a connection string
Add the following elements to your Web.config file:
connectionString="Data Source=|DataDirectory|Movies.sdf"
providerName="System.Data.SqlServerCe.4.0"/>