//删除列
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());
}
