public static List <CPDbTable> GetView(string dbInstance)
{
List <CPDbTable> col = new List <Global.CPDbTable>();
if (CurDbType() == DbTypeEnum.SqlServer)
{
#region sql
string strSql = @"select name from sysobjects where xtype='V'
" ;
DbHelper _db = new DbHelper(dbInstance, DbTypeEnum.SqlServer);
DataTable dt = _db.ExecuteDataSet(strSql).Tables[0];
foreach (DataRow dr in dt.Rows)
{
string TableName = Convert.IsDBNull(dr["name"]) ? "" : dr["name"].ToString();
CPDbTable f = new CPDbTable();
f.TableName = TableName;
f.PKNames = "";
col.Add(f);
}
#endregion
}
return(col);
}
public static List <CPDbTable> GetTable(string dbInstance)
{
List <CPDbTable> col = new List <Global.CPDbTable>();
if (CurDbType() == DbTypeEnum.SqlServer)
{
#region sql
string strSql = @"SELECT * FROM (
SELECT 表名 = d.name ,
字段序号 = a.colorder ,
字段名 = a.name ,
标识 = CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1
THEN '1'
ELSE '0'
END ,
主键 = CASE WHEN EXISTS ( SELECT 1
FROM sysobjects
WHERE xtype = 'PK '
AND name IN (
SELECT name
FROM sysindexes
WHERE indid IN (
SELECT indid
FROM sysindexkeys
WHERE id = a.id
AND colid = a.colid ) ) )
THEN '1'
ELSE '0'
END ,
类型 = b.name ,
占用字节数 = a.length ,
长度 = COLUMNPROPERTY(a.id, a.name, 'PRECISION ') ,
小数位数 = ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale '), 0) ,
允许空 = CASE WHEN a.isnullable = 1 THEN '1'
ELSE '0'
END ,
默认值 = ISNULL(e.text, ' ')
FROM syscolumns a
LEFT JOIN systypes b ON a.xtype = 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
) AS ccc WHERE ccc.主键=1 ORDER BY ccc.表名
" ;
DbHelper _db = new DbHelper(dbInstance, DbTypeEnum.SqlServer);
DataTable dt = _db.ExecuteDataSet(strSql).Tables[0];
foreach (DataRow dr in dt.Rows)
{
string TableName = Convert.IsDBNull(dr["表名"]) ? "" : dr["表名"].ToString();
List <CPDbTable> tmpCol = col.Where(t => t.TableName.Equals(TableName)).ToList();
if (tmpCol.Count > 0)
{
tmpCol[0].PKNames += "," + (Convert.IsDBNull(dr["字段名"]) ? "" : dr["字段名"].ToString());
}
else
{
CPDbTable f = new CPDbTable();
f.TableName = TableName;
f.PKNames = Convert.IsDBNull(dr["字段名"]) ? "" : dr["字段名"].ToString();
col.Add(f);
}
}
#endregion
}
return(col);
}
