SQL commands in combination of dataset
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