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))//缓存里获取
{
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 = AppDomain.CurrentDomain.BaseDirectory + 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.Add(key, mdcs.Clone(), null, 1440);
}
}
}
return(returnResult);
}
public static MDataColumn GetColumns(string tableName, string conn)
{
string key = GetSchemaKey(tableName, conn);
#region 缓存检测
if (_ColumnCache.ContainsKey(key))
{
return(_ColumnCache[key].Clone());
}
if (!string.IsNullOrEmpty(AppConfig.DB.SchemaMapPath))
{
string fullPath = AppConfig.RunPath + AppConfig.DB.SchemaMapPath + key + ".ts";
if (System.IO.File.Exists(fullPath))
{
MDataColumn columns = MDataColumn.CreateFrom(fullPath);
if (columns.Count > 0)
{
CacheManage.LocalInstance.Set(key, columns.Clone(), 1440);
return(columns);
}
}
}
#endregion
string fixName;
conn = CrossDB.GetConn(tableName, out fixName, conn ?? AppConfig.DB.DefaultConn);
tableName = fixName;
if (conn == null)
{
return(null);
}
MDataColumn mdcs = null;
using (DalBase dbHelper = DalCreate.CreateDal(conn))
{
DataBaseType dalType = dbHelper.DataBaseType;
if (dalType == DataBaseType.Txt || dalType == DataBaseType.Xml)
{
#region 文本数据库处理。
if (!tableName.Contains(" ")) // || tableName.IndexOfAny(Path.GetInvalidPathChars()) == -1
{
tableName = SqlFormat.NotKeyword(tableName); //处理database..tableName;
tableName = Path.GetFileNameWithoutExtension(tableName); //视图表，带“.“的，会出问题
string fileName = dbHelper.Con.DataSource + tableName + (dalType == DataBaseType.Txt ? ".txt" : ".xml");
mdcs = MDataColumn.CreateFrom(fileName);
mdcs.DataBaseType = dalType;
mdcs.Conn = conn;
}
#endregion
}
else
{
#region 其它数据库
mdcs = new MDataColumn();
mdcs.Conn = conn;
mdcs.TableName = tableName;
mdcs.DataBaseType = dalType;
tableName = SqlFormat.Keyword(tableName, dbHelper.DataBaseType);//加上关键字：引号
//如果table和helper不在同一个库
DalBase helper = dbHelper.ResetDalBase(tableName);
helper.IsRecordDebugInfo = false || AppDebug.IsContainSysSql;//内部系统，不记录SQL表结构语句。
try
{
bool isView = tableName.Contains(" ");//是否视图。
if (!isView)
{
isView = CrossDB.Exists(tableName, "V", conn);
}
if (!isView)
{
TableInfo info = CrossDB.GetTableInfoByName(mdcs.TableName, conn);
if (info != null)
{
mdcs.Description = info.Description;
}
}
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 DataBaseType.MsSql:
case DataBaseType.Oracle:
case DataBaseType.MySql:
case DataBaseType.Sybase:
case DataBaseType.PostgreSQL:
#region Sql
string sql = string.Empty;
if (dalType == DataBaseType.MsSql)
{
#region Mssql
string dbName = null;
if (!helper.Version.StartsWith("08"))
{
//先获取同义词，检测是否跨库
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) //跨库了
{
dbName = realTableName.Split('.')[0];
}
}
}
sql = GetMSSQLColumns(helper.Version.StartsWith("08"), dbName ?? helper.DataBaseName);
#endregion
}
else if (dalType == DataBaseType.MySql)
{
sql = GetMySqlColumns(helper.DataBaseName);
}
else if (dalType == DataBaseType.Oracle)
{
tableName = tableName.ToUpper(); //Oracle转大写。
//先获取同义词，不检测是否跨库
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 == DataBaseType.Sybase)
{
tableName = SqlFormat.NotKeyword(tableName);
sql = GetSybaseColumns();
}
else if (dalType == DataBaseType.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的长度可能大于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.DataBaseType);
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 DataBaseType.MsSql:
case DataBaseType.MySql:
case DataBaseType.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 == DataBaseType.Oracle && mdcs.Count > 0) //默认没有自增概念，只能根据情况判断。
{
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 DataBaseType.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的长度可能大于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 DataBaseType.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;
}
}
}
catch (Exception err)
{
Log.Write(err, LogType.DataBase);
//helper.DebugInfo.Append(err.Message);
}
#endregion
}
}
if (mdcs.Count > 0)
{
//移除被标志的列：
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);
}
}
}
#region 缓存设置
if (!_ColumnCache.ContainsKey(key) && mdcs.Count > 0)
{
_ColumnCache.Add(key, mdcs.Clone());
if (!string.IsNullOrEmpty(AppConfig.DB.SchemaMapPath))
{
string folderPath = AppConfig.RunPath + AppConfig.DB.SchemaMapPath;
if (!System.IO.Directory.Exists(folderPath))
{
System.IO.Directory.CreateDirectory(folderPath);
}
mdcs.WriteSchema(folderPath + key + ".ts");
}
}
#endregion
return(mdcs);
}
public override DataTable GetSchema(string collectionName, string[] restrictionValues)
{
DataTable dt = new DataTable(collectionName);
dt.Columns.Add("TABLE_NAME");
switch (collectionName.ToLower())
{
case "tables":
string[] tsList = Directory.GetFiles(DataSource, "*.ts", SearchOption.TopDirectoryOnly);
if (tsList != null && tsList.Length > 0)
{
string dalType = ".txt";
if (restrictionValues != null && restrictionValues.Length > 0)
{
if (restrictionValues[0] != null && Convert.ToString(restrictionValues[0]).ToLower() == "xml")
{
dalType = ".xml";
}
}
DataRow row = null;
string tName = string.Empty;
foreach (string tsName in tsList)
{
tName = Path.GetFileNameWithoutExtension(tsName); //获得表名。
string[] tableList = Directory.GetFiles(DataSource, tName + ".*", SearchOption.TopDirectoryOnly);
foreach (string tableName in tableList)
{
if (tableName.EndsWith(dalType))
{
continue;
}
row = dt.NewRow();
row[0] = tableName.EndsWith(".ts") ? Path.GetFileNameWithoutExtension(tableName) : Path.GetFileName(tableName);
dt.Rows.Add(row);
}
}
}
break;
case "columns":
dt.Columns.Add("COLUMN_NAME");
dt.Columns.Add("COLUMN_LCID", typeof(int));
dt.Columns.Add("DATA_TYPE", typeof(int));
dt.Columns.Add("TABLE_MaxSize", typeof(int));
dt.Columns.Add("COLUMN_ISREADONLY", typeof(bool));
dt.Columns.Add("TABLE_ISCANNULL", typeof(bool));
dt.Columns.Add("COLUMN_DEFAULT");
tsList = Directory.GetFiles(DataSource, "*.ts");
if (tsList != null && tsList.Length > 0)
{
DataRow row = null;
string tName = string.Empty;
foreach (string tsName in tsList)
{
MDataColumn mdc = MDataColumn.CreateFrom(tsName);
if (mdc.Count > 0)
{
tName = Path.GetFileNameWithoutExtension(tsName);
MCellStruct cs = null;
for (int i = 0; i < mdc.Count; i++)
{
cs = mdc[i];
row = dt.NewRow();
row[0] = tName;
row[1] = cs.ColumnName;
row[2] = i;
row[3] = (int)cs.SqlType;
row[4] = cs.MaxSize;
row[5] = cs.IsAutoIncrement;
row[6] = cs.IsCanNull;
row[7] = Convert.ToString(cs.DefaultValue);
dt.Rows.Add(row);
}
}
}
}
break;
}
return(dt);
}