Creating an OData Service for a SQL Server Database

2 Comments

To create an OData service for a SQL Server database, we will create a new Visual Studio ASP.NET web application:

Select Empty template and check Web API:

This will create:

Now, add the OData NuGet packages:

Add Entity Framework:

Next, add a new item:

Select EF Designer from database:

Select the database connection, click new connection if required, and click Next. We will connect to an Azure SQL database with Adventure Works sample data:

Select tables and click Finish:

A new connection string is added to the Web.Config:

With the Entity Framework model:

Now, open the file App_Start/WebApiConfig.cs:

We will add OData code to the Register method:

        public static void Register(HttpConfiguration config)
        {
            ODataModelBuilder builder = new ODataConventionModelBuilder();
            builder.EntitySet<Product>("Products");
            config.MapODataServiceRoute(
                routeName: "ODataRoute",
                routePrefix: null,
                model: builder.GetEdmModel());

            // Web API configuration and services

            /* Remove the original code
            // Web API routes
            config.MapHttpAttributeRoutes();

            config.Routes.MapHttpRoute(
                name: "DefaultApi",
                routeTemplate: "api/{controller}/{id}",
                defaults: new { id = RouteParameter.Optional }
            );
            */
        }

Now we will add a controller. We will add one for Products.

This produces the code:

If we run this now, we see:

Now, add a directive to using System.Web.OData;

Change teh class to an ODataController.

Change the IEnumerable Get to return Products:

    public class ProductsController : ODataController
    {
        adventureworksContext db = new adventureworksContext();
        protected override void Dispose(bool disposing)
        {
            db.Dispose();
            base.Dispose(disposing);
        }

        // GET api/<controller>
        public IEnumerable<Product> Get()
        {
            return db.Products;
        }

Now when we run this, products are returned as OData:

From here, you can add create, update, delete.

THANKS FOR READING. BEFORE YOU LEAVE, I NEED YOUR HELP.
 

I AM SPENDING MORE TIME THESE DAYS CREATING YOUTUBE VIDEOS TO HELP PEOPLE LEARN THE MICROSOFT POWER PLATFORM.

IF YOU WOULD LIKE TO SEE HOW I BUILD APPS, OR FIND SOMETHING USEFUL READING MY BLOG, I WOULD REALLY APPRECIATE YOU SUBSCRIBING TO MY YOUTUBE CHANNEL.

THANK YOU, AND LET'S KEEP LEARNING TOGETHER.

CARL

https://www.youtube.com/carldesouza

 

ABOUT CARL DE SOUZA

Carl de Souza is a developer and architect focusing on Microsoft Dynamics 365, Power BI, Azure, and AI.

carldesouza.comLinkedIn Twitter | YouTube

 

2 Responses to Creating an OData Service for a SQL Server Database

  1. I am using visual studio 2017 and attempting to build a simple oData service for a single table and my visual studio cannot find this and related objects (ODataModelBuilder). Do you know what i need to reference and include in my “Using” statements in order for that to work?

  2. trying the same thing, single table OData endpoint.

    To get this to work I had to put these at start of WebApiConfig.cs
    using Microsoft.AspNet.Odata.Builder;
    using Microsoft.AspNet.Odata.Extensions;

Leave a Reply

Your email address will not be published. Required fields are marked *