public List <FieldInfo> GetTableFields(string tbName)
{
List <FieldInfo> lstField = new List <FieldInfo>();
if (pro != null && !string.IsNullOrEmpty(tbName))
{
//获取所有字段
string sqlFields = "SELECT COLUMN_NAME,DATA_TYPE,DATA_LENGTH,NULLABLE,DATA_PRECISION,DATA_SCALE FROM USER_TAB_COLumnS where TABLE_NAME='" + tbName + "'";
string sqlPrimary = "select column_name from all_cons_columns cc where table_name='" + tbName + "' and exists (select 'x' from all_constraints c where c.owner = cc.owner and c.constraint_name = cc.constraint_name and c.constraint_type ='P') order by position";
string sqlDesc = "select TABLE_NAME,COLUMN_NAME,COMMENTS from user_col_comments where TABLE_NAME='" + tbName + "'";
//获得主键
object o = OracleHelper.ExecuteScalar(CommandType.Text, sqlPrimary, null);
string pk = "";
if (o != DBNull.Value && o != null)
{
pk = o.ToString();
}
//获得unique
List <string> uniqueFields = GetUniqueFields(tbName);
//获得该表所有字段的注释
List <OracleDescInfo> descs = new List <OracleDescInfo>();
using (DbDataReader odr = OracleHelper.ExecuteReader(CommandType.Text, sqlDesc, null))
{
while (odr.Read())
{
OracleDescInfo model = new OracleDescInfo();
model.TableName = odr.GetString(0);
model.ColumnName = odr.GetString(1);
model.Comments = "";
if (!odr.IsDBNull(2))
{
model.Comments = odr.GetString(2);
}
descs.Add(model);
}
}
using (DbDataReader odr = OracleHelper.ExecuteReader(CommandType.Text, sqlFields, null))
{
while (odr.Read())
{
FieldInfo fi = new FieldInfo();
//字段名
fi.FieldName = odr.GetString(0);
//是否主键
if (fi.FieldName.IsEqualTo(pk))
{
fi.IsPrimaryKey = true;
}
else
{
fi.IsPrimaryKey = false;
}
//是否唯一
if (uniqueFields.Exists(s => s == fi.FieldName))
{
fi.IsUnique = true;
}
else
{
fi.IsUnique = false;
}
//c#变量名
fi.PropertyName = NameFormat(fi.FieldName);
//字段类型
fi.FieldType = odr.GetString(1);
//字段长度
fi.FieldLength = odr.GetInt32(2);
//是否可空
if (!odr.IsDBNull(3))
{
fi.IsNullable = odr.GetString(3) == "Y" ? true : false;
}
else
{
fi.IsNullable = false;
}
//是否自增
fi.IsAutoIncrease = false;
//如果是Number类型，FieldLength为Number的整数部分
if (fi.FieldType.IsEqualTo("NUMBER"))
{
if (!odr.IsDBNull(4))
{
fi.FieldLength = odr.GetInt32(4);
}
}
if (!odr.IsDBNull(5))
{
fi.FieldScale = odr.GetInt32(5);
}
//显示名和描述
OracleDescInfo myDesc = descs.Find(s => s.ColumnName.IsEqualTo(fi.FieldName));
fi.DisplayName = "";
fi.Desc = "";
if (myDesc != null)
{
fi.Desc = myDesc.Comments;
}
string desc = fi.Desc.Replace("，", ",");
string[] arr = desc.Split(',');
fi.DisplayName = arr[0];
//C#类型
fi.VarType = ToCSType(fi.FieldType, fi.FieldLength, fi.FieldScale);
//ReaderGetType
fi.ReaderGetType = ToReaderGetType(fi.FieldType, fi.FieldLength, fi.FieldScale);
lstField.Add(fi);
}
}
}
return(lstField);
}
public List <FieldInfo> GetTableFields(string tbName)
{
List <FieldInfo> lstField = new List <FieldInfo>();
if (pro != null && !string.IsNullOrEmpty(tbName))
{
//获取所有字段
string sqlGetFields = @"select sys.columns.name, sys.types.name as ctype, sys.columns.max_length, sys.columns.is_nullable, (select count(*) from sys.identity_columns where sys.identity_columns.object_id = sys.columns.object_id and sys.columns.column_id = sys.identity_columns.column_id) as is_identity , (select value from sys.extended_properties where sys.extended_properties.major_id = sys.columns.object_id and sys.extended_properties.minor_id = sys.columns.column_id) as description from sys.columns, sys.tables, sys.types where sys.columns.object_id = sys.tables.object_id and sys.columns.system_type_id=sys.types.system_type_id and sys.tables.name='" + tbName + "' order by sys.columns.column_id ";
string sqlGetPrimary = "Select name From SysColumns Where id=Object_Id('" + tbName + "') and ColID in (select keyno from sysindexkeys where id=Object_Id('" + tbName + "'))";
//获得主键
object o = SqlHelper.ExecuteScalar(CommandType.Text, sqlGetPrimary, null);
string pk = "";
if (o != DBNull.Value && o != null)
{
pk = o.ToString();
}
//获得unique
List <string> uniqueFields = GetUniqueFields(tbName);
using (DbDataReader sdr = SqlHelper.ExecuteReader(CommandType.Text, sqlGetFields, null))
{
while (sdr.Read())
{
FieldInfo fi = new FieldInfo();
//字段名
fi.FieldName = sdr.GetString(0);
//是否主键
if (fi.FieldName.IsEqualTo(pk))
{
fi.IsPrimaryKey = true;
}
else
{
fi.IsPrimaryKey = false;
}
//是否唯一
if (uniqueFields.Exists(s => s == fi.FieldName))
{
fi.IsUnique = true;
}
else
{
fi.IsUnique = false;
}
//c#变量名
fi.PropertyName = NameFormat(fi.FieldName);
//字段类型
fi.FieldType = sdr.GetString(1);
//字段长度
fi.FieldLength = sdr.GetInt16(2);
//是否可空
if (!sdr.IsDBNull(3))
{
fi.IsNullable = sdr.GetBoolean(3);
}
else
{
fi.IsNullable = false;
}
//是否自增
if (!sdr.IsDBNull(4))
{
fi.IsAutoIncrease = sdr.GetInt32(4) == 1 ? true : false;
}
else
{
fi.IsAutoIncrease = false;
}
//显示名和描述
if (!sdr.IsDBNull(5))
{
fi.Desc = sdr.GetString(5);
string desc = fi.Desc.Replace("，", ",");
string[] arr = desc.Split(',');
fi.DisplayName = arr[0];
}
else
{
fi.DisplayName = "";
fi.Desc = "";
}
//小数位数
fi.FieldScale = 0;
//C#类型
fi.VarType = ToCSType(fi.FieldType, fi.FieldLength, fi.FieldScale);
//ReaderGetType
fi.ReaderGetType = ToReaderGetType(fi.FieldType, fi.FieldLength, fi.FieldScale);
if (fi.FieldType != "sysname")
{
lstField.Add(fi);
}
}
}
}
return(lstField);
}