public IDataReader ReadData <T>(params Expression <Func <T, object> >[] fields) where T : class, new()
{
string field = string.Join(",", SchemaCache.GetColumns(fields).Select(t => $"{t.Name}"));
string sql = $"SELECT {field} FROM {typeof(T).GetTableName()};";
return(db.ReadData(CommandType.Text, sql));
}
public bool InsertIdentity <T>(T entity) where T : class, new()
{
ColumnProperty identity = SchemaCache.GetColumns <T>().FirstOrDefault(t => t.Identity);
if (!identity)
{
throw new InvalidOperationException();
}
IEnumerable <ColumnProperty> fields = SchemaCache.GetColumns <T>().Where(t => !t.Identity);
string sql = $"INSERT INTO {typeof(T).GetTableName()} ({ string.Join(",", fields.Select(t => $"{t.Name}")) }) VALUES({ string.Join(",", fields.Select(t => $"@{t.Name}")) });SELECT LAST_INSERT_ID();";
DynamicParameters parameters = new DynamicParameters();
foreach (ColumnProperty field in fields)
{
parameters.Add($"@{field.Name}", field.Property.GetValue(entity).GetSafeValue(field.Property.PropertyType));
}
object value = this.db.ExecuteScalar(CommandType.Text, sql, parameters);
if (value == null || value == DBNull.Value)
{
return(false);
}
identity.Property.SetValue(entity, Convert.ChangeType(value, identity.Property.PropertyType));
return(true);
}
public IDataReader ReadData <T>(Expression <Func <T, bool> > condition, params Expression <Func <T, object> >[] fields) where T : class, new()
{
string field = string.Join(",", SchemaCache.GetColumns(fields).Select(t => $"{t.Name}"));
using (IExpressionCondition expression = db.GetExpressionCondition(condition))
{
string conditionSql = expression.ToCondition(out DynamicParameters parameters);
string sql = $"SELECT {field} FROM {typeof(T).GetTableName()} {conditionSql};";
return(db.ReadData(CommandType.Text, sql, parameters));
}
}
/// <summary>
/// 获取DataSet
/// </summary>
public DataSet GetDataSet <T>(Expression <Func <T, bool> > condition, params Expression <Func <T, object> >[] fields) where T : class, new()
{
string field = string.Join(",", SchemaCache.GetColumns(fields).Select(t => $"[{t.Name}]"));
using (ExpressionCondition expression = db.GetExpressionCondition(condition))
{
string conditionSql = expression.ToCondition(out DynamicParameters parameters);
string sql = $"SELECT {field} FROM [{typeof(T).GetTableName()}] {conditionSql}";
return(db.GetDataSet(CommandType.Text, sql, parameters.ToDbParameter()));
}
}
public bool Insert <T>(T entity) where T : class, new()
{
IEnumerable <ColumnProperty> fields = SchemaCache.GetColumns <T>().Where(t => !t.Identity);
string sql = $"INSERT INTO {typeof(T).GetTableName()} ({ string.Join(",", fields.Select(t => $"{t.Name}")) }) VALUES({ string.Join(",", fields.Select(t => $"@{t.Name}")) });";
DynamicParameters parameters = new DynamicParameters();
foreach (ColumnProperty field in fields)
{
parameters.Add($"@{field.Name}", field.Property.GetValue(entity).GetSafeValue(field.Property.PropertyType));
}
return(this.db.ExecuteNonQuery(CommandType.Text, sql, parameters) == 1);
}
public bool Delete <T>(T entity) where T : class, new()
{
DynamicParameters parameters = new DynamicParameters();
Stack <string> where = new Stack <string>();
foreach (ColumnProperty column in SchemaCache.GetColumns <T>().Where(t => t.IsKey))
{
where.Push($"[{column.Name}]=@{column.Name}");
parameters.Add(column.Name, column.Property.GetValue(entity));
}
string sql = $"DELETE FROM {typeof(T).GetTableName()} WHERE {string.Join(" AND ", where)};";
return(db.ExecuteNonQuery(CommandType.Text, sql, parameters) > 0);
}
/// <summary>
/// 获取一行值
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="obj"></param>
/// <param name="precate"></param>
/// <returns></returns>
public SQLResult Info <T>(T obj, params Expression <Func <T, object> >[] precate) where T : class, new()
{
Dictionary <ColumnProperty, object> condition = obj.GetCondition(precate);
string tableName = obj.GetTableName();
IEnumerable <ColumnProperty> fields = SchemaCache.GetColumns <T>();
string sql = $"SELECT TOP 1 { string.Join(",", fields.Select(t => string.Format("[{0}]", t.Name))) } FROM [{tableName}] WHERE { string.Join(" AND ", condition.Select(t => $"[{t.Key.Name}] = @{t.Key.Property.Name}")) }";
DbParameter[] parameters = condition.Select(t => new SqlParameter($"@{t.Key.Property.Name}", t.Value)).ToArray();
return(new SQLResult()
{
CommandText = sql,
Prameters = parameters
});
}
public bool Exists <T>(T entity) where T : class, new()
{
IEnumerable <ColumnProperty> fields = SchemaCache.GetColumns <T>(t => t.IsKey);
if (!fields.Any())
{
throw new Exception($"{ typeof(T).GetTableName() } No primary key");
}
string sql = $"SELECT 0 WHERE EXISTS(SELECT 0 FROM {typeof(T).GetTableName()} WHERE { string.Join(" AND ", fields.Select(t => $"{t.Name}=@{t.Name}")) });";
DynamicParameters parameters = new DynamicParameters();
foreach (ColumnProperty column in fields)
{
parameters.Add($"@{column.Name}", column.Property.GetValue(entity));
}
return(db.ExecuteScalar(CommandType.Text, sql, parameters) != null);
}
public static object GetReaderData(this IDataReader reader, object source)
{
//映射数据库中的字段到实体属性
IEnumerable <ColumnProperty> propertys = SchemaCache.GetColumns(source.GetType());
foreach (ColumnProperty property in propertys)
{
//对实体属性进行设值
object value = reader[property.Name];
if (value == null)
{
continue;
}
property.Property.SetValue(source, value);
}
return(source);
}
