How We Can Add the SQL Database in ASP.NET Web API

Introduction

This article explains how to create a SQL database in the ASP .NET Web API.

Now we will provide the procedure for creating the database.

Step 1

First we create the MVC4 Web API application.

  • Start Visual Studio 2012.
  • Select "File" -> "New" -> "Project...".
  • In the New Project window select "Visual C#" -> "Web"
  • Now select "ASP .NET MVC4 Web Application".
  • Change the name of application to "Database_WebAPI".
  • Click the "OK" button.
dt.jpg

Step 2

After performing Step 1, the New ASP .NET MVC4 Project Window is opened; then:

  • Select "Web API" from the "ASP .NET MVC4 Project" window.
  • Click on "OK" button.
dt1.jpg

Step 3

Now to add the SQL Server Compact 4.0 Local Database. Follow these steps:

  • In the Solution Explorer, right-click the "App_Data" folder
  • Select "Add" -> "Add New Item".
  • In the template window select "Visual C#" -> "Data".
  • Then select "SQL Server Compact 4.0 local database".
  • Change the name to "Movies.sdf".
  • Click on the "OK" button.
dt2.jpg

Step 4

Now we create the table in the database using the following:

  • Select the "App_Data" folder. The Movies.sdf database is showing.
  • Double-click on "Movies.sdf". Open a Server Explorer window.
  • In the Server Explorer window right-click on "Tables" -> "Create table".
  • Now open the New table window.
dt3.jpg

dt4.jpg

dt5.jpg
 
The Table looks like this.

Step 5

Now we add the Field in the table.

dt6.jpg

After adding the field in the table it is looks like  this.

Step 6

Add the record in the table.

  • In the Solution Explorer, double click on the Movies.sdf.
  • Open a Server Explorer.
  • In the Server Explorer click on the table folder then select Movies.sdf.
  • Right-click on Movies.sdf then select "Show the table data".

dt7.jpg

Now we add the record in the table.

dt8.jpg

Step 7

Now we add the <connectionStrings> element to the Web.config file.

  • In the Solution Explorer double-click on the Web.config file.
  • Add the <connectionStrings> element and add the following code.
dt9.jpg

<add name="DefaultConnection" providerName="System.Data.SqlClient" connectionString="Data Source=(LocalDb)\v11.0;Initial Catalog=aspnet-Database_WebAPI-20130530090816;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnet-Database_WebAPI-20130530090816.mdf" />