/// <summary> /// foreach数据 /// </summary> /// <param name="name"></param> /// <param name="db"></param> /// <param name="key"></param> /// <returns></returns> public static List <Dictionary <string, object> > MapForEach(List <Dictionary <string, object> > data, string name, DataContext db, string key, ConfigModel config, int i = 1) { var result = new List <Dictionary <string, object> >(); var param = new List <DbParameter>(); var dicName = DbCache.Get(config.CacheType, string.Format("{0}.foreach.name.{1}", name.ToLower(), i)); var field = DbCache.Get(config.CacheType, string.Format("{0}.foreach.field.{1}", name.ToLower(), i)); var sql = DbCache.Get(config.CacheType, string.Format("{0}.foreach.sql.{1}", name.ToLower(), i)); data.ForEach(a => { param.Clear(); if (field.IndexOf(',') > 0) { foreach (var split in field.Split(',')) { var tempParam = DbProviderFactories.GetFactory(config).CreateParameter(); tempParam.ParameterName = split; tempParam.Value = a.GetValue(split); param.Add(tempParam); } } else { var tempParam = DbProviderFactories.GetFactory(config).CreateParameter(); tempParam.ParameterName = field; tempParam.Value = a.GetValue(field); param.Add(tempParam); } a.Add(dicName, FastRead.ExecuteSql(sql, param.ToArray(), db, key)); result.Add(a); }); return(result); }
/// <summary> /// maq 执行返回 List<Dictionary<string, object>> /// </summary> public List <Dictionary <string, object> > Query(string name, DbParameter[] param, DataContext db = null, string key = null, bool isOutSql = false) { key = key == null?MapDb(name) : key; var config = db == null?DataConfig.Get(key) : db.config; if (config.IsUpdateCache) { InstanceMap(key); } if (DbCache.Exists(config.CacheType, name.ToLower())) { var sql = MapXml.GetMapSql(name, ref param, db, key); isOutSql = isOutSql ? isOutSql : IsMapLog(name); BaseAop.AopMapBefore(name, sql, param, config, AopType.Map_List_Dic); var result = FastRead.ExecuteSql(sql, param, db, key, isOutSql, false); if (MapXml.MapIsForEach(name, config)) { if (db == null) { using (var tempDb = new DataContext(key)) { for (var i = 1; i <= MapXml.MapForEachCount(name, config); i++) { result = MapXml.MapForEach(result, name, tempDb, key, config, i); } } } else { result = MapXml.MapForEach(result, name, db, key, config); } } BaseAop.AopMapAfter(name, sql, param, config, AopType.Map_List_Dic, result); return(result); } else { BaseAop.AopMapBefore(name, "", param, config, AopType.Map_List_Dic); var data = new List <Dictionary <string, object> >(); BaseAop.AopMapAfter(name, "", param, config, AopType.Map_List_Dic, data); return(data); } }
/// <summary> /// maq 执行返回 List<Dictionary<string, object>> /// </summary> public List <Dictionary <string, object> > Query(string name, DbParameter[] param, DataContext db = null, string key = null) { key = key == null?MapDb(name) : key; var config = db == null?DataConfig.Get(key) : db.config; if (config.IsUpdateCache) { InstanceMap(key); } if (DbCache.Exists(config.CacheType, name.ToLower())) { var sql = MapXml.GetMapSql(name, ref param, db, key); var result = FastRead.ExecuteSql(sql, param, db, key); if (MapXml.MapIsForEach(name, config)) { if (db == null) { using (var tempDb = new DataContext(key)) { for (var i = 1; i <= MapXml.MapForEachCount(name, config); i++) { result = MapXml.MapForEach(result, name, tempDb, key, config, i); } } } else { result = MapXml.MapForEach(result, name, db, key, config); } } return(result); } else { return(new List <Dictionary <string, object> >()); } }
/// <summary> /// 获取表结构 /// </summary> private static TableModel GetTable(DataQuery item, string tableName) { var result = new TableModel(); result.Column = result.Column ?? new List <ColumnModel>(); using (var db = new DataContext(item.Key)) { var param = new List <DbParameter>(); if (item.Config.DbType == DataDbType.Oracle) { #region oracle //参数 var tempParam = DbProviderFactories.GetFactory(item.Config).CreateParameter(); tempParam.ParameterName = "name"; tempParam.Value = tableName.ToUpper(); param.Add(tempParam); //表 var sql = "select a.table_name,comments from user_tables a inner join user_tab_comments b on a.TABLE_NAME = b.TABLE_NAME and a.table_name = :name"; var dic = db.ExecuteSql(sql, param.ToArray(), item.Config.IsOutSql).DicList[0]; result.Name = dic.GetValue("table_name").ToStr(); result.Comments = dic.GetValue("comments").ToStr(); //列 sql = string.Format(@"select a.column_name,data_type,data_length,b.comments, (select count(0) from user_cons_columns aa, user_constraints bb where aa.constraint_name = bb.constraint_name and bb.constraint_type = 'P' and bb.table_name = :name and aa.column_name = a.column_name) iskey, nullable,data_precision,data_scale from user_tab_columns a inner join user_col_comments b on a.table_name =:name and a.table_name = b.table_name and a.column_name = b.column_name order by a.column_id asc"); var dicList = db.ExecuteSql(sql, param.ToArray(), item.Config.IsOutSql).DicList; dicList.ForEach(a => { var model = new ColumnModel(); model.Comments = a.GetValue("comments").ToStr(); model.DataType = a.GetValue("data_type").ToStr(); model.IsKey = a.GetValue("iskey").ToStr() == "1" ? true : false; model.IsNull = a.GetValue("nullable").ToStr() == "Y" ? true : false; model.Length = a.GetValue("data_length").ToStr().ToInt(0); model.Name = a.GetValue("column_name").ToStr(); model.Precision = a.GetValue("data_precision").ToStr().ToInt(0); model.Scale = a.GetValue("data_scale").ToStr().ToInt(0); result.Column.Add(model); }); #endregion } if (item.Config.DbType == DataDbType.MySql) { #region MySql //参数 var tempParam = DbProviderFactories.GetFactory(item.Config).CreateParameter(); tempParam.ParameterName = "name"; tempParam.Value = tableName.ToUpper(); param.Add(tempParam); //表 var sql = "select table_name,table_comment count from information_schema.tables where upper(table_name)=?name"; var dic = db.ExecuteSql(sql, param.ToArray(), item.Config.IsOutSql).DicList[0]; result.Name = dic.GetValue("table_name").ToStr(); result.Comments = dic.GetValue("table_comment").ToStr(); //列 sql = string.Format(@"select column_name,data_type,character_maximum_length,column_comment, (select count(0) from INFORMATION_SCHEMA.KEY_COLUMN_USAGE a where upper(TABLE_NAME)=?name and constraint_name='PRIMARY' and c.column_name=a.column_name) iskey, is_nullable,numeric_precision,numeric_scale from information_schema.columns c where upper(table_name)=?name order by ordinal_position asc"); var dicList = FastRead.ExecuteSql(sql, param.ToArray()) ?? new List <Dictionary <string, object> >(); dicList.ForEach(a => { var model = new ColumnModel(); model.Comments = a.GetValue("column_comment").ToStr(); model.DataType = a.GetValue("data_type").ToStr(); model.IsKey = a.GetValue("iskey").ToStr() == "1" ? true : false; model.IsNull = a.GetValue("is_nullabl").ToStr() == "YES" ? true : false; model.Length = a.GetValue("character_maximum_length").ToStr().ToInt(0); model.Name = a.GetValue("column_name").ToStr(); model.Precision = a.GetValue("numeric_precision").ToStr().ToInt(0); model.Scale = a.GetValue("numeric_scale").ToStr().ToInt(0); result.Column.Add(model); }); #endregion } if (item.Config.DbType == DataDbType.SqlServer) { #region SqlServer //参数 var tempParam = DbProviderFactories.GetFactory(item.Config).CreateParameter(); tempParam.ParameterName = "name"; tempParam.Value = tableName.ToUpper(); param.Add(tempParam); //表 var sql = "select name,(select top 1 value from sys.extended_properties where major_id=object_id(a.name) and minor_id=0) as value from sys.objects a where type = 'U'and upper(name) = @name"; var dic = db.ExecuteSql(sql, param.ToArray(), item.Config.IsOutSql).DicList[0]; result.Name = dic.GetValue("name").ToStr(); result.Comments = dic.GetValue("value").ToStr(); //列 sql = string.Format(@"select a.name,(select top 1 name from sys.systypes c where a.xtype=c.xtype) as type , length,b.value,(select count(0) from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME='@name' and COLUMN_NAME=a.name) as iskey, isnullable,prec,scale from syscolumns a left join sys.extended_properties b on major_id = id and minor_id = colid and b.name ='MS_Description' where a.id=object_id('@name') order by a.colid asc"); var dicList = db.ExecuteSql(sql, param.ToArray(), item.Config.IsOutSql).DicList; dicList.ForEach(a => { var model = new ColumnModel(); model.Comments = a.GetValue("value").ToStr(); model.DataType = a.GetValue("type").ToStr(); model.IsKey = a.GetValue("iskey").ToStr() == "1" ? true : false; model.IsNull = a.GetValue("isnullable").ToStr() == "1" ? true : false; model.Length = a.GetValue("length").ToStr().ToInt(0); model.Name = a.GetValue("name").ToStr(); model.Precision = a.GetValue("prec").ToStr().ToInt(0); model.Scale = a.GetValue("scale").ToStr().ToInt(0); result.Column.Add(model); }); #endregion } return(result); } }