private int GetTableMaxValue(string tableName, string fieldName, int defaultValue, string sqlWhere, BizDataContext context)
{
int result = 0;
string sql = string.Format("SELECT MAX({0}) maxValue FROM {1} WHERE 1=1 ", fieldName, tableName);
if (!string.IsNullOrEmpty(sqlWhere))
{
sql = sql + sqlWhere;
}
object obj2 = context.ExecuteScalar(sql, new DbParameter[0]);
if (((obj2 != null) && (obj2 != DBNull.Value)) && int.TryParse(obj2.ToString(), out result))
{
return(result);
}
return(defaultValue);
}
private void InternalPublish_ForOracle()
{
string sqlUser = "******"; //当前数据库登录用户
string user = Convert.ToString(_db.ExecuteScalar(sqlUser));
var existTableDict = _db.ExecuteDataTable(string.Format("Select TABLE_NAME,COLUMN_NAME from DBA_TAB_COLUMNS where OWNER = '{0}'", user)).Rows.Cast <DataRow>()
.Select(p => new
{
TableName = Convert.ToString(p["TABLE_NAME"]).ToLower(),
ColumnName = Convert.ToString(p["COLUMN_NAME"]).ToLower(),
}).GroupBy(p => p.TableName).ToDictionary(p => p.Key, p => p.Select(x => x.ColumnName).ToList());
if (existTableDict.ContainsKey(_entity.EntityName.ToLower()))//表已经存在，添加字段
{
var existColumnList = existTableDict[_entity.EntityName.ToLower()];
foreach (SysField _field in _fieldList)
{
if (existColumnList.IndexOf(_field.FieldName.ToLower()) < 0) //字段不存在
{
switch (_field.DataType)
{
case (int)FormFieldDataType.Int: _db.ExecuteNonQuery(string.Format(" ALTER TABLE {0} ADD {1} number(10) ", _entity.EntityName, _field.FieldName)); break;
case (int)FormFieldDataType.DateTime: _db.ExecuteNonQuery(string.Format(" ALTER TABLE {0} ADD {1} timestamp(6) ", _entity.EntityName, _field.FieldName)); break;
case (int)FormFieldDataType.File: _db.ExecuteNonQuery(string.Format(" ALTER TABLE {0} ADD {1} number(10) ", _entity.EntityName, _field.FieldName)); break;
case (int)FormFieldDataType.Number: _db.ExecuteNonQuery(string.Format(" ALTER TABLE {0} ADD {1} number(38,10) ", _entity.EntityName, _field.FieldName)); break;
case (int)FormFieldDataType.String: _db.ExecuteNonQuery(string.Format(" ALTER TABLE {0} ADD {1} nvarchar2(256) ", _entity.EntityName, _field.FieldName)); break;
case (int)FormFieldDataType.Text: _db.ExecuteNonQuery(string.Format(" ALTER TABLE {0} ADD {1} nclob ", _entity.EntityName, _field.FieldName)); break;
default: throw new Exception(string.Format("表单字段类型不支持{0}", _field.DataType));
}
}
}
}
else //表不存在
{
StringBuilder sbSQL = new StringBuilder();
sbSQL.AppendFormat("CREATE TABLE {0} ( ", _entity.EntityName);
string keyName = null;
foreach (SysField _field in _fieldList)
{
switch (_field.DataType)
{
case (int)DataTypeEnum.pkey: keyName = _field.FieldName; sbSQL.AppendFormat(" {0} number(10) NOT NULL, ", _field.FieldName); break;
case (int)FormFieldDataType.Int: sbSQL.AppendFormat(" {0} number(10) NULL, ", _field.FieldName); break;
case (int)FormFieldDataType.DateTime: sbSQL.AppendFormat(" {0} timestamp(6) NULL, ", _field.FieldName); break;
case (int)FormFieldDataType.File: sbSQL.AppendFormat(" {0} number(10) NULL, ", _field.FieldName); break;
case (int)FormFieldDataType.Number: sbSQL.AppendFormat(" {0} number(38, 10) NULL, ", _field.FieldName); break;
case (int)FormFieldDataType.String: sbSQL.AppendFormat(" {0} nvarchar2(256) NULL, ", _field.FieldName); break;
case (int)FormFieldDataType.Text: sbSQL.AppendFormat(" {0} nclob NULL, ", _field.FieldName); break;
default: throw new Exception(string.Format("表单字段类型不支持{0}", _field.DataType));
}
}
sbSQL.AppendFormat(" constraint PK_{0} primary key ({1}))", _entity.EntityName, keyName);
_db.ExecuteNonQuery(sbSQL.ToString());
}
}