Post
Ask Question

Building a Web API Structure with Database-First Approach (Using EDMX)

Introduction

In this article, we will learn about how to create a web API structure with a database-first approach using EDMX. We can better understand this step by step.

Step 1. Create New Project ASP.NET web application(.net framework) set name WebApiwithDatabaseFirst and select WebApi and Ok select version 6.0 then ok.

Step 2. Right Click on Project Solution. Create a new project, select Class Library (.Net Framework) and Name For Project DAL, and Ok.

Step 3. First of all, create DemoDB and create one Table, for example, says User.

Step 4. Go to DAL Project; Right Click on DAL --> Add New Item -> Select Data from Tab -> Select ADO.Net Entity Data Model to give a name like DatabaseFirstModel and Ok.

Now Choose the Model to contain Select EF Designer from a database. Next, click on the New Connection button and select the server name. If you are using window authentication in SQL so right click on SQL connection, go to property select Name: DESKTOP-Test/SQLEXPRESS. Copy and paste the server name, then select your database for ex. DemoDB and then OK. 

Now your Entity is ready, Like DemoDBEntities, next select EF version, next select Table, and click finish.

Step 5. Change Webapi projetc(WebApiwithDatabaseFirst) select APP_Start Open WebApiConfic.cs and code copy paste for Route Config.

Replace Register Method

public static void Register(HttpConfiguration config) {
    config.MapHttpAttributeRoutes();
    var corsAttr = new System.Web.Http.Cors.EnableCorsAttribute("*", "*", "*");
    config.EnableCors(corsAttr);
    config.Routes.MapHttpRoute(name: "DefaultApi", routeTemplate: "api/{controller}/{id}", defaults: new {
        id = RouteParameter.Optional
    });
    config.Formatters.JsonFormatter.SerializerSettings.ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore;
}

After Import System.Web.HTTP Package Install

Go to tool select Nuget Package Manager to select Manage Nuget Package for solution and search System.Web.HTTP selects and installs the particular project.

After installing some Packages in our Web API project.

  1. Microsoft.AspNet.Cors 
  2. Entity Framework
  3. Microsoft.AspNet.WebApi.Cors

After resolving the Cros Error.

Step 6. Create Web API Controller Name: User    

Code for User controller

using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;
using System.Web.Http;
using DAL;
namespace WebApiwithDatabaseFirst.Controllers {
    [RoutePrefix("api/User")]
    public class UserController: ApiController {
        private UserDAL obj = new UserDAL();
        DemoDBEntities db = new DemoDBEntities();
        [HttpGet]
        [Route("GetallUser")]
        public IHttpActionResult GetallUser() {
            return Ok(obj.GetallUser());
        }
    }
}

Step 7. Create DAL and code copy and paste here, give DAL Error click on Web API Project((WebApiwithDatabaseFirst)). Right-click on References and add DAL References.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace DAL {
    public class UserDAL {
        DemoDBEntities db = new DemoDBEntities();
        public List < User > GetallUser() {
            var userlist = new List < User > ();
            try {
                using(db = new DemoDBEntities()) {
                    db.Configuration.ProxyCreationEnabled = false;
                    userlist = db.Users.ToList();
                }
            } catch (Exception ex) {
                throw ex;
            }
            return userlist;
        }
    }
}

Step 8. Code Run After Give Connection error, add web config Connection for Web api Project (WebApiwithDatabaseFirst).

<connectionStrings>
    <add name="DemoDBEntities" connectionString="metadata=res://*/DatabaseFirstModel.csdl|res://*/DatabaseFirstModel.ssdl|res://*/DatabaseFirstModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=DESKTOP-FE06TRG\SQLEXPRESS;initial catalog=DemoDB;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />
</connectionStrings>

Step 9. After calling api using url like http://localhost:55444/api/User/GetallUser and finally got the output in xml format.

<ArrayOfUser xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/DAL">
  <User>
    <Id>1</Id>
    <mono>6565656565</mono>
    <name>abc</name>
  </User>
  <User>
    <Id>2</Id>
    <mono>1212115656</mono>
    <name>tst</name>
  </User>
</ArrayOfUser>

Step 10. We have data in json format, then go to web api project Global.asax file replace Application_start() Method. The code is here.

protected void Application_Start() {
    //AreaRegistration.RegisterAllAreas();
    //GlobalConfiguration.Configure(WebApiConfig.Register);
    //FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
    //RouteConfig.RegisterRoutes(RouteTable.Routes);
    //BundleConfig.RegisterBundles(BundleTable.Bundles);
    GlobalConfiguration.Configure(WebApiConfig.Register);
    GlobalConfiguration.Configuration.Formatters.JsonFormatter.SerializerSettings.ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore;
    GlobalConfiguration.Configuration.Formatters.Remove(GlobalConfiguration.Configuration.Formatters.XmlFormatter);
}

After running http://localhost:55444/api/User/GetallUser Url to display api response in json format.

[{"Id":1,"name":"abc","mono":"6565656565"},{"Id":2,"name":"tst","mono":"1212115656"}]

 


Similar Articles