internal void Load(MCellStruct ms) { ColumnName = ms.ColumnName; SqlTypeName = ms.SqlTypeName; SqlType = ms.SqlType; IsAutoIncrement = ms.IsAutoIncrement; IsCanNull = ms.IsCanNull; MaxSize = ms.MaxSize; Scale = ms.Scale; IsPrimaryKey = ms.IsPrimaryKey; IsUniqueKey = ms.IsUniqueKey; IsForeignKey = ms.IsForeignKey; FKTableName = ms.FKTableName; AlterOp = ms.AlterOp; IsJsonIgnore = ms.IsJsonIgnore; if (ms.DefaultValue != null) { DefaultValue = ms.DefaultValue; } if (!string.IsNullOrEmpty(ms.Description)) { Description = ms.Description; } }
/// <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) { //智能判断 if (isContains) // 存在,则修改 { string alterSql = SqlFormat.Keyword(ms.ColumnName, dalType) + " " + DataType.GetDataType(ms, dalType, version); //检测是否相同 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); }