//修改表中的字段
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);
}
//强制删除并创建表
public static bool CreateTableForced(string dbname, string tableName, string Columns, string Key, out string msg)
{
msg = string.Empty;
string vsDroptable = string.Empty;
//如果已经存在就返回
if (CheckTableExist(dbname, tableName))
{
vsDroptable = string.Format("drop table {0}", tableName);
return(false);
}
var sql = string.Empty;
if (Key.Length > 0)
{
sql = string.Format("create table {0}({1} CONSTRAINT PK_{0} PRIMARY KEY({2}))", tableName, Columns, Key);
}
else
{
sql = string.Format("create table {0}({1})", tableName, Columns.Remove(Columns.Length - 1));
}
try
{
if (vsDroptable != string.Empty)
{
DbFunction.ExecuteNonQuery(dbname, vsDroptable);
}
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());
}
