public void NullSettingThrowsArgumentNullException()
{
var connector = new DbConnector(_settings);
var exception = Throws <ArgumentNullException>(() => connector.CreateDbConnection(typeof(CollectionPocoA)));
Equal($"Value cannot be null.\r\nParameter name: No connection string settings could be found for '{typeof(CollectionPocoA).FullName}'. Please check configuration.", exception.Message);
}
public void InvalidProviderNameThrowsArgumentException()
{
var connector = new DbConnector(_settings);
var exception = Throws <ArgumentException>(() => connector.CreateDbConnection(typeof(CreateDbConnection)));
Equal("Unable to find the requested .Net Framework Data Provider. It may not be installed.", exception.Message);
}
/// <summary>
/// 指定されたユーザーに対してアクセストークンを新規に発行します。
/// </summary>
/// <param name="userId">ユーザーID</param>
/// <returns>生成されたトークン</returns>
public async Task <AccessToken> CreateAsync(int userId)
{
using (var transaction = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
{
//--- レコード追加
using (var connection = DbConnector.CreateDbConnection())
{
connection.Open();
var now = DateTime.Now;
var token = new T_ACCESS_TOKEN
{
VALUE = Guid.NewGuid().ToString(),
USR_ID = userId,
CREATION_TIME = now,
EXPIRATION_TIME = now + AuthSettings.AccessTokenValidDuration,
};
var result = await connection.InsertAsync(token).ConfigureAwait(false);
if (result == 1)
{
transaction.Complete();
return(token.ToApiModel());
}
return(null);
}
}
}
public void NonExistentDatabaseThrowsException()
{
var type = typeof(Query);
var connector = new DbConnector(_settings);
var connection = connector.CreateDbConnection(type);
var exception = Throws <SqlException>(() => connection.Open());
}
public void MissingFallbackConnectionStringThrowsArgumentNullException()
{
var connector = new DbConnector(_settings);
var exception = Throws <ArgumentNullException>(() => connector.CreateDbConnection(typeof(DbCommander)));
Equal("Value cannot be null.\r\nParameter name: No connection string settings could be found for 'Drapper.DbCommander'. Please check configuration.", exception.Message);
}
public void NullTypePassedThrowsArgumentNullException()
{
Type type = null;
var connector = new DbConnector(_settings);
var exception = Throws <ArgumentNullException>(() => connector.CreateDbConnection(type));
Equal("Value cannot be null.\r\nParameter name: The 'type' variable passed to CreateDbConnection was null.", exception.Message);
}
public void ValidProviderNameAndConnectionStringReturnsConnection()
{
var connector = new DbConnector(_settings);
var connection = connector.CreateDbConnection(typeof(Query));
NotNull(connection);
Equal(ConnectionState.Closed, connection.State);
}
public void FallsbackToRootNamespaceConnection()
{
var type = typeof(FallbackConnectionType);
var connector = new DbConnector(_settings);
var connection = connector.CreateDbConnection(type);
NotNull(connection);
Equal(ConnectionState.Closed, connection.State);
}
public void ConnectionFromCommandSettings()
{
var setting = _settings.Namespaces.Single(
x => x.Namespace == "Drapper.Tests.Relative.Path.Tests").Types.Single(
x => x.Name == "Drapper.Tests.Relative.Path.Tests.TypeH").Commands.Single(
x => x.Key == "Valid");
var connector = new DbConnector(_settings);
var connection = connector.CreateDbConnection(typeof(TypeH), setting.Value);
NotNull(connection);
}
public void ConnectionFromCommandSettingNullProviderThrowsArgumentNullException()
{
var setting = _settings.Namespaces.Single(
x => x.Namespace == "Drapper.Tests.Relative.Path.Tests").Types.Single(
x => x.Name == "Drapper.Tests.Relative.Path.Tests.TypeH").Commands.Single(
x => x.Key == "WithoutProvider");
var connector = new DbConnector(_settings);
var exception = Throws <ArgumentNullException>(() => connector.CreateDbConnection(null, setting.Value));
Equal("Value cannot be null.\r\nParameter name: The 'type' variable passed to CreateDbConnection was null.", exception.Message);
}
/// <summary>
/// 有効期限が切れたトークンをすべて削除します。
/// </summary>
/// <returns>削除した件数</returns>
public Task <int> DeleteExpiredAsync()
{
var builder = new StringBuilder();
builder.AppendLine(PrimitiveSql.CreateDeleteAllSql <T_REQUEST_TOKEN>());
builder.Append("where EXPIRATION_TIME < :now");
using (var connection = DbConnector.CreateDbConnection())
{
connection.Open();
return(connection.ExecuteAsync(builder.ToString(), new { now = DateTime.Now }));
}
}
/// <summary>
/// 指定されたアクセストークンを削除します。
/// </summary>
/// <param name="token">アクセストークン</param>
/// <returns>削除に成功した場合true</returns>
public async Task <bool> DeleteAsync(string token)
{
var sql = PrimitiveSql.CreateDeleteSql <T_ACCESS_TOKEN>();
using (var connection = DbConnector.CreateDbConnection())
{
connection.Open();
var result = await connection.ExecuteAsync(sql, new { VALUE = token }).ConfigureAwait(false);
return(result == 1);
}
}
/// <summary>
/// 指定されたユーザーIDに対して発行されたトークンをすべて削除します。
/// </summary>
/// <param name="userId">ユーザーID</param>
/// <returns>削除した件数</returns>
public Task <int> DeleteAsync(int userId)
{
var builder = new StringBuilder();
builder.AppendLine(PrimitiveSql.CreateDeleteAllSql <T_ACCESS_TOKEN>());
builder.Append("where USR_ID = :userId");
using (var connection = DbConnector.CreateDbConnection())
{
connection.Open();
return(connection.ExecuteAsync(builder.ToString(), new { userId = userId }));
}
}
/// <summary>
/// 全レコードを非同期的に取得します。
/// </summary>
/// <returns>全レコード</returns>
public Task <IEnumerable <Employee> > GetAllAsync()
{
using (var connection = DbConnector.CreateDbConnection())
{
connection.Open();
return(connection.QueryAsync <Employee>
(
this.CreateQueryAllSql(),
new { ACTIVE_FLG = 1 }
));
}
}
public void ConnectionFromCommandSettingNullConnectionStringThrowsArgumentNullException()
{
var setting = _settings.Namespaces.Single(
x => x.Namespace == "Drapper.Tests.Relative.Path.Tests").Types.Single(
x => x.Name == "Drapper.Tests.Relative.Path.Tests.TypeH").Commands.Single(
x => x.Key == "WithoutConnectionString");
var connector = new DbConnector(_settings);
var exception = Throws <ArgumentNullException>(() => connector.CreateDbConnection(typeof(TypeH), null));
Equal($"Value cannot be null.\r\nParameter name: The command setting passed was null. Check configuration for type '{typeof(TypeH).FullName}'.", exception.Message);
}
public void ConnectionFromCommandSettingWithoutConnectionStringThrowsArgumentNullException()
{
var setting = _settings.Namespaces.Single(
x => x.Namespace == "Drapper.Tests.Relative.Path.Tests").Types.Single(
x => x.Name == "Drapper.Tests.Relative.Path.Tests.TypeH").Commands.Single(
x => x.Key == "WithoutConnectionString");
var connector = new DbConnector(_settings);
var exception = Throws <ArgumentNullException>(() => connector.CreateDbConnection(typeof(TypeH), setting.Value));
Equal("Value cannot be null.\r\nParameter name: The connectionString from the command setting cannot be null.", exception.Message);
}
public static async Task <IEnumerable <Product> > GetAll(int id)
{
using (var connection = DbConnector.CreateDbConnection())
{
var sql = @"
select * from dbo.product
where ID = :ID
;";
var param = id == 0 ? null : new { ID = id };
var result = await connection.QueryAsync <Product>(sql, param).ConfigureAwait(false);
return(result);
}
}
/// <summary>
/// 指定されたトークンを削除します。
/// </summary>
/// <param name="token">トークン</param>
/// <returns>削除に成功したかどうか</returns>
public async Task <bool> DeleteAsync(string token)
{
var parameter = new T_REQUEST_TOKEN {
VALUE = token
};
using (var connection = DbConnector.CreateDbConnection())
{
connection.Open();
var result = await connection.DeleteAsync(parameter);
return(result == 1);
}
}
/// <summary>
/// 認証処理を行います。
/// </summary>
/// <param name="info">ユーザー認証情報</param>
/// <returns>認証されたかどうか</returns>
public async Task <bool> AuthenticateAsync(UserAuthInfo info)
{
using (var connection = DbConnector.CreateDbConnection())
{
connection.Open();
var sql = PrimitiveSql.CreateSelectSql <M_USER>();
var parameter = new { ID = info.Id };
var users = await connection.QueryAsync <M_USER>(sql, parameter).ConfigureAwait(false);
var user = users.FirstOrDefault();
return(user != null &&
Convert.ToBoolean(user.ACTIVE_FLG) &&
user.PASSWORD == info.Password);
}
}
/// <summary>
/// 指定された主キーに一致するレコードを非同期的に取得します。
/// </summary>
/// <param name="id">社員ID</param>
/// <returns>レコード</returns>
public async Task <Employee> GetAsync(int id)
{
using (var connection = DbConnector.CreateDbConnection())
{
connection.Open();
var records = await connection.QueryAsync <Employee>
(
string.Format("{0}{1}and U.ID = :ID", this.CreateQueryAllSql(), Environment.NewLine),
new { ID = id, ACTIVE_FLG = 1 }
)
.ConfigureAwait(false);
return(records.SingleOrDefault());
}
}
/// <summary>
/// 新規にトークンを発行します。
/// </summary>
/// <returns>生成されたリクエストトークン</returns>
public async Task <RequestToken> CreateAsync()
{
using (var connection = DbConnector.CreateDbConnection())
{
connection.Open();
var now = DateTime.Now;
var token = new T_REQUEST_TOKEN
{
VALUE = Guid.NewGuid().ToString(),
CREATION_TIME = now,
EXPIRATION_TIME = now + AuthSettings.RequestTokenValidDuration,
};
var result = await connection.InsertAsync(token).ConfigureAwait(false);
return(result == 1 ? token.ToApiModel() : null);
}
}
public void InvalidConnectionStringThrowsArgumentException()
{
var connector = new DbConnector(_settings);
IDbConnection connection = null;
try
{
var type = typeof(CollectionPocoB);
connection = connector.CreateDbConnection(type);
}
catch (Exception ex)
{
IsType <ArgumentException>(ex);
// check that it's still null.
Null(connection);
Equal("Format of the initialization string does not conform to specification starting at index 0.", ex.Message);
}
}
/// <summary>
/// 指定されたトークンが有効かどうかを確認します。
/// </summary>
/// <param name="token">トークン</param>
/// <returns>有効かどうか</returns>
public async Task <bool> IsValidAsync(string token)
{
var sql = PrimitiveSql.CreateSelectSql <T_REQUEST_TOKEN>();
var parameter = new T_REQUEST_TOKEN {
VALUE = token
};
using (var connection = DbConnector.CreateDbConnection())
{
connection.Open();
var record = (await connection.QueryAsync <T_REQUEST_TOKEN>(sql, parameter)
.ConfigureAwait(false))
.FirstOrDefault();
return(record == null
? false
: DateTime.Now <= record.EXPIRATION_TIME);
}
}
/// <summary>
/// 指定されたレコードを保存しつつ、削除を許可します。
/// </summary>
/// <param name="userId">ユーザーID</param>
/// <param name="action">削除処理</param>
/// <returns>処理に成功したかどうか</returns>
public static async Task <bool> SafeDelete(int userId, Func <IDbConnection, Task <IEnumerable <object> > > deleteAction)
{
using (var transaction = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
{
using (var connection = DbConnector.CreateDbConnection())
{
connection.Open();
//--- 実処理の呼び出し
var targets = await deleteAction(connection).ConfigureAwait(false);
if (targets == null || !targets.Any())
{
return(false);
}
//--- ログ記録
var id = await connection.GenerateAutoIdAsync <T_DELETED_RECORD>().ConfigureAwait(false);
var json = JsonConvert.SerializeObject(targets);
var record = new T_DELETED_RECORD
{
ID = id,
DELETE_TIME = DateTime.Now,
USR_ID = userId,
DATA = json,
};
var result = await connection.InsertAsync(record).ConfigureAwait(false);
if (result == 1)
{
transaction.Complete();
return(true);
}
return(false);
}
}
}
/// <summary>
/// 指定されたトークンを利用しているユーザーのIDを取得します。
/// </summary>
/// <param name="token">トークン</param>
/// <returns>ユーザーID</returns>
public async Task <int?> GetUserIdAsync(string token)
{
var sql = PrimitiveSql.CreateSelectSql <T_ACCESS_TOKEN>();
var parameter = new T_ACCESS_TOKEN {
VALUE = token
};
using (var connection = DbConnector.CreateDbConnection())
{
connection.Open();
var record = (await connection.QueryAsync <T_ACCESS_TOKEN>(sql, parameter)
.ConfigureAwait(false))
.FirstOrDefault();
if (record == null)
{
return(null);
}
if (record.EXPIRATION_TIME < DateTime.Now)
{
return(null);
}
return(record.USR_ID);
}
}
