/// <summary> /// Gets all of the foreign keys within a table. /// </summary> /// <param name="table"></param> /// <returns></returns> public virtual ForeignKeyColumnSchemaList GetForeignKeys(TableSchema table) { ForeignKeyColumnSchemaList result = new ForeignKeyColumnSchemaList(); return result; // string cmdtext = @" //select // col_name(fc.parent_object_id, fc.parent_column_id) as column_name, // c.data_type, // object_name (f.referenced_object_id) as primary_key_table, // c.* //from sys.foreign_keys as f //inner join sys.foreign_key_columns as fc // on f.object_id = fc.constraint_object_id //inner join information_schema.columns c // on c.column_name = col_name(fc.parent_object_id, fc.parent_column_id) // and c.table_name = object_name(f.parent_object_id) //where object_name(f.parent_object_id) = @table_name //"; // using (SqlCeCommand cmd = new SqlCeCommand(cmdtext)) // { // cmd.Parameters.Add("@table_name", SqlDbType.NVarChar, 128).Value = table.Name; // using (IDataReader reader = this._helper.ExecuteReader(cmd)) // { // while (reader.Read()) // { // string columnName = reader["column_name"].ToString(); // SqlDbType sqldatatype = Enum<SqlDbType>.Parse(reader["data_type"].ToString()); // Type datatype = getDataType(reader["data_type"].ToString()); // Dictionary<string, object> props = new Dictionary<string, object>(); // props.Add("primary_key_table", reader["primary_key_table"].ToString()); // int length = reader["CHARACTER_MAXIMUM_LENGTH"] != DBNull.Value ? System.Convert.ToInt32(reader["CHARACTER_MAXIMUM_LENGTH"]) : -1; // ForeignKeyColumnSchema column = new ForeignKeyColumnSchema(this, table, sqldatatype, datatype, columnName, length, props); // result.Add(column); // } // } // } // return result; }
/// <summary> /// Gets all of the objects representing tables that have references to a primary key. /// </summary> /// <param name="primaryKey"></param> /// <returns></returns> public virtual ForeignKeyColumnSchemaList GetForeignKeyReferences(PrimaryKeyColumnSchema primaryKey) { ForeignKeyColumnSchemaList result = new ForeignKeyColumnSchemaList(); return result; // string cmdtext = @" //select // col_name(fc.parent_object_id, fc.parent_column_id) as foreign_key_column, // object_name(fc.parent_object_id) as foreign_key_table, // col_name(fc.referenced_object_id, fc.referenced_column_id) as primary_key_column, // object_name(fc.referenced_object_id) as primary_key_table, // c.data_type, // c.character_maximum_length, // c.is_nullable, // c.table_name, // c.column_name //from sys.foreign_keys as f //inner join sys.foreign_key_columns as fc // on f.object_id = fc.constraint_object_id //inner join information_schema.columns c // on c.column_name = col_name(fc.parent_object_id, fc.parent_column_id) // and c.table_name = object_name(f.parent_object_id) //where object_name (f.referenced_object_id) = @table_name //"; // using (SqlCeCommand cmd = new SqlCeCommand(cmdtext)) // { // cmd.Parameters.Add("@table_name", SqlDbType.NVarChar, 128).Value = primaryKey.Table.Name; // using (IDataReader reader = this._helper.ExecuteReader(cmd)) // { // while (reader.Read()) // { // string tableName = reader["TABLE_NAME"].ToString(); // SqlDbType sqlType = Enum<SqlDbType>.Parse(reader["DATA_TYPE"].ToString()); // Type dataType = getDataType(reader["DATA_TYPE"].ToString()); // string name = reader["COLUMN_NAME"].ToString(); // Dictionary<string, object> props = new Dictionary<string, object>(); // props.Add("is_identity", false); // int length = reader["CHARACTER_MAXIMUM_LENGTH"] != DBNull.Value ? System.Convert.ToInt32(reader["CHARACTER_MAXIMUM_LENGTH"]) : -1; // props.Add("is_primary_key", false); // props.Add("is_foreign_key", true); // props.Add("is_nullable", (reader["is_nullable"].ToString().Equals("YES"))); // result.Add(new ForeignKeyColumnSchema(this, new TableSchema(this, tableName), sqlType, dataType, name, length, props)); // } // } // } // return result; }