1172 lines
58 KiB
C#
1172 lines
58 KiB
C#
|
using System;
|
|||
|
|
using System.Collections.Generic;
|
|||
|
|
using System.Text;
|
|||
|
|
using CYQ.Data.Table;
|
|||
|
|
|
|||
|
|
using System.Data.Common;
|
|||
|
|
using System.Data;
|
|||
|
|
using System.IO;
|
|||
|
|
using System.Data.OleDb;
|
|||
|
|
using CYQ.Data.Cache;
|
|||
|
|
using System.Reflection;
|
|||
|
|
using CYQ.Data.Tool;
|
|||
|
|
using CYQ.Data.Orm;
|
|||
|
|
|
|||
|
|
|
|||
|
|
namespace CYQ.Data.SQL
|
|||
|
|
{
|
|||
|
|
/// <summary>
|
|||
|
|
/// <20><><EFBFBD>ṹ<EFBFBD><E1B9B9>
|
|||
|
|
/// </summary>
|
|||
|
|
internal partial class TableSchema
|
|||
|
|
{
|
|||
|
|
/// <summary>
|
|||
|
|
/// ȫ<>ֱ<EFBFBD><D6B1><EFBFBD><EFBFBD><EFBFBD><EFBFBD>棨ֻ<E6A3A8><D6BB><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ͱ<EFBFBD><CDB1><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
/// </summary>
|
|||
|
|
internal static Dictionary<string, Dictionary<string, string>> tableCache = new Dictionary<string, Dictionary<string, string>>(StringComparer.OrdinalIgnoreCase);
|
|||
|
|
/// <summary>
|
|||
|
|
/// ȫ<>ֻ<EFBFBD><D6BB><EFBFBD>ʵ<EFBFBD><CAB5><EFBFBD><EFBFBD><EFBFBD>ı<EFBFBD><C4B1>ṹ<EFBFBD><E1B9B9><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>Type<70><65>ȡ<EFBFBD>Ľṹ<C4BD><E1B9B9>
|
|||
|
|
/// </summary>
|
|||
|
|
internal static MDictionary<string, MDataColumn> columnCache = new MDictionary<string, MDataColumn>(StringComparer.OrdinalIgnoreCase);
|
|||
|
|
internal static string GetTableCacheKey(DbBase dbBase)
|
|||
|
|
{
|
|||
|
|
return GetTableCacheKey(dbBase.dalType, dbBase.DataBase, dbBase.conn);
|
|||
|
|
}
|
|||
|
|
internal static string GetTableCacheKey(DalType dalType, string dataBase, string conn)
|
|||
|
|
{
|
|||
|
|
return "TableCache_" + dalType + "." + dataBase + Math.Abs(conn.GetHashCode());
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public static MDataColumn GetColumns(Type typeInfo)
|
|||
|
|
{
|
|||
|
|
string key = "ColumnCache_" + typeInfo.FullName;
|
|||
|
|
if (columnCache.ContainsKey(key))
|
|||
|
|
{
|
|||
|
|
return columnCache[key].Clone();
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
#region <EFBFBD><EFBFBD>ȡ<EFBFBD>нṹ
|
|||
|
|
MDataColumn mdc = new MDataColumn();
|
|||
|
|
switch (StaticTool.GetSystemType(ref typeInfo))
|
|||
|
|
{
|
|||
|
|
case SysType.Base:
|
|||
|
|
case SysType.Enum:
|
|||
|
|
mdc.Add(typeInfo.Name, DataType.GetSqlType(typeInfo), false);
|
|||
|
|
return mdc;
|
|||
|
|
case SysType.Generic:
|
|||
|
|
case SysType.Collection:
|
|||
|
|
Type[] argTypes;
|
|||
|
|
Tool.StaticTool.GetArgumentLength(ref typeInfo, out argTypes);
|
|||
|
|
foreach (Type type in argTypes)
|
|||
|
|
{
|
|||
|
|
mdc.Add(type.Name, DataType.GetSqlType(type), false);
|
|||
|
|
}
|
|||
|
|
argTypes = null;
|
|||
|
|
return mdc;
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
List<PropertyInfo> pis = StaticTool.GetPropertyInfo(typeInfo);
|
|||
|
|
|
|||
|
|
SqlDbType sqlType;
|
|||
|
|
for (int i = 0; i < pis.Count; i++)
|
|||
|
|
{
|
|||
|
|
sqlType = SQL.DataType.GetSqlType(pis[i].PropertyType);
|
|||
|
|
mdc.Add(pis[i].Name, sqlType);
|
|||
|
|
MCellStruct column = mdc[i];
|
|||
|
|
LengthAttribute la = GetAttr<LengthAttribute>(pis[i]);//<2F><>ȡ<EFBFBD><C8A1><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
if (la != null)
|
|||
|
|
{
|
|||
|
|
column.MaxSize = la.MaxSize;
|
|||
|
|
column.Scale = la.Scale;
|
|||
|
|
}
|
|||
|
|
if (column.MaxSize <= 0)
|
|||
|
|
{
|
|||
|
|
column.MaxSize = DataType.GetMaxSize(sqlType);
|
|||
|
|
}
|
|||
|
|
KeyAttribute ka = GetAttr<KeyAttribute>(pis[i]);//<2F><>ȡ<EFBFBD>ؼ<EFBFBD><D8BC><EFBFBD><EFBFBD>ж<EFBFBD>
|
|||
|
|
if (ka != null)
|
|||
|
|
{
|
|||
|
|
column.IsPrimaryKey = ka.IsPrimaryKey;
|
|||
|
|
column.IsAutoIncrement = ka.IsAutoIncrement;
|
|||
|
|
column.IsCanNull = ka.IsCanNull;
|
|||
|
|
}
|
|||
|
|
else if (i == 0)
|
|||
|
|
{
|
|||
|
|
column.IsPrimaryKey = true;
|
|||
|
|
column.IsCanNull = false;
|
|||
|
|
if (column.ColumnName.ToLower().Contains("id") && (column.SqlType == System.Data.SqlDbType.Int || column.SqlType == SqlDbType.BigInt))
|
|||
|
|
{
|
|||
|
|
column.IsAutoIncrement = true;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
DefaultValueAttribute dva = GetAttr<DefaultValueAttribute>(pis[i]);
|
|||
|
|
if (dva != null && dva.DefaultValue != null)
|
|||
|
|
{
|
|||
|
|
if (column.SqlType == SqlDbType.Bit)
|
|||
|
|
{
|
|||
|
|
column.DefaultValue = (dva.DefaultValue.ToString() == "True" || dva.DefaultValue.ToString() == "1") ? 1 : 0;
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
column.DefaultValue = dva.DefaultValue;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
else if (i > pis.Count - 3 && sqlType == SqlDbType.DateTime && pis[i].Name.EndsWith("Time"))
|
|||
|
|
{
|
|||
|
|
column.DefaultValue = SqlValue.GetDate;
|
|||
|
|
}
|
|||
|
|
DescriptionAttribute da = GetAttr<DescriptionAttribute>(pis[i]);//<2F><><EFBFBD>Ƿ<EFBFBD><C7B7><EFBFBD><EFBFBD>ֶ<EFBFBD><D6B6><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ԡ<EFBFBD>
|
|||
|
|
if (da != null)
|
|||
|
|
{
|
|||
|
|
column.Description = da.Description;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
object[] tableAttr = typeInfo.GetCustomAttributes(typeof(DescriptionAttribute), false);//<2F><><EFBFBD>Ƿ<EFBFBD><C7B7><EFBFBD><EFBFBD><EFBFBD><EFBFBD>˱<EFBFBD><CBB1><EFBFBD><EFBFBD>ԣ<EFBFBD><D4A3><EFBFBD>ȡ<EFBFBD><C8A1><EFBFBD><EFBFBD><EFBFBD>ͱ<EFBFBD><CDB1><EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
if (tableAttr != null && tableAttr.Length == 1)
|
|||
|
|
{
|
|||
|
|
DescriptionAttribute attr = tableAttr[0] as DescriptionAttribute;
|
|||
|
|
if (attr != null && !string.IsNullOrEmpty(attr.Description))
|
|||
|
|
{
|
|||
|
|
mdc.Description = attr.Description;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
pis = null;
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
if (!columnCache.ContainsKey(key))
|
|||
|
|
{
|
|||
|
|
columnCache.Set(key, mdc.Clone());
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return mdc;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
private static T GetAttr<T>(PropertyInfo pi)
|
|||
|
|
{
|
|||
|
|
object[] attr = pi.GetCustomAttributes(typeof(T), false);//<2F><><EFBFBD>Ƿ<EFBFBD><C7B7><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
if (attr != null && attr.Length == 1)
|
|||
|
|
{
|
|||
|
|
return (T)attr[0];
|
|||
|
|
}
|
|||
|
|
return default(T);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
//private static KeyAttribute GetKeyAttr(PropertyInfo pi)
|
|||
|
|
//{
|
|||
|
|
// object[] attr = pi.GetCustomAttributes(typeof(KeyAttribute), false);//<2F><><EFBFBD>Ƿ<EFBFBD><C7B7><EFBFBD><EFBFBD><EFBFBD><EFBFBD>˱<EFBFBD><CBB1><EFBFBD><EFBFBD>ԣ<EFBFBD><D4A3><EFBFBD>ȡ<EFBFBD><C8A1><EFBFBD><EFBFBD><EFBFBD>ͱ<EFBFBD><CDB1><EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
// if (attr != null && attr.Length == 1)
|
|||
|
|
// {
|
|||
|
|
// return attr[0] as KeyAttribute;
|
|||
|
|
// }
|
|||
|
|
// return null;
|
|||
|
|
//}
|
|||
|
|
//private static LengthAttribute GetLengthAttr(PropertyInfo pi)
|
|||
|
|
//{
|
|||
|
|
// object[] attr = pi.GetCustomAttributes(typeof(LengthAttribute), false);//<2F><><EFBFBD>Ƿ<EFBFBD><C7B7><EFBFBD><EFBFBD><EFBFBD><EFBFBD>˱<EFBFBD><CBB1><EFBFBD><EFBFBD>ԣ<EFBFBD><D4A3><EFBFBD>ȡ<EFBFBD><C8A1><EFBFBD><EFBFBD><EFBFBD>ͱ<EFBFBD><CDB1><EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
// if (attr != null && attr.Length == 1)
|
|||
|
|
// {
|
|||
|
|
// return attr[0] as LengthAttribute;
|
|||
|
|
// }
|
|||
|
|
// return null;
|
|||
|
|
//}
|
|||
|
|
//private static DefaultValueAttribute GetDefaultValueAttr(PropertyInfo pi)
|
|||
|
|
//{
|
|||
|
|
// object[] attr = pi.GetCustomAttributes(typeof(DefaultValueAttribute), false);//<2F><><EFBFBD>Ƿ<EFBFBD><C7B7><EFBFBD><EFBFBD><EFBFBD><EFBFBD>˱<EFBFBD><CBB1><EFBFBD><EFBFBD>ԣ<EFBFBD><D4A3><EFBFBD>ȡ<EFBFBD><C8A1><EFBFBD><EFBFBD><EFBFBD>ͱ<EFBFBD><CDB1><EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
// if (attr != null && attr.Length == 1)
|
|||
|
|
// {
|
|||
|
|
// return attr[0] as DefaultValueAttribute;
|
|||
|
|
// }
|
|||
|
|
// return null;
|
|||
|
|
//}
|
|||
|
|
private static DescriptionAttribute GetDescriptionAttr(PropertyInfo pi)
|
|||
|
|
{
|
|||
|
|
object[] attr = pi.GetCustomAttributes(typeof(DescriptionAttribute), false);//<2F><><EFBFBD>Ƿ<EFBFBD><C7B7><EFBFBD><EFBFBD><EFBFBD><EFBFBD>˱<EFBFBD><CBB1><EFBFBD><EFBFBD>ԣ<EFBFBD><D4A3><EFBFBD>ȡ<EFBFBD><C8A1><EFBFBD><EFBFBD><EFBFBD>ͱ<EFBFBD><CDB1><EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
if (attr != null && attr.Length == 1)
|
|||
|
|
{
|
|||
|
|
return attr[0] as DescriptionAttribute;
|
|||
|
|
}
|
|||
|
|
return null;
|
|||
|
|
}
|
|||
|
|
public static MDataColumn GetColumns(string tableName, ref DbBase dbHelper)
|
|||
|
|
{
|
|||
|
|
tableName = Convert.ToString(SqlCreate.SqlToViewSql(tableName));
|
|||
|
|
string key = GetSchemaKey(tableName, dbHelper.DataBase, dbHelper.dalType);
|
|||
|
|
if (CacheManage.LocalInstance.Contains(key))//<2F><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ȡ
|
|||
|
|
{
|
|||
|
|
return CacheManage.LocalInstance.Get<MDataColumn>(key).Clone();
|
|||
|
|
}
|
|||
|
|
DalType dalType = dbHelper.dalType;
|
|||
|
|
|
|||
|
|
#region <EFBFBD>ı<EFBFBD><EFBFBD><EFBFBD><EFBFBD>ݿ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
if (dalType == DalType.Txt || dalType == DalType.Xml)
|
|||
|
|
{
|
|||
|
|
if (!tableName.Contains(" "))// || tableName.IndexOfAny(Path.GetInvalidPathChars()) == -1
|
|||
|
|
{
|
|||
|
|
tableName = SqlFormat.NotKeyword(tableName);//<2F><><EFBFBD><EFBFBD>database..tableName;
|
|||
|
|
tableName = Path.GetFileNameWithoutExtension(tableName);//<2F><>ͼ<EFBFBD><CDBC><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>.<2E><><EFBFBD>ģ<EFBFBD><C4A3><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
string fileName = dbHelper.Con.DataSource + tableName + (dalType == DalType.Txt ? ".txt" : ".xml");
|
|||
|
|
MDataColumn mdc = MDataColumn.CreateFrom(fileName);
|
|||
|
|
mdc.dalType = dalType;
|
|||
|
|
return mdc;
|
|||
|
|
}
|
|||
|
|
return GetTxtDBViewColumns(tableName);//<2F><><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ͼ
|
|||
|
|
}
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
|
|||
|
|
|
|||
|
|
tableName = SqlFormat.Keyword(tableName, dbHelper.dalType);
|
|||
|
|
|
|||
|
|
//switch (dalType)
|
|||
|
|
//{
|
|||
|
|
// case DalType.SQLite:
|
|||
|
|
// case DalType.MySql:
|
|||
|
|
// tableName = SqlFormat.NotKeyword(tableName);
|
|||
|
|
// break;
|
|||
|
|
|
|||
|
|
//}
|
|||
|
|
|
|||
|
|
|
|||
|
|
MDataColumn mdcs = new MDataColumn();
|
|||
|
|
mdcs.dalType = dalType
|
|||
|
|
;
|
|||
|
|
//<2F><><EFBFBD><EFBFBD>table<6C><65>helper<65><72><EFBFBD><EFBFBD>ͬһ<CDAC><D2BB><EFBFBD><EFBFBD>
|
|||
|
|
DbBase helper = dbHelper.ResetDbBase(tableName);
|
|||
|
|
|
|||
|
|
helper.IsAllowRecordSql = false;//<2F>ڲ<EFBFBD>ϵͳ<CFB5><CDB3><EFBFBD><EFBFBD><EFBFBD><EFBFBD>¼SQL<51><4C><EFBFBD>ṹ<EFBFBD><E1B9B9><EFBFBD>䡣
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
bool isView = tableName.Contains(" ");//<2F>Ƿ<EFBFBD><C7B7><EFBFBD>ͼ<EFBFBD><CDBC>
|
|||
|
|
if (!isView)
|
|||
|
|
{
|
|||
|
|
isView = Exists("V", tableName, ref helper);
|
|||
|
|
}
|
|||
|
|
MCellStruct mStruct = null;
|
|||
|
|
SqlDbType sqlType = SqlDbType.NVarChar;
|
|||
|
|
if (isView)
|
|||
|
|
{
|
|||
|
|
string sqlText = SqlFormat.BuildSqlWithWhereOneEqualsTow(tableName);// string.Format("select * from {0} where 1=2", tableName);
|
|||
|
|
mdcs = GetViewColumns(sqlText, ref helper);
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
mdcs.AddRelateionTableName(SqlFormat.NotKeyword(tableName));
|
|||
|
|
switch (dalType)
|
|||
|
|
{
|
|||
|
|
case DalType.MsSql:
|
|||
|
|
case DalType.Oracle:
|
|||
|
|
case DalType.MySql:
|
|||
|
|
case DalType.Sybase:
|
|||
|
|
case DalType.PostgreSQL:
|
|||
|
|
#region Sql
|
|||
|
|
string sql = string.Empty;
|
|||
|
|
if (dalType == DalType.MsSql)
|
|||
|
|
{
|
|||
|
|
#region Mssql
|
|||
|
|
string dbName = null;
|
|||
|
|
if (!helper.Version.StartsWith("08"))
|
|||
|
|
{
|
|||
|
|
//<2F>Ȼ<EFBFBD>ȡͬ<C8A1><CDAC><EFBFBD>ʣ<EFBFBD><CAA3><EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ƿ<EFBFBD><C7B7><EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
string realTableName = Convert.ToString(helper.ExeScalar(string.Format(MSSQL_SynonymsName, SqlFormat.NotKeyword(tableName)), false));
|
|||
|
|
if (!string.IsNullOrEmpty(realTableName))
|
|||
|
|
{
|
|||
|
|
string[] items = realTableName.Split('.');
|
|||
|
|
tableName = realTableName;
|
|||
|
|
if (items.Length > 0)//<2F><><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
{
|
|||
|
|
dbName = realTableName.Split('.')[0];
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
sql = GetMSSQLColumns(helper.Version.StartsWith("08"), dbName ?? helper.DataBase);
|
|||
|
|
#endregion
|
|||
|
|
}
|
|||
|
|
else if (dalType == DalType.MySql)
|
|||
|
|
{
|
|||
|
|
sql = GetMySqlColumns(helper.DataBase);
|
|||
|
|
}
|
|||
|
|
else if (dalType == DalType.Oracle)
|
|||
|
|
{
|
|||
|
|
tableName = tableName.ToUpper();//Oracleת<65><D7AA>д<EFBFBD><D0B4>
|
|||
|
|
//<2F>Ȼ<EFBFBD>ȡͬ<C8A1><CDAC><EFBFBD>ʣ<EFBFBD><CAA3><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ƿ<EFBFBD><C7B7><EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
string realTableName = Convert.ToString(helper.ExeScalar(string.Format(Oracle_SynonymsName, SqlFormat.NotKeyword(tableName)), false));
|
|||
|
|
if (!string.IsNullOrEmpty(realTableName))
|
|||
|
|
{
|
|||
|
|
tableName = realTableName;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
sql = GetOracleColumns();
|
|||
|
|
}
|
|||
|
|
else if (dalType == DalType.Sybase)
|
|||
|
|
{
|
|||
|
|
tableName = SqlFormat.NotKeyword(tableName);
|
|||
|
|
sql = GetSybaseColumns();
|
|||
|
|
}
|
|||
|
|
else if (dalType == DalType.PostgreSQL)
|
|||
|
|
{
|
|||
|
|
sql = GetPostgreColumns();
|
|||
|
|
}
|
|||
|
|
helper.AddParameters("TableName", SqlFormat.NotKeyword(tableName), DbType.String, 150, ParameterDirection.Input);
|
|||
|
|
DbDataReader sdr = helper.ExeDataReader(sql, false);
|
|||
|
|
if (sdr != null)
|
|||
|
|
{
|
|||
|
|
long maxLength;
|
|||
|
|
bool isAutoIncrement = false;
|
|||
|
|
short scale = 0;
|
|||
|
|
string sqlTypeName = string.Empty;
|
|||
|
|
while (sdr.Read())
|
|||
|
|
{
|
|||
|
|
short.TryParse(Convert.ToString(sdr["Scale"]), out scale);
|
|||
|
|
if (!long.TryParse(Convert.ToString(sdr["MaxSize"]), out maxLength))//mysql<71>ij<EFBFBD><C4B3>ȿ<EFBFBD><C8BF>ܴ<EFBFBD><DCB4><EFBFBD>int.MaxValue
|
|||
|
|
{
|
|||
|
|
maxLength = -1;
|
|||
|
|
}
|
|||
|
|
else if (maxLength > int.MaxValue)
|
|||
|
|
{
|
|||
|
|
maxLength = int.MaxValue;
|
|||
|
|
}
|
|||
|
|
sqlTypeName = Convert.ToString(sdr["SqlType"]);
|
|||
|
|
sqlType = DataType.GetSqlType(sqlTypeName);
|
|||
|
|
isAutoIncrement = Convert.ToBoolean(sdr["IsAutoIncrement"]);
|
|||
|
|
mStruct = new MCellStruct(mdcs.dalType);
|
|||
|
|
mStruct.ColumnName = Convert.ToString(sdr["ColumnName"]).Trim();
|
|||
|
|
mStruct.OldName = mStruct.ColumnName;
|
|||
|
|
mStruct.SqlType = sqlType;
|
|||
|
|
mStruct.IsAutoIncrement = isAutoIncrement;
|
|||
|
|
mStruct.IsCanNull = Convert.ToBoolean(sdr["IsNullable"]);
|
|||
|
|
mStruct.MaxSize = (int)maxLength;
|
|||
|
|
mStruct.Scale = scale;
|
|||
|
|
mStruct.Description = Convert.ToString(sdr["Description"]);
|
|||
|
|
mStruct.DefaultValue = SqlFormat.FormatDefaultValue(dalType, sdr["DefaultValue"], 0, sqlType);
|
|||
|
|
mStruct.IsPrimaryKey = Convert.ToString(sdr["IsPrimaryKey"]) == "1";
|
|||
|
|
switch (dalType)
|
|||
|
|
{
|
|||
|
|
case DalType.MsSql:
|
|||
|
|
case DalType.MySql:
|
|||
|
|
case DalType.Oracle:
|
|||
|
|
mStruct.IsUniqueKey = Convert.ToString(sdr["IsUniqueKey"]) == "1";
|
|||
|
|
mStruct.IsForeignKey = Convert.ToString(sdr["IsForeignKey"]) == "1";
|
|||
|
|
mStruct.FKTableName = Convert.ToString(sdr["FKTableName"]);
|
|||
|
|
break;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
mStruct.SqlTypeName = sqlTypeName;
|
|||
|
|
mStruct.TableName = SqlFormat.NotKeyword(tableName);
|
|||
|
|
mdcs.Add(mStruct);
|
|||
|
|
}
|
|||
|
|
sdr.Close();
|
|||
|
|
if (dalType == DalType.Oracle && mdcs.Count > 0)//Ĭ<><C4AC>û<EFBFBD><C3BB><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֻ<EEA3AC>ܸ<EFBFBD><DCB8><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>жϡ<D0B6>
|
|||
|
|
{
|
|||
|
|
MCellStruct firstColumn = mdcs[0];
|
|||
|
|
if (firstColumn.IsPrimaryKey && firstColumn.ColumnName.ToLower().Contains("id") && firstColumn.Scale == 0 && DataType.GetGroup(firstColumn.SqlType) == 1 && mdcs.JointPrimary.Count == 1)
|
|||
|
|
{
|
|||
|
|
firstColumn.IsAutoIncrement = true;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
#endregion
|
|||
|
|
break;
|
|||
|
|
case DalType.SQLite:
|
|||
|
|
#region SQlite
|
|||
|
|
if (helper.Con.State != ConnectionState.Open)
|
|||
|
|
{
|
|||
|
|
helper.Con.Open();
|
|||
|
|
}
|
|||
|
|
DataTable sqliteDt = helper.Con.GetSchema("Columns", new string[] { null, null, SqlFormat.NotKeyword(tableName) });
|
|||
|
|
if (!helper.isOpenTrans)
|
|||
|
|
{
|
|||
|
|
helper.Con.Close();
|
|||
|
|
}
|
|||
|
|
int size;
|
|||
|
|
short sizeScale;
|
|||
|
|
string dataTypeName = string.Empty;
|
|||
|
|
|
|||
|
|
foreach (DataRow row in sqliteDt.Rows)
|
|||
|
|
{
|
|||
|
|
object len = row["NUMERIC_PRECISION"];
|
|||
|
|
if (len == null || len == DBNull.Value)
|
|||
|
|
{
|
|||
|
|
len = row["CHARACTER_MAXIMUM_LENGTH"];
|
|||
|
|
}
|
|||
|
|
short.TryParse(Convert.ToString(row["NUMERIC_SCALE"]), out sizeScale);
|
|||
|
|
if (!int.TryParse(Convert.ToString(len), out size))//mysql<71>ij<EFBFBD><C4B3>ȿ<EFBFBD><C8BF>ܴ<EFBFBD><DCB4><EFBFBD>int.MaxValue
|
|||
|
|
{
|
|||
|
|
size = -1;
|
|||
|
|
}
|
|||
|
|
dataTypeName = Convert.ToString(row["DATA_TYPE"]);
|
|||
|
|
if (dataTypeName == "text" && size > 0)
|
|||
|
|
{
|
|||
|
|
sqlType = DataType.GetSqlType("varchar");
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
sqlType = DataType.GetSqlType(dataTypeName);
|
|||
|
|
}
|
|||
|
|
//COLUMN_NAME,DATA_TYPE,PRIMARY_KEY,IS_NULLABLE,CHARACTER_MAXIMUM_LENGTH AUTOINCREMENT
|
|||
|
|
|
|||
|
|
mStruct = new MCellStruct(row["COLUMN_NAME"].ToString(), sqlType, Convert.ToBoolean(row["AUTOINCREMENT"]), Convert.ToBoolean(row["IS_NULLABLE"]), size);
|
|||
|
|
mStruct.Scale = sizeScale;
|
|||
|
|
mStruct.Description = Convert.ToString(row["DESCRIPTION"]);
|
|||
|
|
mStruct.DefaultValue = SqlFormat.FormatDefaultValue(dalType, row["COLUMN_DEFAULT"], 0, sqlType);//"COLUMN_DEFAULT"
|
|||
|
|
mStruct.IsPrimaryKey = Convert.ToBoolean(row["PRIMARY_KEY"]);
|
|||
|
|
mStruct.SqlTypeName = dataTypeName;
|
|||
|
|
mStruct.TableName = SqlFormat.NotKeyword(tableName);
|
|||
|
|
mdcs.Add(mStruct);
|
|||
|
|
}
|
|||
|
|
#endregion
|
|||
|
|
break;
|
|||
|
|
case DalType.Access:
|
|||
|
|
#region Access
|
|||
|
|
DataTable keyDt, valueDt;
|
|||
|
|
string sqlText = SqlFormat.BuildSqlWithWhereOneEqualsTow(tableName);// string.Format("select * from {0} where 1=2", tableName);
|
|||
|
|
OleDbConnection con = new OleDbConnection(helper.Con.ConnectionString);
|
|||
|
|
OleDbCommand com = new OleDbCommand(sqlText, con);
|
|||
|
|
con.Open();
|
|||
|
|
keyDt = com.ExecuteReader(CommandBehavior.KeyInfo).GetSchemaTable();
|
|||
|
|
valueDt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, SqlFormat.NotKeyword(tableName) });
|
|||
|
|
con.Close();
|
|||
|
|
con.Dispose();
|
|||
|
|
|
|||
|
|
if (keyDt != null && valueDt != null)
|
|||
|
|
{
|
|||
|
|
string columnName = string.Empty, sqlTypeName = string.Empty;
|
|||
|
|
bool isKey = false, isCanNull = true, isAutoIncrement = false;
|
|||
|
|
int maxSize = -1;
|
|||
|
|
short maxSizeScale = 0;
|
|||
|
|
SqlDbType sqlDbType;
|
|||
|
|
foreach (DataRow row in keyDt.Rows)
|
|||
|
|
{
|
|||
|
|
columnName = row["ColumnName"].ToString();
|
|||
|
|
isKey = Convert.ToBoolean(row["IsKey"]);//IsKey
|
|||
|
|
isCanNull = Convert.ToBoolean(row["AllowDBNull"]);//AllowDBNull
|
|||
|
|
isAutoIncrement = Convert.ToBoolean(row["IsAutoIncrement"]);
|
|||
|
|
sqlTypeName = Convert.ToString(row["DataType"]);
|
|||
|
|
sqlDbType = DataType.GetSqlType(sqlTypeName);
|
|||
|
|
short.TryParse(Convert.ToString(row["NumericScale"]), out maxSizeScale);
|
|||
|
|
if (Convert.ToInt32(row["NumericPrecision"]) > 0)//NumericPrecision
|
|||
|
|
{
|
|||
|
|
maxSize = Convert.ToInt32(row["NumericPrecision"]);
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
long len = Convert.ToInt64(row["ColumnSize"]);
|
|||
|
|
if (len > int.MaxValue)
|
|||
|
|
{
|
|||
|
|
maxSize = int.MaxValue;
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
maxSize = (int)len;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
mStruct = new MCellStruct(columnName, sqlDbType, isAutoIncrement, isCanNull, maxSize);
|
|||
|
|
mStruct.Scale = maxSizeScale;
|
|||
|
|
mStruct.IsPrimaryKey = isKey;
|
|||
|
|
mStruct.SqlTypeName = sqlTypeName;
|
|||
|
|
mStruct.TableName = SqlFormat.NotKeyword(tableName);
|
|||
|
|
foreach (DataRow item in valueDt.Rows)
|
|||
|
|
{
|
|||
|
|
if (columnName == item[3].ToString())//COLUMN_NAME
|
|||
|
|
{
|
|||
|
|
if (item[8].ToString() != "")
|
|||
|
|
{
|
|||
|
|
mStruct.DefaultValue = SqlFormat.FormatDefaultValue(dalType, item[8], 0, sqlDbType);//"COLUMN_DEFAULT"
|
|||
|
|
}
|
|||
|
|
break;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
mdcs.Add(mStruct);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
break;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
helper.ClearParameters();
|
|||
|
|
}
|
|||
|
|
catch (Exception err)
|
|||
|
|
{
|
|||
|
|
helper.debugInfo.Append(err.Message);
|
|||
|
|
}
|
|||
|
|
finally
|
|||
|
|
{
|
|||
|
|
helper.IsAllowRecordSql = true;//<2F>ָ<EFBFBD><D6B8><EFBFBD>¼SQL<51><4C><EFBFBD>ṹ<EFBFBD><E1B9B9><EFBFBD>䡣
|
|||
|
|
if (helper != dbHelper)
|
|||
|
|
{
|
|||
|
|
helper.Dispose();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
if (mdcs.Count > 0)
|
|||
|
|
{
|
|||
|
|
//<2F>Ƴ<EFBFBD><C6B3><EFBFBD><EFBFBD><EFBFBD>־<EFBFBD><D6BE><EFBFBD>У<EFBFBD>
|
|||
|
|
string[] fields = AppConfig.DB.HiddenFields.Split(',');
|
|||
|
|
foreach (string item in fields)
|
|||
|
|
{
|
|||
|
|
string field = item.Trim();
|
|||
|
|
if (!string.IsNullOrEmpty(field) & mdcs.Contains(field))
|
|||
|
|
{
|
|||
|
|
mdcs.Remove(field);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
if (!CacheManage.LocalInstance.Contains(key) && mdcs.Count > 0)//<2F>ò<EFBFBD><C3B2><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ṹʱ<E1B9B9><CAB1><EFBFBD><EFBFBD><EFBFBD>档
|
|||
|
|
{
|
|||
|
|
CacheManage.LocalInstance.Set(key, mdcs.Clone());
|
|||
|
|
}
|
|||
|
|
return mdcs;
|
|||
|
|
}
|
|||
|
|
/// <summary>
|
|||
|
|
/// DbDataReader<65><72>GetSchema<6D>õ<EFBFBD><C3B5><EFBFBD>DataType<70><65>Size<7A><65>Scale<6C>ܲ<EFBFBD><DCB2><EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
/// </summary>
|
|||
|
|
internal static void FixTableSchemaType(DbDataReader sdr, DataTable tableSchema)
|
|||
|
|
{
|
|||
|
|
if (sdr != null && tableSchema != null)
|
|||
|
|
{
|
|||
|
|
tableSchema.Columns.Add("DataTypeString");
|
|||
|
|
for (int i = 0; i < sdr.FieldCount; i++)
|
|||
|
|
{
|
|||
|
|
tableSchema.Rows[i]["DataTypeString"] = sdr.GetDataTypeName(i);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
internal static MDataColumn GetColumns(DataTable tableSchema)
|
|||
|
|
{
|
|||
|
|
MDataColumn mdcs = new MDataColumn();
|
|||
|
|
if (tableSchema != null && tableSchema.Rows.Count > 0)
|
|||
|
|
{
|
|||
|
|
mdcs.isViewOwner = true;
|
|||
|
|
string columnName = string.Empty, sqlTypeName = string.Empty, tableName = string.Empty;
|
|||
|
|
bool isKey = false, isCanNull = true, isAutoIncrement = false;
|
|||
|
|
int maxSize = -1;
|
|||
|
|
short maxSizeScale = 0;
|
|||
|
|
SqlDbType sqlDbType;
|
|||
|
|
string dataTypeName = "DataTypeString";
|
|||
|
|
if (!tableSchema.Columns.Contains(dataTypeName))
|
|||
|
|
{
|
|||
|
|
dataTypeName = "DataType";
|
|||
|
|
if (!tableSchema.Columns.Contains(dataTypeName))
|
|||
|
|
{
|
|||
|
|
dataTypeName = "DataTypeName";
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
bool isHasAutoIncrement = tableSchema.Columns.Contains("IsAutoIncrement");
|
|||
|
|
bool isHasHidden = tableSchema.Columns.Contains("IsHidden");
|
|||
|
|
string hiddenFields = "," + AppConfig.DB.HiddenFields.ToLower() + ",";
|
|||
|
|
for (int i = 0; i < tableSchema.Rows.Count; i++)
|
|||
|
|
{
|
|||
|
|
DataRow row = tableSchema.Rows[i];
|
|||
|
|
tableName = Convert.ToString(row["BaseTableName"]);
|
|||
|
|
mdcs.AddRelateionTableName(tableName);
|
|||
|
|
if (isHasHidden && Convert.ToString(row["IsHidden"]) == "True")// !dcList.Contains(columnName))
|
|||
|
|
{
|
|||
|
|
continue;//<2F><><EFBFBD><EFBFBD><EFBFBD>Ǹ<EFBFBD><C7B8><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֶΡ<D6B6>
|
|||
|
|
}
|
|||
|
|
columnName = row["ColumnName"].ToString().Trim('"');//sqlite<74><65>ͼʱ<CDBC><CAB1><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
if (string.IsNullOrEmpty(columnName))
|
|||
|
|
{
|
|||
|
|
columnName = "Empty_" + i;
|
|||
|
|
}
|
|||
|
|
#region <EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ƿ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
bool isHiddenField = hiddenFields.IndexOf("," + columnName + ",", StringComparison.OrdinalIgnoreCase) > -1;
|
|||
|
|
if (isHiddenField)
|
|||
|
|
{
|
|||
|
|
continue;
|
|||
|
|
}
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
bool.TryParse(Convert.ToString(row["IsKey"]), out isKey);
|
|||
|
|
bool.TryParse(Convert.ToString(row["AllowDBNull"]), out isCanNull);
|
|||
|
|
// isKey = Convert.ToBoolean();//IsKey
|
|||
|
|
//isCanNull = Convert.ToBoolean(row["AllowDBNull"]);//AllowDBNull
|
|||
|
|
if (isHasAutoIncrement)
|
|||
|
|
{
|
|||
|
|
isAutoIncrement = Convert.ToBoolean(row["IsAutoIncrement"]);
|
|||
|
|
}
|
|||
|
|
sqlTypeName = Convert.ToString(row[dataTypeName]);
|
|||
|
|
sqlDbType = DataType.GetSqlType(sqlTypeName);
|
|||
|
|
|
|||
|
|
if (short.TryParse(Convert.ToString(row["NumericScale"]), out maxSizeScale) && maxSizeScale == 255)
|
|||
|
|
{
|
|||
|
|
maxSizeScale = 0;
|
|||
|
|
}
|
|||
|
|
if (!int.TryParse(Convert.ToString(row["NumericPrecision"]), out maxSize) || maxSize == 255)//NumericPrecision
|
|||
|
|
{
|
|||
|
|
long len;
|
|||
|
|
if (long.TryParse(Convert.ToString(row["ColumnSize"]), out len))
|
|||
|
|
{
|
|||
|
|
if (len > int.MaxValue)
|
|||
|
|
{
|
|||
|
|
maxSize = int.MaxValue;
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
maxSize = (int)len;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
MCellStruct mStruct = new MCellStruct(columnName, sqlDbType, isAutoIncrement, isCanNull, maxSize);
|
|||
|
|
mStruct.Scale = maxSizeScale;
|
|||
|
|
mStruct.IsPrimaryKey = isKey;
|
|||
|
|
mStruct.SqlTypeName = sqlTypeName;
|
|||
|
|
mStruct.TableName = tableName;
|
|||
|
|
mStruct.OldName = mStruct.ColumnName;
|
|||
|
|
mStruct.ReaderIndex = i;
|
|||
|
|
mdcs.Add(mStruct);
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
tableSchema = null;
|
|||
|
|
}
|
|||
|
|
return mdcs;
|
|||
|
|
}
|
|||
|
|
private static MDataColumn GetViewColumns(string sqlText, ref DbBase helper)
|
|||
|
|
{
|
|||
|
|
helper.OpenCon(null, AllowConnLevel.MaterBackupSlave);
|
|||
|
|
helper.Com.CommandText = sqlText;
|
|||
|
|
DbDataReader sdr = helper.Com.ExecuteReader(CommandBehavior.KeyInfo);
|
|||
|
|
DataTable keyDt = null;
|
|||
|
|
if (sdr != null)
|
|||
|
|
{
|
|||
|
|
keyDt = sdr.GetSchemaTable();
|
|||
|
|
FixTableSchemaType(sdr, keyDt);
|
|||
|
|
sdr.Close();
|
|||
|
|
}
|
|||
|
|
MDataColumn mdc = GetColumns(keyDt);
|
|||
|
|
mdc.dalType = helper.dalType;
|
|||
|
|
return mdc;
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
private static MDataColumn GetTxtDBViewColumns(string sqlText)
|
|||
|
|
{
|
|||
|
|
//sqlText=sqlText.ToLower();
|
|||
|
|
//List<string> tables = SqlFormat.GetTableNamesFromSql(sqlText);
|
|||
|
|
////string key="select "
|
|||
|
|
//string selectItems=sqlText.Substring("select
|
|||
|
|
return null;//<2F><>δʵ<CEB4><CAB5>
|
|||
|
|
}
|
|||
|
|
private static readonly object lockGetTables = new object();
|
|||
|
|
/// <summary>
|
|||
|
|
/// <20><>ȡ<EFBFBD><C8A1><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>棩
|
|||
|
|
/// </summary>
|
|||
|
|
public static Dictionary<string, string> GetTables(ref DbBase helper)
|
|||
|
|
{
|
|||
|
|
string key = GetTableCacheKey(helper);
|
|||
|
|
if (!tableCache.ContainsKey(key))
|
|||
|
|
{
|
|||
|
|
lock (lockGetTables)
|
|||
|
|
{
|
|||
|
|
if (!tableCache.ContainsKey(key))
|
|||
|
|
{
|
|||
|
|
Dictionary<string, string> tables = null;
|
|||
|
|
if (!string.IsNullOrEmpty(AppConfig.DB.SchemaMapPath))
|
|||
|
|
{
|
|||
|
|
string fullPath = AppConfig.RunPath + AppConfig.DB.SchemaMapPath + key + ".ts";
|
|||
|
|
if (System.IO.File.Exists(fullPath))
|
|||
|
|
{
|
|||
|
|
tables = JsonHelper.Split(IOHelper.ReadAllText(fullPath));
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
if (tables == null)
|
|||
|
|
{
|
|||
|
|
helper.IsAllowRecordSql = false;
|
|||
|
|
string sql = string.Empty;
|
|||
|
|
switch (helper.dalType)
|
|||
|
|
{
|
|||
|
|
case DalType.MsSql:
|
|||
|
|
sql = GetMSSQLTables(helper.Version.StartsWith("08"));
|
|||
|
|
break;
|
|||
|
|
case DalType.Oracle:
|
|||
|
|
sql = GetOracleTables();
|
|||
|
|
break;
|
|||
|
|
case DalType.MySql:
|
|||
|
|
sql = GetMySqlTables(helper.DataBase);
|
|||
|
|
break;
|
|||
|
|
case DalType.PostgreSQL:
|
|||
|
|
sql = GetPostgreTables(helper.DataBase);
|
|||
|
|
break;
|
|||
|
|
case DalType.Txt:
|
|||
|
|
case DalType.Xml:
|
|||
|
|
tables = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase);
|
|||
|
|
// string folder = Path.GetDirectoryName(helper.conn.Split(';')[0].Split('=')[1] + "\\");
|
|||
|
|
string[] files = Directory.GetFiles(helper.Con.DataSource, "*.ts");
|
|||
|
|
foreach (string file in files)
|
|||
|
|
{
|
|||
|
|
tables.Add(Path.GetFileNameWithoutExtension(file), "");
|
|||
|
|
}
|
|||
|
|
files = null;
|
|||
|
|
break;
|
|||
|
|
case DalType.Access:
|
|||
|
|
case DalType.SQLite:
|
|||
|
|
case DalType.Sybase:
|
|||
|
|
#region <EFBFBD><EFBFBD>ADO.NET<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
string restrict = "TABLE";
|
|||
|
|
if (helper.dalType == DalType.Sybase)
|
|||
|
|
{
|
|||
|
|
restrict = "BASE " + restrict;
|
|||
|
|
}
|
|||
|
|
tables = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase);
|
|||
|
|
helper.Con.Open();
|
|||
|
|
DataTable dt = helper.Con.GetSchema("Tables", new string[] { null, null, null, restrict });
|
|||
|
|
helper.Con.Close();
|
|||
|
|
if (dt != null && dt.Rows.Count > 0)
|
|||
|
|
{
|
|||
|
|
string tableName = string.Empty;
|
|||
|
|
foreach (DataRow row in dt.Rows)
|
|||
|
|
{
|
|||
|
|
tableName = Convert.ToString(row["TABLE_NAME"]);
|
|||
|
|
if (!tables.ContainsKey(tableName))
|
|||
|
|
{
|
|||
|
|
tables.Add(tableName, string.Empty);
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
Log.WriteLogToTxt("Dictionary Has The Same TableName<6D><65>" + tableName);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
dt = null;
|
|||
|
|
}
|
|||
|
|
#endregion
|
|||
|
|
break;
|
|||
|
|
}
|
|||
|
|
if (tables == null)
|
|||
|
|
{
|
|||
|
|
#region <EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ<EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
tables = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase);
|
|||
|
|
DbDataReader sdr = helper.ExeDataReader(sql, false);
|
|||
|
|
if (sdr != null)
|
|||
|
|
{
|
|||
|
|
string tableName = string.Empty;
|
|||
|
|
while (sdr.Read())
|
|||
|
|
{
|
|||
|
|
tableName = Convert.ToString(sdr["TableName"]);
|
|||
|
|
if (!tables.ContainsKey(tableName))
|
|||
|
|
{
|
|||
|
|
if (!tableName.StartsWith("BIN$"))//Oracle<6C><65><EFBFBD><EFBFBD>ɾ<EFBFBD><C9BE><EFBFBD>ı<EFBFBD><C4B1><EFBFBD>
|
|||
|
|
{
|
|||
|
|
tables.Add(tableName, Convert.ToString(sdr["Description"]));
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
Log.WriteLogToTxt("Dictionary Has The Same TableName<6D><65>" + tableName);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
sdr.Close();
|
|||
|
|
sdr = null;
|
|||
|
|
}
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
if (!string.IsNullOrEmpty(AppConfig.DB.SchemaMapPath))
|
|||
|
|
{
|
|||
|
|
string fullPath = AppConfig.RunPath + AppConfig.DB.SchemaMapPath + key + ".ts";
|
|||
|
|
IOHelper.Save(fullPath, JsonHelper.ToJson(tables), false, true);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
if (!tableCache.ContainsKey(key) && tables.Count > 0)//<2F><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>档
|
|||
|
|
{
|
|||
|
|
tableCache.Add(key, tables);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
if (tableCache.ContainsKey(key))
|
|||
|
|
{
|
|||
|
|
return tableCache[key];
|
|||
|
|
}
|
|||
|
|
return null;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
|
|||
|
|
#region <EFBFBD><EFBFBD><EFBFBD>ṹ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
// private static CacheManage _SchemaCache = CacheManage.Instance;//Cache<68><65><EFBFBD><EFBFBD>
|
|||
|
|
internal static bool FillTableSchema(ref MDataRow row, ref DbBase dbBase, string tableName, string sourceTableName)
|
|||
|
|
{
|
|||
|
|
if (FillSchemaFromCache(ref row, ref dbBase, tableName, sourceTableName))
|
|||
|
|
{
|
|||
|
|
return true;
|
|||
|
|
}
|
|||
|
|
else//<2F><>Cache<68><65><EFBFBD><EFBFBD>ʧ<EFBFBD><CAA7>
|
|||
|
|
{
|
|||
|
|
return FillSchemaFromDb(ref row, ref dbBase, tableName, sourceTableName);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ܹ<EFBFBD>Key
|
|||
|
|
/// </summary>
|
|||
|
|
internal static string GetSchemaKey(string tableName, string dbName, DalType dalType)
|
|||
|
|
{
|
|||
|
|
string key = tableName;
|
|||
|
|
int start = key.IndexOf('(');
|
|||
|
|
int end = key.LastIndexOf(')');
|
|||
|
|
if (start > -1 && end > -1)//<2F>Զ<EFBFBD><D4B6><EFBFBD>table
|
|||
|
|
{
|
|||
|
|
key = "View" + StaticTool.GetHashKey(key);
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
if (key.IndexOf('.') > 0)
|
|||
|
|
{
|
|||
|
|
dbName = key.Split('.')[0];
|
|||
|
|
}
|
|||
|
|
key = SqlFormat.NotKeyword(key);
|
|||
|
|
}
|
|||
|
|
return "ColumnsCache_" + dalType + "_" + dbName + "_" + key;
|
|||
|
|
}
|
|||
|
|
private static bool FillSchemaFromCache(ref MDataRow row, ref DbBase dbBase, string tableName, string sourceTableName)
|
|||
|
|
{
|
|||
|
|
bool returnResult = false;
|
|||
|
|
|
|||
|
|
string key = GetSchemaKey(tableName, dbBase.DataBase, dbBase.dalType);
|
|||
|
|
if (CacheManage.LocalInstance.Contains(key))//<2F><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ȡ
|
|||
|
|
{
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
row = ((MDataColumn)CacheManage.LocalInstance.Get(key)).ToRow(sourceTableName);
|
|||
|
|
returnResult = row.Count > 0;
|
|||
|
|
}
|
|||
|
|
catch (Exception err)
|
|||
|
|
{
|
|||
|
|
Log.WriteLogToTxt(err);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
else if (!string.IsNullOrEmpty(AppConfig.DB.SchemaMapPath))
|
|||
|
|
{
|
|||
|
|
string fullPath = AppConfig.RunPath + AppConfig.DB.SchemaMapPath + key + ".ts";
|
|||
|
|
if (System.IO.File.Exists(fullPath))
|
|||
|
|
{
|
|||
|
|
MDataColumn mdcs = MDataColumn.CreateFrom(fullPath);
|
|||
|
|
if (mdcs.Count > 0)
|
|||
|
|
{
|
|||
|
|
row = mdcs.ToRow(sourceTableName);
|
|||
|
|
returnResult = row.Count > 0;
|
|||
|
|
CacheManage.LocalInstance.Set(key, mdcs.Clone(), 1440);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return returnResult;
|
|||
|
|
}
|
|||
|
|
private static bool FillSchemaFromDb(ref MDataRow row, ref DbBase dbBase, string tableName, string sourceTableName)
|
|||
|
|
{
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
MDataColumn mdcs = null;
|
|||
|
|
//if (tableName.IndexOf('(') > -1 && tableName.IndexOf(')') > -1)//<2F>Զ<EFBFBD><D4B6><EFBFBD><EFBFBD><EFBFBD>ͼtable
|
|||
|
|
//{
|
|||
|
|
// dbBase.tempSql = "view";//ʹ<><CAB9>access<73><73>ʽ<EFBFBD><CABD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
//}
|
|||
|
|
mdcs = GetColumns(tableName, ref dbBase);
|
|||
|
|
if (dbBase != null && dbBase.Con != null && !dbBase.isOpenTrans && dbBase.Con.State != ConnectionState.Closed)
|
|||
|
|
{
|
|||
|
|
dbBase.Con.Close();//<2F><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>£<EFBFBD><C2A3><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ȹر<C8B9>
|
|||
|
|
}
|
|||
|
|
if (mdcs.Count == 0)
|
|||
|
|
{
|
|||
|
|
return false;
|
|||
|
|
}
|
|||
|
|
row = mdcs.ToRow(sourceTableName);
|
|||
|
|
row.TableName = sourceTableName;
|
|||
|
|
string key = GetSchemaKey(tableName, dbBase.DataBase, dbBase.dalType);
|
|||
|
|
CacheManage.LocalInstance.Set(key, mdcs.Clone(), 1440);
|
|||
|
|
|
|||
|
|
switch (dbBase.dalType)//<2F>ı<EFBFBD><C4B1><EFBFBD><EFBFBD>ݿⲻ<DDBF><E2B2BB><EFBFBD>档
|
|||
|
|
{
|
|||
|
|
//case DalType.Access:
|
|||
|
|
//case DalType.SQLite:
|
|||
|
|
//case DalType.MsSql:
|
|||
|
|
//case DalType.MySql:
|
|||
|
|
//case DalType.Oracle:
|
|||
|
|
|
|||
|
|
case DalType.Txt:
|
|||
|
|
case DalType.Xml:
|
|||
|
|
break;
|
|||
|
|
default:
|
|||
|
|
if (!string.IsNullOrEmpty(AppConfig.DB.SchemaMapPath))
|
|||
|
|
{
|
|||
|
|
string folderPath = AppConfig.RunPath + AppConfig.DB.SchemaMapPath;
|
|||
|
|
if (System.IO.Directory.Exists(folderPath))
|
|||
|
|
{
|
|||
|
|
mdcs.WriteSchema(folderPath + key + ".ts");
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
break;
|
|||
|
|
}
|
|||
|
|
return true;
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
catch (Exception err)
|
|||
|
|
{
|
|||
|
|
Log.WriteLogToTxt(err);
|
|||
|
|
return false;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// <20>Ƿ<EFBFBD><C7B7><EFBFBD><EFBFBD>ڱ<EFBFBD><DAB1><EFBFBD><EFBFBD><EFBFBD>ͼ
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="type">"U"<22><>"V"</param>
|
|||
|
|
/// <param name="name"><3E><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ͼ<EFBFBD><CDBC><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>database.tableName</param>
|
|||
|
|
public static bool Exists(string type, string name, ref DbBase helper)
|
|||
|
|
{
|
|||
|
|
if (type == "U" && tableCache.Count > 0)
|
|||
|
|
{
|
|||
|
|
string key = GetTableCacheKey(helper);
|
|||
|
|
if (tableCache.ContainsKey(key))
|
|||
|
|
{
|
|||
|
|
return tableCache[key].ContainsKey(name);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
int result = 0;
|
|||
|
|
string exist = string.Empty;
|
|||
|
|
helper.IsAllowRecordSql = false;
|
|||
|
|
DalType dalType = helper.dalType;
|
|||
|
|
name = SqlFormat.Keyword(name, helper.dalType);
|
|||
|
|
switch (dalType)
|
|||
|
|
{
|
|||
|
|
case DalType.Access:
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(helper.Con.ConnectionString);
|
|||
|
|
con.Open();
|
|||
|
|
DataTable dt = null;
|
|||
|
|
if (type == "U")
|
|||
|
|
{
|
|||
|
|
dt = con.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, SqlFormat.NotKeyword(name), "Table" });
|
|||
|
|
}
|
|||
|
|
else if (type == "V")
|
|||
|
|
{
|
|||
|
|
dt = con.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Views, new object[] { null, null, SqlFormat.NotKeyword(name) });
|
|||
|
|
}
|
|||
|
|
if (dt != null)
|
|||
|
|
{
|
|||
|
|
result = dt.Rows.Count;
|
|||
|
|
}
|
|||
|
|
con.Close();
|
|||
|
|
}
|
|||
|
|
catch (Exception err)
|
|||
|
|
{
|
|||
|
|
Log.WriteLogToTxt(err);
|
|||
|
|
}
|
|||
|
|
break;
|
|||
|
|
case DalType.MySql:
|
|||
|
|
if (type != "V" || (type == "V" && name.ToLower().StartsWith("v_")))//<2F><>ͼ<EFBFBD><CDBC><EFBFBD><EFBFBD>v_<76><5F>ͷ
|
|||
|
|
{
|
|||
|
|
exist = string.Format(ExistMySql, SqlFormat.NotKeyword(name), helper.DataBase);
|
|||
|
|
}
|
|||
|
|
break;
|
|||
|
|
case DalType.Oracle:
|
|||
|
|
exist = string.Format(ExistOracle, name, (type == "U" ? "TABLE" : "VIEW"));
|
|||
|
|
break;
|
|||
|
|
case DalType.MsSql:
|
|||
|
|
exist = string.Format(helper.Version.StartsWith("08") ? Exist2000 : Exist2005, name, type);
|
|||
|
|
break;
|
|||
|
|
case DalType.SQLite:
|
|||
|
|
exist = string.Format(ExistSqlite, SqlFormat.NotKeyword(name), (type == "U" ? "table" : "view"));
|
|||
|
|
break;
|
|||
|
|
case DalType.Sybase:
|
|||
|
|
exist = string.Format(ExistSybase, SqlFormat.NotKeyword(name), type);
|
|||
|
|
break;
|
|||
|
|
case DalType.PostgreSQL:
|
|||
|
|
exist = string.Format(ExistPostgre, SqlFormat.NotKeyword(name), (type == "U" ? "BASE TABLE" : "VIEW"));
|
|||
|
|
break;
|
|||
|
|
case DalType.Txt:
|
|||
|
|
case DalType.Xml:
|
|||
|
|
string folder = helper.Con.DataSource + Path.GetFileNameWithoutExtension(name);
|
|||
|
|
FileInfo info = new FileInfo(folder + ".ts");
|
|||
|
|
result = (info.Exists && info.Length > 10) ? 1 : 0;
|
|||
|
|
if (result == 0)
|
|||
|
|
{
|
|||
|
|
info = new FileInfo(folder + (dalType == DalType.Txt ? ".txt" : ".xml"));
|
|||
|
|
result = (info.Exists && info.Length > 10) ? 1 : 0;
|
|||
|
|
}
|
|||
|
|
break;
|
|||
|
|
}
|
|||
|
|
if (exist != string.Empty)
|
|||
|
|
{
|
|||
|
|
helper.IsAllowRecordSql = false;
|
|||
|
|
result = Convert.ToInt32(helper.ExeScalar(exist, false));
|
|||
|
|
}
|
|||
|
|
return result > 0;
|
|||
|
|
}
|
|||
|
|
/// <summary>
|
|||
|
|
/// <20><><EFBFBD>ñ<EFBFBD><C3B1><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
/// </summary>
|
|||
|
|
internal static string GetTableDescription(string conn, string tableName)
|
|||
|
|
{
|
|||
|
|
using (DbBase dbBase = DalCreate.CreateDal(conn))
|
|||
|
|
{
|
|||
|
|
string key = GetTableCacheKey(dbBase);
|
|||
|
|
if (tableCache.ContainsKey(key))
|
|||
|
|
{
|
|||
|
|
if (tableCache[key].ContainsKey(tableName))
|
|||
|
|
{
|
|||
|
|
return tableCache[key][tableName];
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
return string.Empty;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
#region ICloneable <EFBFBD><EFBFBD>Ա
|
|||
|
|
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
}
|
|||
|
|
internal partial class TableSchema
|
|||
|
|
{
|
|||
|
|
internal const string Exist2000 = "SELECT count(*) FROM sysobjects where id = OBJECT_ID(N'{0}') AND xtype in (N'{1}')";
|
|||
|
|
internal const string Exist2005 = "SELECT count(*) FROM sys.objects where object_id = OBJECT_ID(N'{0}') AND type in (N'{1}')";
|
|||
|
|
internal const string ExistOracle = "Select count(*) From user_objects where object_name=upper('{0}') and object_type='{1}'";
|
|||
|
|
internal const string ExistMySql = "SELECT count(*) FROM `information_schema`.`COLUMNS` where TABLE_NAME='{0}' and TABLE_SCHEMA='{1}'";
|
|||
|
|
internal const string ExistSybase = "SELECT count(*) FROM sysobjects where id = OBJECT_ID(N'{0}') AND type in (N'{1}')";
|
|||
|
|
internal const string ExistSqlite = "SELECT count(*) FROM sqlite_master where name='{0}' and type='{1}'";
|
|||
|
|
internal const string ExistPostgre = "SELECT count(*) FROM information_schema.tables where table_schema = 'public' and table_name='{0}' and table_type='{1}'";
|
|||
|
|
internal const string ExistOracleSequence = "SELECT count(*) FROM All_Sequences where Sequence_name='{0}'";
|
|||
|
|
internal const string CreateOracleSequence = "create sequence {0} start with {1} increment by 1";
|
|||
|
|
internal const string GetOracleMaxID = "select max({0}) from {1}";
|
|||
|
|
|
|||
|
|
#region <EFBFBD><EFBFBD>ȡ<EFBFBD><EFBFBD><EFBFBD>ݿ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֶ<EFBFBD>
|
|||
|
|
private const string MSSQL_SynonymsName = "SELECT TOP 1 base_object_name from sys.synonyms WHERE NAME = '{0}'";
|
|||
|
|
private const string Oracle_SynonymsName = "SELECT TABLE_NAME FROM USER_SYNONYMS WHERE SYNONYM_NAME='{0}' and rownum=1";
|
|||
|
|
internal static string GetMSSQLColumns(bool for2000, string dbName)
|
|||
|
|
{
|
|||
|
|
// 2005<30><35><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ͬ<EFBFBD><CDAC><EFBFBD><EFBFBD>֧<EFBFBD>֡<EFBFBD> case s2.name WHEN 'timestamp' THEN 'variant' ELSE s2.name END as [SqlType],
|
|||
|
|
return string.Format(@"select s1.name as ColumnName,case s2.name WHEN 'uniqueidentifier' THEN 36
|
|||
|
|
WHEN 'ntext' THEN -1 WHEN 'text' THEN -1 WHEN 'image' THEN -1 else s1.[prec] end as [MaxSize],s1.scale as [Scale],
|
|||
|
|
isnullable as [IsNullable],colstat&1 as [IsAutoIncrement],s2.name as [SqlType],
|
|||
|
|
case when exists(SELECT 1 FROM {0}..sysobjects where xtype='PK' and name in (SELECT name FROM {0}..sysindexes WHERE id=s1.id and
|
|||
|
|
indid in(SELECT indid FROM {0}..sysindexkeys WHERE id=s1.id AND colid=s1.colid))) then 1 else 0 end as [IsPrimaryKey],
|
|||
|
|
case when exists(SELECT 1 FROM {0}..sysobjects where xtype='UQ' and name in (SELECT name FROM {0}..sysindexes WHERE id=s1.id and
|
|||
|
|
indid in(SELECT indid FROM {0}..sysindexkeys WHERE id=s1.id AND colid=s1.colid))) then 1 else 0 end as [IsUniqueKey],
|
|||
|
|
case when s5.rkey=s1.colid or s5.fkey=s1.colid then 1 else 0 end as [IsForeignKey],
|
|||
|
|
case when s5.fkey=s1.colid then object_name(s5.rkeyid) else null end [FKTableName],
|
|||
|
|
isnull(s3.text,'') as [DefaultValue],
|
|||
|
|
s4.value as Description
|
|||
|
|
from {0}..syscolumns s1 right join {0}..systypes s2 on s2.xtype =s1.xtype
|
|||
|
|
left join {0}..syscomments s3 on s1.cdefault=s3.id " +
|
|||
|
|
(for2000 ? "left join {0}..sysproperties s4 on s4.id=s1.id and s4.smallid=s1.colid " : "left join {0}.sys.extended_properties s4 on s4.major_id=s1.id and s4.minor_id=s1.colid")
|
|||
|
|
+ " left join {0}..sysforeignkeys s5 on (s5.rkeyid=s1.id and s5.rkey=s1.colid) or (s5.fkeyid=s1.id and s5.fkey=s1.colid) where s1.id=object_id('{0}..'+@TableName) and s2.name<>'sysname' and s2.usertype<100 order by s1.colid", "[" + dbName + "]");
|
|||
|
|
}
|
|||
|
|
internal static string GetOracleColumns()
|
|||
|
|
{
|
|||
|
|
//ͬ<><CDAC><EFBFBD><EFBFBD><EFBFBD>ѱ<EFBFBD><D1B1><EFBFBD>ȡ<EFBFBD><C8A1><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ִ<EFBFBD>У<EFBFBD><D0A3>ⲿ<EFBFBD>Ա<EFBFBD><D4B1><EFBFBD><EFBFBD><EFBFBD>ת<EFBFBD><D7AA>д<EFBFBD><D0B4><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ż<EFBFBD><C5BB><EFBFBD>
|
|||
|
|
return @"select A.COLUMN_NAME as ColumnName,case DATA_TYPE when 'DATE' then 23 when 'CLOB' then 2147483647 when 'NCLOB' then 1073741823 else case when CHAR_COL_DECL_LENGTH is not null then CHAR_COL_DECL_LENGTH
|
|||
|
|
else case when DATA_PRECISION is not null then DATA_PRECISION else DATA_LENGTH end end end as MaxSize,DATA_SCALE as Scale,
|
|||
|
|
case NULLABLE when 'Y' then 1 else 0 end as IsNullable,
|
|||
|
|
0 as IsAutoIncrement,
|
|||
|
|
case when (DATA_TYPE='NUMBER' and DATA_SCALE>0 and DATA_PRECISION<13) then 'float'
|
|||
|
|
when (DATA_TYPE='NUMBER' and DATA_SCALE>0 and DATA_PRECISION<22) then 'double'
|
|||
|
|
when (DATA_TYPE='NUMBER' and DATA_SCALE=0 and DATA_PRECISION<11) then 'int'
|
|||
|
|
when (DATA_TYPE='NUMBER' and DATA_SCALE=0 and DATA_PRECISION<20) then 'long'
|
|||
|
|
when DATA_TYPE='NUMBER' then'decimal'
|
|||
|
|
else DATA_TYPE end as SqlType,
|
|||
|
|
case when v.CONSTRAINT_TYPE='P' then 1 else 0 end as IsPrimaryKey,
|
|||
|
|
case when v.CONSTRAINT_TYPE='U' then 1 else 0 end as IsUniqueKey,
|
|||
|
|
case when v.CONSTRAINT_TYPE='R' then 1 else 0 end as IsForeignKey,
|
|||
|
|
case when length(r_constraint_name)>1
|
|||
|
|
then (select table_name from user_constraints s where s.constraint_name=v.r_constraint_name)
|
|||
|
|
else null
|
|||
|
|
end as FKTableName ,
|
|||
|
|
data_default as DefaultValue,
|
|||
|
|
COMMENTS AS Description
|
|||
|
|
from USER_TAB_COLS A left join user_col_comments B on A.Table_Name = B.Table_Name and A.Column_Name = B.Column_Name
|
|||
|
|
left join
|
|||
|
|
(select uc1.table_name,ucc.column_name, uc1.constraint_type,uc1.r_constraint_name from user_constraints uc1
|
|||
|
|
left join (SELECT column_name,constraint_name FROM user_cons_columns WHERE TABLE_NAME=:TableName) ucc on ucc.constraint_name=uc1.constraint_name
|
|||
|
|
where uc1.constraint_type in('P','U','R') ) v
|
|||
|
|
on A.TABLE_NAME=v.table_name and A.COLUMN_NAME=v.column_name
|
|||
|
|
where A.TABLE_NAME=:TableName order by COLUMN_ID";
|
|||
|
|
// left join user_constraints uc2 on uc1.r_constraint_name=uc2.constraint_name
|
|||
|
|
// where A.TABLE_NAME= nvl((SELECT TABLE_NAME FROM USER_SYNONYMS WHERE SYNONYM_NAME=UPPER(:TableName) and rownum=1),UPPER(:TableName)) order by COLUMN_ID";
|
|||
|
|
}
|
|||
|
|
internal static string GetMySqlColumns(string dbName)
|
|||
|
|
{
|
|||
|
|
return string.Format(@"SELECT DISTINCT s1.COLUMN_NAME as ColumnName,case DATA_TYPE when 'int' then 10 when 'date' then 10 when 'time' then 8 when 'datetime' then 23 when 'year' then 4
|
|||
|
|
else IFNULL(CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION) end as MaxSize,NUMERIC_SCALE as Scale,
|
|||
|
|
case IS_NULLABLE when 'YES' then 1 else 0 end as IsNullable,
|
|||
|
|
CASE extra when 'auto_increment' then 1 else 0 END AS IsAutoIncrement,
|
|||
|
|
DATA_TYPE as SqlType,
|
|||
|
|
case Column_key WHEN 'PRI' then 1 else 0 end as IsPrimaryKey,
|
|||
|
|
case s3.CONSTRAINT_TYPE when 'UNIQUE' then 1 else 0 end as IsUniqueKey,
|
|||
|
|
case s3.CONSTRAINT_TYPE when 'FOREIGN KEY' then 1 else 0 end as IsForeignKey,
|
|||
|
|
s2.REFERENCED_TABLE_NAME as FKTableName,
|
|||
|
|
COLUMN_DEFAULT AS DefaultValue,
|
|||
|
|
COLUMN_COMMENT AS Description
|
|||
|
|
FROM `information_schema`.`COLUMNS` s1
|
|||
|
|
LEFT JOIN `information_schema`.KEY_COLUMN_USAGE s2 on s2.TABLE_SCHEMA=s1.TABLE_SCHEMA and s2.TABLE_NAME=s1.TABLE_NAME and s2.COLUMN_NAME=s1.COLUMN_NAME
|
|||
|
|
LEFT JOIN `information_schema`.TABLE_CONSTRAINTS s3 on s3.TABLE_SCHEMA=s2.TABLE_SCHEMA and s3.TABLE_NAME=s2.TABLE_NAME and s3.CONSTRAINT_NAME=s2.CONSTRAINT_NAME
|
|||
|
|
where s1.TABLE_SCHEMA='{0}' and s1.TABLE_NAME=?TableName order by s1.ORDINAL_POSITION", dbName);
|
|||
|
|
}
|
|||
|
|
internal static string GetSybaseColumns()
|
|||
|
|
{
|
|||
|
|
return @"select
|
|||
|
|
s1.name as ColumnName,
|
|||
|
|
s1.length as MaxSize,
|
|||
|
|
s1.scale as Scale,
|
|||
|
|
case s1.status&8 when 8 then 1 ELSE 0 END AS IsNullable,
|
|||
|
|
case s1.status&128 when 128 then 1 ELSE 0 END as IsAutoIncrement,
|
|||
|
|
s2.name as SqlType,
|
|||
|
|
case when exists(SELECT 1 FROM sysindexes WHERE id=s1.id AND s1.name=index_col(@TableName,indid,s1.colid)) then 1 else 0 end as IsPrimaryKey,
|
|||
|
|
str_replace(s3.text,'DEFAULT ',null) as DefaultValue,
|
|||
|
|
null as Description
|
|||
|
|
from syscolumns s1 left join systypes s2 on s1.usertype=s2.usertype
|
|||
|
|
left join syscomments s3 on s1.cdefault=s3.id
|
|||
|
|
where s1.id =object_id(@TableName) and s2.usertype<100
|
|||
|
|
order by s1.colid";
|
|||
|
|
}
|
|||
|
|
internal static string GetPostgreColumns()
|
|||
|
|
{
|
|||
|
|
return @"select
|
|||
|
|
a.attname AS ColumnName,
|
|||
|
|
i.data_type AS SqlType,
|
|||
|
|
coalesce(character_maximum_length,numeric_precision,-1) as MaxSize,numeric_scale as Scale,
|
|||
|
|
case a.attnotnull when 'true' then 0 else 1 end AS IsNullable,
|
|||
|
|
case when position('nextval' in column_default)>0 then 1 else 0 end as IsAutoIncrement,
|
|||
|
|
case when o.conname is null then 0 else 1 end as IsPrimaryKey,
|
|||
|
|
d.description AS Description,
|
|||
|
|
i.column_default as DefaultValue
|
|||
|
|
from pg_class c
|
|||
|
|
left join pg_attribute a on c.oid=a.attrelid
|
|||
|
|
left join pg_description d on a.attrelid=d.objoid AND a.attnum = d.objsubid
|
|||
|
|
left join pg_type t on a.atttypid = t.oid
|
|||
|
|
left join information_schema.columns i on i.table_schema='public' and i.table_name=c.relname and i.column_name=a.attname
|
|||
|
|
left join pg_constraint o on a.attnum = o.conkey[1] and o.contype='p' and o.conrelid=c.oid
|
|||
|
|
where c.relname =:TableName
|
|||
|
|
and a.attnum > 0 and a.atttypid>0
|
|||
|
|
ORDER BY a.attnum";
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
#region <EFBFBD><EFBFBD>ȡ<EFBFBD><EFBFBD><EFBFBD>б<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
|
|||
|
|
internal static string GetMSSQLTables(bool for2000)
|
|||
|
|
{
|
|||
|
|
return @"Select o.name as TableName, p.value as Description from sysobjects o " + (for2000 ? "left join sysproperties p on p.id = o.id and smallid = 0" : "left join sys.extended_properties p on p.major_id = o.id and minor_id = 0")
|
|||
|
|
+ " and p.name = 'MS_Description' where o.type = 'U' AND o.name<>'dtproperties' AND o.name<>'sysdiagrams'" + (for2000 ? "" : " and category=0");
|
|||
|
|
}
|
|||
|
|
internal static string GetOracleTables()
|
|||
|
|
{
|
|||
|
|
return "select TABLE_NAME AS TableName,COMMENTS AS Description from user_tab_comments where TABLE_TYPE='TABLE'";
|
|||
|
|
}
|
|||
|
|
internal static string GetMySqlTables(string dbName)
|
|||
|
|
{
|
|||
|
|
return string.Format("select TABLE_NAME as TableName,TABLE_COMMENT as Description from `information_schema`.`TABLES` where TABLE_SCHEMA='{0}'", dbName);
|
|||
|
|
}
|
|||
|
|
internal static string GetPostgreTables(string dbName)
|
|||
|
|
{
|
|||
|
|
return string.Format("select table_name as TableName,cast(obj_description(relfilenode,'pg_class') as varchar) as Description from information_schema.tables t left join pg_class p on t.table_name=p.relname where table_schema='public' and table_type='BASE TABLE' and table_catalog='{0}'", dbName);
|
|||
|
|
}
|
|||
|
|
#endregion
|
|||
|
|
}
|
|||
|
|
}
|