//删除列 public static bool DeleteColumn(string dbname, string tableName, string colName, out string msg) { Database db = DbFunction.GetDB(dbname); string sql = string.Format("alter table {0} drop column {1} ", tableName, colName); msg = string.Empty; //检查是否存在列信息 if (!CheckColumnExist(dbname, tableName, colName)) { msg = "列" + colName + "不存在"; return(false); } try { DbFunction.ExecuteNonQuery(dbname, sql); return(true); } catch (Exception ex) { msg = ex.Message; } return(false); }
//检查表中是否存在指定的列 public static bool CheckColumnExist(string dbname, string tableName, string colName) { Database db = DbFunction.GetDB(dbname); string sql = string.Empty; if (db.DBType == DataBaseType.ORA) { sql = "SELECT COUNT(1) as EXISTFLAG FROM USER_TAB_COLS WHERE COLUMN_NAME=UPPER('" + colName + "') AND TABLE_NAME=UPPER('" + tableName + "')"; } else { sql = "SELECT COUNT(1) from syscolumns c,sysobjects o where o.id=c.id and Upper(o.name) = Upper('" + tableName + "') and o.type = 'U' and Upper(c.name) = Upper('" + colName + "')"; } int count = Convert.ToInt32(DbFunction.ExecuteScalar(dbname, sql)); if (count == 0) { return(false); } else { return(true); } }
//判断表在数据库中是否存在 public static bool CheckTableExist(string dbname, string tableName) { Database db = DbFunction.GetDB(dbname); string sql = string.Empty; if (db.DBType == DataBaseType.ORA) { sql = "SELECT COUNT(1) FROM USER_TABLES WHERE TABLE_NAME = UPPER('" + tableName + "')"; } else { sql = "SELECT COUNT(1) from sysobjects where Upper(name) = Upper('" + tableName + "') and type = 'U'"; } int count = Convert.ToInt32(DbFunction.ExecuteScalar(dbname, sql)); if (count == 0) { return(false); } else { return(true); } }
//修改表中的字段 public static bool ModifyColumn(string dbname, string tableName, string colName, string colType, string colLenght, string defaultValue, string nullable, out string msg) { Database db = DbFunction.GetDB(dbname); msg = string.Empty; //检查是否存在列信息 if (!CheckColumnExist(dbname, tableName, colName)) { msg = "列" + colName + "不存在"; return(false); } var isOraDb = db.DBType == DataBaseType.ORA; string sql = string.Format("alter table {0} alter column {1} ", tableName, colName); if (isOraDb) { sql = string.Format("alter table {0} modify ( {1} ", tableName, colName); } sql += getColTypeStr(db, colType, colLenght); //默认值 if (!string.IsNullOrEmpty(defaultValue)) { sql += string.Format(" default {0}", defaultValue); } //是否为空 if (nullable == "0") { sql += " not null"; } else { sql += " null"; } if (isOraDb) { sql += ")"; } try { DbFunction.ExecuteNonQuery(dbname, sql); return(true); } catch (Exception ex) { msg = ex.Message; } return(false); }
//根据GSYCOL生成建表字符串 public static string GenerateCol(DataTable dt, string dbname, out string key) { Database db = DbFunction.GetDB(dbname); var isOraDb = db.DBType == DataBaseType.ORA; //是否oracle数据库 StringBuilder sql = new StringBuilder(); key = string.Empty; foreach (DataRow dr in dt.Rows) { sql.AppendFormat("{0} ", dr["GSYCOL_FIELDID"]); //字段类型判断 var colType = dr["GSYCOL_TYPE"].ToString(); string vstype = db.Provider.getDataType(colType); switch (colType.ToLower()) { case "char": case "varchar": sql.AppendFormat("{0}({1})", vstype, dr["GSYCOL_LEN"]); break; case "int": case "datetime": case "blob": case "clob": sql.AppendFormat("{0}", vstype); break; case "decimal": sql.AppendFormat("{0}({1},{2})", vstype, dr["GSYCOL_LEN"], dr["GSYCOL_PREC"]); break; default: break; } //默认值 if (!string.IsNullOrEmpty(Convert.ToString(dr["GSYCOL_DEF"]))) { sql.AppendFormat(" default {0}", dr["GSYCOL_DEF"]); } //是否为空 if (dr["GSYCOL_NULLABLE"].ToString() == "0") { sql.Append(" not null,"); } else { sql.Append(" null,"); } //是否主键 if (dr["GSYCOL_ISKEY"].ToString() == "1") { key = string.Format("{0},{1}", key, dr["GSYCOL_FIELDID"]); } } if (key.Length > 0) { key = key.Substring(1); } return(sql.ToString()); }