public virtual async Task <IEnumerable <T> > SelectAsync <T>(MapperDb db, string sql, params object[] args) where T : new()
{
using (var command = db.GetCommand(sql, args))
{
return(await SelectAsync <T>(db, command).ConfigureAwait(false));
}
}
public virtual async Task NonQueryAsync(MapperDb db, string sql, params object[] args)
{
using (var command = db.GetCommand(sql, args))
{
await this.NonQueryAsync(db, command).ConfigureAwait(false);
}
}
public virtual async Task <IEnumerable <T> > SelectAsync <T>(MapperDb db, Sql sql) where T : new()
{
using (var command = db.GetCommand(sql))
{
return(await SelectAsync <T>(db, command).ConfigureAwait(false));
}
}
public virtual async Task NonQueryAsync(MapperDb db, Sql sql)
{
using (var command = db.GetCommand(sql))
{
await this.NonQueryAsync(db, command).ConfigureAwait(false);
}
}
public virtual async Task <T> ScalarAsync <T>(MapperDb db, string sql, params object[] args)
{
using (var command = db.GetCommand(sql, args))
{
return(await ScalarAsync <T>(db, command).ConfigureAwait(false));
}
}
public static async Task <Contact> SearchDuplicateContactAsync(MapperDb db, int userId, string name, IEnumerable <string> emails, IEnumerable <string> phones)
{
if (string.IsNullOrWhiteSpace(name))
{
return(new Contact());
}
var sql = new Sql("SELECT * FROM addressbook.contacts");
sql.Append("WHERE deleted = @0", false);
sql.Append("AND (is_private = @0 OR created_by = @1)", false, userId);
sql.Append("AND (lower(formatted_name) LIKE @0", name.ToSqlLikeExpression().ToLower());
foreach (string email in emails)
{
sql.Append("OR lower(email_addresses) LIKE @0", email.ToSqlLikeExpression().ToLower());
}
foreach (string phone in phones)
{
sql.Append("OR telephones LIKE @0", phone.ToSqlLikeExpression().ToLower());
sql.Append("OR fax_numbers LIKE @0", phone.ToSqlLikeExpression().ToLower());
sql.Append("OR mobile_numbers LIKE @0", phone.ToSqlLikeExpression().ToLower());
}
sql.Append(")");
sql.Limit(db.DatabaseType, 1, 0, "contact_id");
var awaiter = await db.SelectAsync <Contact>(sql).ConfigureAwait(false);
return(awaiter.FirstOrDefault());
}
public static DbCommand GetCommand(this MapperDb db, Sql sql)
{
var command = db.DbFactory.CreateCommand();
if (command == null)
{
throw new MapperException("Could not create database command.");
}
command.CommandText = sql.GetQuery();
var parameters = sql.GetParameterValues();
//Console.WriteLine(sql.GetQuery());
//Console.WriteLine(string.Join(",", sql.GetParameterValues()));
foreach (int arg in sql.GetParameters())
{
var parameter = db.DbFactory.CreateParameter();
if (parameter == null)
{
throw new MapperException("Could not create database command.");
}
parameter.ParameterName = "@" + arg;
parameter.Value = parameters[arg] ?? DBNull.Value;
command.Parameters.Add(parameter);
}
return(command);
}
public virtual async Task <T> ScalarAsync <T>(MapperDb db, Sql sql)
{
using (var command = db.GetCommand(sql))
{
return(await ScalarAsync <T>(db, command).ConfigureAwait(false));
}
}
public virtual async Task DeleteAsync(MapperDb db, object primaryKeyValue, string tableName, string primaryKeyName)
{
var sql = this.GetSql(tableName, primaryKeyName, primaryKeyValue);
//Console.WriteLine(sql.GetQuery());
//Console.WriteLine(string.Join(",", sql.GetParameterValues()));
await db.NonQueryAsync(sql.GetCommand(db)).ConfigureAwait(false);
}
public virtual async Task DeleteAsync <T>(MapperDb db, T poco, object primaryKeyValue)
{
string tableName = PocoHelper.GetTableName(poco);
var primaryKey = PocoHelper.GetPrimaryKey(poco);
string primaryKeyName = primaryKey.ColumnName;
await this.DeleteAsync(db, primaryKeyValue, tableName, primaryKeyName).ConfigureAwait(false);
}
public virtual async Task UpdateAsync <T>(MapperDb db, T poco, object primaryKeyValue, string tableName, string primaryKeyName, bool isIdentity = true)
{
var sql = this.GetSql(poco, tableName, primaryKeyName, primaryKeyValue, isIdentity);
//Console.WriteLine(sql.GetQuery());
//Console.WriteLine(string.Join(",", sql.GetParameterValues()));
await db.NonQueryAsync(sql.GetCommand(db)).ConfigureAwait(false);
}
public virtual async Task UpdateAsync <T>(MapperDb db, T poco, object primaryKeyValue)
{
string tableName = PocoHelper.GetTableName(poco);
var primaryKey = PocoHelper.GetPrimaryKey(poco);
string primaryKeyName = primaryKey.ColumnName;
bool isIdentity = primaryKey.IsIdentity;
await this.UpdateAsync(db, poco, primaryKeyValue, tableName, primaryKeyName, isIdentity).ConfigureAwait(false);
}
public virtual async Task <object> InsertAsync <T>(MapperDb db, T poco)
{
string tableName = PocoHelper.GetTableName(poco);
var primaryKey = PocoHelper.GetPrimaryKey(poco);
string primaryKeyName = primaryKey.ColumnName;
bool autoincrement = primaryKey.AutoIncrement;
return(await db.InsertAsync(tableName, primaryKeyName, autoincrement, poco).ConfigureAwait(false));
}
public virtual async Task <IEnumerable <T> > SelectAsync <T>(MapperDb db, DbCommand command) where T : new()
{
var result = ResultsetCache.Get(db, command);
if (result != null)
{
return(result.ToObject <T>());
}
var connection = db.GetConnection();
if (connection == null)
{
throw new MapperException("Could not create database connection.");
}
await db.OpenSharedConnectionAsync().ConfigureAwait(false);
command.Connection = connection;
command.Transaction = db.GetTransaction();
using (var reader = await command.ExecuteReaderAsync().ConfigureAwait(false))
{
if (!reader.HasRows)
{
return(new List <T>());
}
var mapped = new Collection <ICollection <KeyValuePair <string, object> > >();
var properties = Enumerable.Range(0, reader.FieldCount).Select(reader.GetName).ToList();
while (await reader.ReadAsync().ConfigureAwait(false))
{
var instance = new Collection <KeyValuePair <string, object> >();
foreach (string property in properties)
{
var value = reader[property];
if (value == DBNull.Value)
{
value = null;
}
instance.Add(new KeyValuePair <string, object>(property.ToPascalCase(), value));
}
mapped.Add(instance);
}
ResultsetCache.Set(db, command, mapped);
return(mapped.ToObject <T>());
}
}
private static string GetCacheKey(MapperDb db, DbCommand command)
{
var key = db.ConnectionString;
key += "." + command.CommandText + ".";
var dbParameters = (from DbParameter parameter in command.Parameters select parameter.Value.ToString()).ToList();
key += "." + string.Join(",", dbParameters);
return(key);
}
public static Collection <ICollection <KeyValuePair <string, object> > > Get(MapperDb db, DbCommand command)
{
if (!db.CacheResults)
{
return(null);
}
string key = GetCacheKey(db, command);
var item = MapperCache.GetCache <Collection <ICollection <KeyValuePair <string, object> > > >(key);
return(item);
}
public virtual async Task <object> InsertAsync <T>(MapperDb db, string tableName, string primaryKeyName, bool autoIncrement, T poco)
{
var sql = this.GetSql(tableName, primaryKeyName, autoIncrement, poco);
var connection = db.GetConnection();
if (connection == null)
{
throw new MapperException("Could not create database connection.");
}
return(await db.ScalarAsync <object>(sql.GetCommand(db)).ConfigureAwait(false));
}
public static void Set(MapperDb db, DbCommand command, Collection <ICollection <KeyValuePair <string, object> > > item)
{
if (!db.CacheResults)
{
return;
}
string key = GetCacheKey(db, command);
var cacheDuration = db.CacheMilliseconds;
var expiresOn = DateTimeOffset.UtcNow.AddMilliseconds(cacheDuration);
MapperCache.AddToCache(key, item, expiresOn);
}
public static async Task <Contact> SearchDuplicateContactAsync(MapperDb db, int userId, string formattedName)
{
var sql = new Sql("SELECT * FROM addressbook.contacts");
sql.Append("WHERE deleted = @0", false);
sql.Append("AND (is_private = @0 OR created_by = @1)", false, userId);
sql.Append("AND LOWER(formatted_name)=LOWER(@0)", formattedName);
sql.Limit(db.DatabaseType, 1, 0, "contact_id");
var awaiter = await db.SelectAsync <Contact>(sql).ConfigureAwait(false);
return(awaiter.FirstOrDefault());
}
public virtual async Task NonQueryAsync(MapperDb db, DbCommand command)
{
var connection = db.GetConnection();
if (connection == null)
{
throw new MapperException("Could not create database connection.");
}
await db.OpenSharedConnectionAsync().ConfigureAwait(false);
command.Connection = connection;
command.Transaction = db.GetTransaction();
await command.ExecuteNonQueryAsync().ConfigureAwait(false);
}
public virtual async Task <T> ScalarAsync <T>(MapperDb db, DbCommand command)
{
var connection = db.GetConnection();
if (connection == null)
{
throw new MapperException("Could not create database connection.");
}
await db.OpenSharedConnectionAsync().ConfigureAwait(false);
command.Connection = connection;
command.Transaction = db.GetTransaction();
var value = await command.ExecuteScalarAsync().ConfigureAwait(false);
return(value.To <T>());
}
private static NonQueryOperation GetOperation(MapperDb db)
{
NonQueryOperation operation;
switch (db.DatabaseType)
{
case DatabaseType.MySql:
operation = new MySqlNonQuery();
break;
case DatabaseType.SqlServer:
operation = new SqlServerNonQuery();
break;
default:
operation = new PostgreSQLNonQuery();
break;
}
return(operation);
}
private static DeleteOperation GetOperation(MapperDb db)
{
DeleteOperation operation;
switch (db.DatabaseType)
{
case DatabaseType.SqlServer:
operation = new SqlServerDelete();
break;
case DatabaseType.MySql:
operation = new MySqlDelete();
break;
default:
operation = new PostgreSQLDelete();
break;
}
return(operation);
}
private static InsertOperation GetOperation(MapperDb db)
{
InsertOperation operation;
switch (db.DatabaseType)
{
case DatabaseType.SqlServer:
operation = new SqlServerInsert();
break;
case DatabaseType.MySql:
operation = new MySqlInsert();
break;
default:
operation = new PostgreSQLInsert();
break;
}
return(operation);
}
public static DbCommand GetCommand(this MapperDb db, string sql, params object[] args)
{
var command = db.DbFactory.CreateCommand();
if (command == null)
{
throw new MapperException("Could not create database command.");
}
if (command == null)
{
throw new MapperException("Could not create database command.");
}
command.CommandTimeout = db.CommandTimeout;
command.CommandText = sql;
int index = 0;
foreach (var arg in args)
{
var parameter = db.DbFactory.CreateParameter();
if (parameter == null)
{
throw new MapperException("Could not create database command.");
}
parameter.ParameterName = "@" + index;
parameter.Value = arg ?? DBNull.Value;
command.Parameters.Add(parameter);
index++;
}
return(command);
}
public DatabaseFactory(MapperDb db)
{
this.Db = db;
}
public static async Task DeleteAsync(this MapperDb db, object primaryKeyValue, string tableName, string primaryKeyName)
{
var operation = GetOperation(db);
await operation.DeleteAsync(db, primaryKeyValue, tableName, primaryKeyName).ConfigureAwait(false);
}
public static async Task <object> InsertAsync(this MapperDb db, string tableName, string primaryKeyName, object poco)
{
var operation = GetOperation(db);
return(await operation.InsertAsync(db, tableName, primaryKeyName, true, poco).ConfigureAwait(false));
}
public static async Task <object> InsertAsync <T>(this MapperDb db, string tableName, string primaryKeyName, bool autoIncrement, T poco)
{
var operation = GetOperation(db);
return(await operation.InsertAsync(db, tableName, primaryKeyName, autoIncrement, poco).ConfigureAwait(false));
}
public static async Task <object> InsertAsync <T>(this MapperDb db, T poco)
{
var operation = GetOperation(db);
return(await operation.InsertAsync(db, poco).ConfigureAwait(false));
}
