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