Google API Authentication Data Store for .NET 4.6+ and SQL Server using ADO.NET

DatabaseDataStore.cs

namespace GmailTest.Google
{
    using System;
    using System.Configuration;
    using System.Data.SqlClient;
    using System.Threading.Tasks;

    using global::Google.Apis.Json;
    using global::Google.Apis.Util.Store;

    using JetBrains.Annotations;

    /// <summary>
    ///     The database data store.
    /// </summary>
    public class DatabaseDataStore : IDataStore
    {
        #region Constants

        /// <summary>
        ///     The users table name
        /// </summary>
        private const string UsersTableName = "GoogleUsers";

        /// <summary>
        ///     The users table schema
        /// </summary>
        private const string UsersTableSchema = "dbo";

        #endregion

        #region Static Fields

        /// <summary>
        ///     The connection string
        /// </summary>
        private static readonly string ConnectionString = ConfigurationManager.ConnectionStrings["GmailTestConnectionString"].ConnectionString;

        #endregion

        #region Constructors and Destructors

        /// <summary>
        ///     Initializes a new instance of the <see cref="DatabaseDataStore" /> class.
        /// </summary>
        public DatabaseDataStore()
        {
            Initialize().Wait();
        }

        #endregion

        #region Public Methods and Operators

        /// <summary>Creates a unique stored key based on the key and the class type.</summary>
        /// <param name="key">The object key</param>
        /// <param name="t">The type to store or retrieve</param>
        public static string GenerateStoredKey(string key, Type t) => $"{t.FullName}-{key}";

        /// <summary>
        ///     Clears all values in the data store.
        /// </summary>
        public async Task ClearAsync()
        {
            using (var connection = new SqlConnection(ConnectionString))
            {
                await connection.OpenAsync();

                // Removes all data from the Table.
                var truncateUserTableSql = $"truncate table [{UsersTableSchema}].[{UsersTableName}]";
                using (var command = new SqlCommand(truncateUserTableSql, connection))
                {
                    await command.ExecuteNonQueryAsync();
                }
            }
        }

        /// <summary>
        ///     Deletes the user with the given key.
        /// </summary>
        /// <param name="key">The key to delete from the data store</param>
        public async Task DeleteAsync<T>(string key)
        {
            if (string.IsNullOrWhiteSpace(key))
            {
                throw new ArgumentNullException(nameof(key));
            }

            using (var connection = new SqlConnection(ConnectionString))
            {
                await connection.OpenAsync();

                // Delete the user's data.
                var deleteString = $"delete from [{UsersTableSchema}].[{UsersTableName}] where UserName = @key";

                using (var command = new SqlCommand(deleteString, connection))
                {
                    command.Parameters.AddWithValue("@key", key);
                    await command.ExecuteNonQueryAsync();
                }
            }
        }

        /// <summary>
        ///     Returns the stored value for the given key or <c>null</c> if the matching record doesn't exist.
        /// </summary>
        /// <typeparam name="T">The type to retrieve</typeparam>
        /// <param name="key">The key to retrieve from the data store</param>
        /// <returns>The stored object</returns>
        public async Task<T> GetAsync<T>(string key)
        {
            // Key is the user string sent with AuthorizeAsync
            if (string.IsNullOrWhiteSpace(key))
            {
                throw new ArgumentNullException(nameof(key));
            }

            using (var connection = new SqlConnection(ConnectionString))
            {
                await connection.OpenAsync();

                var refreshTokenSql = $"select RefreshToken from [{UsersTableSchema}].[{UsersTableName}] where UserName = @username;";

                using (var command = new SqlCommand(refreshTokenSql, connection))
                {
                    command.Parameters.AddWithValue("@username", key);

                    var refreshToken = (await command.ExecuteScalarAsync())?.ToString();

                    // If null then token not found in database else token found so return it.
                    return string.IsNullOrWhiteSpace(refreshToken) ? default(T) : NewtonsoftJsonSerializer.Instance.Deserialize<T>(refreshToken);
                }
            }
        }

