Creating an OData service using .NET Data Provider for Teradata

Connectivity
Connectivity covers the mechanisms for connecting to the Teradata Database, including driver connectivity via JDBC or ODBC.
Highlighted
Teradata Employee

Creating an OData service using .NET Data Provider for Teradata

The Open Data Protocol (OData) is a protocol to share or exchange data over the web.  The protocol was developed by Microsoft in 2007 as part of the ADO .NET Data Services technology (a.k.a. Project Astoria) for creating and consuming data services using the Web. This protocol became widely used that Microsoft gave the protocol its own identification, named it OData and considered ADO.NET Data Services as the .NET implementation of OData.

OData is a REST based protocol for CRUD style operations against a data source that is exposed as a data service. The data that is being exposed can come from a variety of sources including but not limited to relational databases, file systems even simple .NET classes.

The protocol allows a consumer to query a data source over the HTTP protocol and get the results back in formats like Atom (default), JSONP. It provides an entire query language directly in the URL format to retrieve entries from a service.  It also allows pagination, ordering and filtering of entries that are retrieved.

When you query an OData producer, what you get back are feeds. Feeds are nothing but a collection of typed entries. Each entry represents a structured record with a key that has a list of properties of primitive or complex types. Entries can be part of a type hierarchy and may have related entries and related feeds through links.

In addition to feeds and entries, OData services can expose service operations. Service operations are functions that accept input parameters and return entries of complex or primitive types.

OData services may expose 2 types of metadata documents.

    1. Service document that lists all top level feeds so clients can discover them and find out the address of each of them. The service document is typically available at the service root URI.
    1. It may also expose a service metadata document that describes the data model. i.e. the structure and organization of all the resources.

An OData Service can be created to expose any data structure (relational database, files, .NET classes) that return an implementation of IQueryable interface.  It will support update, delete, create operations on it if it implements an IUpdatable interface. 

NOTE: From .NET Framework 4.0, ADO.NET Data Services is renamed as Windows Communication Foundation (WCF for short) Data Services and will be referred as WCF Data Services for the rest of this article.

Developing an OData Producer

In the following sections, we will create a web service that exposes relational data as an OData endpoint (producer) and also a client application that consumes those OData feeds. We will also see how to query the service using a browser, different query string options, create a service operation, options to secure the data service using authorization, query and change interceptors. 

We will be using WCF Data Services, a component of .NET Framework, for creating an OData web service (producer). WCF Data Services also includes a client library which we will use to build a consumer. 

With WCF Data Services and ADO.NET Entity Framework, it is extremely easy to expose a relational database as an OData feed. All we need to is to create an Entity Framework model created over the relational data.

Prerequisites

 To proceed further, you will need to have access to the following:

    1.  Visual Studio 2010,
    1. .NET Data Provider for Teradata 13.11.00.01 (or above),
    1. Teradata database version 12 (or above).

The DDL/DML statements required to create the tables, constraints and populate the tables with data are available as a file attachment (DatabaseSetup.txt) (see Attachments in the side bar). You can use BTEQ or SQLA to create the setup against the Teradata database of your choice. The database setup needs to be complete before we can proceed with creating the OData service.

Once the database setup is complete, follow the below steps to create an OData service:

1) From VS2010, select File -> New Project -> ASP.NET Web Application. Name it ODataCRMService.

2) In Solution Explorer, right-click the name of the ASP.NET project, and then click Add New Item.

 3) In the Add New Item dialog box, click the Data template and then select ADO.NET Entity Data Model. Name the data model as ODataCRM.edmx.

 4) In the Entity Data Model Wizard, select Generate from database.

 5) Click on New Connection and provide the details about where you have created the tables in the Teradata database. Select the radio button Yes, include the sensitive data in the connection string, select the checkbox Save entity connection settings in Web.config as and change it to CRMEntities and click Next.

 

6) In Choose Your Database Objects page, under Tables, select Vehicles, Branch, Customer and Reservations tables. Uncheck Include foreign key columns in the model and change the Model Namespace to CRMEntitiesModel. Select Finish.

