/// <summary>
/// 执行sql语句返回DataReader
/// </summary>
/// <param name="connection"></param>
/// <param name="sql">sql语句</param>
/// <returns></returns>
public static DataReader ExecuteReader(this DataConnection connection, string sql)
{
return(new CommandInfo(connection, sql).ExecuteReader());
}
static DataParameter[] GetDataParameters(DataConnection dataConnection, object parameters)
{
if (parameters == null)
{
return(null);
}
if (parameters is DataParameter[])
{
return((DataParameter[])parameters);
}
if (parameters is DataParameter)
{
return new[] { (DataParameter)parameters }
}
;
if (parameters is IEnumerable <DataParameter> )
{
return(((IEnumerable <DataParameter>)parameters).ToArray());
}
Func <object, DataParameter[]> func;
var type = parameters.GetType();
var key = new ParamKey(type, dataConnection.ID);
if (!_parameterReaders.TryGetValue(key, out func))
{
var td = dataConnection.MappingSchema.GetEntityDescriptor(type);
var p = Expression.Parameter(typeof(object), "p");
var obj = Expression.Parameter(parameters.GetType(), "obj");
var expr = Expression.Lambda <Func <object, DataParameter[]> >(
Expression.Block(
new[] { obj },
new Expression[]
{
Expression.Assign(obj, Expression.Convert(p, type)),
Expression.NewArrayInit(
typeof(DataParameter),
td.Columns.Select(m =>
{
if (m.MemberType == typeof(DataParameter))
{
var pobj = Expression.Parameter(typeof(DataParameter));
return(Expression.Block(
new[] { pobj },
new Expression[]
{
Expression.Assign(pobj, Expression.PropertyOrField(obj, m.MemberName)),
Expression.MemberInit(
Expression.New(typeof(DataParameter)),
Expression.Bind(
_dataParameterName,
Expression.Coalesce(
Expression.MakeMemberAccess(pobj, _dataParameterName),
Expression.Constant(m.ColumnName))),
Expression.Bind(
_dataParameterDataType,
Expression.MakeMemberAccess(pobj, _dataParameterDataType)),
Expression.Bind(
_dataParameterValue,
Expression.Convert(
Expression.MakeMemberAccess(pobj, _dataParameterValue),
typeof(object))))
}));
}
var memberType = m.MemberType.ToNullableUnderlying();
var valueGetter = Expression.PropertyOrField(obj, m.MemberName) as Expression;
var mapper = dataConnection.MappingSchema.GetConvertExpression(memberType, typeof(DataParameter), createDefault: false);
if (mapper != null)
{
return(Expression.Call(
MemberHelper.MethodOf(() => PrepareDataParameter(null, null)),
mapper.GetBody(valueGetter),
Expression.Constant(m.ColumnName)));
}
if (memberType.IsEnumEx())
{
var mapType = ConvertBuilder.GetDefaultMappingFromEnumType(dataConnection.MappingSchema, memberType);
var convExpr = dataConnection.MappingSchema.GetConvertExpression(m.MemberType, mapType);
memberType = mapType;
valueGetter = convExpr.GetBody(valueGetter);
}
return((Expression)Expression.MemberInit(
Expression.New(typeof(DataParameter)),
Expression.Bind(
_dataParameterName,
Expression.Constant(m.ColumnName)),
Expression.Bind(
_dataParameterDataType,
Expression.Constant(dataConnection.MappingSchema.GetDataType(memberType).DataType)),
Expression.Bind(
_dataParameterValue,
Expression.Convert(valueGetter, typeof(object)))));
}))
}
),
p);
_parameterReaders[key] = func = expr.Compile();
}
return(func(parameters));
}
/// <summary>
/// 带有参数的sql语句查询 返回DataTable
/// </summary>
/// <param name="connection"></param>
/// <param name="sql">sql语句</param>
/// <param name="parameters">参数集合 Array</param>
/// <returns>返回一个DataTable</returns>
public static DataTable QueryTable(this DataConnection connection, string sql, params DataParameter[] parameters)
{
return(new CommandInfo(connection, sql, parameters).QueryTable());
}
public CommandInfo(DataConnection dataConnection, string commandText)
{
DataConnection = dataConnection;
CommandText = commandText;
}
public CommandInfo(DataConnection dataConnection, string commandText, DataParameter parameter)
{
DataConnection = dataConnection;
CommandText = commandText;
Parameters = new[] { parameter };
}
/// <summary>
/// sqlserver下可以使用
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="dataConnection"></param>
/// <param name="predicate"></param>
/// <param name="source"></param>
/// <param name="tableName"></param>
/// <param name="databaseName"></param>
/// <param name="schemaName"></param>
/// <returns></returns>
public static int Merge <T>(this DataConnection dataConnection, Expression <Func <T, bool> > predicate, IEnumerable <T> source,
string tableName = null, string databaseName = null, string schemaName = null)
where T : class
{
return(dataConnection.DataProvider.Merge(dataConnection, predicate, true, source, tableName, databaseName, schemaName));
}
/// <summary>
/// 根据sql语句查询 返回结果集
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connection"></param>
/// <param name="sql">sql语句</param>
/// <returns>返回IEnumerable</returns>
public static IEnumerable <T> Query <T>(this DataConnection connection, string sql)
{
return(new CommandInfo(connection, sql).Query <T>());
}
/// <summary>
/// 执行SQL对象返回单个数据 例如查询select count(*)等返回单个结果是类型的查询
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connection"></param>
/// <param name="sql">SQL对象</param>
/// <returns></returns>
public static T Execute <T>(this DataConnection connection, SQL sql)
{
return(new CommandInfo(connection, sql.ToString(), sql.Parameters.ToArray()).Execute <T>());
}
/// <summary>
/// 查询分库分表的总数 要求sql是返回数字类型
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connection"></param>
/// <param name="sql">SQL对象</param>
/// <returns></returns>
public static long ExcuteSharding(this DataConnection connection, SQL sql)
{
return(new CommandInfo(connection, sql.ToString(), sql.Parameters.ToArray()).ExcuteSharding());
}
/// <summary>
/// 异步 执行SQL对象返回DataTable
/// </summary>
/// <param name="connection"></param>
/// <param name="sql">SQL对象</param>
/// <returns>Task<DataTable></returns>
public static async Task <DataTable> QueryTableAsync(this DataConnection connection, SQL sql)
{
return(await new CommandInfo(connection, sql.ToString(), sql.Parameters.ToArray()).QueryTableAsync());
}
/// <summary>
/// 执行带有模板的SQL对象返回结果集
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connection"></param>
/// <param name="template">模板 可以是一个匿名对象或者是一个class</param>
/// <param name="sql">SQL对象</param>
/// <returns>IEnumerable</returns>
public static IEnumerable <T> Query <T>(this DataConnection connection, T template, SQL sql)
{
return(new CommandInfo(connection, sql.ToString(), sql.Parameters.ToArray()).Query(template));
}
/// <summary>
/// 执行SQL对象返回DataTable
/// </summary>
/// <param name="connection"></param>
/// <param name="sql">SQL对象</param>
/// <returns>DataTable</returns>
public static DataTable QueryTable(this DataConnection connection, SQL sql)
{
return(new CommandInfo(connection, sql.ToString(), sql.Parameters.ToArray()).QueryTable());
}
/// <summary>
/// 带有模板的执行sql语句返回结果集
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connection"></param>
/// <param name="template">模板 可以是一个匿名对象 或者一个 class</param>
/// <param name="sql">sql语句</param>
/// <param name="parameters">匿名对象</param>
/// <returns>IEnumerable</returns>
public static IEnumerable <T> Query <T>(this DataConnection connection, T template, string sql, object parameters)
{
return(new CommandInfo(connection, sql, parameters).Query(template));
}
/// <summary>
/// 带有参数的sql语句查询 异步 返回DataTable
/// </summary>
/// <param name="connection"></param>
/// <param name="sql">sql语句</param>
/// <param name="parameters">参数集合 Array</param>
/// <returns>Task<DataTable></returns>
public static async Task <DataTable> QueryTableAsync(this DataConnection connection, string sql, params DataParameter[] parameters)
{
return(await new CommandInfo(connection, sql, parameters).QueryTableAsync());
}
/// <summary>
/// 带匿名参数执行sql语句返回DataReader
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connection"></param>
/// <param name="sql">sql语句</param>
/// <param name="parameters">匿名对象</param>
/// <returns>DataReader</returns>
public static DataReader ExecuteReader(this DataConnection connection, string sql, object parameters)
{
return(new CommandInfo(connection, sql, parameters).ExecuteReader());
}
public static long ExcuteSharding(this DataConnection connection, string sql)
{
return(new CommandInfo(connection, sql).ExcuteSharding());
}
/// <summary>
/// 带有匿名参数的执行sql返回数据使用自定义序列化Func
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connection"></param>
/// <param name="objectReader">自定义序列化Func</param>
/// <param name="sql">sql语句</param>
/// <param name="parameters">参数集合 Array</param>
/// <returns></returns>
public static IEnumerable <T> Query <T>(this DataConnection connection, Func <IDataReader, T> objectReader, string sql, object parameters)
{
return(new CommandInfo(connection, sql, parameters).Query(objectReader));
}
public static long ExcuteSharding(this DataConnection connection, string sql, object parameters)
{
return(new CommandInfo(connection, sql, parameters).ExcuteSharding());
}
/// <summary>
/// sqlserver下可以使用
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="dataConnection"></param>
/// <param name="source"></param>
/// <param name="tableName"></param>
/// <param name="databaseName"></param>
/// <param name="schemaName"></param>
/// <returns></returns>
public static int Merge <T>(this DataConnection dataConnection, IEnumerable <T> source,
string tableName = null, string databaseName = null, string schemaName = null)
where T : class
{
return(dataConnection.DataProvider.Merge(dataConnection, null, false, source, tableName, databaseName, schemaName));
}
/// <summary>
/// 执行sql语句返回单个结果
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connection"></param>
/// <param name="sql">sql语句</param>
/// <returns></returns>
public static T Execute <T>(this DataConnection connection, string sql)
{
return(new CommandInfo(connection, sql).Execute <T>());
}
/// <summary>
/// 带有参数的sql语句查询 返回结果集
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connection"></param>
/// <param name="sql">sql语句</param>
/// <param name="parameters">参数集合 Array</param>
/// <returns></returns>
public static IEnumerable <T> Query <T>(this DataConnection connection, string sql, params DataParameter[] parameters)
{
return(new CommandInfo(connection, sql, parameters).Query <T>());
}
public static object ExecuteScalar(this DataConnection connection, string sql, params DataParameter[] parameters)
{
return(new CommandInfo(connection, sql, parameters).ExecuteScalar());
}
public CommandInfo(DataConnection dataConnection, string commandText, params DataParameter[] parameters)
{
DataConnection = dataConnection;
CommandText = commandText;
Parameters = parameters;
}
public static int ExecuteNonQuery(this DataConnection connection, string sql, params DataParameter[] parameters)
{
return(new CommandInfo(connection, sql, parameters).ExecuteNonQuery());
}
public CommandInfo(DataConnection dataConnection, string commandText, object parameters)
{
DataConnection = dataConnection;
CommandText = commandText;
Parameters = GetDataParameters(dataConnection, parameters);
}
/// <summary>
/// 带一个参数执行sql语句返回单个结果
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connection"></param>
/// <param name="sql">sql语句</param>
/// <param name="parameter">单个参数</param>
/// <returns></returns>
public static T Execute <T>(this DataConnection connection, string sql, DataParameter parameter)
{
return(new CommandInfo(connection, sql, parameter).Execute <T>());
}
static Func <IDataReader, T> CreateObjectReader <T>(
DataConnection dataConnection,
IDataReader dataReader,
Func <Type, int, Expression, Expression> getMemberExpression)
{
var parameter = Expression.Parameter(typeof(IDataReader));
var dataReaderExpr = Expression.Convert(parameter, dataReader.GetType());
Expression expr;
if (dataConnection.MappingSchema.IsScalarType(typeof(T)))
{
expr = getMemberExpression(typeof(T), 0, dataReaderExpr);
}
else
{
var td = dataConnection.MappingSchema.GetEntityDescriptor(typeof(T));
var names = new List <string>(dataReader.FieldCount);
for (var i = 0; i < dataReader.FieldCount; i++)
{
names.Add(dataReader.GetName(i));
}
expr = null;
var ctors = typeof(T).GetConstructors().Select(c => new { c, ps = c.GetParameters() }).ToList();
if (ctors.Count > 0 && ctors.All(c => c.ps.Length > 0))
{
var q =
from c in ctors
let count = c.ps.Count(p => names.Contains(p.Name))
orderby count descending
select c;
var ctor = q.FirstOrDefault();
if (ctor != null)
{
expr = Expression.New(
ctor.c,
ctor.ps.Select(p => names.Contains(p.Name) ?
getMemberExpression(p.ParameterType, names.IndexOf(p.Name), dataReaderExpr) :
Expression.Constant(dataConnection.MappingSchema.GetDefaultValue(p.ParameterType), p.ParameterType)));
}
}
if (expr == null)
{
var members =
(
from n in names.Select((name, idx) => new { name, idx })
let member = td.Columns.FirstOrDefault(m =>
string.Compare(m.ColumnName, n.name, dataConnection.MappingSchema.ColumnComparisonOption) == 0)
where member != null
select new
{
Member = member,
Expr = getMemberExpression(member.MemberType, n.idx, dataReaderExpr),
}
).ToList();
expr = Expression.MemberInit(
Expression.New(typeof(T)),
members.Select(m => Expression.Bind(m.Member.MemberInfo, m.Expr)));
}
}
if (expr.GetCount(e => e == dataReaderExpr) > 1)
{
var dataReaderVar = Expression.Variable(dataReaderExpr.Type, "dr");
var assignment = Expression.Assign(dataReaderVar, dataReaderExpr);
expr = expr.Transform(e => e == dataReaderExpr ? dataReaderVar : e);
expr = Expression.Block(new[] { dataReaderVar }, new[] { assignment, expr });
}
var lex = Expression.Lambda <Func <IDataReader, T> >(expr, parameter);
return(lex.Compile());
}
/// <summary>
/// 根据sql语句查询 返回DataTable
/// </summary>
/// <param name="connection"></param>
/// <param name="sql">sql语句</param>
/// <returns>返回一个DataTable</returns>
public static DataTable QueryTable(this DataConnection connection, string sql)
{
return(new CommandInfo(connection, sql).QueryTable());
}