/// <summary> /// 分页-同PagedEntity<T2> GetPagedEntity /// 多出一个功能:为某一列做统计信息,统计信息需为能够被double覆盖的类型 /// </summary> /// <param name="statisticalFiled">统计字段</param> /// <returns></returns> public virtual PagedEntityWithStatistical <T2> GetPagedEntityWithStatistical <T2>(int pageIndex, int pageSize, string orderBy, ICollection <Tuple <string, string, object> > parameters, string statisticalFiled) { var baseSelectSql = GetBaseSelectSqlForNoStatus(typeof(T2)); var tableName = SqlMapperExtensions.GetTableName(typeof(T2)); var allProperties = SqlMapperExtensions.TypePropertiesCache(typeof(T2)); var computedProperties = SqlMapperExtensions.ComputedPropertiesCache(typeof(T2)); var allPropertiesExceptComputed = allProperties.Except(computedProperties); bool status = false; var hasStatus = allPropertiesExceptComputed.FirstOrDefault(o => o.Name == "Status"); if (hasStatus != null) { status = !SqlMapperExtensions.ExcludeStatus(hasStatus); } var countsql = string.Format("select count(1) from {0} where 1 = 1", tableName); var statisticalSql = string.Format("select sum({0}) from {1} where 1 = 1", statisticalFiled, tableName); var sql = new StringBuilder(); var dynamicParameters = new DynamicParameters(); if (parameters != null) { for (var i = 0; i < parameters.Count; i++) { var parameter = parameters.ElementAt(i); var parameterName = string.Format("{0}{1}", parameter.Item1, i); sql.AppendFormat(" and {0} {1} @{2}", parameter.Item1, parameter.Item2, parameterName); dynamicParameters.Add(parameterName, parameter.Item3); } } if (status) { sql.Append(" and Status = 1"); } var entities = new PagedEntityWithStatistical <T2>(); entities.Items = new List <T2>(); OpenSlaveConnection(conn => { entities.TotalItems = conn.Query <int>(string.Format("{0}{1}", countsql, sql.ToString()), dynamicParameters).FirstOrDefault(); if (entities.TotalItems == 0) { return; } entities.TotalStatistical = conn.Query <double>(string.Format("{0}{1}", statisticalSql, sql.ToString()), dynamicParameters).FirstOrDefault(); sql.AppendFormat(" order by {0} limit @pageIndex,@pageSize", orderBy); dynamicParameters.Add("pageIndex", pageIndex * pageSize); dynamicParameters.Add("pageSize", pageSize); entities.Items = conn.Query <T2>(string.Format("{0}{1}", baseSelectSql, sql.ToString()), dynamicParameters).ToList(); }); return(entities); }
protected void Insert(T entity) { var tableName = SqlMapperExtensions.GetTableName(Type); var allProperties = SqlMapperExtensions.TypePropertiesCache(Type); var keyProperties = SqlMapperExtensions.KeyPropertiesCache(Type); var computedProperties = SqlMapperExtensions.ComputedPropertiesCache(Type); var allPropertiesExceptKeyAndComputed = allProperties.Except(keyProperties.Union(computedProperties)).ToList(); if (keyProperties.Count != 1) { throw new DataException("Get<T> only supports an entity with a single [Key] property"); } var idProperty = keyProperties.First(); var sbColumnList = new StringBuilder(null); for (var i = 0; i < allPropertiesExceptKeyAndComputed.Count(); i++) { var property = allPropertiesExceptKeyAndComputed.ElementAt(i); sbColumnList.AppendFormat("`{0}`", property.Name); 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(", "); } } //string sql = String.Format("insert into {0} ({1},Status,LastChanged) values ({2},1,ADDTIME(now(),'00:00:10'))", tableName, sbColumnList.ToString(), sbParameterList.ToString()); string sql = String.Format("insert into {0} ({1}) values ({2});select LAST_INSERT_ID()", tableName, sbColumnList.ToString(), sbParameterList.ToString()); OpenConnection(conn => { var inserted = conn.Query <int>(sql, entity).FirstOrDefault(); if (idProperty.PropertyType.Name == "Int16") //for short id/key types issue #196 { idProperty.SetValue(entity, (Int16)inserted, null); } else { idProperty.SetValue(entity, inserted, null); } }); }
public bool Update(T entity) { var keyProperties = SqlMapperExtensions.KeyPropertiesCache(Type); if (keyProperties.Count == 0) { throw new ArgumentException("Entity must have at least one [Key] property"); } var name = SqlMapperExtensions.GetTableName(Type); var sb = new StringBuilder(); sb.AppendFormat("update {0} set ", name); var allProperties = SqlMapperExtensions.TypePropertiesCache(Type); var computedProperties = SqlMapperExtensions.ComputedPropertiesCache(Type); var nonIdProps = allProperties.Except(keyProperties.Union(computedProperties)).ToList(); for (var i = 0; i < nonIdProps.Count(); i++) { var property = nonIdProps.ElementAt(i); sb.AppendFormat("`{0}` = @{1}", property.Name, property.Name); if (i < nonIdProps.Count() - 1) { sb.AppendFormat(", "); } } //sb.Append(",LastChanged=ADDTIME(now(),'00:00:10')"); sb.Append(" where "); for (var i = 0; i < keyProperties.Count; i++) { var property = keyProperties.ElementAt(i); sb.AppendFormat("`{0}` = @{1}", property.Name, property.Name); if (i < keyProperties.Count() - 1) { sb.AppendFormat(" and "); } } var updated = 0; OpenConnection(conn => { updated = conn.Execute(sb.ToString(), entity); }); return(updated > 0); }
protected static string GetBaseSelectSqlForNoStatus(Type type) { string baseSelectSql; if (!SqlMapperExtensions.SelectQueries.TryGetValue(type.TypeHandle, out baseSelectSql)) { var tableName = SqlMapperExtensions.GetTableName(type); var allProperties = SqlMapperExtensions.TypePropertiesCache(type); var computedProperties = SqlMapperExtensions.ComputedPropertiesCache(type); var allPropertiesExceptComputed = allProperties.Except(computedProperties); baseSelectSql = string.Format("select {0} from {1} where {2} = 1", string.Join(",", allPropertiesExceptComputed.Select(o => string.Format("`{0}`", o.Name))), tableName, "1"); SqlMapperExtensions.SelectQueries[type.TypeHandle] = baseSelectSql; } return(baseSelectSql); }
public virtual PagedEntity <T2> GetPagedEntityByDefine <T2>(int start, int pageSize, string orderBy, ICollection <Tuple <string, string, object> > parameters) { var baseSelectSql = GetBaseSelectSql(typeof(T2)); var tableName = SqlMapperExtensions.GetTableName(typeof(T2)); var allProperties = SqlMapperExtensions.TypePropertiesCache(typeof(T2)); var computedProperties = SqlMapperExtensions.ComputedPropertiesCache(typeof(T2)); var allPropertiesExceptComputed = allProperties.Except(computedProperties); bool status = false; var hasStatus = allPropertiesExceptComputed.FirstOrDefault(o => o.Name == "Status"); if (hasStatus != null) { status = !SqlMapperExtensions.ExcludeStatus(hasStatus); } var countsql = $"select count(1) from {tableName} where {(status ? "Status" : "1")} = 1"; var sql = new StringBuilder(); var dynamicParameters = new DynamicParameters(); if (parameters != null) { for (var i = 0; i < parameters.Count; i++) { var parameter = parameters.ElementAt(i); var parameterName = $"{parameter.Item1}{i}"; sql.AppendFormat(" and {0} {1} @{2}", parameter.Item1, parameter.Item2, parameterName); dynamicParameters.Add(parameterName, parameter.Item3); } } var entities = new PagedEntity <T2>(); entities.Items = new List <T2>(); OpenSlaveConnection(conn => { entities.TotalItems = conn.Query <int>($"{countsql}{sql.ToString()}", dynamicParameters).FirstOrDefault(); if (entities.TotalItems == 0) { return; } sql.AppendFormat(" order by {0} limit @pageIndex,@pageSize", orderBy); dynamicParameters.Add("pageIndex", start); dynamicParameters.Add("pageSize", pageSize); entities.Items = conn.Query <T2>($"{baseSelectSql}{sql.ToString()}", dynamicParameters).ToList(); }); return(entities); }
public int DeleteAnyStatus(ICollection <Tuple <string, string, object> > parameters, bool isDelete) { var tableName = SqlMapperExtensions.GetTableName(Type); var allProperties = SqlMapperExtensions.TypePropertiesCache(Type); var computedProperties = SqlMapperExtensions.ComputedPropertiesCache(Type); var allPropertiesExceptComputed = allProperties.Except(computedProperties); var hasStatus = allPropertiesExceptComputed.FirstOrDefault(o => o.Name == "Status"); var sql = new StringBuilder(); if (!isDelete && hasStatus != null) { sql.AppendFormat("update {0} set Status=-1 where 1=1 ", tableName); //sql.AppendFormat("update {0} set LastChanged =ADDTIME(now(),'00:00:10'),Status=-1 where Status=1 ", tableName); } else { sql.AppendFormat("delete from {0} where 1=1 ", tableName); } var dynamicParameters = new DynamicParameters(); if (parameters != null) { for (var i = 0; i < parameters.Count; i++) { var parameter = parameters.ElementAt(i); var parameterName = string.Format("{0}{1}", parameter.Item1, i); sql.AppendFormat(" and {0} {1} @{2}", parameter.Item1, parameter.Item2, parameterName); dynamicParameters.Add(parameterName, parameter.Item3); } } var deleted = 0; OpenConnection(conn => { deleted = conn.Execute(sql.ToString(), dynamicParameters); }); return(deleted); }
protected static string GetBaseSelectSql(Type type) { string baseSelectSql; if (!SqlMapperExtensions.SelectQueries.TryGetValue(type.TypeHandle, out baseSelectSql)) { var tableName = SqlMapperExtensions.GetTableName(type); var allProperties = SqlMapperExtensions.TypePropertiesCache(type); var computedProperties = SqlMapperExtensions.ComputedPropertiesCache(type); var allPropertiesExceptComputed = allProperties.Except(computedProperties); var status = false; var hasStatus = allPropertiesExceptComputed.FirstOrDefault(o => o.Name == "Status"); if (hasStatus != null) { status = !SqlMapperExtensions.ExcludeStatus(hasStatus); } baseSelectSql = string.Format("select {0} from {1} where {2} = 1", string.Join(",", allPropertiesExceptComputed.Select(o => string.Format("`{0}`", o.Name))), tableName, status ? "Status" : "1"); SqlMapperExtensions.SelectQueries[type.TypeHandle] = baseSelectSql; } return(baseSelectSql); }