Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Google API Authentication Data Store for .NET 4.6+ and SQL Server using ADO.NET
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
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment