Using OAuth and CData ADO.NET Providers to Connect your App to OData Services

The OAuth process in application code can be a bit cumbersome to follow. This sample demonstrates a generic solution for CData ADO.NET Providers to connect to OData sources.

Preparations

Apply for OAuth for the data source you are gonna access.
When registration is complete you should have retrieved values for

• OAuth Client ID (this value can also be called "Consumer Key" or similar)
• OAuth Client Secret (this value can also be called "Consumer Secret" or similar)

and you should also have noted addresses for

• OAuth Authorization URL
• OAuth Access Token URL

These four values are retrieved from the data source OAuth service.

Connecting a desktop application

Retrieving the Access Token and Access Token Secret

The following sample code will cause the users default browser to launch the login and permission grant pages at the OAuth data source website. The CData driver will handle the retrieval of the Access Token and Access Token Secret. (Google URLs in the sample are changed as desired).

string OAuthAccessToken = "";
string OAuthAccessTokenSecret = "";

using (
    ODataConnection conn = new ODataConnection(
    "URL=http://dataService.com;OAuth Version=2.0;OAuth Client ID=myRegisteredClientID;OAuth Client Secret=secretCodeRetrievedUponClientRegistration;OAuth Authorization URL=https://dataService.com/oauth2/auth;OAuth Access Token URL=https://dataService.com/oauth2/token;"
    )
) {
  ODataCommand cmd = new ODataCommand("GetOAuthAccessToken", conn);
  cmd.CommandType = System.Data.CommandType.StoredProcedure;
  cmd.Parameters.Add(new ODataParameter("CallbackUrl", "http://localhost/success.html"));
  // Add other parameters as needed...

  ODataDataReader rdr = cmd.ExecuteReader();
  rdr.Read();

  OAuthAccessToken = rdr["OAuthAccessToken"].ToString();
  OAuthAccessTokenSecret = rdr["OAuthAccessTokenSecret"].ToString();
}

Retrieve data using the previously obtained access token

using (
    ODataConnection conn = new ODataConnection(
    "URL=http://dataService.com;OAuth Version=2.0; OAuth Access Token=" + OAuthAccessToken + ";"
    )
) {
    // Declare and execute commands
    // Process readers
}

Connecting a web application

To connect a web app, there is an additional step of getting the verifier code. You can do this using the
GetOAuthAuthorizationUrl procedure.

The page example below has a single button to start the OAuth process. When you press the button, we redirect the user to the authorization URL and allow them to log in. Importantly, we set the CallbackUrl to the current page. When the user is redirected back to our page, we get the verifier code from the response. From here the flow is the same as the desktop app except that we set the verifier code and also set the Auth Mode to WEB.

using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.RSSBus.OData;
using System.Data;

namespace RSSBus.OData.Demos {
  public partial class odataDemo : System.Web.UI.Page {

    string connectionString = "URL=http://dataService.com;OAuth Version=2.0;OAuth Client ID=myRegisteredClientID;OAuth Client Secret=secretCodeRetrievedUponClientRegistration;OAuth Authorization URL=https://dataService.com/oauth2/auth;OAuth Access Token URL=https://dataService.com/oauth2/token;";

    protected void btnOAuthConnect_Click(object sender, EventArgs e) {
      ODataConnection conn = new ODataConnection(connectionString);
      ODataCommand cmd = new ODataCommand("GetOAuthAuthorizationURL", conn);
      String Thispage = "http://" + Request.ServerVariables["SERVER_NAME"] + ":" + Request.ServerVariables["SERVER_PORT"] + Request.ServerVariables["URL"];
      Session["ThisPage"] = Thispage;
      cmd.Parameters.Add(new ODataParameter("@AuthMode", "WEB"));
      cmd.Parameters.Add(new ODataParameter("@CallbackURL", Thispage));
      cmd.Parameters.Add(new ODataParameter("@ResponseType", "Code"));
      cmd.CommandType = CommandType.StoredProcedure;
      ODataDataReader dr = cmd.ExecuteReader();
      String URL = "";
      while (dr.Read()) {
        URL = ((String)dr["URL"]);
      }
      Response.Redirect(URL, true);
    }
    
    protected void Page_Load(object sender, EventArgs e) {
      if (Request.QueryString["code"] != null && tbAccessToken.Text == "") {
        GetAccessToken();
      }
    }

    protected void GetAccessToken() {
      using (ODataConnection connection = new ODataConnection(connectionString)) {
        ODataCommand cmd = new ODataCommand("GetOAuthAccessToken", connection);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new ODataParameter("@AuthMode", "WEB"));
        cmd.Parameters.Add(new ODataParameter("@Verifier", Request.QueryString["code"]));
        cmd.Parameters.Add(new ODataParameter("@CallbackURL", Session["ThisPage"]));
        cmd.Parameters.Add(new ODataParameter("@Grant_Type", "Code"));

        ODataDataReader rdr = cmd.ExecuteReader();
        while (rdr.Read()) {
          tbAccessToken.Text = rdr["OAuthAccessToken"] + "";
        }
        btnOAuthConnect.Text = "Get Data";
        connectionString = String.Format("Offline=False;OAuth Access Token={0};OAuth Version=2.0;URL=http://dataService.com;", tbAccessToken.Text);
      }
    }
  }
}