        /// <summary>
        ///     Stores the given value for the given key.
        /// </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 in the data store</param>
        public async Task StoreAsync<T>([NotNull] string key, T value)
        {
            if (string.IsNullOrWhiteSpace(key))
            {
                throw new ArgumentNullException(nameof(key));
            }

            var serialized = NewtonsoftJsonSerializer.Instance.Serialize(value);

            using (var connection = new SqlConnection(ConnectionString))
            {
                await connection.OpenAsync();

                using (var command = new SqlCommand($"select UserId from [{UsersTableSchema}].[{UsersTableName}] where UserName = @username", connection))
                {
                    command.Parameters.AddWithValue("@username", key);

                    string hold = null;
                    using (var myReader = await command.ExecuteReaderAsync())
                    {
                        while (await myReader.ReadAsync())
                        {
                            hold = myReader["UserId"].ToString();
                        }
                    }

                    if (hold == null)
                    {
                        try
                        {
                            // Insert a new user into the database.
                            var insertUserSql = $"INSERT INTO [{UsersTableSchema}].[{UsersTableName}] ([UserName],[RefreshToken],[UserId]) VALUES (@key,@value,'1' )";

                            using (var insertCommand = new SqlCommand(insertUserSql, connection))
                            {
                                insertCommand.Parameters.AddWithValue("@key", key);
                                insertCommand.Parameters.AddWithValue("@value", serialized);
                                await insertCommand.ExecuteNonQueryAsync();
                            }
                        }
                        catch (Exception ex)
                        {
                            throw new Exception("Error inserting new row: " + ex.Message, ex);
                        }
                    }
                    else
                    {
                        try
                        {
                            // Update an existing user.
                            var updateUserSql = $"update [{UsersTableSchema}].[{UsersTableName}] set [RefreshToken] = @value where UserName = @key";

                            using (var updateCommand = new SqlCommand(updateUserSql, connection))
                            {
                                updateCommand.Parameters.AddWithValue("@key", key);
                                updateCommand.Parameters.AddWithValue("@value", serialized);
                                await updateCommand.ExecuteNonQueryAsync();
                            }
                        }
                        catch (Exception ex)
                        {
                            throw new Exception("Error updating user: " + ex.Message, ex);
                        }
                    }
                }
            }
        }

        #endregion

        #region Methods

        /// <summary>
        ///     Initializes this instance.
        /// </summary>
        /// <returns>A Task.</returns>
        private static async Task Initialize()
        {
            using (var connection = new SqlConnection(ConnectionString))
            {
                await connection.OpenAsync();

                var existenceTestSql = string.Concat(
                    "SELECT CASE WHEN ",
                    "EXISTS(",
                    "SELECT * FROM INFORMATION_SCHEMA.TABLES ",
                    $" WHERE TABLE_SCHEMA = '{UsersTableSchema}' AND TABLE_NAME = '{UsersTableName}'",
                    ") THEN 1 ELSE 0 END");

                // Check if the table exists...
                using (var command = new SqlCommand(existenceTestSql, connection))
                {
                    var result = (int)await command.ExecuteScalarAsync() == 1;
                    if (!result)
                    {
                        // The table doesn't exist so let's create it...
                        var createTableSql = string.Concat(
                            $"CREATE TABLE [{UsersTableSchema}].[{UsersTableName}](",
                            "[UserName] [nvarchar](4000) NOT NULL, ",
                            "[RefreshToken] [nvarchar](4000) NOT NULL, ",
                            "[UserId] [nvarchar](4000) NOT NULL) ON [PRIMARY]");
                        using (var createTableCommand = new SqlCommand(createTableSql, connection))
                        {
                            await createTableCommand.ExecuteNonQueryAsync();
                        }
                    }
                }
            }
        }

        #endregion
    }
}