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:

[sourcecode language=”csharp”] 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 }
);
*/
}
[/sourcecode]

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:

[sourcecode language=”csharp”] 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;
}
[/sourcecode]

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

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