/// <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 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 #5
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 #6
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 #7
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 #8
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 #10
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 #11
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 #12
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 #13
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());
        }