/// <summary>
        /// 生成insert语句
        /// </summary>
        public static string GenerateInsertql <T>() where T : BaseEntity
        {
            TableMapper   tableMapper = MappingCachePool.GetOrAdd <T>();
            StringBuilder sb          = new StringBuilder();

            sb.Append($"INSERT INTO {tableMapper.TableName} ");
            sb.Append("(");
            StringBuilder cols    = new StringBuilder();
            var           columns = tableMapper.Columns.Where(p => p.Value.IsAuto == false).Select(p => p.Value).ToList();

            foreach (var item in columns)
            {
                cols.Append($"{item.ColumnName},");
            }
            sb.Append(cols.ToString().Trim(','));
            sb.Append(") ");
            sb.Append("VALUES (");
            cols = new StringBuilder();
            foreach (var item in columns)
            {
                cols.Append($"@{item.PropertyName},");
            }
            sb.Append(cols.ToString().Trim(','));
            sb.Append(");");
            return(sb.ToString());
        }
Exemple #2
0
        /// <summary>
        /// 通过主键值查询单个实体
        /// </summary>
        public static async Task <T> SingleByIdAsync <T>(this DbConnection connection, object idValue) where T : BaseEntity
        {
            if (idValue == null || string.IsNullOrWhiteSpace(idValue.ToString()))
            {
                throw new ArgumentNullException(nameof(idValue));
            }
            TableMapper  mapper = MappingCachePool.GetOrAdd <T>();
            ColumnMapper column = mapper.KeyColumn;

            if (column == null)
            {
                throw new Exception($"The {mapper.TypeName} class does not define a primary key.");
            }
            else
            {
                string sql     = $"select * from {mapper.TableName} where {column.ColumnName} = @p;";
                object dbValue = idValue;
                if (column.ValueConvert != null)
                {
                    dbValue = column.ValueConvert.Write(idValue);
                }
                List <T> list = await connection.SelectAsync <T>(sql, new { p = dbValue });

                if (list.Count == 0)
                {
                    return(null);
                }
                else
                {
                    return(list[0]);
                }
            }
        }
Exemple #3
0
 public static TableMapper GetOrAdd <T>() where T : BaseEntity
 {
     return(Cache.GetOrAdd(typeof(T), t =>
     {
         TableMapper mapper = ReflectionHelper.ToMapper <T>();
         return mapper;
     }));
 }
Exemple #4
0
        public static string CreateTableSQL <T>() where T : BaseEntity
        {
            TableMapper   mapper = MappingCachePool.GetOrAdd <T>();
            StringBuilder sb     = new StringBuilder();

            sb.AppendLine($"CREATE TABLE {mapper.TableName}(");
            sb.AppendLine(CreateColumns <T>());
            sb.AppendLine(");");
            return(sb.ToString());
        }
        public static string GenerateDeleteSql <T>() where T : BaseEntity
        {
            TableMapper  tableMapper = MappingCachePool.GetOrAdd <T>();
            ColumnMapper keyColumn   = tableMapper.KeyColumn;

            if (keyColumn == null)
            {
                throw new Exception($"{tableMapper.TypeName} class does not define a primary key");
            }
            return($"delete from {tableMapper.TableName} where {keyColumn.ColumnName} = @{keyColumn.PropertyName};");
        }
Exemple #6
0
        public static string CreateIndexSQL <T>() where T : BaseEntity
        {
            TableMapper   tableMapper = MappingCachePool.GetOrAdd <T>();
            StringBuilder sb          = new StringBuilder();
            var           columns     = tableMapper.Columns.Where(p => p.Value.IsIndex == true).ToList();

            foreach (var item in columns)
            {
                var column = item.Value;
                sb.AppendLine($"CREATE INDEX idx_{tableMapper.TableName}_{column.ColumnName} on {tableMapper.TableName}({column.ColumnName});");
            }
            return(sb.ToString());
        }
