/// <summary> /// 获取表列架构(链接错误时,抛异常) /// </summary> /// <param name="tableName">表名</param> /// <param name="conn">数据库链接</param> /// <param name="errInfo">出错时的错误信息</param> /// <returns></returns> public static MDataColumn GetColumns(string tableName, string conn, out string errInfo) { errInfo = string.Empty; DbBase helper = DalCreate.CreateDal(conn); helper.ChangeDatabaseWithCheck(tableName);//检测dbname.dbo.tablename的情况 if (!helper.TestConn()) { errInfo = helper.debugInfo.ToString(); if (string.IsNullOrEmpty(errInfo)) { errInfo = "Open database fail : " + tableName; } helper.Dispose(); Error.Throw(errInfo); return(null); } if (!tableName.Contains(" ")) { tableName = GetMapTableName(conn, tableName); } MDataColumn mdc = TableSchema.GetColumns(tableName, ref helper); helper.Dispose(); return(mdc); }
/// <summary> /// 获取表列架构(链接错误时,抛异常) /// </summary> /// <param name="tableName">表名</param> /// <param name="conn">数据库链接</param> /// <param name="errInfo">出错时的错误信息</param> /// <returns></returns> public static MDataColumn GetColumns(string tableName, string conn, out string errInfo) { errInfo = string.Empty; DbBase helper = DalCreate.CreateDal(conn); DbResetResult result = helper.ChangeDatabaseWithCheck(tableName);//检测dbname.dbo.tablename的情况 switch (result) { case DbResetResult.No_DBNoExists: helper.Dispose(); return(null); case DbResetResult.No_SaveDbName: case DbResetResult.Yes: tableName = SqlFormat.NotKeyword(tableName); //same database no need database.tablename break; } if (!helper.TestConn(AllowConnLevel.MaterBackupSlave)) { errInfo = helper.debugInfo.ToString(); if (string.IsNullOrEmpty(errInfo)) { errInfo = "Open database fail : " + tableName; } helper.Dispose(); Error.Throw(errInfo); return(null); } if (!tableName.Contains(" "))// { tableName = GetMapTableName(conn, tableName); } MDataColumn mdc = TableSchema.GetColumns(tableName, ref helper); helper.Dispose(); return(mdc); }
/// <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); }