public List <ColumnEntity> GetColumnList(TableEntity tableEntity)
{
string sql = "pragma table_info('" + tableEntity.Name + "')";
IEnumerable <dynamic> data;
using (var conn = DbHelper.GetConn())
{
data = conn.Query(sql);
}
List <ColumnEntity> columnList = new List <ColumnEntity>();
foreach (var item in data)
{
ColumnEntity model = new ColumnEntity();
model.Name = item.name; //列名
if (item.pk == 1)
{
tableEntity.KeyName = model.Name;
}
model.NameUpper = MyUtils.ToUpper(model.Name); //首字母大写
model.NameLower = MyUtils.ToLower(model.Name); //首字母小写
string columnType = item.type; //数据类型
if (string.IsNullOrEmpty(columnType))
{
columnType = "";
}
string t = columnType.ToLower();
var cs = ConfigHelper.DbTypeDictionary[ConfigHelper.SQLiteCSharp].FirstOrDefault(f => f.Name == t);
if (cs != null)
{
model.CsType = cs.To;
}
else
{
model.CsType = ConfigHelper.UnKnowDbType;
}
var java = ConfigHelper.DbTypeDictionary[ConfigHelper.SQLiteJava].FirstOrDefault(f => f.Name == t);
if (java != null)
{
model.JavaType = java.To;
}
else
{
model.JavaType = ConfigHelper.UnKnowDbType;
}
model.DbType = item.type;
model.AllowNull = Convert.ToString(item.notnull); //是否允许空
model.DefaultValue = item.dflt_value; //默认值
columnList.Add(model);
}
return(columnList);
}
public List <ColumnEntity> GetColumnList(TableEntity tableEntity)
{
string sql = "SELECT C.COLUMN_ID AS \"Id\",C.TABLE_NAME AS \"TableId\",C.COLUMN_NAME AS \"Name\",C.DATA_TYPE AS \"DbType\",C.DATA_LENGTH AS \"DataLength\",NVL(CC.COMMENTS,C.COLUMN_NAME) AS \"Description\",";
sql += "to_number(CASE C.NULLABLE WHEN 'N' THEN '0' ELSE '1' END) AS \"IsNullable\",";
sql += "to_number('0') AS \"AutoIncrement\",";
sql += "to_number(CASE WHEN P.COLUMN_NAME = C.COLUMN_NAME THEN '1' ELSE '0' END) AS \"IsPrimaryKey\" ";
sql += "FROM USER_TAB_COLUMNS C ";
sql += "LEFT JOIN USER_COL_COMMENTS CC ON C.TABLE_NAME = CC.TABLE_NAME AND C.COLUMN_NAME = CC.COLUMN_NAME ";
sql += "LEFT JOIN ( ";
sql += "SELECT CU.COLUMN_NAME FROM USER_CONS_COLUMNS CU ";
sql += "LEFT JOIN USER_CONSTRAINTS AU ON CU.CONSTRAINT_NAME = AU.CONSTRAINT_NAME ";
sql += "WHERE CU.TABLE_NAME = :name AND AU.CONSTRAINT_TYPE='P' ";
sql += ")P ON C.COLUMN_NAME = P.COLUMN_NAME ";
sql += "WHERE C.TABLE_NAME = :name ORDER BY C.COLUMN_ID";
IEnumerable <dynamic> data;
using (var conn = DbHelper.GetConn())
{
data = conn.Query(sql, new { name = tableEntity.Name });
}
List <ColumnEntity> columnList = new List <ColumnEntity>();
foreach (var item in data)
{
ColumnEntity model = new ColumnEntity();
model.Name = item.Name; //列表
model.NameUpper = MyUtils.ToUpper(model.Name); //首字母大写
model.NameLower = MyUtils.ToLower(model.Name); //首字母小写
if (item.IsPrimaryKey.ToString() == "1")
{
tableEntity.KeyName = model.Name;
if (item.AutoIncrement.ToString() == "1")
{
tableEntity.IsIdentity = "true";
}
}
string t = item.DbType;//数据类型
if (string.IsNullOrEmpty(t))
{
t = "";
}
var cs = ConfigHelper.DbTypeDictionary[ConfigHelper.OracleCSharp].FirstOrDefault(f => f.Name == t);
if (cs != null)
{
model.CsType = cs.To;
}
else
{
model.CsType = ConfigHelper.UnKnowDbType;
}
var java = ConfigHelper.DbTypeDictionary[ConfigHelper.OracleJava].FirstOrDefault(f => f.Name == t);
if (java != null)
{
model.JavaType = java.To;
}
else
{
model.JavaType = ConfigHelper.UnKnowDbType;
}
model.DbType = item.DbType + "," + item.DataLength;
if (ConfigHelper.ColumnComment)
{
model.Comment = item.Description; //说明
}
model.AllowNull = item.IsNullable.ToString(); //是否允许空
model.DefaultValue = item.DefaultValue; //默认值
columnList.Add(model);
}
return(columnList);
}
