/// <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);
}
}