NOTE: Leaving the Include foreign key columns in the model checkbox selected (default) will create scalar properties on entity types that are mapped to foreign key columns in the database and foreign key associations are created between entities. Clearing this check box will cause independent associations to be created between entity types.

7) The Entity Data Model generated should look like this:

8) The next step is to create the data service. In Solution Explorer, right click on the name of the project and click on Add new item.

9) In the Add New Item dialog box, select WCF Data Service and give the name ODataCRMService.

 10) Visual Studio then creates the code and other files necessary for the new service and opens up the code for the data service in the code-editor.

11) You will see  DataService< /* TODO: put your data source class name here */ >. Replace the /* TODO: put your data source class name here */ with the Entity framework context created earlier in step 5. i.e. CRMEntities. The code should look like below:

namespace ODataCRMService
{
    public class ODataCRMService : DataService< CRMEntities >
    {
        // This method is called only once to initialize service-wide policies.
        public static void InitializeService(DataServiceConfiguration config)
        {
            // TODO: set rules to indicate which entity sets and service operations are visible, updatable, etc.
            // Examples:
            // config.SetEntitySetAccessRule("MyEntityset", EntitySetRights.AllRead);
            // config.SetServiceOperationAccessRule("MyServiceOperation", ServiceOperationRights.All);
            config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V2;
        }
    }
}

 12) DataService class is the entry point for developing the service. In WCF Data Services, a data service is a class that inherits from the DataService class, where the type of the data service is the entity container of the data model. This entity container has one or more properties that return an IQueryable, which are used to access entity sets in the data model. As mentioned earlier, the DataService class isn’t restricted to just working with Entity framework contexts and will accept any collection of CLR objects that implements the IQueryable interface.

 13) The next thing to look at in the code is the InitializeService method. This method takes a DataServiceConfiguration class as input. The behaviors of the data service are defined by the members of the DataServiceConfiguration class, and by members of the DataServiceBehavior class.  The DataServiceBehavior class can be accessed from the DataServiceBehavior property of the DataServiceConfiguration class. In WCF Data Services, you must explicitly grant access to the resources that are exposed by a data service. This means that after you create a new data service, you must still explicitly provide access to individual resources as entity sets. This enables authorized clients to have read and write access to the specified entity sets and read-only access to the remaining entity sets. This authorization is enabled by the SetEntitySetAccessRule method of the DataServiceConfiguration class.

14) For the sake of simplicity, we will expose all resources as read only by the service and later in the below sections we will see how to define read, write access separately for each entity set exposed by the data service. We will also see how to define a service operation and how to define access rule for the service operation later in this article.  For now, modify the InitializeService method as show in the code below

        public static void InitializeService(DataServiceConfiguration config)
        {
            // TODO: set rules to indicate which entity sets and service operations are visible, updatable, etc.
            // Examples:
             config.SetEntitySetAccessRule("*", EntitySetRights.AllRead);
            // config.SetServiceOperationAccessRule("MyServiceOperation", ServiceOperationRights.All);
            config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V2;
        }

15) Your data service is now ready to be exposed,  simply hit Ctrl+F5 within Visual Studio, which will start the development web server and will run the Data Services server inside it.

16) When you run the service, a browser window will open and display the service document.

We will now see how to access the resources in this service using the powerful URI syntax from the browser. As mentioned earlier, OData provides an entire query language directly in the URL format you use to retrieve entries from a service. We will later develop a .NET consumer application that consumes this service.

To access a feed for each entity set, you append the name of the entity set to the base URI for the service; for example, http://localhost:33574/ODataCRMService.svc/Vehicles would address the set of entities in the Vehicles entity set.

Note: In the above instance, ASP.NET development server hosted the service on 33574. In your case the port number could be different and you will need to use that for your queries.

