/// <summary>
/// 获取指定的表架构生成的SQL(Alter Table)语句
/// </summary>
public static List <string> AlterTableSql(string tableName, MDataColumn columns, string conn)
{
List <string> sql = new List <string>();
DbBase helper = DalCreate.CreateDal(conn);
helper.ChangeDatabaseWithCheck(tableName);//检测dbname.dbo.tablename的情况
if (!helper.TestConn())
{
helper.Dispose();
return(sql);
}
DalType dalType = helper.dalType;
string version = helper.Version;
MDataColumn dbColumn = TableSchema.GetColumns(tableName, ref helper);//获取数据库的列结构
helper.Dispose();
//开始比较异同
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);
if (ms.AlterOp == AlterOp.Drop)
{
#region MyRegion
if (isContains)
{
switch (dalType)
{
case DalType.MsSql:
case DalType.Access:
case DalType.MySql:
case DalType.Oracle:
if (dalType == DalType.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 Base_Button drop constraint '+ @name) end");
}
sql.Add(alterTable + " drop column " + cName);
break;
case DalType.Sybase:
sql.Add(alterTable + " drop " + cName);
break;
}
}
#endregion
}
else if (ms.AlterOp == AlterOp.Rename)
{
#region MyRegion
if (!string.IsNullOrEmpty(ms.OldName) && ms.OldName != ms.ColumnName && !isContains)
{
string oName = SqlFormat.Keyword(ms.OldName, dalType);
switch (dalType)
{
case DalType.MsSql:
sql.Add("exec sp_rename '" + tbName + "." + oName + "', '" + ms.ColumnName + "', 'column'");
break;
case DalType.Sybase:
sql.Add("exec sp_rename \"" + tableName + "." + ms.OldName + "\", " + ms.ColumnName);
break;
case DalType.MySql:
sql.Add(alterTable + " change " + oName + " " + GetKey(ms, dalType, ref primaryKeyList, version).TrimEnd(','));
break;
case DalType.Oracle:
sql.Add(alterTable + " rename column " + oName + " to " + cName);
break;
}
}
#endregion
}
else if (ms.AlterOp == AlterOp.AddOrModify)
{
string alterSql = SqlFormat.Keyword(ms.ColumnName, dalType) + " " + DataType.GetDataType(ms, dalType, version);
//智能判断
if (isContains) // 存在，则修改
{
//检测是否相同
MCellStruct dbStruct = dbColumn[ms.ColumnName];
if (dbStruct.IsCanNull != ms.IsCanNull || dbStruct.SqlType != ms.SqlType || dbStruct.MaxSize != ms.MaxSize || dbStruct.Scale != ms.Scale)
{
string modify = "";
switch (dalType)
{
case DalType.Oracle:
case DalType.Sybase:
modify = " modify ";
break;
case DalType.MySql:
modify = " change " + cName + " ";
break;
case DalType.MsSql:
case DalType.Access:
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);
}
private static string GetKey(MCellStruct column, DalType dalType, ref List <MCellStruct> primaryKeyList, string version)
{
string key = SqlFormat.Keyword(column.ColumnName, dalType); //列名。
int groupID = DataType.GetGroup(column.SqlType); //数据库类型。
bool isAutoOrPKey = column.IsPrimaryKey || column.IsAutoIncrement; //是否主键或自增列。
if (dalType != DalType.Access || !isAutoOrPKey || !column.IsAutoIncrement)
{
SqlDbType sdt = column.SqlType;
if (sdt == SqlDbType.DateTime && dalType == DalType.MySql && Convert.ToString(column.DefaultValue) == SqlValue.GetDate)
{
sdt = SqlDbType.Timestamp;
}
key += " " + DataType.GetDataType(column, dalType, version);
}
if (isAutoOrPKey)
{
if (column.IsAutoIncrement)
{
if (primaryKeyList.Count == 0 || (!column.IsPrimaryKey && dalType == DalType.MySql))//MySql 的自增必须是主键.
{
column.IsPrimaryKey = true;
primaryKeyList.Insert(0, column);
}
}
switch (dalType)
{
case DalType.Access:
if (column.IsAutoIncrement)
{
key += " autoincrement(1,1)";
}
else // 主键。
{
if (groupID == 4) //主键又是GUID
{
key += " default GenGUID()";
}
}
break;
case DalType.MsSql:
if (column.IsAutoIncrement)
{
key += " IDENTITY(1,1)";
}
else
{
if (groupID == 4) //主键又是GUID
{
key += " Default (newid())";
}
}
break;
case DalType.Oracle:
if (Convert.ToString(column.DefaultValue) == SqlValue.GUID) //主键又是GUID
{
key += " Default (SYS_GUID())";
}
break;
case DalType.Sybase:
if (column.IsAutoIncrement)
{
key += " IDENTITY";
}
else
{
if (groupID == 4) //主键又是GUID
{
key += " Default (newid())";
}
}
break;
case DalType.MySql:
if (column.IsAutoIncrement)
{
key += " AUTO_INCREMENT";
if (!column.IsPrimaryKey)
{
primaryKeyList.Add(column);
}
}
break;
case DalType.SQLite: //sqlite的AUTOINCREMENT不能写在primarykey前,
if (column.IsAutoIncrement)
{
key += " PRIMARY KEY AUTOINCREMENT";
primaryKeyList.Clear(); //如果有自增加，只允许存在这一个主键。
}
break;
}
key += " NOT NULL";
}
else
{
string defaultValue = string.Empty;
if (Convert.ToString(column.DefaultValue).Length > 0 && groupID < 5)//默认值只能是基础类型有。
{
if (dalType == DalType.MySql)
{
if ((groupID == 0 && (column.MaxSize < 1 || column.MaxSize > 8000)) || (groupID == 2 && key.Contains("datetime"))) //只能对TIMESTAMP类型的赋默认值。
{
goto er;
}
}
defaultValue = SqlFormat.FormatDefaultValue(dalType, column.DefaultValue, 1, column.SqlType);
if (!string.IsNullOrEmpty(defaultValue))
{
if (dalType == DalType.MySql)
{
defaultValue = defaultValue.Trim('(', ')');
}
key += " Default " + defaultValue;
}
}
er:
if (dalType != DalType.Access)
{
if (dalType == DalType.Sybase && column.SqlType == SqlDbType.Bit)
{
if (string.IsNullOrEmpty(defaultValue))
{
key += " Default 0";
}
key += " NOT NULL";//Sybase bit 不允许为Null
}
else
{
key += column.IsCanNull ? " NULL" : " NOT NULL";
}
}
}
if (!string.IsNullOrEmpty(column.Description))
{
switch (dalType)
{
case DalType.MySql:
key += string.Format(" COMMENT '{0}'", column.Description.Replace("'", "''"));
break;
}
}
return(key + ",");
}
