ADO.NET Implementation of Google API IDataStore

• Download source code - 2 KB

Introduction

During the last few days, I was working on a project with Google OAuth Gmail API, but I needed to store my Google token in the database. Because the implementation of Google is only to store the communication token in a file, I had to implement a custom class to do that.

Background

Google Gmail API offers different services that are very useful, but with the new security technology of OAuth. This protocol, despite its better security, forces us to upgrade our e-mail systems.

This article does not give you a tutorial to install the OAuth G-Mail service. IF you want that, I recommended you the following link in Google: https://developers.Google.com/identity/protocols/OAuth2

The basics of the service is comprise in the following operations:

• You request the service from Gmail API with the credential that you got when you register in Google Development Console.
• Because this credential is only to recognize you, the Gmail returns a web login page and/or a web page that allows you to identify in the system and also authorize the service that you program request.
• If you authorize, then a token information is downloaded to you. Normally, this information is managed by the Google API and stores it in a Store File on your computer.

The problem with that for us, was that we need to store the credentials in a Database Table.

To do that, we implement the IDataStore interface that Google API offers to customize our Token Storage.

Using the Code

We develop in C# for an ASP.NET classical application. Then, we create a class that implements the IDataStore to store the token information in our database.

The code of the class is the following:

using System;
using System.Data.SqlClient;
using System.Threading.Tasks;
using Google.Apis.Json;
using Google.Apis.Util.Store;

namespace GMailApiQuickStart
{
    /// <summary>
    /// Store Gmail token in the database
    /// The information is stored in table OauthToken
    /// OAuth token has only three fields 
    /// ID UserKey and Token
    /// </summary>
    public class DbDataStore : IDataStore
    {
        private readonly string conextionDb;

        /// <summary>
        /// Constructor. Get the conexion string
        /// for your database.
        /// </summary>
        /// <param name="conexionString"></param>
        public DbDataStore(string conexionString)
        {
            conextionDb = conexionString;
        }

        #region Implementation of IDataStore