Exemple #7
0
        /// <summary>
        /// 使用实体类为命令追加参数
        /// </summary>
        public static void AppendEntityParameters <T>(this DbCommand command, T obj) where T : BaseEntity
        {
            if (command == null)
            {
                throw new ArgumentNullException(nameof(command));
            }
            if (obj == null)
            {
                throw new ArgumentNullException(nameof(obj));
            }
            TableMapper tableMapper = MappingCachePool.GetOrAdd <T>();
            var         columns     = tableMapper.Columns;

            foreach (var item in columns)
            {
                DbParameter parameter = command.CreateParameter();
                parameter.ParameterName = "@" + item.Value.PropertyName;
                object value = item.Value.GetPropertyValue(obj);
                if (value == null)
                {
                    parameter.Value = DBNull.Value;
                }
                else
                {
                    BaseValueConvert valueConvert = item.Value.ValueConvert as BaseValueConvert;
                    if (valueConvert != null)
                    {
                        parameter.Value = valueConvert.Write(value);
                    }
                    else
                    {
                        if (item.Value.IsEnum)
                        {
                            parameter.Value = Convert.ToInt16(value);
                        }
                        else if (item.Value.IsGuidString)
                        {
                            parameter.Value = Guid.Parse(value.ToString());
                        }
                        else
                        {
                            parameter.Value = value;
                        }
                    }
                }
                command.Parameters.Add(parameter);
            }
        }
Exemple #8
0
        /// <summary>
        /// 删除表中所有数据
        /// </summary>
        public static async Task <int> DeleteAllAsync <T>(this DbConnection connection, DbTransaction transaction = null) where T : BaseEntity
        {
            if (connection == null)
            {
                throw new ArgumentNullException(nameof(connection));
            }
            TableMapper tableMapper = MappingCachePool.GetOrAdd <T>();

            using (DbCommand command = connection.CreateCommand())
            {
                command.Transaction = transaction;
                command.CommandText = $"DELETE FROM {tableMapper.TableName};";
                command.Parameters.Clear();
                return(await command.ExecuteNonQueryAsync());
            }
        }
Exemple #9
0
        /// <summary>
        /// 获取实体表内的记录总数
        /// </summary>
        public static async Task <int> CountAsync <T>(this DbConnection connection) where T : BaseEntity
        {
            if (connection == null)
            {
                throw new ArgumentNullException(nameof(connection));
            }
            TableMapper tableMapper = MappingCachePool.GetOrAdd <T>();

            using (DbCommand command = connection.CreateCommand())
            {
                string sql = $"select count(*) from {tableMapper.TableName};";
                command.CommandText = sql;
                object result = await command.ExecuteScalarAsync();

                return(Convert.ToInt32(result));
            }
        }
        /// <summary>
        /// 将DbDataReader转换为实体
        /// </summary>
        public static T ToEntity <T>(this DbDataReader reader) where T : BaseEntity
        {
            TableMapper mapper = MappingCachePool.GetOrAdd <T>();
            T           obj    = Activator.CreateInstance <T>();

            foreach (var item in mapper.Columns)
            {
                ColumnMapper column   = item.Value;
                PropertyInfo property = column.PropertyInfo;
                object       value    = reader[column.ColumnName];
                if (value == DBNull.Value)
                {
                    property.SetValue(obj, null);
                }
                else
                {
                    if (column.ValueConvert != null)
                    {
                        property.SetValue(obj, column.ValueConvert.Read(value));
                    }
                    else
                    {
                        if (column.IsGuidString)
                        {
                            Guid guid = (Guid)value;
                            property.SetValue(obj, guid.ToString());
                        }
                        else if (column.IsEnum)
                        {
                            property.SetValue(obj, Enum.ToObject(property.PropertyType, Convert.ToInt32(value)), null);
                        }
                        else
                        {
                            property.SetValue(obj, value);
                        }
                    }
                }
            }
            return(obj);
        }
Exemple #11
0
        private static string CreateColumns <T>() where T : BaseEntity
        {
            StringBuilder sb          = new StringBuilder();
            TableMapper   tableMapper = MappingCachePool.GetOrAdd <T>();

            // 生成主键
            if (tableMapper.KeyColumn != null)
            {
                sb.AppendLine($"{tableMapper.KeyColumn.ColumnName} {GetDbType(tableMapper.KeyColumn)} PRIMARY KEY,");
            }
            // 生成其他列
            var columns = tableMapper.Columns.Where(p => p.Value.IsPrimarykey == false).ToList();

            foreach (var item in columns)
            {
                var column = item.Value;
                sb.AppendLine(CreateColumn(column));
            }
            string sql = sb.ToString().Trim().Trim(',');

            return(sql);
        }
