/// <summary>
/// Returns a single entity by a single id from table "Ts". T must be of interface type.
/// Id must be marked with [Key] attribute.
/// Created entity is tracked/intercepted for changes and used by the Update() extension.
/// </summary>
/// <typeparam name="T">Interface type to create and populate</typeparam>
/// <param name="connection">Open SqlConnection</param>
/// <param name="id">Id of the entity to get, must be marked with [Key] attribute</param>
/// <returns>Entity of T</returns>
public static T Get <T>(this IDbConnection connection, dynamic id, IDbTransaction transaction = null, int?commandTimeout = null) where T : class
{
var type = typeof(T);
string sql;
if (!GetQueries.TryGetValue(type.TypeHandle, out sql))
{
var keys = KeyPropertiesCache(type);
if (keys.Count() > 1)
{
throw new DataException("Get<T> only supports an entity with a single [Key] property");
}
if (keys.Count() == 0)
{
throw new DataException("Get<T> only supports en entity with a [Key] property");
}
var onlyKey = keys.First();
var name = GetTableName(type);
// TODO: pluralizer
// TODO: query information schema and only select fields that are both in information schema and underlying class / interface
SqlEscape escape = GetEscape(connection);
var keyName = escape == null ? onlyKey.Name : string.Format("{0}{1}{2}", escape.LeftEscape, onlyKey.Name, escape.RightEscape);
sql = "select * from " + name + " where " + keyName + " = @id";
GetQueries[type.TypeHandle] = sql;
}
var dynParms = new DynamicParameters();
dynParms.Add("@id", id);
T obj = null;
if (type.IsInterface)
{
var res = connection.Query(sql, dynParms).FirstOrDefault() as IDictionary <string, object>;
if (res == null)
{
return((T)((object)null));
}
obj = ProxyGenerator.GetInterfaceProxy <T>();
foreach (var property in TypePropertiesCache(type))
{
var val = res[property.Name];
property.SetValue(obj, val, null);
}
((IProxy)obj).IsDirty = false; //reset change tracking and return
}
else
{
obj = connection.Query <T>(sql, dynParms, transaction: transaction, commandTimeout: commandTimeout).FirstOrDefault();
}
return(obj);
}
public static int UpdatePart <T>(this IDbConnection connection, IDictionary <string, object> source, IDictionary <string, object> condition, IDbTransaction transaction = null) where T : class
{
var type = typeof(T);
SqlEscape escape = GetEscape(connection);
var cmd = GetUpdatePartQuery(type, source, condition, escape);
return(connection.Execute(cmd, source.Union(condition), transaction));
}
public static bool UpdatePart <T>(this IDbConnection connection, object entityToUpdate, IDbTransaction transaction = null) where T : class
{
var type = typeof(T);
SqlEscape escape = GetEscape(connection);
var cmd = GetUpdatePartQuery(type, entityToUpdate, escape);
return(connection.Execute(cmd, entityToUpdate, transaction) > 0);
}
public static int Insert <T>(this IDbConnection connection, IEnumerable <T> entitiesToInsert, IDbTransaction transaction = null) where T : class
{
var type = typeof(T);
SqlEscape escape = GetEscape(connection);
var cmd = InsertQueriesCache(type, escape);
return(connection.Execute(cmd, entitiesToInsert, transaction));
}
/// <summary>
/// Inserts an entity into table "Ts" and returns identity id.
/// </summary>
/// <param name="connection">Open SqlConnection</param>
/// <param name="entityToInsert">Entity to insert</param>
/// <returns>Identity of inserted entity</returns>
public static long Insert <T>(this IDbConnection connection, T entityToInsert, IDbTransaction transaction = null, int?commandTimeout = null) where T : class
{
var type = typeof(T);
ISqlAdapter adapter = GetFormatter(connection);
SqlEscape escape = GetEscape(connection);
var cmd = InsertQueriesCache(type, escape);
return(connection.ExecuteScalar <long>(string.Format("{0};{1}", cmd, adapter.GetIdentity()), entityToInsert, transaction: transaction, commandTimeout: commandTimeout));
}
public static string GetUpdatePartQuery(Type type, object entity, SqlEscape escape = null)
{
var keyProperties = KeyPropertiesCache(type);
if (!keyProperties.Any())
{
throw new ArgumentException("Entity must have at least one [Key] property");
}
var name = GetTableName(type);
var sb = new StringBuilder();
sb.AppendFormat("update {0} set ", name);
var keyNames = keyProperties.Select(p => p.Name.ToLower());
IEnumerable <string> nonIdPropNames;
if (entity is IEnumerable <KeyValuePair <string, object> > )
{
var obj = entity as IEnumerable <KeyValuePair <string, object> >;
nonIdPropNames = obj.Where(p => !keyNames.Contains(p.Key.ToLower())).Select(p => p.Key);
}
else
{
var allProperties = entity.GetType().GetProperties().Where(IsWriteable).ToArray();
nonIdPropNames = allProperties.Where(p => !keyNames.Contains(p.Name.ToLower())).Select(p => p.Name);
}
for (var i = 0; i < nonIdPropNames.Count(); i++)
{
var property = nonIdPropNames.ElementAt(i);
var keyName = escape == null ? property : string.Format("{0}{1}{2}", escape.LeftEscape, property, escape.RightEscape);
sb.AppendFormat("{0} = @{1}", keyName, property);
if (i < nonIdPropNames.Count() - 1)
{
sb.AppendFormat(", ");
}
}
sb.Append(" where ");
for (var i = 0; i < keyProperties.Count(); i++)
{
var property = keyProperties.ElementAt(i);
var keyName = escape == null ? property.Name : string.Format("{0}{1}{2}", escape.LeftEscape, property.Name, escape.RightEscape);
sb.AppendFormat("{0} = @{1}", keyName, property.Name);
if (i < keyProperties.Count() - 1)
{
sb.AppendFormat(" and ");
}
}
return(sb.ToString());
}
private static string UpdateQueriesCache(Type type, SqlEscape escape = null)
{
string sql;
if (!UpdateQueries.TryGetValue(type.TypeHandle, out sql))
{
var keyProperties = KeyPropertiesCache(type);
if (!keyProperties.Any())
{
throw new ArgumentException("Entity must have at least one [Key] property");
}
var name = GetTableName(type);
var sb = new StringBuilder();
sb.AppendFormat("update {0} set ", name);
var allProperties = TypePropertiesCache(type);
var computedProperties = ComputedPropertiesCache(type);
var nonIdProps = allProperties.Except(keyProperties.Union(computedProperties));
for (var i = 0; i < nonIdProps.Count(); i++)
{
var property = nonIdProps.ElementAt(i);
var keyName = escape == null ? property.Name : string.Format("{0}{1}{2}", escape.LeftEscape, property.Name, escape.RightEscape);
sb.AppendFormat("{0} = @{1}", keyName, property.Name);
if (i < nonIdProps.Count() - 1)
{
sb.AppendFormat(", ");
}
}
sb.Append(" where ");
for (var i = 0; i < keyProperties.Count(); i++)
{
var property = keyProperties.ElementAt(i);
var keyName = escape == null ? property.Name : string.Format("{0}{1}{2}", escape.LeftEscape, property.Name, escape.RightEscape);
sb.AppendFormat("{0} = @{1}", keyName, property.Name);
if (i < keyProperties.Count() - 1)
{
sb.AppendFormat(" and ");
}
}
sql = sb.ToString();
UpdateQueries[type.TypeHandle] = sql;
}
return(sql);
}
/// <summary>
/// Updates entity in table "Ts", checks if the entity is modified if the entity is tracked by the Get() extension.
/// </summary>
/// <typeparam name="T">Type to be updated</typeparam>
/// <param name="connection">Open SqlConnection</param>
/// <param name="entityToUpdate">Entity to be updated</param>
/// <returns>true if updated, false if not found or not modified (tracked entities)</returns>
public static bool Update <T>(this IDbConnection connection, T entityToUpdate, IDbTransaction transaction = null, int?commandTimeout = null) where T : class
{
var proxy = entityToUpdate as IProxy;
if (proxy != null)
{
if (!proxy.IsDirty)
{
return(false);
}
}
var type = typeof(T);
SqlEscape escape = GetEscape(connection);
var updated = connection.Execute(UpdateQueriesCache(type, escape), entityToUpdate, commandTimeout: commandTimeout, transaction: transaction);
return(updated > 0);
}
public static string InsertQueriesCache(Type type, SqlEscape escape = null)
{
string sql;
if (!InsertQueries.TryGetValue(type.TypeHandle, out sql))
{
var name = GetTableName(type);
var sbColumnList = new StringBuilder(null);
var allProperties = TypePropertiesCache(type);
var keyProperties = KeyPropertiesCache(type);
var computedProperties = ComputedPropertiesCache(type);
var allPropertiesExceptKeyAndComputed = allProperties.Except(keyProperties.Union(computedProperties));
for (var i = 0; i < allPropertiesExceptKeyAndComputed.Count(); i++)
{
var property = allPropertiesExceptKeyAndComputed.ElementAt(i);
var keyName = escape == null ? property.Name : string.Format("{0}{1}{2}", escape.LeftEscape, property.Name, escape.RightEscape);
sbColumnList.AppendFormat("{0}", keyName);
if (i < allPropertiesExceptKeyAndComputed.Count() - 1)
{
sbColumnList.Append(", ");
}
}
var sbParameterList = new StringBuilder(null);
for (var i = 0; i < allPropertiesExceptKeyAndComputed.Count(); i++)
{
var property = allPropertiesExceptKeyAndComputed.ElementAt(i);
sbParameterList.AppendFormat("@{0}", property.Name);
if (i < allPropertiesExceptKeyAndComputed.Count() - 1)
{
sbParameterList.Append(", ");
}
}
sql = String.Format("insert into {0} ({1}) values ({2})", name, sbColumnList.ToString(), sbParameterList.ToString());
InsertQueries[type.TypeHandle] = sql;
}
return(sql);
}
/// <summary>
/// Delete entity in table "Ts".
/// </summary>
/// <typeparam name="T">Type of entity</typeparam>
/// <param name="connection">Open SqlConnection</param>
/// <param name="entityToDelete">Entity to delete</param>
/// <returns>true if deleted, false if not found</returns>
public static bool Delete <T>(this IDbConnection connection, T entityToDelete, IDbTransaction transaction = null, int?commandTimeout = null) where T : class
{
if (entityToDelete == null)
{
throw new ArgumentException("Cannot Delete null Object", "entityToDelete");
}
var type = typeof(T);
var keyProperties = KeyPropertiesCache(type);
if (keyProperties.Count() == 0)
{
throw new ArgumentException("Entity must have at least one [Key] property");
}
var name = GetTableName(type);
var sb = new StringBuilder();
sb.AppendFormat("delete from {0} where ", name);
SqlEscape escape = GetEscape(connection);
for (var i = 0; i < keyProperties.Count(); i++)
{
var property = keyProperties.ElementAt(i);
var keyName = escape == null ? property.Name : string.Format("{0}{1}{2}", escape.LeftEscape, property.Name, escape.RightEscape);
sb.AppendFormat("{0} = @{1}", keyName, property.Name);
if (i < keyProperties.Count() - 1)
{
sb.AppendFormat(" and ");
}
}
var deleted = connection.Execute(sb.ToString(), entityToDelete, transaction: transaction, commandTimeout: commandTimeout);
return(deleted > 0);
}
public static string GetUpdatePartQuery(Type type, IDictionary <string, object> source, IDictionary <string, object> condition, SqlEscape escape = null)
{
var name = GetTableName(type);
var sb = new StringBuilder();
sb.AppendFormat("update {0} set ", name);
var keyNames = condition.Select(p => p.Key).ToList();
IEnumerable <string> nonIdPropNames = source.Select(p => p.Key);
for (var i = 0; i < nonIdPropNames.Count(); i++)
{
var property = nonIdPropNames.ElementAt(i);
var keyName = escape == null ? property : string.Format("{0}{1}{2}", escape.LeftEscape, property, escape.RightEscape);
sb.AppendFormat("{0} = @{1}", keyName, property);
if (i < nonIdPropNames.Count() - 1)
{
sb.AppendFormat(", ");
}
}
sb.Append(" where ");
for (var i = 0; i < keyNames.Count(); i++)
{
var property = keyNames.ElementAt(i);
var keyName = escape == null ? property : string.Format("{0}{1}{2}", escape.LeftEscape, property, escape.RightEscape);
if (source.ContainsKey(property))
{
string rename = property + DateTime.Now.Ticks;
condition.Add(rename, condition[property]);
condition.Remove(property);
property = rename;
}
sb.AppendFormat("{0} = @{1}", keyName, property);
if (i < keyNames.Count() - 1)
{
sb.AppendFormat(" and ");
}
}
return(sb.ToString());
}
