/// <summary>
/// 获取序列是否存在
/// </summary>
/// <param name="seqName">序列名</param>
/// <param name="oper">数据库链接</param>
/// <returns></returns>
private static bool IsSequenceExists(string seqName, DataBaseOperate oper)
{
string sql = "select count(*) from information_schema.sequences where SEQUENCE_NAME='" + seqName + "'";
IDataReader reader = null;
int count = 0;
try
{
reader = oper.Query(sql, null, null);
if (reader.Read())
{
count = Convert.ToInt32(reader[0]);
}
}
catch (Exception ex)
{
throw new Exception("查询序列时候出现错误:" + ex.Message);
}
finally
{
reader.Close();
}
return(count > 0);
}
public List <DBTableInfo> GetAllTableName(DataBaseOperate oper, DBInfo info)
{
List <DBTableInfo> lstName = new List <DBTableInfo>();
ParamList lstParam = new ParamList();
//填充表
using (IDataReader reader = oper.Query(_sqlTable, lstParam, null))
{
while (reader.Read())
{
DBTableInfo tableInfo = new DBTableInfo();
if (reader.IsDBNull(0))
{
continue;
}
tableInfo.Name = reader["TABNAME"] as string;
tableInfo.Description = reader["REMARKS"] as string;
tableInfo.IsView = false;
string tableType = reader["TYPE"] as string;
if (tableType != null && tableType.Trim().Equals("V", StringComparison.CurrentCultureIgnoreCase))
{
tableInfo.IsView = true;
}
lstName.Add(tableInfo);
}
}
return(lstName);
}
/// <summary>
/// 初始化分页存储过程
/// </summary>
private static void InitProc(DataBaseOperate oper)
{
if (isCheck)
{
return;
}
string sql = "select * from sysobjects where [xtype]='p' and [name]='" + ProcName + "'";
bool hasProc = false;
IDataReader reader = null;
try
{
reader = oper.Query(sql, null, null);
if (reader.Read())
{
hasProc = true;
}
reader.Close();
if (!hasProc)
{
oper.Execute(GetProcCode(), null, null);
}
}
finally
{
oper.AutoClose();
}
isCheck = true;
}
public void RemoveBySQL(IDictionary <string, bool> tableNames, string sql, DataBaseOperate oper)
{
try
{
using (T client = CreateClient(false, QueryCache.CommandDeleteSQL))
{
//client.PrimitiveAsString = true;
string md5 = GetSQLMD5(sql);
string verKey = FormatVersionKey(md5);
if (!string.IsNullOrEmpty(md5))
{
DeleteValue(md5, client);
DeleteValue(verKey, client);
}
if (_info.SqlOutputer.HasOutput)
{
OutPutMessage(QueryCache.CommandDeleteSQL, sql, oper);
}
}
}
catch (Exception ex)
{
if (_throwExcertion)
{
throw ex;
}
else
{
OutExceptionMessage(ex, oper);
}
}
}
/// <summary>
/// 填充字类列表
/// </summary>
/// <param name="pks">ID集合</param>
/// <param name="fatherInfo">父表对应类的信息</param>
/// <param name="dicElement">元素</param>
/// <param name="mappingInfo">当前父表对应属性的映射信息</param>
private static void FillParent(object pk, EntityInfoHandle fatherInfo,
EntityMappingInfo mappingInfo, string propertyName, EntityBase sender)
{
DBInfo db = fatherInfo.DBInfo;
DataBaseOperate oper = fatherInfo.DBInfo.DefaultOperate;
BQLDbBase dao = new BQLDbBase(oper);
ScopeList lstScope = new ScopeList();
sender.OnFillParent(propertyName, lstScope);
lstScope.AddEqual(mappingInfo.TargetProperty.PropertyName, pk);
IDataReader reader = dao.QueryReader(lstScope, fatherInfo.EntityType);
try
{
//获取子表的get列表
List <EntityPropertyInfo> lstParamNames = CacheReader.GenerateCache(reader, fatherInfo);//创建一个缓存数值列表
while (reader.Read())
{
object newObj = fatherInfo.CreateSelectProxyInstance();
mappingInfo.SetValue(sender, newObj);
CacheReader.FillObjectFromReader(reader, lstParamNames, newObj, db);
}
sender.OnPropertyUpdated(mappingInfo.PropertyName);
}
finally
{
reader.Close();
oper.AutoClose();
}
}
/// <summary>
/// 获取表信息
/// </summary>
/// <param name="oper"></param>
/// <param name="info"></param>
/// <param name="tableNames"></param>
/// <returns></returns>
public List <DBTableInfo> GetTablesInfo(DataBaseOperate oper, DBInfo info, IEnumerable <string> tableNames)
{
Dictionary <string, DBTableInfo> dicTables = new Dictionary <string, DBTableInfo>();
List <DBTableInfo> lst = GetTableNames(oper, info, tableNames);
DataTable dtSchema = oper.GetSchema("Columns");
DataTable dtDataTypes = oper.GetSchema("DataTypes");
foreach (DBTableInfo table in lst)
{
dicTables[table.Name] = table;
table.Params = new List <EntityParam>();
table.RelationItems = new List <TableRelationAttribute>();
}
foreach (DataRow dr in dtSchema.Rows)
{
string tableName = dr["TABLE_NAME"] as string;
if (string.IsNullOrEmpty(tableName))
{
continue;
}
DBTableInfo table = null;
if (dicTables.TryGetValue(tableName, out table))
{
FillParam(table, dr, dtDataTypes);
}
}
List <TableRelationAttribute> lstRelation = GetRelation(oper, info, tableNames);
Buffalo.DB.DataBaseAdapter.SqlServer2KAdapter.DBStructure.FillRelation(dicTables, lstRelation);
return(lst);
}
/// <summary>
/// 获取所有关系
/// </summary>
/// <param name="oper"></param>
/// <param name="info"></param>
/// <param name="childName"></param>
/// <returns></returns>
public List <TableRelationAttribute> GetRelation(DataBaseOperate oper, DBInfo info, IEnumerable <string> childName)
{
return(null);
DataTable dtRelation = oper.GetSchema("ForeignKeys");
List <TableRelationAttribute> lstRet = new List <TableRelationAttribute>();
foreach (DataRow row in dtRelation.Rows)
{
TableRelationAttribute tr = new TableRelationAttribute();
tr.Name = row["CONSTRAINT_NAME"] as string;
tr.SourceName = row["FKEY_FROM_COLUMN"] as string;
tr.SourceTable = row["TABLE_NAME"] as string;
tr.TargetName = row["FKEY_TO_COLUMN"] as string;
tr.TargetTable = row["FKEY_TO_TABLE"] as string;
tr.IsParent = true;
lstRet.Add(tr);
tr = new TableRelationAttribute();
tr.Name = row["CONSTRAINT_NAME"] as string;
tr.TargetName = row["FKEY_FROM_COLUMN"] as string;
tr.TargetTable = row["TABLE_NAME"] as string;
tr.SourceName = row["FKEY_TO_COLUMN"] as string;
tr.SourceTable = row["FKEY_TO_TABLE"] as string;
tr.IsParent = false;
lstRet.Add(tr);
}
return(lstRet);
}
/// <summary>
/// 获取表信息
/// </summary>
/// <param name="oper"></param>
/// <param name="info"></param>
/// <param name="tableNames"></param>
/// <returns></returns>
private List <DBTableInfo> GetTableNames(DataBaseOperate oper, DBInfo info, IEnumerable <string> tableNames)
{
ParamList lstParam = new ParamList();
string inTables = Buffalo.DB.DataBaseAdapter.SqlServer2KAdapter.DBStructure.AllInTableNames(tableNames);
StringBuilder sbSQL = new StringBuilder(_sqlTables);
if (string.IsNullOrEmpty(inTables))
{
sbSQL.Append(" and [sql]<>''");
}
else
{
sbSQL.Append(" and [Name] IN(" + inTables + ")");
}
List <DBTableInfo> lstName = new List <DBTableInfo>();
///只能获取表名,其它的没用
using (IDataReader reader = oper.Query(sbSQL.ToString(), lstParam, null))
{
while (reader.Read())
{
DBTableInfo tableInfo = new DBTableInfo();
tableInfo.Name = reader["name"] as string;
tableInfo.IsView = (reader["type"] as string) == "view";
lstName.Add(tableInfo);
}
}
return(lstName);
}
/// <summary>
/// 获取序列是否存在
/// </summary>
/// <param name="seqName">序列名</param>
/// <param name="oper">数据库链接</param>
/// <returns></returns>
internal static bool IsSequenceExists(string seqName, DataBaseOperate oper)
{
string sql = "select SEQUENCE_NAME from user_sequences where SEQUENCE_NAME='" + seqName + "'";
IDataReader reader = null;
int count = 0;
try
{
reader = oper.Query(sql, null, null);
if (reader.Read())
{
if (!reader.IsDBNull(0))
{
count = 1;
}
}
}
catch (Exception ex)
{
throw new Exception("查询序列时候出现错误:" + ex.Message);
}
finally
{
reader.Close();
}
return(count > 0);
}
/// <summary>
/// 获取所有用户表
/// </summary>
/// <returns></returns>
public virtual List <DBTableInfo> GetAllTableName(DataBaseOperate oper, DBInfo info)
{
ParamList lstParam = new ParamList();
//DataBaseOperate oper = info.DefaultOperate;
List <DBTableInfo> lstName = new List <DBTableInfo>();
using (IDataReader reader = oper.Query(_sqlTables, lstParam, null))
{
while (reader.Read())
{
DBTableInfo tableInfo = new DBTableInfo();
if (reader.IsDBNull(0))
{
continue;
}
tableInfo.Name = reader[0] as string;
string type = reader[1] as string;
if (!string.IsNullOrEmpty(type))
{
if (type.Trim() == "V")
{
tableInfo.IsView = true;
}
}
lstName.Add(tableInfo);
}
}
return(lstName);
}
/// <summary>
/// 获取所有关系
/// </summary>
/// <param name="chileName">null则查询所有表</param>
/// <returns></returns>
public List <TableRelationAttribute> GetRelation(DataBaseOperate oper, DBInfo info, IEnumerable <string> childNames)
{
List <TableRelationAttribute> lst = new List <TableRelationAttribute>();
using (BatchAction ba = oper.StarBatchAction())
{
OleDbConnection conn = oper.Connection as OleDbConnection;
oper.ConnectDataBase();
DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, null);
foreach (DataRow dr in dt.Rows)
{
TableRelationAttribute tinfo = new TableRelationAttribute();
tinfo.CreateName();
tinfo.SourceTable = dr["FK_TABLE_NAME"] as string;
tinfo.SourceName = dr["FK_COLUMN_NAME"] as string;
tinfo.TargetTable = dr["PK_TABLE_NAME"] as string;
tinfo.TargetName = dr["PK_COLUMN_NAME"] as string;
tinfo.IsParent = true;
lst.Add(tinfo);
tinfo = new TableRelationAttribute();
tinfo.CreateName();
tinfo.SourceTable = dr["PK_TABLE_NAME"] as string;
tinfo.SourceName = dr["PK_COLUMN_NAME"] as string;
tinfo.TargetTable = dr["FK_TABLE_NAME"] as string;
tinfo.TargetName = dr["FK_COLUMN_NAME"] as string;
tinfo.IsParent = false;
lst.Add(tinfo);
}
}
return(lst);
}
/// <summary>
/// 生成SQL语句
/// </summary>
/// <param name="list">参数列表</param>
/// <param name="oper">连接对象</param>
/// <param name="objCondition">条件对象</param>
/// <param name="objPage">分页记录类</param>
/// <returns></returns>
public static string CreatePageSql(ParamList list, DataBaseOperate oper,
SelectCondition objCondition, PageContent objPage, bool useCache)
{
//if (objCondition.Condition == null || objCondition.Condition == "")//初始化查询条件
//{
// objCondition.Condition = "1=1";
//}
if (objPage.CurrentPage < 0 || objPage.PageSize <= 0)//初始化页数
{
return("");
}
if (objPage.IsFillTotalRecords)
{
objPage.TotalRecords = GetTotalRecord(list, oper, objCondition, objPage,
(useCache?objCondition.CacheTables:null));//获取总记录数
long totalPage = (long)Math.Ceiling((double)objPage.TotalRecords / (double)objPage.PageSize);
objPage.TotalPage = totalPage;
if (objPage.CurrentPage >= objPage.TotalPage - 1)
{
objPage.CurrentPage = objPage.TotalPage - 1;
//objCondition.CurrentPage = objPage.CurrentPage;
}
}
return(CreateCutPageSql(objCondition, objPage));
}
/// <summary>
/// 获取所有用户表
/// </summary>
/// <param name="oper"></param>
/// <param name="info"></param>
/// <returns></returns>
private List <DBTableInfo> GetTableNames(DataBaseOperate oper, DBInfo info, IEnumerable <string> tableNames)
{
ParamList lstParam = new ParamList();
string sql = "select table_name,table_type from information_schema.tables where table_schema = 'public'";
List <DBTableInfo> lstName = new List <DBTableInfo>();
string inTable = Buffalo.DB.DataBaseAdapter.SqlServer2KAdapter.DBStructure.AllInTableNames(tableNames);
if (!string.IsNullOrEmpty(inTable))
{
sql += " and table_name in(" + inTable + ")";
}
using (IDataReader reader = oper.Query(sql, lstParam, null))
{
while (reader.Read())
{
DBTableInfo tableInfo = new DBTableInfo();
tableInfo.Name = reader[0] as string;
string type = reader[1] as string;
tableInfo.IsView = false;
if (!string.IsNullOrEmpty(type))
{
tableInfo.IsView = type.Equals("VIEW", StringComparison.CurrentCultureIgnoreCase);
}
tableInfo.Description = "";
lstName.Add(tableInfo);
}
}
return(lstName);
}
private void DataGridViewBindData(string tableName)
{
DataSet ds = DataBaseOperate.GetLogInfo(tableName);
if (ds.Tables.Count == 1 && ds.Tables[0].Rows.Count == 0)
{
this.dataGridView_AnalyzeResult.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
}
else
{
this.dataGridView_AnalyzeResult.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells;
}
System.Data.DataTable table = new DataTable();
System.Data.DataColumn column = new DataColumn();
column.ColumnName = "序号";
column.AutoIncrement = true;
column.AutoIncrementSeed = 1;
column.AutoIncrementStep = 1;
table.Columns.Add(column);
table.Merge(ds.Tables[0]);
this.dataGridView_AnalyzeResult.DataSource = table;
this.dataGridView_AnalyzeResult.Refresh();
}
private void OutExceptionMessage(Exception ex, DataBaseOperate oper)
{
MessageInfo info = new MessageInfo();
info.Value = ex;
info.Type = GetCacheName();
oper.OutMessage(MessageType.CacheException, info);
}
/// <summary>
/// 创建输出器
/// </summary>
/// <returns></returns>
internal MessageOutputBase CreateOutput(DataBaseOperate oper)
{
if (OnOutputerCreate != null)
{
return(OnOutputerCreate(oper));
}
return(null);
}
/// <summary>
/// 获取表信息
/// </summary>
/// <param name="oper"></param>
/// <param name="info"></param>
/// <param name="tableNames"></param>
/// <returns></returns>
public List <DBTableInfo> GetTablesInfo(DataBaseOperate oper, DBInfo info, IEnumerable <string> tableNames)
{
string inTable = Buffalo.DB.DataBaseAdapter.SqlServer2KAdapter.DBStructure.AllInTableNames(tableNames);
string sql = GetTableParamsSQL();
string tableNamesSql = "";
if (!string.IsNullOrEmpty(inTable))
{
tableNamesSql = " and d.[name] in(" + inTable + ")";
}
sql = sql.Replace("<%=TableNames%>", tableNamesSql);
List <DBTableInfo> lst = new List <DBTableInfo>();
Dictionary <string, DBTableInfo> dicTables = new Dictionary <string, DBTableInfo>();
using (IDataReader reader = oper.Query(sql.ToString(), new ParamList(), null))
{
while (reader.Read())
{
string tableName = reader["tableName"] as string;
if (string.IsNullOrEmpty(tableName))
{
continue;
}
DBTableInfo table = null;
dicTables.TryGetValue(tableName, out table);
if (table == null)
{
table = new DBTableInfo();
table.Name = tableName;
string type = reader["tabletype"] as string;
table.IsView = false;
if (!string.IsNullOrEmpty(type))
{
if (type.Trim() == "V")
{
table.IsView = true;
}
}
if (!table.IsView)
{
table.Description = reader["tableDescription"] as string;
}
table.RelationItems = new List <TableRelationAttribute>();
table.Params = new List <EntityParam>();
lst.Add(table);
dicTables[table.Name] = table;
}
FillParam(table, reader);
}
}
List <TableRelationAttribute> lstRelation = GetRelation(oper, info, tableNames);
FillRelation(dicTables, lstRelation);
return(lst);
}
public List <DBTableInfo> GetTablesInfo(DataBaseOperate oper, DBInfo info, IEnumerable <string> tableNames)
{
string inTable = Buffalo.DB.DataBaseAdapter.SqlServer2KAdapter.DBStructure.AllInTableNames(tableNames);
string sql = "SELECT cols.TABNAME, cols.COLNAME, cols.TYPENAME, cols.LENGTH, cols.REMARKS, cols.IDENTITY, cols.KEYSEQ, cols.NULLS,tables.TYPE,tables.REMARKS as TABREMARK FROM SYSCAT.COLUMNS as cols inner join SYSCAT.TABLES as tables on tables.TABNAME=cols.TABNAME where cols.TABSCHEMA=current schema";
string tableNamesSql = "";
if (!string.IsNullOrEmpty(inTable))
{
sql += " and cols.TABNAME in(" + inTable + ")";
}
List <DBTableInfo> lst = new List <DBTableInfo>();
Dictionary <string, DBTableInfo> dicTables = new Dictionary <string, DBTableInfo>();
using (IDataReader reader = oper.Query(sql.ToString(), new ParamList(), null))
{
while (reader.Read())
{
string tableName = reader["TABNAME"] as string;
if (string.IsNullOrEmpty(tableName))
{
continue;
}
DBTableInfo table = null;
dicTables.TryGetValue(tableName, out table);
if (table == null)
{
table = new DBTableInfo();
table.Name = tableName;
string type = reader["TYPE"] as string;
table.IsView = false;
if (!string.IsNullOrEmpty(type))
{
if (type.Trim() == "V")
{
table.IsView = true;
}
}
if (!table.IsView)
{
table.Description = reader["TABREMARK"] as string;
}
table.RelationItems = new List <TableRelationAttribute>();
table.Params = new List <EntityParam>();
lst.Add(table);
dicTables[table.Name] = table;
}
FillParam(table, reader);
}
}
List <TableRelationAttribute> lstRelation = GetRelation(oper, info, tableNames);
Buffalo.DB.DataBaseAdapter.SqlServer2KAdapter.DBStructure.FillRelation(dicTables, lstRelation);
return(lst);
}
/// <summary>
/// 初始化序列
/// </summary>
/// <param name="seqName"></param>
public static string GetInitSequence(string seqName, DataBaseOperate oper)
{
if (!IsSequenceExists(seqName, oper)) //判断是否已经存在序列
{
string sql = "CREATE SEQUENCE \"" + seqName + "\" INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE NoCACHE"; //创建序列
return(sql);
}
return(null);
}
/// <summary>
/// 填充所有表信息
/// </summary>
private List <DBTableInfo> FillAllTableInfos(DataBaseOperate oper, DBInfo info, IEnumerable <string> tableNames)
{
string inTable = Buffalo.DB.DataBaseAdapter.SqlServer2KAdapter.DBStructure.AllInTableNames(tableNames);
ParamList lstParam = new ParamList();
List <DBTableInfo> lstName = new List <DBTableInfo>();
string sql = _sqlTables;
if (!string.IsNullOrEmpty(inTable))
{
sql += " and user_tables.TABLE_NAME in(" + inTable + ")";
}
//填充表
using (IDataReader reader = oper.Query(sql, lstParam, null))
{
while (reader.Read())
{
DBTableInfo tableInfo = new DBTableInfo();
if (reader.IsDBNull(0))
{
continue;
}
tableInfo.Name = reader["TABLE_NAME"] as string;
tableInfo.Description = reader["COMMENTS"] as string;
tableInfo.IsView = false;
lstName.Add(tableInfo);
}
}
sql = _sqlViews;
if (!string.IsNullOrEmpty(inTable))
{
sql += " and user_views.VIEW_NAME in(" + inTable + ")";
}
//填充视图
using (IDataReader reader = oper.Query(sql, lstParam, null))
{
while (reader.Read())
{
DBTableInfo tableInfo = new DBTableInfo();
if (reader.IsDBNull(0))
{
continue;
}
tableInfo.Name = reader["VIEW_NAME"] as string;
tableInfo.Description = reader["COMMENTS"] as string;
tableInfo.IsView = true;
lstName.Add(tableInfo);
}
}
return(lstName);
}
/// <summary>
/// 根据SQL语句从缓存中找出数据
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="tableNames">表名集合</param>
/// <returns></returns>
public DataSet GetData(IDictionary <string, bool> tableNames, string sql, DataBaseOperate oper)
{
string key = GetKey(sql);
if (_info.SqlOutputer.HasOutput)
{
OutPutMessage(QueryCache.CommandGetDataSet, sql, oper);
}
return(_cache[key] as DataSet);
}
/// <summary>
/// 通过SQL删除某项
/// </summary>
/// <param name="sql"></param>
public void RemoveBySQL(IDictionary <string, bool> tableNames, string sql, DataBaseOperate oper)
{
string key = GetKey(sql);
_cache.Remove(key);
if (_info.SqlOutputer.HasOutput)
{
OutPutMessage(QueryCache.CommandDeleteSQL, sql, oper);
}
}
/// <summary>
/// 获取数据库表信息
/// </summary>
/// <param name="oper"></param>
/// <param name="info"></param>
/// <param name="tableNames"></param>
/// <returns></returns>
public List <DBTableInfo> GetTablesInfo(DataBaseOperate oper, DBInfo info, IEnumerable <string> tableNames)
{
string inTable = Buffalo.DB.DataBaseAdapter.SqlServer2KAdapter.DBStructure.AllInTableNames(tableNames);
StringBuilder sql = new StringBuilder();
sql.Append("SELECT USER_TAB_COLUMNS.TABLE_NAME,USER_TAB_COLUMNS.COLUMN_NAME , USER_TAB_COLUMNS.DATA_TYPE,USER_TAB_COLUMNS.DATA_LENGTH,USER_TAB_COLUMNS.NULLABLE,USER_TAB_COLUMNS.COLUMN_ID,user_col_comments.comments FROM USER_TAB_COLUMNS left join user_col_comments on user_col_comments.TABLE_NAME=USER_TAB_COLUMNS.TABLE_NAME and user_col_comments.COLUMN_NAME=USER_TAB_COLUMNS.COLUMN_NAME where 1=1");
if (!string.IsNullOrEmpty(inTable))
{
sql.Append(" and USER_TAB_COLUMNS.TABLE_NAME in(" + inTable + ")");
}
List <DBTableInfo> lst = FillAllTableInfos(oper, info, tableNames);
Dictionary <string, Dictionary <string, bool> > dicPkMap = GetPrimaryKeyMap(oper, info, tableNames);
Dictionary <string, DBTableInfo> dicTables = new Dictionary <string, DBTableInfo>();
foreach (DBTableInfo table in lst)
{
dicTables[table.Name] = table;
table.Params = new List <EntityParam>();
table.RelationItems = new List <TableRelationAttribute>();
}
using (IDataReader reader = oper.Query(sql.ToString(), new ParamList(), null))
{
Dictionary <string, bool> dicPkNames = null;
while (reader.Read())
{
string tableName = reader["TABLE_NAME"] as string;
if (string.IsNullOrEmpty(tableName))
{
continue;
}
DBTableInfo table = null;
if (dicTables.TryGetValue(tableName, out table))
{
if (dicPkMap.TryGetValue(tableName, out dicPkNames))
{
FillParam(table, reader, dicPkNames);
}
else
{
FillParam(table, reader, new Dictionary <string, bool>());
}
}
}
}
List <TableRelationAttribute> lstRelation = GetRelation(oper, info, tableNames);
Buffalo.DB.DataBaseAdapter.SqlServer2KAdapter.DBStructure.FillRelation(dicTables, lstRelation);
return(lst);
}
/// <summary>
/// 根据实体类型获取对应的数据访问层的实例
/// </summary>
/// <param name="interfaceType">实体类型</param>
/// <returns></returns>
public static object GetInstance(Type interfaceType, DataBaseOperate oper)
{
//InitConfig();
Type objType = null;
if (!_dicLoaderConfig.TryGetValue(interfaceType.FullName, out objType))
{
throw new Exception("找不到接口" + interfaceType.FullName + "的对应配置，请检查配置文件");
}
return(Activator.CreateInstance(objType, oper));
}
/// <summary>
/// 根据实体类型获取对应的数据访问层的实例
/// </summary>
/// <param name="entityType">实体类型</param>
/// <returns></returns>
public static object GetInstanceByEntity(Type entityType, DataBaseOperate oper)
{
//InitConfig();
Type objType = null;
if (!_dicEntityLoaderConfig.TryGetValue(entityType.FullName, out objType))
{
throw new Exception("找不到对应的类型" + entityType.FullName + "的配置，请检查配置文件");
}
return(Activator.CreateInstance(objType, oper));
}
/// <summary>
/// 获取所有关系
/// </summary>
/// <param name="oper"> </param>
/// <param name="info"> </param>
/// <param name="childNames">null则查询所有表</param>
/// <returns></returns>
public List <TableRelationAttribute> GetRelation(DataBaseOperate oper, DBInfo info, IEnumerable <string> childNames)
{
string sql = "SELECT t1.CONSTRAINT_NAME,t1.TABLE_NAME, t1.COLUMN_NAME, t1.POSITION_IN_UNIQUE_CONSTRAINT, t1.REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE t1 INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS t2 ON t2.TABLE_SCHEMA = t1.TABLE_SCHEMA AND t2.TABLE_NAME = t1.TABLE_NAME AND t2.CONSTRAINT_NAME = t1.CONSTRAINT_NAME WHERE t1.TABLE_SCHEMA = ?dbName AND t2.CONSTRAINT_TYPE = 'FOREIGN KEY'";
StringBuilder sqlFk = new StringBuilder();
StringBuilder sqlPk = new StringBuilder();
//sql.Append("SELECT constraint_schema,constraint_name,unique_constraint_name,table_name,referenced_table_name FROM `information_schema`.`REFERENTIAL_CONSTRAINTS`;");
sqlFk.Append(sql);
sqlPk.Append(sql);
ParamList lstParam = new ParamList();
lstParam.AddNew("?dbName", DbType.String, oper.DataBaseName);
string childName = Buffalo.DB.DataBaseAdapter.SqlServer2KAdapter.DBStructure.AllInTableNames(childNames);
if (!string.IsNullOrEmpty(childName))
{
sqlFk.Append(" and t1.TABLE_NAME in(" + childName + ")");
}
if (!string.IsNullOrEmpty(childName))
{
sqlPk.Append(" and t1.REFERENCED_TABLE_NAME in(" + childName + ")");
}
List <TableRelationAttribute> lst = new List <TableRelationAttribute>();
using (IDataReader reader = info.DefaultOperate.Query(sqlFk.ToString(), lstParam, null))
{
while (reader.Read())
{
TableRelationAttribute tinfo = new TableRelationAttribute();
tinfo.Name = reader["CONSTRAINT_NAME"] as string;
tinfo.SourceTable = reader["TABLE_NAME"] as string;
tinfo.SourceName = reader["COLUMN_NAME"] as string;
tinfo.TargetTable = reader["REFERENCED_TABLE_NAME"] as string;
tinfo.TargetName = reader["REFERENCED_COLUMN_NAME"] as string;
tinfo.IsParent = true;
lst.Add(tinfo);
}
}
using (IDataReader reader = info.DefaultOperate.Query(sqlPk.ToString(), lstParam, null))
{
while (reader.Read())
{
TableRelationAttribute tinfo = new TableRelationAttribute();
tinfo.Name = reader["CONSTRAINT_NAME"] as string;
tinfo.TargetTable = reader["TABLE_NAME"] as string;
tinfo.TargetName = reader["COLUMN_NAME"] as string;
tinfo.SourceTable = reader["REFERENCED_TABLE_NAME"] as string;
tinfo.SourceName = reader["REFERENCED_COLUMN_NAME"] as string;
tinfo.IsParent = false;
lst.Add(tinfo);
}
}
return(lst);
}
/// <summary>
/// 初始化序列
/// </summary>
/// <param name="seqName"></param>
public static string GetInitSequence(string seqName, EntityParam prm, DataBaseOperate oper)
{
string dbType = oper.DBInfo.CurrentDbAdapter.DBTypeToSQL(prm.SqlType, 4);
if (!IsSequenceExists(seqName, oper)) //判断是否已经存在序列
{
string sql = "CREATE SEQUENCE \"" + seqName + "\" INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1"; //创建序列
return(sql);
}
return(null);
}
public IDictionary <string, object> GetValues(string[] keys, DataBaseOperate oper)
{
Dictionary <string, object> dic = new Dictionary <string, object>(keys.Length);
foreach (string str in keys)
{
object val = _cache[str];
dic[str] = val;
}
return(dic);
}
/// <summary>
/// 添加字段的语句xrrrr
/// <summary>
/// 获取所有关系
/// </summary>
/// <param name="oper"> </param>
/// <param name="info"> </param>
/// <param name="childNames">null则查询所有表</param>
/// <returns></returns>
public List <TableRelationAttribute> GetRelation(DataBaseOperate oper, DBInfo info, IEnumerable <string> childNames)
{
StringBuilder sqlFk = new StringBuilder();
StringBuilder sqlPk = new StringBuilder();
string sql = "select px.conname as constraintname, px.contype, home.relname as thisname, fore.relname as theirname, px.conrelid as homeid, px.confrelid as foreid, px.conkey as thiscols, px.confkey as fcols, att.attname as colname, fatt.attname as fcolname, px.confupdtype, px.confdeltype from information_schema.table_constraints tc inner join pg_constraint px on (px.conname=tc.constraint_name) left join pg_class home on (home.oid = px.conrelid) left join pg_class fore on (fore.oid = px.confrelid) right join pg_attribute att on (att.attrelid = px.conrelid AND att.attnum = ANY(px.conkey)) right join pg_attribute fatt on (fatt.attrelid = px.confrelid AND fatt.attnum = ANY(px.confkey)) where tc.constraint_type='FOREIGN KEY'";
//sql.Append("SELECT constraint_schema,constraint_name,unique_constraint_name,table_name,referenced_table_name FROM `information_schema`.`REFERENTIAL_CONSTRAINTS`;");
sqlFk.Append(sql);
sqlPk.Append(sql);
ParamList lstParam = new ParamList();
string childName = Buffalo.DB.DataBaseAdapter.SqlServer2KAdapter.DBStructure.AllInTableNames(childNames);
if (!string.IsNullOrEmpty(childName))
{
sqlFk.Append(" and home.relname in(" + childName + ")");
}
if (!string.IsNullOrEmpty(childName))
{
sqlPk.Append(" and fore.relname in(" + childName + ")");
}
List <TableRelationAttribute> lst = new List <TableRelationAttribute>();
using (IDataReader reader = info.DefaultOperate.Query(sqlFk.ToString(), lstParam, null))
{
while (reader.Read())
{
TableRelationAttribute tinfo = new TableRelationAttribute();
tinfo.Name = reader["constraintname"] as string;
tinfo.SourceTable = reader["thisname"] as string;
tinfo.SourceName = reader["colname"] as string;
tinfo.TargetTable = reader["theirname"] as string;
tinfo.TargetName = reader["fcolname"] as string;
tinfo.IsParent = true;
lst.Add(tinfo);
}
}
using (IDataReader reader = info.DefaultOperate.Query(sqlPk.ToString(), lstParam, null))
{
while (reader.Read())
{
TableRelationAttribute tinfo = new TableRelationAttribute();
tinfo.Name = reader["constraintname"] as string;
tinfo.TargetTable = reader["thisname"] as string;
tinfo.TargetName = reader["colname"] as string;
tinfo.SourceTable = reader["theirname"] as string;
tinfo.SourceName = reader["fcolname"] as string;
tinfo.IsParent = false;
lst.Add(tinfo);
}
}
return(lst);
}
/// <summary>
/// 获取所有关系
/// </summary>
/// <param name="chileName">null则查询所有表</param>
/// <returns></returns>
public List <TableRelationAttribute> GetRelation(DataBaseOperate oper, DBInfo info, IEnumerable <string> childNames)
{
string fksql = "select * from (select fk.name fkname ,ftable.[name] childname, cn.[name] childparam, rtable.[name] parentname,pn.[name] parentparam from sysforeignkeys join sysobjects fk on sysforeignkeys.constid = fk.id join sysobjects ftable on sysforeignkeys.fkeyid = ftable.id join sysobjects rtable on sysforeignkeys.rkeyid = rtable.id join syscolumns cn on sysforeignkeys.fkeyid = cn.id and sysforeignkeys.fkey = cn.colid join syscolumns pn on sysforeignkeys.rkeyid = pn.id and sysforeignkeys.rkey = pn.colid) a where 1=1";
StringBuilder sqlfk = new StringBuilder();
StringBuilder sqlpk = new StringBuilder();
sqlfk.Append(fksql);
sqlpk.Append(fksql);
ParamList lstParam = new ParamList();
string childName = AllInTableNames(childNames);
if (!string.IsNullOrEmpty(childName))
{
sqlfk.Append(" and childname in(" + childName + ")");
}
if (!string.IsNullOrEmpty(childName))
{
sqlpk.Append(" and parentname in(" + childName + ")");
}
List <TableRelationAttribute> lst = new List <TableRelationAttribute>();
using (IDataReader reader = info.DefaultOperate.Query(sqlfk.ToString(), lstParam, null))
{
while (reader.Read())
{
TableRelationAttribute tinfo = new TableRelationAttribute();
tinfo.Name = reader["fkname"] as string;
tinfo.SourceTable = reader["childname"] as string;
tinfo.SourceName = reader["childparam"] as string;
tinfo.TargetTable = reader["parentname"] as string;
tinfo.TargetName = reader["parentparam"] as string;
tinfo.IsParent = true;
lst.Add(tinfo);
}
}
using (IDataReader reader = info.DefaultOperate.Query(sqlpk.ToString(), lstParam, null))
{
while (reader.Read())
{
TableRelationAttribute tinfo = new TableRelationAttribute();
tinfo.Name = reader["fkname"] as string;
tinfo.TargetTable = reader["childname"] as string;
tinfo.TargetName = reader["childparam"] as string;
tinfo.SourceTable = reader["parentname"] as string;
tinfo.SourceName = reader["parentparam"] as string;
tinfo.IsParent = false;
lst.Add(tinfo);
}
}
return(lst);
}