C#.Net Retrieving database relationships

[Gun Slinger]

Hi guys,

Just a simple question which has frustrated me for a little while searching for the answer. How do you manage to get the relationships from an external database?

I'm making a program which i am linking up databases and will give functionality to see the relationships between tables and so forth.

I have not had much luck finding any solutions

I tried something along these lines, with a table i know has relationships of both types (parent and child) but it doesn't give me a response.

 
SqlConnection mySqlConnection = new SqlConnection(connectionString);
 
SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
mySqlCommand.CommandText = "SELECT * FROM MovieDetails";
 
mySqlConnection.Open();
 
SqlDataReader productsSqlDataReader = mySqlCommand.ExecuteReader();
DataTable myDataTable = productsSqlDataReader.GetSchemaTable();
if (myDataTable.ChildRelations.Count > 0) {
         MessageBox.Show(myDataTable.ChildRelations[0].RelationName);
}
mySqlConnection.Close();
 

[iam_clint]

You can use a dataset to create child/parent relationships.

The dataset can hold as many tables as you put in it and then you can refrence these tables such as ds.tables(index) and add relationships on certain columns.

[Gun Slinger]

Hi Clint

Thanks for the reply. But i think you may have skimmed my topic slightly

I know that datasets can create and use relationships when you set them manually. But i want to recover a relationship from a database that is separate from my program completely. Is it that the relationships are not actually saved in the database itself? I find that hard to believe.

Anyway, I look forward to your next reply.

[Plater]

can do you get something from this information:
http://developer.mimer.com/documenta...ic_views2.html

For instance to get all the columns in a table called 'mytable' I use this:

SELECT * 
FROM information_schema.columns
WHERE TABLE_NAME= 'mytable'
 

Perhaps one of the other schema things contains the relationships you wanted

[Gun Slinger]

Ah i see!

I'll have a look around them. But by you saying perhaps seems to tell me that people don't do this very often. What is the method people do usually do for this type of problem?

I'll see if i can find the solution in there, but it may not be hey?

[bbnayak]

I have to make an application which will fetch the result from a databse/table by criteria Then it will show all the result matching the criteria. i want to make the application using Dot Net 2.0. If some one can help me how to start the project and what will be the flow .

B.B.Nayak

[Gun Slinger]

You want to make a search that searches all tables? thats pretty much it?

Kind of not the same as my topic. Post a topic if you want this question answered!

[Gun Slinger]

I know this is a double post, but i seem to have found the answer i was looking for. It doesn't seem to run slowly, and gets the information that i need.

Found it here http://www.mssqltips.com/tip.asp?tip=1106 but its probably in other places.

The code is in the 5th row. The code is:

"SELECT OBJECT_NAME(f.constraint_object_id) AS 'ForeignKey',"+
" OBJECT_NAME(f.parent_object_id) AS 'FKTable', c1.[name]" +
" AS 'FKColumnName', OBJECT_NAME(f.referenced_object_id)" +
" AS 'PKTable', c2.[name] AS 'PKColumnName' FROM" + 
" sys.foreign_key_columns f INNER JOIN sys.all_columns c1" +
" ON f.parent_object_id = c1.[object_id] AND" + 
" f.parent_column_id = c1.column_id INNER JOIN" +
" sys.all_columns c2 ON f.referenced_object_id =" +
" c2.[object_id] AND f.referenced_column_id =" + 
"c2.column_id ORDER BY OBJECT_NAME(f.referenced_object_id)"

I have to test this out a bit more, but it seems to be fine! I'm not planning on dealing with too many tables, so it shouldn't be too problematic.