/// <summary> /// 获取表的约束条件 /// constraint_name - 约束名称 /// column_name - 约束的字段 /// constraint_type - 约束类型,P: 主键,U: 唯一键,R: 外键 /// </summary> /// <param name="tableName"></param> /// <returns></returns> public ConstraintCollection GetTableConstraint(string tableName) { tableName = tableName.ToUpper(); string sql = "select col.constraint_name, col.column_name, c.constraint_type from user_cons_columns col, user_constraints c where col.constraint_name = c.constraint_name and c.constraint_type in ('P', 'U') and col.table_name = :table_name order by c.constraint_type, col.constraint_name, col.position"; try { DbParameter[] prams = { DbFactory <OracleConn, OracleFactory <OracleConn> > .MakeInParam(":table_name", OracleType.VarChar, 30, tableName), }; IDbOperation db = new OracleExecute(); DataSet ds = db.FillDataset(sql, prams); DataSet dsEmtpy = GetEmptyTable(tableName); string prevType = "", prevColName = ""; List <DataColumn> cols = null; foreach (DataRow dr in ds.Tables[0].Rows) { string constraintName = dr["constraint_name"].ToString(); string columnName = dr["column_name"].ToString(); string constraintType = dr["constraint_type"].ToString(); if (constraintType != prevType || constraintName != prevColName) { if (prevType.Length > 0) { UniqueConstraint uc = new UniqueConstraint(prevColName, cols.ToArray(), prevType == "P"); dsEmtpy.Tables[0].Constraints.Add(uc); } cols = new List <DataColumn>(); } cols.Add(dsEmtpy.Tables[0].Columns[columnName]); prevType = constraintType; prevColName = constraintName; } if (prevType.Length > 0) { UniqueConstraint uc = new UniqueConstraint(prevColName, cols.ToArray(), prevType == "P"); dsEmtpy.Tables[0].Constraints.Add(uc); } return(dsEmtpy.Tables[0].Constraints); } catch (CustomDataException exdb) { throw exdb; } catch (System.Exception ex) { throw new CustomDataException(ex, sql); } }
public DataSet GetEmptyTable(string tableName) { string sql = "select * from " + tableName + " where 0 = 1"; try { IDbOperation db = new OracleExecute(); return(db.FillDataset(sql)); } catch (System.Exception ex) { throw new CustomDataException(ex, sql); } }
/// <summary> /// 获取某个表的信息 /// cname - 字段名 /// coltype - 字段类型 /// width - 字段长度 /// </summary> /// <param name="tableName"></param> /// <returns></returns> public DataSet GetTableInfo(string tableName) { tableName = tableName.ToUpper(); string sql = "select cname, coltype, width from col where tname = :tname"; DbParameter[] dbParams = { DbFactory <OracleConn, OracleFactory <OracleConn> > .MakeInParam(":tname", OracleType.VarChar, 30, tableName), }; IDbOperation db = new OracleExecute(); DataSet dataSet = db.FillDataset(sql, dbParams); return(dataSet); }