Also since OData is an application of Atom format, the results we get back are in feeds. To view the results in XML format instead, disable the feed reading option in Internet Explorer. To do so, from Internet Explorer, go to the Tools menu, Internet Options. Go to the Content tab, and click the Settings button located in the Feed and Web Slice Settings section. Disable the Turn on feed reading view option and click OK.

 

If you re-run the above query in the browser, you should get back the result in  AtomPub format.

It’s also possible to address a particular entity individually using its key value; the URI, http://localhost:33574/ODataCRMService.svc/Vehicles(1) would address the property with VehicleID = 1. You can address a relationship from this entity to another entity or set of entities by appending the name of the relationship to the end of the URI, so http://localhost:33574/ODataCRMService.svc/Vehicles(1)/Reservations would access the set of reservations for VehicleID = 1. Using this syntax, it’s possible to navigate through many levels of relationships.

Query Options

The URI syntax also defines a number of query options that can be appended to a URI to modify the base query in some way, and each query option is defined as a name/value pair. Query options are query string parameters a client may specify to control the amount and order of the data that an OData Service returns for the resource identified by the URI. The names of all system query options are prefixed with a “$” character. 

Few examples:

$top can be used to restrict or select only the first N items of the returned set. http://localhost:33574/ODataCRMService.svc/Reservations?$top=2. Gives only the first 2 reservation entries returned.

$skip can be used to skip the first N items from the returned set and select only the remaining entries starting with N+1 http://localhost:33574/ODataCRMService.svc/Reservations?$skip=4&$top=2&$orderby=ReservationID Gives the 5th and 6th reservation entries. In the above example, we order by ReservationId, skip the first 4 items and select the remaining 2.

$filter can be used to specify an expression to restrict the results returned by the query. http://localhost:33574/ODataCRMService.svc/Vehicles?$filter=Category eq 'suv'. This query gives the list of SUV vehicles.

$expand option allows you to identify related entries with a single URI such that a graph of entries could be retrieved with a single HTTP request. http://localhost:33574/ODataCRMService.svc/Vehicles(2)?$expand=Reservations This query retrieves vehicle with VehicleId=2 along with all of its reservation entities.

For a comprehensive list of the different query string options, see here

Service Operations

WCF Data Services enables one to define service operations on a data service to expose methods on the server. Like other data service resources, service operations are addressed by URIs.  A service operation can return collections of entity types, single entity type instances, and primitive types, such as integer and string. These kinds of service operations are defined as methods that have the WebGet attribute applied.

In the following section, we will implement a service operation to retrieve the list of reservations made for a given VehicleID.

Please add the following code:

        [WebGet]
        public IQueryable<Reservation> GetReservationsByVehicle(Int32 vehicleID)
        {

            CRMEntities ctx = this.CurrentDataSource;

            var selectedReservations = from reservations in ctx.Reservations.Include("Vehicle")
                                       where reservations.Vehicle.VehicleID == vehicleID
                                       select reservations;

            return selectedReservations;

        }

Before we could access the service operation, from say a browser, we need to expose it. This is done via the SetServiceOperationAccessRule method of the DataServiceConfiguration class. Please modify the InitializeService method as below:

        public static void InitializeService(DataServiceConfiguration config)
        {
            // TODO: set rules to indicate which entity sets and service operations are visible, updatable, etc.
            // Examples:
             config.SetEntitySetAccessRule("*", EntitySetRights.AllRead);
             config.SetServiceOperationAccessRule("GetReservationsByVehicle", ServiceOperationRights.All);
             config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V2;
        }

After you rebuild the service, you should be able to access the GetReservationsByVehicle service operation like below:

http://localhost:33574/ODataCRMService.svc/GetReservationsByVehicle?vehicleid=2

The above query should list only the reservations made for Vehicle with vehicleid 2

Interceptors

WCF Data Services enables an application to intercept request messages so that you can add custom logic to an operation. Interceptors are defined on a per-entity set basis and they do not accept parameters. These interceptor methods will be invoked whenever the entities are accessed. There are 2 types of interceptors - QueryInterceptor and ChangeInterceptor.

