/// <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()); }