Creating an OData Service for a SQL Server Database

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.

ABOUT CARL DE SOUZA

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

carldesouza.com | LinkedIn | Twitter | YouTube

Facebook

Twitter

Linkedin