SQL commands in combination of dataset

Question

Hello, I've hard-coded al my tables of a database in a dataset. This helps me for doing unit-testing later on a database that I initialize (in fact the dataset I call) Sow, that was the why I made a database by using a dataset. Now, I need to do several operations and I strungle with it. The DataSet has 4 tables, Category, Topics, Identifiers and TopCat. Category has following fields: - id (prim.key) - name (string) Topics: - id (prim.key) - sectiondata (string) Identifiers - name (string, prim.key, unique) - text - istitle (boolean) - topic_ID (foreign key to topics.id) topcat - topsid (foreign key to topics.id) - catgid (foreign key to category.id) I've defined the foreign keys on this way: ForeignKeyConstraint FK_CATG_ID2 = new ForeignKeyConstraint("FK_CATG_ID2", db.Tables["category"].Columns["id"], db.Tables["topcat"].Columns["catgid"]); ForeignKeyConstraint FK_TOPS_ID2 = new ForeignKeyConstraint("FK_TOPS_ID2", db.Tables["topics"].Columns["id"], db.Tables["topcat"].Columns["topsid"]); db.Tables["topcat"].Constraints.Add(FK_CATG_ID2); db.Tables["topcat"].Constraints.Add(FK_TOPS_ID2); Now, the creation of the database and the foreign keys are going well But now: How can you do the following SQL queries? - SELECT category.id, category.name FROM category, topcat WHERE ((category.id = topcat.catgid) AND (topcat.topsid = :id)) id is variable that I set on before - SELECT * FROM category WHERE id NOT IN(SELECT DISTINCT Catgid from Topcat WHERE Topcat.topsid = :id ) ORDER BY name id is variable that I set on before Now I'm doing it this way, but I think there must be a much better way to do this stuff: public override DataTable ListAllCategoriesLinkedByTopicID(int id) { try { DataTable dt = new DataTable(); dt.TableName = "CatsWithTopic"; DataColumn dc1 = new DataColumn(); DataColumn dc2 = new DataColumn(); dc1.DataType = System.Type.GetType("System.Int32"); dc1.AllowDBNull = true; dc1.AutoIncrement = false; dc1.ColumnName = "id"; dt.Columns.Add(dc1); dc2.DataType = System.Type.GetType("System.String"); dc2.AllowDBNull = true; dc2.AutoIncrement = false; dc2.ColumnName = "name"; dt.Columns.Add(dc2); foreach (DataRow p in DsDataBase.Current.Db.Tables["category"].Rows) { foreach (DataRow r in DsDataBase.Current.Db.Tables["topcat"].Rows) { if ( Int32.Parse(r["tops_id"].ToString()) == id && Int32.Parse(r["catg_id"].ToString()) == Int32.Parse(p["id"].ToString()) ) { DataRow t = dt.NewRow(); t["id"] = Int32.Parse(p["id"].ToString()); t["name"] = p["name"].ToString(); dt.Rows.Add(t); } } } return dt ; } catch { throw new DbException(); } } Anyone who can give me some ideas or can help me? Best regards, karel Maeseele