/// <summary>
/// 获取表结构定义
/// </summary>
/// <param name="tb">DataTable</param>
/// <returns>结果</returns>
private MTableDefine GetTableDefine(DataTable tb)
{
MTableDefine ret = new MTableDefine();
if (tb == null)
{
return(null);
}
if (tb.TableName.Contains("首页") || tb.TableName.Contains("数据表一栏"))
{
return(null);
}
// 估计带超链接，还真不准，先注释掉
//if (tb.Rows[0][0].ToString() != "返回一览表")
//{
// return null;
//}
if (tb.Rows[1][0].ToString() != "TableName")
{
return(null);
}
ret.TableName = tb.Rows[2][0].ToString();
ret.TableNameCH = tb.Rows[2][1].ToString();
ret.TableDescrption = tb.Rows[2][2].ToString();
ret.FieldList = new List <MFieldDefine>();
for (int i = 5; i < tb.Rows.Count; i++)
{
MFieldDefine col = new MFieldDefine();
col.FieldNameCH = tb.Rows[i][1].ToString();
col.FieldName = tb.Rows[i][2].ToString();
col.DataType = tb.Rows[i][3].ToString();
string tempstr = tb.Rows[i][4].ToString();
int tempint = 0;
if (!string.IsNullOrEmpty(tempstr) && int.TryParse(tempstr, out tempint))
{
col.Length = tempint;
}
tempstr = tb.Rows[i][5].ToString();
if (!string.IsNullOrEmpty(tempstr) && tempstr == "○")
{
col.IsNullable = false;
}
tempstr = tb.Rows[i][6].ToString();
if (!string.IsNullOrEmpty(tempstr) && int.TryParse(tempstr, out tempint))
{
col.PrimaryKeyIndex = tempint;
}
col.ForeignRelation = tb.Rows[i][7].ToString();
tempstr = tb.Rows[i][7].ToString();
if (!string.IsNullOrEmpty(tempstr) && tempstr == "○")
{
col.IsUniqueIndex = true;
}
tempstr = tb.Rows[i][8].ToString();
if (!string.IsNullOrEmpty(tempstr) && int.TryParse(tempstr, out tempint))
{
col.IndexNo = tempint;
}
tempstr = tb.Rows[i][9].ToString();
if (!string.IsNullOrEmpty(tempstr) && tempstr == "○")
{
col.IsAutoIncrement = true;
}
col.FieldFormat = tb.Rows[i][10].ToString();
col.DefaultValue = tb.Rows[i][11].ToString();
col.ValueConstraint = tb.Rows[i][12].ToString();
col.ProjectSignificance = tb.Rows[i][13].ToString();
ret.FieldList.Add(col);
}
return(ret);
}
private MTableDefine GetTableDefine(System.Data.IDbConnection conn, string tableName)
{
MTableDefine table = new MTableDefine();
table.TableName = tableName;
string sql = string.Format(@"
SELECT
TableName = case when a.colorder=1 then d.name else '' end,
TableNameCH = case when a.colorder=1 then isnull(f.value,'') else '' end,
FieldIndex = a.colorder,
FieldName = a.name,
IsIdentity = case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then 'true'else 'false' end,
IsPrimaryKey = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then 'true' else 'false' end,
DataType = b.name,
DataTypeLength = COLUMNPROPERTY(a.id,a.name,'PRECISION'),
DigitalLength = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
IsNullable = case when a.isnullable=1 then 'true'else 'false' end,
DefaultValue = isnull(e.text,''),
FieldNameCH = isnull(g.[value],'')
FROM
syscolumns a
left join
systypes b
on
a.xusertype=b.xusertype
inner join
sysobjects d
on
a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join
syscomments e
on
a.cdefault=e.id
left join
sys.extended_properties g
on
a.id=G.major_id and a.colid=g.minor_id
left join
sys.extended_properties f
on
d.id=f.major_id and f.minor_id=0
where
d.name='{0}' --如果只查询指定表,加上此红色where条件，tablename是要查询的表名；去除红色where条件查询说有的表信息
order by
a.id,a.colorder;", tableName);
SqlCommand comm = new SqlCommand(sql, (SqlConnection)conn);
SqlDataReader reader = comm.ExecuteReader();
List <MFieldDefine> fieldList = new List <MFieldDefine>();
while (reader.Read())
{
MFieldDefine field = new MFieldDefine();
table.TableNameCH = reader["TableNameCH"] == DBNull.Value ? string.Empty : reader["TableNameCH"].ToString();
field.Index = DbUtil.GetDBValueInt(reader, "FieldIndex", 0);
field.FieldName = DbUtil.GetDBValueStr(reader, "FieldName", string.Empty);
field.IsAutoIncrement = DbUtil.GetDBValueBool(reader, "IsIdentity", false);
bool IsPrimaryKey = DBUtil.DbUtil.GetDBValueBool(reader, "IsPrimaryKey", false);
int maxPrimaryKeyIndex = fieldList != null && fieldList.Count > 0 ? fieldList.Max(sa => sa.PrimaryKeyIndex) : 0;
if (IsPrimaryKey)
{
field.PrimaryKeyIndex = maxPrimaryKeyIndex + 1;
}
field.IsPrimaryKey = IsPrimaryKey;
field.DataType = DbUtil.GetDBValueStr(reader, "DataType", string.Empty);
field.Length = DbUtil.GetDBValueInt(reader, "DataTypeLength", 0);
field.DigitalLength = DbUtil.GetDBValueInt(reader, "DigitalLength", 0);
field.IsNullable = DbUtil.GetDBValueBool(reader, "IsNullable", false);
field.DefaultValue = DbUtil.GetDBValueStr(reader, "DefaultValue", string.Empty);
// 处理默认值
if (!string.IsNullOrEmpty(field.DefaultValue))
{
string[] twoBracketsSpecial = { "int", "tinyint" };
if (twoBracketsSpecial.Contains(field.DataType.ToLower()))
{
field.DefaultValue = field.DefaultValue.Replace("(", string.Empty).Replace(")", string.Empty);
}
else
{
// 只有一个括号
field.DefaultValue = field.DefaultValue.Substring(1, field.DefaultValue.Length - 2);
}
}
field.FieldNameCH = DbUtil.GetDBValueStr(reader, "FieldNameCH", string.Empty);
fieldList.Add(field);
}
table.FieldList = fieldList;
return(table);
}
