/// <summary>
/// 将查询自动转化为存储过程执行
/// </summary>
/// <typeparam name="TKey"></typeparam>
/// <typeparam name="TValue"></typeparam>
/// <param name="sql"></param>
/// <returns></returns>
public Dictionary <TKey, TValue> AutoSpQuery <TKey, TValue>(string sql, params Type[] types)
{
sql = AutoFormat(sql, types);
sql = _DBAdapter.SqlFormat(sql);
string sp = CompileSqlToSp(_DBAdapter.TemplateSp, sql);
System.Data.Common.DbDataReader reader = null;
reader = dbHelper.RunDataReader(sp);
ClearParame();
return(ObjectConvert.DataReadToDictionary <TKey, TValue>(reader));
}
/// <summary>
/// 返回指定类型
/// </summary>
/// <typeparam name="TModel"></typeparam>
/// <typeparam name="TResult"></typeparam>
/// <param name="query"></param>
/// <returns></returns>
public List <TResult> QueryDynamic <TModel, TResult>(LambdaQuery <TModel> query)
where TModel : IModel, new()
where TResult : class, new()
{
var reader = GetQueryDynamicReader(query);
double runTime;
var list = ObjectConvert.DataReaderToList <TResult>(reader, out runTime, false);
query.MapingTime += runTime;
query.RowCount = list.Count;
return(list);
}
/// <summary>
/// 返回首行首列
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <param name="types"></param>
/// <returns></returns>
public T AutoExecuteScalar <T>(string sql, params Type[] types)
{
object obj;
sql = _DBAdapter.SqlFormat(sql);
sql = AutoFormat(sql, types);
sql = _DBAdapter.SqlFormat(sql);
string sp = CompileSqlToSp(_DBAdapter.TemplateSp, sql);
obj = RunScalar(sp);
ClearParame();
return(ObjectConvert.ConvertObject <T>(obj));
}
static object ToType(Dictionary <string, PropertyInfo> sourceTypes, Dictionary <string, PropertyInfo> destTypes, object source, Type toType)
{
if (source == null)
{
return(null);
}
object obj;
//obj = System.Activator.CreateInstance(toType);
try
{
obj = System.Runtime.Serialization.FormatterServices.GetUninitializedObject(toType);
}
catch
{
throw new CRLException(string.Format("{1}不能转换为{0},请检查属性定义", toType, source.GetType()));
}
foreach (var kv in destTypes)
{
var key = kv.Key;
var info = kv.Value;
PropertyInfo sourceInfo;
var a = sourceTypes.TryGetValue(key, out sourceInfo);
if (!a)
{
continue;
}
object value;
var nameSpace = sourceInfo.PropertyType.Namespace;
if (nameSpace == "System" || sourceInfo.PropertyType.BaseType == typeof(Enum))
{
value = sourceInfo.GetValue(source, null);
value = ObjectConvert.ConvertObject(info.PropertyType, value);
}
else//如果是class,则再转换一次
{
object value2 = sourceInfo.GetValue(source, null);
if (value2 == null)
{
continue;
}
var sourceTypes2 = GetObjProperty(sourceInfo.PropertyType);
var destTypes2 = GetObjProperty(info.PropertyType);
value = ToType(sourceTypes2, destTypes2, value2, info.PropertyType);
}
info.SetValue(obj, value, null);
}
return(obj);
}
/// <summary>
/// 使用完整的LamadaQuery查询
/// </summary>
/// <typeparam name="TItem"></typeparam>
/// <param name="query"></param>
/// <param name="cacheTime"></param>
/// <param name="cacheKey">过期时间,分</param>
/// <param name="compileSp">是否编译成储过程</param>
/// <returns></returns>
public List <TItem> QueryList <TItem>(LambdaQuery <TItem> query, int cacheTime, out string cacheKey, bool compileSp = false) where TItem : IModel, new()
{
CheckTableCreated <TItem>();
if (query.PageSize > 0)//按分页
{
cacheKey = "";
int count;
return(AutoSpPage(query, out count));
}
string sql = "";
bool setConstraintObj = true;
cacheKey = "";
//foreach (var n in query.QueryParames)
//{
// AddParam(n.Key, n.Value);
//}
query.FillParames(this);
sql = query.GetQuery();
sql = _DBAdapter.SqlFormat(sql);
//DataTable dt;
System.Data.Common.DbDataReader reader;
List <TItem> list;
if (cacheTime <= 0)
{
if (!compileSp)
{
if (query.QueryTop > 0)
{
helper.AutoFormatWithNolock = false;
}
reader = helper.ExecDataReader(sql);
}
else//生成储过程
{
string sp = CompileSqlToSp(_DBAdapter.TemplateSp, sql);
reader = helper.RunDataReader(sql);
}
list = ObjectConvert.DataReaderToList <TItem>(reader, setConstraintObj, query.FieldMapping);
}
else
{
list = MemoryDataCache.GetCacheList <TItem>(sql, cacheTime, helper, out cacheKey).Values.ToList();
}
ClearParame();
query.RowCount = list.Count;
SetOriginClone(list);
return(list);
}
/// <summary>
/// 使用完整的LamadaQuery查询
/// </summary>
/// <typeparam name="TItem"></typeparam>
/// <param name="query"></param>
/// <param name="cacheKey">cacheKey</param>
/// <returns></returns>
public List <TItem> QueryList <TItem>(LambdaQuery <TItem> query, out string cacheKey) where TItem : IModel, new()
{
CheckTableCreated <TItem>();
if (query.__PageSize > 0)//按分页
{
cacheKey = "";
return(Page <TItem, TItem>(query));
}
string sql = "";
cacheKey = "";
query.FillParames(this);
sql = query.GetQuery();
sql = _DBAdapter.SqlFormat(sql);
System.Data.Common.DbDataReader reader;
var cacheTime = query.__ExpireMinute;
var compileSp = query.__CompileSp;
List <TItem> list;
double runTime;
if (cacheTime <= 0)
{
if (!compileSp)
{
if (query.__QueryTop > 0)
{
dbHelper.AutoFormatWithNolock = false;
}
reader = dbHelper.ExecDataReader(sql);
}
else//生成储过程
{
string sp = CompileSqlToSp(_DBAdapter.TemplateSp, sql);
reader = dbHelper.RunDataReader(sql);
}
query.ExecuteTime += dbHelper.ExecuteTime;
list = ObjectConvert.DataReaderToList <TItem>(reader, out runTime, true);
query.MapingTime += runTime;
}
else
{
list = MemoryDataCache.CacheService.GetCacheList <TItem>(sql, cacheTime, dbHelper, out cacheKey).Values.ToList();
}
ClearParame();
query.RowCount = list.Count;
SetOriginClone(list);
return(list);
}
static object ToType(IEnumerable <PropertyInfo> sourceTypes, IEnumerable <PropertyInfo> destTypes, object source, Type toType)
{
if (source == null)
{
return(null);
}
object obj;
//obj = System.Activator.CreateInstance(toType);
try
{
obj = System.Activator.CreateInstance(toType);
}
catch
{
throw new CRLException(string.Format("{1}不能转换为{0},请检查属性定义", toType, source.GetType()));
}
foreach (var info in destTypes)
{
var sourceInfo = sourceTypes.Find(b => b.Name.ToLower() == info.Name.ToLower());
if (sourceInfo != null)
{
object value;
var nameSpace = sourceInfo.PropertyType.Namespace;
if (nameSpace == "System" || sourceInfo.PropertyType.BaseType == typeof(Enum))
{
value = sourceInfo.GetValue(source, null);
value = ObjectConvert.ConvertObject(info.PropertyType, value);
}
else//如果是class,则再转换一次
{
object value2 = sourceInfo.GetValue(source, null);
if (value2 == null)
{
continue;
}
var sourceTypes2 = sourceInfo.PropertyType.GetProperties().ToList();
sourceTypes2.RemoveAll(b => b.SetMethod == null || (b.SetMethod != null && b.SetMethod.Name == "set_Item"));
var destTypes2 = info.PropertyType.GetProperties().ToList();
destTypes2.RemoveAll(b => b.SetMethod == null || (b.SetMethod != null && b.SetMethod.Name == "set_Item"));
value = ToType(sourceTypes2, destTypes2, value2, info.PropertyType);
}
info.SetValue(obj, value, null);
}
}
return(obj);
}
static List <object> QueryData(string key, Type type, string query, DBHelper helper)
{
if (cacheDatas.Count > 1000)
{
EventLog.Log("数据缓存超过了1000个,请检查程序调用是否正确", true);
}
DateTime time = DateTime.Now;
System.Data.Common.DbDataReader reader;
string sql;
//语句
if (query.IndexOf("select ") > -1)
{
sql = query;
reader = helper.ExecDataReader(sql);
}//存储过程
else if (query.IndexOf("exec ") > -1)
{
string sp = query.Replace("exec ", "");
reader = helper.RunDataReader(sp);
}//表名
else
{
sql = "select * from " + query;
reader = helper.ExecDataReader(sql);
}
var list = ObjectConvert.DataReaderToList <object>(reader, type, true);
string par = "";
foreach (KeyValuePair <string, object> item in helper.Params)
{
par += item.Key + ":" + item.Value;
}
//list.ForEach(b =>
// {
// var item = b as IModel;
// item.AddCacheListen();
// });
var ts = DateTime.Now - time;
//WriteLog("更新查询 " + tableName + " 参数 " + par);
EventLog.Log("更新查询 " + key + " 用时:" + ts.TotalSeconds + "秒", "DataCache");
return(list);
}
/// <summary>
/// 转换共同属性的对象
/// </summary>
/// <typeparam name="TDest"></typeparam>
/// <param name="source"></param>
/// <returns></returns>
public static TDest ToType <TDest>(this object source)
where TDest : class, new()
{
var simpleTypes = typeof(TDest).GetProperties();
List <PropertyInfo> complexTypes = source.GetType().GetProperties().ToList();
complexTypes.RemoveAll(b => b.Name == "Item");
TDest obj = new TDest();
foreach (var info in simpleTypes)
{
var complexInfo = complexTypes.Find(b => b.Name == info.Name);
if (complexInfo != null)
{
object value = complexInfo.GetValue(source, null);
value = ObjectConvert.ConvertObject(info.PropertyType, value);
info.SetValue(obj, value, null);
}
}
return(obj);
}
/// <summary>
/// 格式化为更新值查询
/// </summary>
/// <param name="setValue"></param>
/// <returns></returns>
string ForamtSetValue <T>(ParameCollection setValue) where T : IModel
{
string tableName = TypeCache.GetTableName(typeof(T));
string setString = "";
foreach (var pair in setValue)
{
string name = pair.Key;
object value = pair.Value;
value = ObjectConvert.SetNullValue(value);
if (name.StartsWith("$"))//直接按值拼接 c2["$SoldCount"] = "SoldCount+" + num;
{
name = name.Substring(1, name.Length - 1);
setString += string.Format(" {0}={1},", name, value);
}
else
{
setString += string.Format(" {0}=@{0},", name);
helper.AddParam(name, value);
}
}
setString = setString.Substring(0, setString.Length - 1);
return(setString);
}
/// <summary>
/// 设置参数
/// </summary>
/// <param name="name"></param>
/// <param name="value"></param>
public void SetParam(string name, object value)
{
value = ObjectConvert.SetNullValue(value);
helper.SetParam(name, value);
}
public Dictionary <TKey, TValue> ExecDictionary <TKey, TValue>(string sql, params Type[] types)
{
var reader = GetDataReader(sql, types);
return(ObjectConvert.DataReadToDictionary <TKey, TValue>(reader));
}
public T GetOutParam <T>(string name)
{
var obj = helper.GetOutParam(name);
return(ObjectConvert.ConvertObject <T>(obj));
}
public void SetParam(string name, object value)
{
value = ObjectConvert.CheckNullValue(value);
_Parame[name] = value;
}
/// <summary>
/// 增加参数
/// </summary>
/// <param name="name"></param>
/// <param name="value"></param>
public void AddParam(string name, object value)
{
value = ObjectConvert.CheckNullValue(value);
//__DbHelper.AddParam(name, value);
_Parame.Add(name, value);
}
/// <summary>
/// 增加参数
/// </summary>
/// <param name="name"></param>
/// <param name="value"></param>
public void AddParam(string name, object value)
{
value = ObjectConvert.SetNullValue(value);
dbHelper.AddParam(name, value);
}
/// <summary>
/// 将查询自动转化为存储过程执行
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <returns></returns>
public List <T> AutoSpQuery <T>(string sql, params Type[] types) where T : class, new()
{
var reader = AutoSpQuery(sql, types);
return(ObjectConvert.DataReaderToList <T>(reader));
}