/// <summary> /// 获得链接的配置或语句。 /// </summary> /// <param name="nameOrSql">表名、视图名、存储过程名</param> /// <returns></returns> public static string GetConn(string nameOrSql, out string fixName, string priorityConn) { string firstTableName = null; string conn = null; nameOrSql = nameOrSql.Trim(); fixName = nameOrSql; if (nameOrSql.IndexOf(' ') == -1)//单表。 { #region 单表 if (nameOrSql.IndexOf('.') > -1) //dbname.tablename { string[] items = nameOrSql.Split('.'); conn = items[0] + "Conn"; fixName = items[items.Length - 1]; } else { firstTableName = nameOrSql; } #endregion } else { if (nameOrSql[0] == '(') // 视图 { int index = nameOrSql.LastIndexOf(')'); string viewSQL = nameOrSql; string startSql = viewSQL.Substring(0, index + 1); //a部分 viewSQL = viewSQL.Substring(index + 1).Trim(); //b部分。ddd.v_xxx if (viewSQL.Contains(".") && !viewSQL.Contains(" ")) //修改原对像 { string[] items = viewSQL.Split('.'); fixName = startSql + " " + items[items.Length - 1]; conn = items[0] + "Conn"; } else { firstTableName = GetFirstTableNameFromSql(startSql); } } else { //sql 语句 firstTableName = GetFirstTableNameFromSql(nameOrSql); fixName = SqlCreate.SqlToViewSql(nameOrSql);//Sql修正为视图 } } if (!string.IsNullOrEmpty(firstTableName)) { TableInfo info = GetTableInfoByName(firstTableName, priorityConn); if (info != null && info.DBInfo != null) { if (nameOrSql == firstTableName) { fixName = info.Name; } conn = info.DBInfo.ConnName; } } return(string.IsNullOrEmpty(conn) ? priorityConn : conn); }
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.DataBaseVersion = dbHelper.Version; mdcs.Conn = conn; } #endregion } else { #region 其它数据库 mdcs = new MDataColumn(); mdcs.Conn = conn; mdcs.TableName = tableName; mdcs.DataBaseType = dalType; mdcs.DataBaseVersion = dbHelper.Version; 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: case DataBaseType.DB2: #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(float.Parse(dbHelper.Version)); } else if (dalType == DataBaseType.DB2) { tableName = SqlFormat.NotKeyword(tableName).ToUpper(); sql = GetDB2Columns(); } 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); }