/// <summary>
/// 获取内存占用大小，单位/字节
/// </summary>
/// <returns>内存占用大小</returns>
private long PrimitiveGetMemorySize(DbConnection dbConnection)
{
const string sqlStr = @"SELECT physical_memory_in_use_kb FROM sys.dm_os_process_memory";
object obj = base.PrimitiveExecuteScalar(dbConnection, sqlStr);
return(DBAccessEx.ConvertObject <long>(obj) * 1024);
}
/// <summary>
/// 判断表是否存在[存在返回true,不存在返回false]
/// </summary>
/// <param name="con">IDbConnection</param>
/// <param name="tableName">表名[表名区分大小写的数据库:Oracle,SQLite]</param>
/// <returns>存在返回true,不存在返回false</returns>
protected override bool PrimitiveExistTable(IDbConnection con, string tableName)
{
string sqlStr = $@"select count(0) from pg_tables where schemaname='public' and tablename = '{tableName}'";
object value = base.PrimitiveExecuteScalar(con, sqlStr);
return(DBAccessEx.ConvertObject <int>(value) > 0);
}
/// <summary>
/// 判断表中是否存在字段[存在返回true,不存在返回false]
/// </summary>
/// <param name="con">数据库连接对象</param>
/// <param name="tableName">表名</param>
/// <param name="fieldName">字段名</param>
/// <returns>存在返回true,不存在返回false</returns>
protected override bool PrimitiveExistField(IDbConnection con, string tableName, string fieldName)
{
string sqlStr = $@"select count(0) from information_schema.columns WHERE table_name ='{tableName}' and column_name='{fieldName}'";
object value = base.PrimitiveExecuteScalar(con, sqlStr);
return(DBAccessEx.ConvertObject <int>(value) > 0);
}
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>
/// <returns>最大连接数</returns>
private int PrimitiveGetMaxConnectCount(DbConnection dbConnection)
{
const string sqlStr = @"show max_connections";
object obj = base.PrimitiveExecuteScalar(dbConnection, sqlStr);
return(DBAccessEx.ConvertObject <int>(obj));
}
/// <summary>
/// 获取数据库系统时间
/// </summary>
/// <param name="con">数据库连接对象</param>
/// <returns>数据库系统时间</returns>
protected override DateTime PrimitiveGetDataBaseSysTime(IDbConnection con)
{
string sqlStr = @"select CURRENT_TIMESTAMP()";
object value = base.PrimitiveExecuteScalar(con, sqlStr);
return(DBAccessEx.ConvertObject <DateTime>(value));
}
private DBIndexInfoCollection ConvertTableIndexs(string tableName, DataColumnCollection cols, DataRow[] rowArr)
{
IEnumerable <IGrouping <string, DataRow> > indexGroups = rowArr.GroupBy(t => { return(DBAccessEx.ConvertObject <string>(t["INDEX_NAME"])); });
var indexinfoList = new List <DBIndexInfo>();
string indexName, fieldName;
StringBuilder sbDetail = new StringBuilder();
DataRow row;
string[] fieldArr;
foreach (var indexGroup in indexGroups)
{
indexName = indexGroup.Key;
row = indexGroup.First();
fieldArr = indexGroup.Select(t => { return(DBAccessEx.ConvertObject <string>(t["COLUMN_NAME"])); }).ToArray();
fieldName = string.Join(",", fieldArr);
sbDetail.Clear();
for (int i = 3; i < cols.Count; i++)
{
sbDetail.AppendLine($"[{cols[i].ColumnName}:{DBAccessEx.ConvertObject<string>(row[cols[i]])}]");
}
indexinfoList.Add(new DBIndexInfo(tableName, indexName, fieldName, sbDetail.ToString()));
}
return(new DBIndexInfoCollection(indexinfoList));
}
/// <summary>
/// 判断表中是否存在字段[存在返回true,不存在返回false]
/// </summary>
/// <param name="con">数据库连接对象</param>
/// <param name="tableName">表名</param>
/// <param name="fieldName">字段名</param>
/// <returns>存在返回true,不存在返回false</returns>
protected override bool PrimitiveExistField(IDbConnection con, string tableName, string fieldName)
{
string sqlStr = $@"SELECT count(0) FROM USER_TAB_COLUMNS WHERE TABLE_NAME = '{tableName}' AND COLUMN_NAME = '{fieldName}'";
object value = base.PrimitiveExecuteScalar(con, sqlStr);
return(DBAccessEx.ConvertObject <int>(value) > 0);
}
/// <summary>
/// 判断表是否存在[存在返回true,不存在返回false]
/// </summary>
/// <param name="con">IDbConnection</param>
/// <param name="tableName">表名[表名区分大小写的数据库:Oracle,SQLite]</param>
/// <returns>存在返回true,不存在返回false</returns>
protected override bool PrimitiveExistTable(IDbConnection con, string tableName)
{
string sqlStr = $@"select count(0) from tabs where TABLE_NAME ='{tableName}'";
object value = base.PrimitiveExecuteScalar(con, sqlStr);
return(DBAccessEx.ConvertObject <int>(value) > 0);
}
/// <summary>
/// 获取数据库系统时间
/// </summary>
/// <param name="con">数据库连接对象</param>
/// <returns>数据库系统时间</returns>
protected override DateTime PrimitiveGetDataBaseSysTime(IDbConnection con)
{
const string sqlStr = @"select current_date from dual";
object value = base.PrimitiveExecuteScalar(con, sqlStr);
return(DBAccessEx.ConvertObject <DateTime>(value));
}
/// <summary>
/// 判断表是否存在[存在返回true,不存在返回false]
/// </summary>
/// <param name="con">IDbConnection</param>
/// <param name="tableName">表名[表名区分大小写的数据库:Oracle,SQLite]</param>
/// <returns>存在返回true,不存在返回false</returns>
protected override bool PrimitiveExistTable(IDbConnection con, string tableName)
{
string sqlStr = $@"select Count(0) from INFORMATION_SCHEMA.TABLES where TABLE_NAME='{tableName}' AND TABLE_SCHEMA='{con.Database}'";
object value = base.PrimitiveExecuteScalar(con, sqlStr);
return(DBAccessEx.ConvertObject <int>(value) > 0);
}
/// <summary>
/// 判断表中是否存在字段[存在返回true,不存在返回false]
/// </summary>
/// <param name="con">数据库连接对象</param>
/// <param name="tableName">表名</param>
/// <param name="fieldName">字段名</param>
/// <returns>存在返回true,不存在返回false</returns>
protected override bool PrimitiveExistField(IDbConnection con, string tableName, string fieldName)
{
string sqlStr = $@"select count(0) from syscolumns where name='{fieldName}' and objectproperty(id,'IsUserTable')=1 and object_name(id)='{tableName}'";
object value = base.PrimitiveExecuteScalar(con, sqlStr);
return(DBAccessEx.ConvertObject <int>(value) > 0);
}
/// <summary>
/// 获取最大连接数
/// </summary>
/// <returns>最大连接数</returns>
private int PrimitiveGetMaxConnectCount(DbConnection dbConnection)
{
const string sqlStr = @"select value from v$parameter where name ='processes'";
object obj = base.PrimitiveExecuteScalar(dbConnection, sqlStr);
return(DBAccessEx.ConvertObject <int>(obj));
}
/// <summary>
/// 获取数据库版本信息
/// </summary>
/// <param name="con">数据库连接对象</param>
/// <returns>数据库版本信息</returns>
protected override DataBaseVersionInfo PrimitiveGetDataBaseVersion(IDbConnection con)
{
string sqlStr = @"select @@version";
object value = base.PrimitiveExecuteScalar(con, sqlStr);
string dataBaseVersion = DBAccessEx.ConvertObject <string>(value);
//dataBaseVersion:Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Data Center Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )
/*******************************************************************************************************************
* dataBaseVersion
* Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
* Apr 2 2010 15:48:46
* Copyright (c) Microsoft Corporation Data Center Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )
*******************************************************************************************************************/
const string startIndex = "Microsoft SQL Server ";
string str = dataBaseVersion.Substring(startIndex.Length);
string verStr = str.Substring(0, str.IndexOf(' '));
int version;
if (!int.TryParse(verStr, out version))
{
version = 2008;
}
return(new DataBaseVersionInfo(version, dataBaseVersion));
}
/// <summary>
/// 获取数据库系统时间
/// </summary>
/// <param name="con">数据库连接对象</param>
/// <returns>数据库系统时间</returns>
protected override DateTime PrimitiveGetDataBaseSysTime(IDbConnection con)
{
string sqlStr = @"select datetime('now','localtime')";
object value = base.PrimitiveExecuteScalar(con, sqlStr);
return(DBAccessEx.ConvertObject <DateTime>(value));
}
private DBIndexInfoCollection ConvertTableIndexs(IEnumerable rows, string tableName)
{
var indexinfoList = new List <DBIndexInfo>();
if (rows != null)
{
string indexName, fieldName, sql;
int lastLeftParenthesisIndex, lastRightParenthesisIndex;
StringBuilder sbDetail = new StringBuilder();
foreach (DataRow row in rows)
{
sbDetail.Clear();
indexName = DBAccessEx.ConvertObject <string>(row["name"]); //Stu_Index_Name
sbDetail.AppendLine($"[rootpage:{DBAccessEx.ConvertObject<string>(row["rootpage"])}]"); //63
sql = DBAccessEx.ConvertObject <string>(row["sql"]); //CREATE INDEX "Stu_Index_Name" ON "Stu"("Name")
lastLeftParenthesisIndex = sql.LastIndexOf('(');
lastRightParenthesisIndex = sql.LastIndexOf(')');
fieldName = sql.Substring(lastLeftParenthesisIndex + 1, lastRightParenthesisIndex - lastLeftParenthesisIndex - 1);
sbDetail.AppendLine($"[sql:{sql}]");
indexinfoList.Add(new DBIndexInfo(tableName, indexName, fieldName, sbDetail.ToString()));
}
}
return(new DBIndexInfoCollection(indexinfoList));
}
/// <summary>
/// 获取总连接数
/// </summary>
/// <returns>总连接数</returns>
private int PrimitiveGetTotalConnectCount(DbConnection dbConnection)
{
const string sqlStr = @"select count(*) from v$process";
object obj = base.PrimitiveExecuteScalar(dbConnection, sqlStr);
return(DBAccessEx.ConvertObject <int>(obj));
}
/// <summary>
/// 获取内存占用大小，单位/字节
/// </summary>
/// <returns>内存占用大小</returns>
private long PrimitiveGetMemorySize(DbConnection dbConnection)
{
const string sqlStr = @"select sum(value) from v$sga";
object obj = base.PrimitiveExecuteScalar(dbConnection, sqlStr);
return(DBAccessEx.ConvertObject <long>(obj));
}
/// <summary>
/// 获取内存占用大小，单位/字节
/// </summary>
/// <returns>内存占用大小</returns>
private long PrimitiveGetMemorySize(DbConnection dbConnection)
{
const string sqlStr = @"SELECT (@@key_buffer_size + @@innodb_buffer_pool_size + @@query_cache_size + @@tmp_table_size) AS result";
object obj = base.PrimitiveExecuteScalar(dbConnection, sqlStr);
return(DBAccessEx.ConvertObject <long>(obj));
}
/// <summary>
/// 获取最大连接数
/// </summary>
/// <returns>最大连接数</returns>
private int PrimitiveGetMaxConnectCount(DbConnection dbConnection)
{
const string sqlStr = @"show variables like 'max_connections'";
DataTable dt = base.PrimitiveQueryDataToDataTable(dbConnection, sqlStr);
object obj = dt.Rows[0][1];
return(DBAccessEx.ConvertObject <int>(obj));
}
/// <summary>
/// 获取数据库启动时间
/// </summary>
/// <returns>数据库启动时间</returns>
private DateTime PrimitiveGetStartTime(DbConnection dbConnection)
{
const string sqlStr = @"select pg_postmaster_start_time()";
object obj = base.PrimitiveExecuteScalar(dbConnection, sqlStr);
DateTime startTime = DBAccessEx.ConvertObject <DateTime>(obj);
return(startTime);
}
/// <summary>
/// 获取数据库启动时间
/// </summary>
/// <returns>数据库启动时间</returns>
private DateTime PrimitiveGetStartTime(DbConnection dbConnection)
{
const string sqlStr = @"SELECT sqlserver_start_time FROM sys.dm_os_sys_info";
object obj = base.PrimitiveExecuteScalar(dbConnection, sqlStr);
DateTime startTime = DBAccessEx.ConvertObject <DateTime>(obj);
return(startTime);
}
/// <summary>
/// 获取磁盘空间占用大小，单位/字节
/// </summary>
/// <returns>磁盘空间占用大小</returns>
private long PrimitiveGetDiskSize(DbConnection dbConnection)
{
string dataBaseName = this.PrimitiveGetDatabaseName(dbConnection);
string sqlStr = $@"SELECT sum(DATA_LENGTH+INDEX_LENGTH) FROM information_schema.TABLES WHERE TABLE_SCHEMA='{dataBaseName}'";
object obj = base.PrimitiveExecuteScalar(dbConnection, sqlStr);
return(DBAccessEx.ConvertObject <long>(obj));
}
/// <summary>
/// 获取数据库创建时间
/// </summary>
/// <returns>数据库创建时间</returns>
private DateTime PrimitiveGetCreatetTime(DbConnection dbConnection)
{
const string sqlStr = @"SELECT MIN(CREATED) FROM user_objects";
object obj = base.PrimitiveExecuteScalar(dbConnection, sqlStr);
DateTime createtTime = DBAccessEx.ConvertObject <DateTime>(obj);
return(createtTime);
}
/// <summary>
/// 判断表是否存在[存在返回true,不存在返回false]
/// </summary>
/// <param name="con">IDbConnection</param>
/// <param name="tableName">表名[表名区分大小写的数据库:Oracle,SQLite]</param>
/// <returns>存在返回true,不存在返回false</returns>
protected override bool PrimitiveExistTable(IDbConnection con, string tableName)
{
//string sqlStr =@"select COUNT(0) from sysobjects where id = object_id('表名') and type = 'u'";
//string sqlStr = @"select COUNT(0) from sys.tables where name='表名' and type = 'u';";
string sqlStr = $@"select COUNT(0) from sys.tables where name='{tableName}' and type = 'u'";
object value = base.PrimitiveExecuteScalar(con, sqlStr);
return(DBAccessEx.ConvertObject <int>(value) > 0);
}
/// <summary>
/// 获取数据库创建时间
/// </summary>
/// <returns>数据库创建时间</returns>
private DateTime PrimitiveGetCreatetTime(DbConnection dbConnection)
{
string dataBaseName = this.PrimitiveGetDatabaseName(dbConnection);
string sqlStr = $@"select MIN(CREATE_TIME) from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='{dataBaseName}'";
object obj = base.PrimitiveExecuteScalar(dbConnection, sqlStr);
DateTime createtTime = DBAccessEx.ConvertObject <DateTime>(obj);
return(createtTime);
}
/// <summary>
/// 获取磁盘空间占用大小，单位/字节
/// </summary>
/// <returns>磁盘空间占用大小</returns>
private long PrimitiveGetDiskSize(DbConnection dbConnection)
{
object obj;
string userName = this.PrimitiveGetLoginUserName(dbConnection);
string sqlStr = $@"select SUM(bytes) from dba_segments where owner='{userName}'";
obj = base.PrimitiveExecuteScalar(dbConnection, sqlStr);
return(DBAccessEx.ConvertObject <long>(obj));
}
/// <summary>
/// 获取最大连接数
/// </summary>
/// <returns>最大连接数</returns>
private int PrimitiveGetMaxConnectCount(DbConnection dbConnection)
{
const string sqlStr = @"select @@max_connections";
//select value from master.dbo.sysconfigures where [config] = 103--0 表示不限制,最大值是32767
//select* from master.dbo.sysconfigures(高级配置 - 比如数据库最大使用内存大小等通过修改此表中的值达到目的)
object obj = base.PrimitiveExecuteScalar(dbConnection, sqlStr);
return(DBAccessEx.ConvertObject <int>(obj));
}
/// <summary>
/// 获取数据库创建时间
/// </summary>
/// <returns>数据库创建时间</returns>
private DateTime PrimitiveGetCreatetTime(DbConnection dbConnection)
{
string dataBaseName = this.PrimitiveGetDatabaseName(dbConnection);
string sqlStr = $@"Select create_date From sys.databases where name='{dataBaseName}'";
object obj = base.PrimitiveExecuteScalar(dbConnection, sqlStr);
DateTime createtTime = DBAccessEx.ConvertObject <DateTime>(obj);
return(createtTime);
}
/// <summary>
/// 获取表的字段信息
/// </summary>
/// <param name="con">数据库连接对象</param>
/// <param name="tableName">表名</param>
/// <returns>字段信息集合</returns>
protected override List <DBFieldInfo> PrimitiveGetTableFieldInfo(IDbConnection con, string tableName)
{
var priKeyCols = this.PrimitiveQueryPriKeyField(con, tableName); //主键列名集合
//string sqlStr = string.Format("SELECT * FROM (SELECT A.*, ROWNUM RN FROM (SELECT * FROM {0}) A WHERE ROWNUM <= 1) WHERE RN >=0", tableName);
string sqlStr = $"SELECT * FROM \"{tableName}\" WHERE ROWNUM < 1";
DataTable dt = base.PrimitiveQueryDataToDataTable(con, sqlStr);
var dicFieldDbClrFieldType = base.GetFieldDbClrFieldType(dt.Columns);//字段的公共语言运行时类型字典集合
Dictionary <string, Type> colDBType = new Dictionary <string, Type>();
//查询表C#中列信息
foreach (DataColumn col in dt.Columns)
{
colDBType.Add(col.ColumnName, col.DataType);
}
sqlStr = $@"select t.COLUMN_NAME,t.DATA_TYPE,t.NULLABLE,t.DATA_DEFAULT,c.COMMENTS from user_tab_columns t,user_col_comments c where t.table_name = c.table_name and t.column_name = c.column_name and t.table_name = '{tableName}'";
using (IDbCommand cmd = DBAccessEx.CreateCommand(base._dbAccess, con, sqlStr))
{
List <DBFieldInfo> colInfos = new List <DBFieldInfo>();
object value;
string fieldName;
string dbTypeName;
bool allowNull;
object defaultValue;
string comments;
Type type;
DBFieldType fieldType;
using (IDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
fieldName = reader.GetString(0);
dbTypeName = reader.GetString(1);
allowNull = reader.GetString(2).ToUpper().Equals("Y") ? true : false;
defaultValue = reader.GetValue(3);
value = reader[4];
if (value != null)
{
comments = value.ToString();
}
else
{
comments = string.Empty;
}
type = colDBType[fieldName];
fieldType = dicFieldDbClrFieldType[fieldName];
colInfos.Add(new DBFieldInfo(tableName, fieldName, dbTypeName, type, comments, defaultValue, allowNull, fieldType, priKeyCols.Contains(fieldName)));
}
}
return(colInfos);
}
}