        /// <summary>
        /// Asynchronously stores the given value for the given key (replacing any existing value).
        /// </summary>
        /// <typeparam name="T">The type to store in the data store.</typeparam>
        /// <param name="key">The key.</param>
        /// <param name="value">The value to store.</param>
        public Task StoreAsync<T>(string key, T value)
        {
            if (string.IsNullOrEmpty(key))
            {
                throw new ArgumentException("Key MUST have a value");
            }

            string contents = NewtonsoftJsonSerializer.Instance.Serialize((object)value);
            var conn = new SqlConnection(conextionDb);
            var comm = new SqlCommand("SELECT COUNT(*) FROM OAuthToken WHERE UserKey = @Param1", conn);
            comm.Parameters.AddWithValue("@Param1", key);
            conn.Open();
            try
            {
                var res = comm.ExecuteScalar();
                if ((int)res == 0)
                {
                    // Insert token
                    comm = new SqlCommand("INSERT INTO OAuthToken (UserKey, Token) 
                                           VALUES (@Param1, @Param2)", conn);
                    comm.Parameters.AddWithValue("@Param1", key);
                    comm.Parameters.AddWithValue("@Param2", contents);
                }
                else
                {
                    //Update token
                    comm = new SqlCommand("UPDATE OAuthToken SET Token = @Param2 
                                           WHERE UserKey = @Param1", conn);
                    comm.Parameters.AddWithValue("@Param1", key);
                    comm.Parameters.AddWithValue("@Param2", contents);
                }

                var exec = comm.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }

            return TaskEx.Delay(0);
        }

        /// <summary>
        /// Asynchronously deletes the given key. The type is provided here as well 
        /// because the "real" saved key should
        /// contain type information as well, so the data store will be able to store 
        /// the same key for different types.
        /// </summary>
        /// <typeparam name="T">
        /// The type to delete from the data store.
        /// </typeparam>
        /// <param name="key">The key to delete.</param>
        public Task DeleteAsync<T>(string key)
        {
            var conn = new SqlConnection(conextionDb);
            var comm = new SqlCommand("DELETE OAuthToken WHERE UserKey = @Param1", conn);
            comm.Parameters.AddWithValue("@Param1", key);
            conn.Open();
            try
            {
                var res = comm.ExecuteScalar();
            }
            finally
            {
                conn.Close();
            }

            return TaskEx.Delay(0);
        }

        /// <summary>
        /// Asynchronously returns the stored value for the given key or <c>null</c> if not found.
        /// </summary>
        /// <typeparam name="T">The type to retrieve from the data store.</typeparam>
        /// <param name="key">The key to retrieve its value.</param>
        /// <returns>
        /// The stored object.
        /// </returns>
        public Task<T> GetAsync<T>(string key)
        {
            if (string.IsNullOrEmpty(key))
            {
                throw new ArgumentException("Key MUST have a value");
            }

            TaskCompletionSource<T> completionSource = new TaskCompletionSource<T>();
            var conn = new SqlConnection(conextionDb);
            var comm = new SqlCommand("SELECT Token FROM OAuthToken WHERE UserKey = @Param1", conn);
            comm.Parameters.AddWithValue("@Param1", key);
            conn.Open();
            try
            {
                var res = comm.ExecuteScalar();
                if (res == null || string.IsNullOrWhiteSpace(res.ToString()))
                {
                    completionSource.SetResult(default(T));
                }
                else
                {
                     completionSource.SetResult(NewtonsoftJsonSerializer
                                                .Instance.Deserialize<T>(res.ToString()));
                }
            }
            catch (Exception ex)
            {
                completionSource.SetException(ex);
            }
            finally
            {
                conn.Close();
            }
            return completionSource.Task;
        }

        /// <summary>
        /// Asynchronously clears all values in the data store.
        /// </summary>
        public Task ClearAsync()
        {
            var conn = new SqlConnection(conextionDb);
            var comm = new SqlCommand("TRUNCATE TABLE OAuthToken", conn);
            conn.Open();
            try
            {
                var res = comm.ExecuteNonQuery();
            }

            finally
            {
                conn.Close();
            }

            return TaskEx.Delay(0);
        }

        #endregion
    }
}

To use this class, you need to supply it your conection string. I tested this using a modification of Quick start of Google API Gmail. You can find the original code and the installer here.

I include in the code a script to generate the table if you want to reply in the test environment. Of course, you can change the queries in the class to adapt it to your system.

To test it, we recommend to download the quick start and install on your computer, create the database using the script that is a companion of this article, and modify it. The code of the class DbDataStore is also in the zip file.

using System;
using System.Collections.Generic;
using System.Configuration;
using System.IO;
using System.Threading;
using Google.Apis.Auth.OAuth2;
using Google.Apis.Gmail.v1;
using Google.Apis.Gmail.v1.Data;
using Google.Apis.Services;

namespace GMailApiQuickStart
{
    // This is a modified version of Gmail API Net Quickstart from Google
    class Program
    {
        // If modifying these scopes, delete your previously saved credentials
        // at ~/.credentials/gmail-dotnet-quickstart.json
        static string[] Scopes = { GmailService.Scope.GmailReadonly };
        static string ApplicationName = "Gmail API .NET Quickstart";

        static void Main(string[] args)
        {
            UserCredential credential;

            using (var stream =
                new FileStream("client_secret.json", FileMode.Open, FileAccess.Read))
            {
                var conexion = ConfigurationManager.ConnectionStrings["Google"].ConnectionString;

                credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
                    GoogleClientSecrets.Load(stream).Secrets,
                    Scopes,
                    "user",
                    CancellationToken.None,
                    new DbDataStore(conexion)).Result;
            }

            // Create Gmail API service.
            var service = new GmailService(new BaseClientService.Initializer()
            {
                HttpClientInitializer = credential,
                ApplicationName = ApplicationName,
            });

            // Define parameters of request.
            UsersResource.LabelsResource.ListRequest request = service.Users.Labels.List("me");

            // List labels.
            IList<Label> labels = request.Execute().Labels;
            Console.WriteLine("Labels:");
            if (labels != null && labels.Count > 0)
            {
                foreach (var labelItem in labels)
                {
                    Console.WriteLine("{0}", labelItem.Name);
                }
            }
            else
            {
                Console.WriteLine("No labels found.");
            }
            Console.Read();
        }
    }
} 

If you see when you call the GoogleWebAuthorizationBroker, you change the FileStore parameter for DbDataStore class described in this article. That is all!

Points of Interest

You can extend this idea and also put the secret file in your database. Then all the managing of Google OAuth can be translated to the database and not used files in your system.

History

• First version

NOTE: Google and G-Mail are registered trademarks of Google.