I have two unrelated tables in SQL Server. I want to form a relationship with them via C# so the database diagram in SQL Server has the relationship line etc (the code may have flaws apart from the lack of using statements etc, other than that let me know).
I have this code so far:
SqlConnection con = new SqlConnection("Data Source=localhost;Initial Catalog=Test;Integrated Security=SSPI");
SqlDataAdapter ad1;
SqlDataAdapter ad2;
DataSet ds = new DataSet();
DataRelation dr;
ad1 = new SqlDataAdapter("Select * from dept", con);
ad2 = new SqlDataAdapter("select * from emp", con);
ad1.Fill(ds, "dept");
ad2.Fill(ds, "emp");
DataColumn pk = ds.Tables["dept"].Columns["deptno"];
DataColumn fk = ds.Tables["emp"].Columns["deptno"];
dr = new DataRelation("rel", pk, fk, false)
ds.Relations.Add(dr);
ds.AcceptChanges();
ad1.Update(ds, "dept");
ad2.Update(ds, "emp");
When I get to this line:
dr = new DataRelation("rel", pk, fk, false)
I get this exception:
'column' argument cannot be null. Parameter name: column
Is it possible to actually form relationships in SQL Server this way?
What gives?
Everything looks ok, unless one of the tables does not have the "deptno" column..
You're real close, try this (semi-colon at end of line :)
dr = new DataRelation("rel", pk, fk, false);
As far as these changes going back to the database, as JWL_ started to go into, no they don't, here's a blurb from MSDN:
There are many times when your application needs to work with related tables. Although a dataset contains tables and columns as in a database, it does not inherently include a database's ability to relate tables. However, you can create DataRelation objects that establish a relationship between a parent (master) and a child (detail) table based on a common key.