private List <DBTableInfo> GetTableInfoListHasGetFieldInfo(DataTable dt, IDbConnection con) { string queryIndexSqlStr = this.GetQueryIndexSql(null); DataTable dtIndex = base.PrimitiveQueryDataToDataTable(con, queryIndexSqlStr); var indexTupleRowArr = new Tuple <string, DataRow> [dtIndex.Rows.Count]; for (int i = 0; i < dtIndex.Rows.Count; i++) { indexTupleRowArr[i] = new Tuple <string, DataRow>(DBAccessEx.ConvertObject <string>(dtIndex.Rows[i]["TABLE_NAME"]), dtIndex.Rows[i]); } DataRow[] indexArr; var tableInfoList = new List <DBTableInfo>(); string tableName; DBIndexInfoCollection indexInfoCollection = null; foreach (DataRow row in dt.Rows) { tableName = row[0].ToString(); indexArr = indexTupleRowArr.Where(t => { return(string.Equals(t.Item1, tableName)); }).Select(t => { return(t.Item2); }).ToArray(); indexInfoCollection = this.ConvertTableIndexs(tableName, dtIndex.Columns, indexArr); tableInfoList.Add(this.OracleGetTableInfoByName(con, tableName, true, indexInfoCollection, row)); } return(tableInfoList); }
/// <summary> /// 获取当前用户有权限的所有表集合 /// </summary> /// <param name="con">数据库连接对象</param> /// <param name="getFieldInfo">是否获取字段信息[true:获取字段信息;false:不获取;默认不获取]</param> /// <returns>当前用户有权限的所有表集合</returns> protected override List <DBTableInfo> PrimitiveGetTableInfoList(IDbConnection con, bool getFieldInfo) { string sqlStr = @"select a.relname as name, b.description as value from pg_class a left join (select * from pg_description where objsubid =0 ) b on a.oid = b.objoid where a.relname in (select tablename from pg_tables where schemaname = 'public') order by a.relname asc"; DataTable dt = base.PrimitiveQueryDataToDataTable(con, sqlStr); string queryIndexSql = this.GetQueryIndexSql(null); DataTable dtIndex = base.PrimitiveQueryDataToDataTable(con, queryIndexSql); var tableInfoList = new List <DBTableInfo>(); string tableName; DBIndexInfoCollection indexInfoCollection = null; string comments; //备注 object tmpValue = null; //临时变量 List <DBFieldInfo> colInfos = null; //字段集合 IEnumerable <DBFieldInfo> priKeyColInfos = null; //主键列字段集合 DataRow[] indexRowArr; foreach (DataRow row in dt.Rows) { tableName = row[0].ToString(); tmpValue = row[1]; if (tmpValue != null && tmpValue != DBNull.Value) { comments = tmpValue.ToString(); } else { comments = null; } if (getFieldInfo) { indexRowArr = dtIndex.Select($"table_name='{tableName}'"); indexInfoCollection = this.DataRowConvertToIndex(tableName, indexRowArr); colInfos = this.PrimitiveGetTableFieldInfo(con, tableName); //获取表所有字段集合 priKeyColInfos = from col in colInfos where col.IsPriKey select col; //获取主键列字段集合 } tableInfoList.Add(new DBTableInfo(tableName, comments, new DBFieldInfoCollection(colInfos), new DBFieldInfoCollection(priKeyColInfos), indexInfoCollection)); } return(tableInfoList); }
/// <summary> /// 获取当前用户有权限的所有表集合 /// </summary> /// <param name="con">数据库连接对象</param> /// <param name="getFieldInfo">是否获取字段信息[true:获取字段信息;false:不获取;默认不获取]</param> /// <returns>当前用户有权限的所有表集合</returns> protected override List <DBTableInfo> PrimitiveGetTableInfoList(IDbConnection con, bool getFieldInfo) { //string sqlStr = $@"select TABLE_NAME,TABLE_COMMENT from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='{con.Database}'"; string sqlStr = @"SHOW TABLES"; DataTable dt = base.PrimitiveQueryDataToDataTable(con, sqlStr); var tableInfoList = new List <DBTableInfo>(); string tableName; DBIndexInfoCollection indexInfoCollection = null; foreach (DataRow row in dt.Rows) { tableName = row[0].ToString(); if (getFieldInfo) { indexInfoCollection = this.PrimitiveGetTableIndexs(con, tableName); } tableInfoList.Add(this.PrimitiveGetTableInfoByName(con, tableName, getFieldInfo, indexInfoCollection)); } return(tableInfoList); }
/// <summary> /// 获取表信息[表不存在返回null] /// </summary> /// <param name="con">数据库连接对象</param> /// <param name="tableName">表名</param> /// <param name="getFieldInfo">是否获取字段信息[true:获取字段信息;false:不获取;默认不获取]</param> /// <param name="indexInfoCollection">索引集合</param> /// <returns>表信息</returns> protected override DBTableInfo PrimitiveGetTableInfoByName(IDbConnection con, string tableName, bool getFieldInfo, DBIndexInfoCollection indexInfoCollection) { string sqlStr = $@"select TABLE_NAME,TABLE_COMMENT from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='{con.Database}' AND TABLE_NAME = '{tableName}'"; DataTable dt = base.PrimitiveQueryDataToDataTable(con, sqlStr); if (dt == null || dt.Rows.Count == 0) { return(null); } string comments; //备注 object tmpValue = null; //临时变量 List <DBFieldInfo> colInfos = null; //字段集合 IEnumerable <DBFieldInfo> priKeyColInfos = null; //主键列字段集合 foreach (DataRow row in dt.Rows) { tableName = row[0].ToString(); tmpValue = row[1]; if (tmpValue != null && tmpValue != DBNull.Value) { comments = tmpValue.ToString(); } else { comments = null; } if (getFieldInfo) //获取字段信息 { colInfos = this.PrimitiveGetTableFieldInfo(con, tableName); //获取表所有字段集合 priKeyColInfos = from col in colInfos where col.IsPriKey select col; //获取主键列字段集合 } else//不获取字段信息 { colInfos = new List <DBFieldInfo>(); priKeyColInfos = new List <DBFieldInfo>(); } return(new DBTableInfo(tableName, comments, new DBFieldInfoCollection(colInfos), new DBFieldInfoCollection(priKeyColInfos), indexInfoCollection)); } return(null); }
private DBTableInfo OracleGetTableInfoByName(IDbConnection con, string tableName, bool getFieldInfo, DBIndexInfoCollection indexInfoCollection, DataRow row) { string comments; //备注 List <DBFieldInfo> colInfos = null; //字段集合 IEnumerable <DBFieldInfo> priKeyColInfos = null; //主键列字段集合 object tmpValue = row[1]; if (tmpValue != null && tmpValue != DBNull.Value) { comments = tmpValue.ToString(); } else { comments = null; } if (getFieldInfo) { //获取字段信息 colInfos = this.PrimitiveGetTableFieldInfo(con, tableName); //获取表所有字段集合 priKeyColInfos = from col in colInfos where col.IsPriKey select col; //获取主键列字段集合 } return(new DBTableInfo(tableName, comments, new DBFieldInfoCollection(colInfos), new DBFieldInfoCollection(priKeyColInfos), indexInfoCollection)); }
/// <summary> /// 获取表信息[表不存在返回null] /// </summary> /// <param name="con">数据库连接对象</param> /// <param name="tableName">表名</param> /// <param name="getFieldInfo">是否获取字段信息[true:获取字段信息;false:不获取;默认不获取]</param> /// <param name="indexInfoCollection">索引集合</param> /// <returns>表信息</returns> protected override DBTableInfo PrimitiveGetTableInfoByName(IDbConnection con, string tableName, bool getFieldInfo, DBIndexInfoCollection indexInfoCollection) { string sqlStr = this.GetQueryTableInfoSql(tableName); DataTable dt = base.PrimitiveQueryDataToDataTable(con, sqlStr); if (dt == null || dt.Rows.Count == 0) { return(null); } return(this.OracleGetTableInfoByName(con, tableName, getFieldInfo, indexInfoCollection, dt.Rows[0])); }
/// <summary> /// 获取表信息[表不存在返回null] /// </summary> /// <param name="con">数据库连接对象</param> /// <param name="tableName">表名</param> /// <param name="getFieldInfo">是否获取字段信息[true:获取字段信息;false:不获取;默认不获取]</param> /// <param name="indexInfoCollection">索引集合</param> /// <returns>表信息</returns> protected override DBTableInfo PrimitiveGetTableInfoByName(IDbConnection con, string tableName, bool getFieldInfo, DBIndexInfoCollection indexInfoCollection) { string sqlStr = $@"select b.description as des from pg_class a left join (select * from pg_description where objsubid =0 ) b on a.oid = b.objoid where a.relname ='{tableName}' order by a.relname asc"; DataTable dt = base.PrimitiveQueryDataToDataTable(con, sqlStr); if (dt == null || dt.Rows.Count == 0) { return(null); } string comments; //备注 object tmpValue = null; //临时变量 DataRow row = dt.Rows[0]; tableName = row[0].ToString(); tmpValue = row[1]; if (tmpValue != null && tmpValue != DBNull.Value) { comments = tmpValue.ToString(); } else { comments = null; } List <DBFieldInfo> colInfos = null; //字段集合 IEnumerable <DBFieldInfo> priKeyColInfos = null; //主键列字段集合 if (getFieldInfo) //获取字段信息 { colInfos = this.PrimitiveGetTableFieldInfo(con, tableName); //获取表所有字段集合 priKeyColInfos = from col in colInfos where col.IsPriKey select col; //获取主键列字段集合 } else//不获取字段信息 { colInfos = new List <DBFieldInfo>(); priKeyColInfos = new List <DBFieldInfo>(); } return(new DBTableInfo(tableName, comments, new DBFieldInfoCollection(colInfos), new DBFieldInfoCollection(priKeyColInfos), indexInfoCollection)); }
/// <summary> /// 获取表信息[表不存在返回null] /// </summary> /// <param name="con">数据库连接对象</param> /// <param name="tableName">表名</param> /// <param name="getFieldInfo">是否获取字段信息[true:获取字段信息;false:不获取;默认不获取]</param> /// <param name="indexInfoCollection">索引集合</param> /// <returns>表信息</returns> protected override DBTableInfo PrimitiveGetTableInfoByName(IDbConnection con, string tableName, bool getFieldInfo, DBIndexInfoCollection indexInfoCollection) { // string sqlStr = @"select name from sysobjects where xtype='u'"; string sqlStr = string.Format(@"select c.name,cast(isnull(f.[value], '') as nvarchar(100)) as remark from sys.objects c left join sys.extended_properties f on f.major_id=c.object_id and f.minor_id=0 and f.class=1 where c.type='u' and c.name='{0}'", tableName); DataTable dt = base.PrimitiveQueryDataToDataTable(con, sqlStr); if (dt == null || dt.Rows.Count == 0) { return(null); } string comments; //备注 object tmpValue = null; //临时变量 List <DBFieldInfo> colInfos = null; //字段集合 IEnumerable <DBFieldInfo> priKeyColInfos = null; //主键列字段集合 foreach (DataRow row in dt.Rows) { tableName = row[0].ToString(); tmpValue = row[1]; if (tmpValue != null && tmpValue != DBNull.Value) { comments = tmpValue.ToString(); } else { comments = null; } if (getFieldInfo) //获取字段信息 { colInfos = this.PrimitiveGetTableFieldInfo(con, tableName); //获取表所有字段集合 priKeyColInfos = from col in colInfos where col.IsPriKey select col; //获取主键列字段集合 } else//不获取字段信息 { colInfos = null; priKeyColInfos = null; } return(new DBTableInfo(tableName, comments, new DBFieldInfoCollection(colInfos), new DBFieldInfoCollection(priKeyColInfos), indexInfoCollection)); } throw new ArgumentException($"未查询到表[{tableName}]信息"); }
/// <summary> /// 获取表信息[表不存在返回null] /// </summary> /// <param name="con">数据库连接对象</param> /// <param name="tableName">表名</param> /// <param name="getFieldInfo">是否获取字段信息[true:获取字段信息;false:不获取;默认不获取]</param> /// <param name="indexInfoCollection">索引集合</param> /// <returns>表信息</returns> protected override DBTableInfo PrimitiveGetTableInfoByName(IDbConnection con, string tableName, bool getFieldInfo, DBIndexInfoCollection indexInfoCollection) { string sqlStr = @"select tbl_name from sqlite_master where type='table'"; DataTable dt = base.PrimitiveQueryDataToDataTable(con, sqlStr); if (dt == null || dt.Rows.Count == 0) { return(null); } string dbTableName = null; foreach (DataRow row in dt.Rows) { dbTableName = row[0].ToString(); if (tableName.Equals(dbTableName, StringComparison.OrdinalIgnoreCase)) { break; } else { dbTableName = null; } } if (string.IsNullOrWhiteSpace(dbTableName)) { throw new ArgumentException($"表[{tableName}]不存在"); } List <DBFieldInfo> colInfos = null; //字段集合 IEnumerable <DBFieldInfo> priKeyColInfos = null; //主键列字段集合 if (getFieldInfo) { colInfos = this.PrimitiveGetTableFieldInfo(con, tableName); //获取表所有字段集合 priKeyColInfos = from col in colInfos where col.IsPriKey select col; //获取主键列字段集合 } return(new DBTableInfo(tableName, string.Empty, new DBFieldInfoCollection(colInfos), new DBFieldInfoCollection(priKeyColInfos), indexInfoCollection)); }
/// <summary> /// 获取表信息[表不存在返回null] /// </summary> /// <param name="con">数据库连接对象</param> /// <param name="tableName">表名</param> /// <param name="getFieldInfo">是否获取字段信息[true:获取字段信息;false:不获取;默认不获取]</param> /// <param name="indexInfoCollection">索引集合</param> /// <returns>表信息</returns> protected abstract DBTableInfo PrimitiveGetTableInfoByName(IDbConnection con, string tableName, bool getFieldInfo, DBIndexInfoCollection indexInfoCollection);