Query interceptor methods, which are called when processing an HTTP GET request, must return a lambda expression that determines whether an instance of the interceptor's entity set should be returned by the query results.

In addition to intercepting queries, update operations (insert, update & delete) also support interceptors and these are called change interceptors.

The following steps will show you how to add a query interceptor.

To define a query interceptor, you will need to add the following using statement:

using System.Linq.Expressions;

and add QueryInterceptor attribute to the method in which you would like to add the logic to intercept the message and perform some operation.

Lets say, there are Branches that are obsolete and we do not want to return those details when queried. The below QueryInterceptor will intercept any query made on Branches entity and will not return those Branches whose address are set to obsolete.

        [QueryInterceptor("Branches")]
        public Expression<Func<Branch, bool>> OnQueryBranches()
        {
            return (o) => o.Address != "Obsolete location";
        }

If you execute the following URI from your browser http://localhost:33574/ODataCRMService.svc/Branches you should get 3 Branch entities before you implement the QueryInterceptor and 2 Branch entities after adding the QueryInterceptor. You will notice that the Branch whose address is set to Obsolete location is not returned anymore when you query for Branches.

We will also create the following ChangeInterceptor that will intercept when a new customer entity is added and make sure that the customer name is not left blank. We will demonstrate its use with the client application we will develop in the below section.

        [ChangeInterceptor("Customers")]
        public void OnCustomerAdd(Customer c, UpdateOperations ops)
        {
            if (ops == UpdateOperations.Add)
            {
                if (c.Name.Trim().Equals(String.Empty))
                {
                    throw new DataServiceException(403, "Customer names must not be empty");
                }
            }
        }

Before we proceed with developing a .NET client to consume the service, please modify the InitializeService method to grant write permissions to Reservations and Customers entities. This would allow a consumer/client to create a new Reservation, Customer entity. Leave the Branch and Vehicle entities are read only.

The modified InitializeService method is as follows:

        public static void InitializeService(DataServiceConfiguration config)
        {
             config.SetEntitySetAccessRule("Branches", EntitySetRights.AllRead);
             config.SetEntitySetAccessRule("Vehicles", EntitySetRights.AllRead);
             config.SetEntitySetAccessRule("Reservations", EntitySetRights.AllRead | EntitySetRights.AllWrite);
             config.SetEntitySetAccessRule("Customers", EntitySetRights.AllRead | EntitySetRights.AllWrite);

             config.SetServiceOperationAccessRule("GetReservationsByVehicle", ServiceOperationRights.All);
             config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V2;
        }

Developing an OData Consumer

The above section showed how to consume an OData service from a browser. OData has a client SDK for various platforms such as JavaScript, Objective-C, PHP and Java. As far as .NET is concerned, Visual Studio automatically creates all the necessary proxy entity classes to interact with the OData service and perform all supported CRUD operations.

The following steps demonstrate how to create a simple client that consumes the OData service hosted and also perform some CRUD operations.

1) From VS2010, Select File -> New -> Project  and select Console Application

2) From Solution Explorer, right click References , select Add Service Reference

3) In the Address text box, specify the address of the service document obtained by running the OData service. For example, in my case, I had to enter http://localhost:33574/ODataCRMService.svc/ and when I select Go, I get back CRMEntities service with Branches, Customers, Reservations, Vehicles entities. Change the Namespace to CRMServiceReference and click on OK.

4) You are now ready to consume the service in the application and the following code sample will demonstrate the select functionality.

        static void SelectVehicles()
        {
            CRMServiceReference.CRMEntities cs = new CRMServiceReference.CRMEntities(new Uri("http://localhost:33574/ODataCRMService.svc/"));

            var result = (from c in cs.Vehicles select new { c.VehicleID, c.Model });

            foreach (var r in result)
            {
                Console.WriteLine(r.VehicleID);
                Console.WriteLine(r.Model);
            }
        }

