public static MDataColumn GetColumns(string tableName, string conn) { string key = GetSchemaKey(tableName, conn); if (CacheManage.LocalInstance.Contains(key))//缓存里获取 { return(CacheManage.LocalInstance.Get <MDataColumn>(key).Clone()); } if (string.IsNullOrEmpty(conn)) { string fixName; conn = CrossDB.GetConn(tableName, out fixName); tableName = fixName; } else { tableName = Convert.ToString(SqlCreate.SqlToViewSql(tableName)); } MDataColumn mdcs = null; using (DalBase dbHelper = DalCreate.CreateDal(conn)) { DalType dalType = dbHelper.DataBaseType; #region 文本数据库处理。 if (dalType == DalType.Txt || dalType == DalType.Xml) { 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 == DalType.Txt ? ".txt" : ".xml"); mdcs = MDataColumn.CreateFrom(fileName); mdcs.dalType = dalType; return(mdcs); } return(GetTxtDBViewColumns(tableName));//处理视图 } #endregion mdcs = new MDataColumn(); mdcs.TableName = tableName; mdcs.dalType = dalType; tableName = SqlFormat.Keyword(tableName, dbHelper.DataBaseType);//加上关键字:引号 //如果table和helper不在同一个库 DalBase helper = dbHelper.ResetDalBase(tableName); helper.IsRecordDebugInfo = false;//内部系统,不记录SQL表结构语句。 try { bool isView = tableName.Contains(" ");//是否视图。 if (!isView) { isView = TableSchema.Exists(tableName, "V", conn); } if (!isView) { mdcs.Description = TableSchema.GetTableDescription(conn, mdcs.TableName); } 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")) { //先获取同义词,检测是否跨库 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.DataBase); #endregion } else if (dalType == DalType.MySql) { sql = GetMySqlColumns(helper.DataBase); } else if (dalType == DalType.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 == 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的长度可能大于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) //默认没有自增概念,只能根据情况判断。 { 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的长度可能大于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; } } } catch (Exception err) { helper.DebugInfo.Append(err.Message); } } 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); } } } if (!CacheManage.LocalInstance.Contains(key) && mdcs.Count > 0)//拿不到表结构时不缓存。 { CacheManage.LocalInstance.Set(key, mdcs.Clone()); } return(mdcs); }
/// <summary> /// 获取指定的表架构生成的SQL(Alter Table)语句 /// </summary> public static List <string> AlterTableSql(string tableName, MDataColumn columns, string conn) { List <string> sql = new List <string>(); string version = null; DataBaseType dalType; using (DalBase helper = DalCreate.CreateDal(conn)) { helper.ChangeDatabaseWithCheck(tableName);//检测dbname.dbo.tablename的情况 if (!helper.TestConn(AllowConnLevel.Master)) { helper.Dispose(); return(sql); } dalType = helper.DataBaseType; version = helper.Version; } MDataColumn dbColumn = TableSchema.GetColumns(tableName, conn);//获取数据库的列结构 if (dbColumn == null || dbColumn.Count == 0) { return(sql); } //开始比较异同 List <MCellStruct> primaryKeyList = new List <MCellStruct>(); string tbName = SqlFormat.Keyword(tableName, dalType); string alterTable = "alter table " + tbName; foreach (MCellStruct ms in columns)//遍历新的结构 { string cName = SqlFormat.Keyword(ms.ColumnName, dalType); if (ms.AlterOp != AlterOp.None) { bool isContains = dbColumn.Contains(ms.ColumnName); AlterOp op = ms.AlterOp; if ((op & AlterOp.Rename) != 0) { op = (AlterOp)(op - AlterOp.Rename); #region MyRegion Rename if (!string.IsNullOrEmpty(ms.OldName) && ms.OldName != ms.ColumnName && !isContains) { string oName = SqlFormat.Keyword(ms.OldName, dalType); switch (dalType) { case DataBaseType.MsSql: sql.Add("exec sp_rename '" + tbName + "." + oName + "', '" + ms.ColumnName + "', 'column'"); break; case DataBaseType.Sybase: sql.Add("exec sp_rename \"" + tableName + "." + ms.OldName + "\", " + ms.ColumnName); break; case DataBaseType.MySql: sql.Add(alterTable + " change " + oName + " " + GetKey(ms, dalType, ref primaryKeyList, version).TrimEnd(',')); break; case DataBaseType.Oracle: sql.Add(alterTable + " rename column " + oName + " to " + cName); break; } isContains = isContains || dbColumn.Contains(ms.OldName); } #endregion } if (op == AlterOp.Drop) { #region MyRegion if (isContains) { switch (dalType) { case DataBaseType.MsSql: case DataBaseType.Access: case DataBaseType.MySql: case DataBaseType.Oracle: if (dalType == DataBaseType.MsSql) { sql.Add(@"declare @name varchar(50) select @name =b.name from sysobjects b join syscolumns a on b.id = a.cdefault where a.id = object_id('" + tableName + "') and a.name ='" + ms.ColumnName + "'if(@name!='') begin EXEC('alter table " + tableName + " drop constraint '+ @name) end"); } sql.Add(alterTable + " drop column " + cName); break; case DataBaseType.Sybase: sql.Add(alterTable + " drop " + cName); break; } } #endregion } //else if (ms.AlterOp == AlterOp.Rename) //{ //} else if (op == AlterOp.AddOrModify) { string alterSql = SqlFormat.Keyword(ms.ColumnName, dalType) + " " + DataType.GetDataType(ms, dalType, version); //智能判断 if (isContains) // 存在,则修改 { //检测是否相同 MCellStruct dbStruct = dbColumn[ms.ColumnName] ?? dbColumn[ms.OldName]; if (dbStruct.IsCanNull != ms.IsCanNull || dbStruct.SqlType != ms.SqlType || dbStruct.MaxSize != ms.MaxSize || dbStruct.Scale != ms.Scale) { string modify = ""; switch (dalType) { case DataBaseType.Oracle: case DataBaseType.Sybase: modify = " modify "; break; case DataBaseType.MySql: modify = " change " + cName + " "; break; case DataBaseType.MsSql: case DataBaseType.Access: case DataBaseType.PostgreSQL: modify = " alter column "; break; } if (ms.IsCanNull != dbStruct.IsCanNull) { alterSql += (ms.IsCanNull ? " NULL" : " NOT NULL"); } sql.Add(alterTable + modify + alterSql); } } else //不在,则添加 { sql.Add(alterTable + " add " + GetKey(ms, dalType, ref primaryKeyList, version).TrimEnd(',')); if (!string.IsNullOrEmpty(ms.Description)) { string description = SqlCreateForSchema.GetTableDescriptionSql(tableName, ms, dalType, true); if (!string.IsNullOrEmpty(description)) { sql.Add(description); } } } } } } return(sql); }