ASP.NET MVC - SQL Database

To learn ASP.NET MVC, we will build an Internet application.

Part 6:Add database.

Create database

Visual Web Developer comes with a free SQL database named SQL Server Compact.

It can be created through the following simple steps:

  1. Right-click the database required for this tutorial in the App_Data folder
  2. SelectAdd,New Project
  3. Select SQL Server Compact Local Database *
  4. Name this database Movies.sdf
  5. ClickAddbutton

* If the option does not include SQL Server Compact Local Database, it means you have not installed SQL Server Compact on your computer. Please install it through the following link:SQL Server Compact

Visual Web Developer will automatically create the database in the App_Data folder.

Note:In this tutorial, you need to master the basic knowledge of SQL databases. If you wish to learn this topic first, please visit our SQL Tutorial.

Add database table

Double-click the App_Data folder in Movies.sdf It will open Database Explorer window.

To create a new table in this database, please right-click Tables folder, then selectCreate a table.

Create the following columns:

Column Type Allow Nulls
ID int (primary key) No
Title nvarchar(100) No
Director nvarchar(100) No
Date datetime No

Explanation of the column:

ID It is an integer used to identify each record in the table.

Title It is a text column of 100 characters in length, used to store the film's name.

Director It is a text column of 100 characters in length, used to store the director's name.

Date It is a date column used to store the release date of the film.

After building the above columns, you must set the ID column as the table'sPrimary Key（record identifier）. To do this, click on the column name (ID) and in Column Properties Select Primary Key in Identity Property set to True.

When you have finished creating the above columns, please save the table and name it MovieDBs.

Note:After you finish creating table columns, please save the table and name it

We have named this table "MovieDBs" (ending with s). In the next chapter, we will see how "MovieDBs" is used in data models. Don't you feel a bit strange? However, this is the naming convention to ensure that controllers are linked to database tables.

Add Database Record

You can use Visual Web Developer to add some test records to the movie database. App_Data Double-click on Movies.sdf File

Right-click on MovieDBs Select Show Table Data.

Add Record:

ID Title Director Date
1 Psycho Alfred Hitchcock 01.01.1960
2 La Dolce Vita Federico Fellini 01.01.1960

Note:The ID column will be automatically updated. You do not need to edit it.

Add Link String

Add a connection string to Web.config in the file <connectionStrings> Add the following element:

<add name="MovieDBContext"
connectionString="Data Source=|DataDirectory|\Movies.sdf"
providerName="System.Data.SqlServerCe.4.0"/>