Exemple #12
0
        /// <summary>
        /// 获取表中的所有记录
        /// </summary>
        public static async Task <List <T> > GetAllAsync <T>(this DbConnection connection) where T : BaseEntity
        {
            if (connection == null)
            {
                throw new ArgumentNullException(nameof(connection));
            }
            List <T>    objList = new List <T>();
            TableMapper mapper  = MappingCachePool.GetOrAdd <T>();

            using (DbCommand command = connection.CreateCommand())
            {
                command.CommandText = $"select * from {mapper.TableName};";
                using (DbDataReader reader = await command.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        T obj = reader.ToEntity <T>();
                        objList.Add(obj);
                    }
                }
            }
            return(objList);
        }
Exemple #13
0
        /// <summary>
        /// 通过ID值删除一个对象
        /// </summary>
        public static async Task <int> DeleteByIdAsync <T>(this DbConnection connection, object idValue, DbTransaction transaction = null) where T : BaseEntity
        {
            if (idValue == null || string.IsNullOrWhiteSpace(idValue.ToString()))
            {
                throw new ArgumentNullException(nameof(idValue));
            }
            TableMapper  tableMapper = MappingCachePool.GetOrAdd <T>();
            ColumnMapper keyColumn   = tableMapper.KeyColumn;

            if (tableMapper.KeyColumn == null)
            {
                throw new Exception(tableMapper.TypeName + " does not define a primary key");
            }
            object dbValue = idValue;

            if (keyColumn.ValueConvert != null)
            {
                dbValue = keyColumn.ValueConvert.Write(idValue);
            }
            string sql = $"delete from {tableMapper.TableName} where {keyColumn.ColumnName} = @p;";

            return(await ExecuteNonQueryAsync(connection, sql, new { p = dbValue }, transaction));
        }
Exemple #14
0
        /// <summary>
        /// 从数据库中删除一个对象
        /// </summary>
        public static async Task <int> DeleteAsync <T>(this DbConnection connection, T obj, DbTransaction transaction = null) where T : BaseEntity
        {
            if (connection == null)
            {
                throw new ArgumentNullException(nameof(connection));
            }
            if (obj == null)
            {
                throw new ArgumentNullException(nameof(obj));
            }
            TableMapper tableMapper = MappingCachePool.GetOrAdd <T>();

            using (DbCommand command = connection.CreateCommand())
            {
                if (transaction != null)
                {
                    command.Transaction = transaction;
                }
                command.CommandText = CodeSegmentHelper.GenerateDeleteSql <T>();
                command.Parameters.Clear();
                command.AppendEntityParameters <T>(obj);
                return(await command.ExecuteNonQueryAsync());
            }
        }
        public static string GenerateUpdateSql <T>() where T : BaseEntity
        {
            TableMapper  tableMapper = MappingCachePool.GetOrAdd <T>();
            ColumnMapper keyColumn   = tableMapper.KeyColumn;

            if (keyColumn == null)
            {
                throw new Exception($"{tableMapper.TypeName} class does not define a primary key");
            }
            StringBuilder sb = new StringBuilder();

            sb.Append($"UPDATE {tableMapper.TableName} SET ");
            var           columns = tableMapper.Columns.Where(p => p.Value.IsPrimarykey == false).ToList();
            StringBuilder cols    = new StringBuilder();

            foreach (var item in columns)
            {
                cols.Append($"{item.Value.ColumnName}=@{item.Value.PropertyName},");
            }
            sb.Append(cols.ToString().Trim(','));
            sb.Append(" WHERE ");
            sb.Append($"{keyColumn.ColumnName} = @{keyColumn.PropertyName};");
            return(sb.ToString());
        }