5) Next we will try to add a Customer with an empty name and see if our ChangeInterceptor intercepts the request and returns an exception.

        static void AddCustomer()
        {
            CRMServiceReference.CRMEntities cs = new CRMServiceReference.CRMEntities(new Uri("http://localhost:33574/ODataCRMService.svc/"));
            CRMServiceReference.Customer newCustomer = CRMServiceReference.Customer.CreateCustomer(3, "", "Address3", "1234");
            cs.AddToCustomers(newCustomer);

            try
            {
                DataServiceResponse response = cs.SaveChanges();
            }
            catch (DataServiceRequestException ex)
            {
                Console.WriteLine(ex.InnerException.Message);
            }
        }

When you execute the above method, the ChangeInterceptor we implemented in the OData producer service would be invoked since we are trying to create a new customer and would throw “Customer names must not be empty” exception as the customer name field is found to be empty. If we specify a non empty string in the CreateCustomer method above, a new customer would be created and a corresponding row would be inserted in the database.

6) Following code snippets shows how to Create, Update and Delete a resource exposed by the OData service.

        static void CreateNewReservation()
        {
            CRMServiceReference.CRMEntities cs = new CRMServiceReference.CRMEntities(new Uri("http://localhost:33574/ODataCRMService.svc/"));
            CRMServiceReference.Reservation newReservation = new CRMServiceReference.Reservation();

            newReservation.ReservationID = 100;

            newReservation.Branch = (from b in cs.Branches where b.BranchID == 1 select b).FirstOrDefault();
            newReservation.Vehicle = (from v in cs.Vehicles where v.VehicleID == 8 select v).FirstOrDefault();
            newReservation.Customer = (from c in cs.Customers where c.CustomerID == 2 select c).FirstOrDefault();

            cs.AddToReservations(newReservation);
            cs.SetLink(newReservation, "Branch", newReservation.Branch);
            cs.SetLink(newReservation, "Vehicle", newReservation.Vehicle);
            cs.SetLink(newReservation, "Customer", newReservation.Customer);

            try
            {
                DataServiceResponse response = cs.SaveChanges();
            }
            catch (DataServiceRequestException ex)
            {
                Console.WriteLine(ex.InnerException.Message);
            }
        }
        static void UpdateReservation()
        {
            CRMServiceReference.CRMEntities cs = new CRMServiceReference.CRMEntities(new Uri("http://localhost:33574/ODataCRMService.svc/"));

            var reservationToChange = (from r in cs.Reservations
                                       where r.ReservationID == 100
                                       select r).Single();

            reservationToChange.Vehicle = (from v in cs.Vehicles where v.VehicleID == 6 select v).FirstOrDefault();

            cs.SetLink(reservationToChange, "Vehicle", reservationToChange.Vehicle);
            cs.UpdateObject(reservationToChange);

            try
            {
                DataServiceResponse response = cs.SaveChanges();
            }
            catch (DataServiceRequestException ex)
            {
                Console.WriteLine(ex.InnerException.Message);
            }

        }
        static void DeleteReservation()
        {
            CRMServiceReference.CRMEntities cs = new CRMServiceReference.CRMEntities(new Uri("http://localhost:33574/ODataCRMService.svc/"));

            var reservationToDelete = (from r in cs.Reservations
                                       where r.ReservationID == 100
                                       select r).Single();

            cs.DeleteObject(reservationToDelete);

            try
            {
                DataServiceResponse response = cs.SaveChanges();
            }
            catch (DataServiceRequestException ex)
            {
                Console.WriteLine(ex.InnerException.Message);
            }
        }


For Further Reference

Given below are some links that you can use to further enhance your knowledge on OData (given in no particular order or preference).

    • www.odata.org  is the official site for OData and has an overview of the protocol, the actual specifications, a set of different producers, consumers, links to some articles, videos that could help developers, a blog maintained by the Microsoft Data Services team and list of FAQs etc. 

Below are some videos that give some insight on how OData originated and tutorials on creating and consuming a OData service