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); }