Exemple #16
0
        public static TableMapper ToMapper <T>() where T : BaseEntity
        {
            Type           type           = typeof(T);
            TableAttribute tableAttribute = type.GetCustomAttribute <TableAttribute>();
            string         tableName;

            if (tableAttribute != null && !string.IsNullOrWhiteSpace(tableAttribute.Name))
            {
                tableName = tableAttribute.Name.Trim();
            }
            else
            {
                tableName = GetName(type.Name);
            }
            TableMapper tableMapper = new TableMapper()
            {
                TableName = tableName, Type = type, TypeName = type.Name
            };
            List <PropertyInfo> pList = type.GetProperties(BindingFlags.Public | BindingFlags.Instance).ToList();

            foreach (PropertyInfo property in pList)
            {
                // 检查 IgnoreAttribute
                IgnoreAttribute ignore = property.GetCustomAttribute <IgnoreAttribute>();
                if (ignore != null)
                {
                    continue;
                }
                // 检查 ColumnAttribute
                bool         isKey;
                bool         isAuto = false;
                KeyAttribute key    = property.GetCustomAttribute <KeyAttribute>();
                if (key != null)
                {
                    isKey  = true;
                    isAuto = key.IsAuto;
                }
                else if (string.Equals(property.Name, "id", StringComparison.OrdinalIgnoreCase))
                {
                    // 当属性名为ID,但又未指定Key特性时,按以下规则判断,如果是整数,则为自动增量,否则不是自动增量。
                    string propertyTypeName = property.PropertyType.FullName;
                    if (propertyTypeName == "System.Int32" || propertyTypeName == "System.UInt32" || propertyTypeName == "System.Int64" || propertyTypeName == "System.UInt64")
                    {
                        isAuto = true;
                    }
                    else
                    {
                        isAuto = false;
                    }
                    isKey = true;
                }
                else
                {
                    isKey  = false;
                    isAuto = false;
                }
                ColumnAttribute  columnAttribute = property.GetCustomAttribute <ColumnAttribute>();
                IndexAttribute   indexAttribute  = property.GetCustomAttribute <IndexAttribute>();
                string           columnName      = "";
                BaseValueConvert valueConvert    = null;
                if (columnAttribute != null)
                {
                    // 获取列名
                    if (!string.IsNullOrWhiteSpace(columnAttribute.Name))
                    {
                        columnName = columnAttribute.Name.Trim();
                    }
                    else
                    {
                        columnName = GetName(property.Name);
                    }
                    // 获取 ValueConvert
                    if (columnAttribute.Convert != null)
                    {
                        Type convertType = columnAttribute.Convert;
                        if (convertType.IsAbstract)
                        {
                            throw new Exception($"{property.Name} value convert cannot be an abstract class.");
                        }
                        if (!convertType.IsSubclassOf(typeof(BaseValueConvert)))
                        {
                            string name = typeof(BaseValueConvert).Name;
                            throw new Exception($"{property.Name} value convert must inherit {name}.");
                        }
                        valueConvert = Activator.CreateInstance(convertType) as BaseValueConvert;
                    }
                }
                else
                {
                    columnName = GetName(property.Name);
                }
                // 创建映射实例
                ColumnMapper columnMapper = new ColumnMapper()
                {
                    ColumnName     = columnName,
                    IsPrimarykey   = isKey,
                    PropertyInfo   = property,
                    PropertyName   = property.Name,
                    ValueConvert   = valueConvert,
                    IsAuto         = isAuto,
                    IsEnum         = property.PropertyType.IsEnum,
                    IsGuidString   = property.PropertyType == typeof(string) && columnName.ToLower().Contains("id"),
                    IsCanNull      = IsNullable(property.PropertyType),
                    IsIndex        = indexAttribute != null,
                    DbType         = (columnAttribute != null && !string.IsNullOrWhiteSpace(columnAttribute.DbType)) ? columnAttribute.DbType : "",
                    DbDefaultValue = (columnAttribute != null && !string.IsNullOrWhiteSpace(columnAttribute.DbDefaultValue)) ? columnAttribute.DbDefaultValue : ""
                };
                tableMapper.Add(columnMapper);
            }
            return(tableMapper);
        }