locked
Loading FK relationships into datasets from database? RRS feed

  • Question

  • Hi, I have a database that contains about 20 tables that contain various primary keys, and unique and foreign key relationships, and would like to read the whole lot into a dataset so that I can have the protection of the relationships while modifying my datasets and datatables in memory.

    I know that I can get the constraints into my dataset if I (1) use VS to create a strongly typed dataset, and (2) use VS to drag the dataset on to my form, and (3) go through each relationship in the data set designer to enable both relationships and foreign key constraints (as another thread here said to do). But I would rather not do things with VS that way. Instead, I'd rather use the usual untyped datasets if possible.

    Is there a way to just read the data model, including all PK and FK constraints and relationships, right from the database directly into a dataset?  I'm hoping that if I use code something like

      adapter.FillSchema(dataset);
      adapter.Fill(dataset);

    that all my constraints will magically be alive and working in the dataset. Can that happen? Is there any way to programatically load constraints into datasets just by reading the database (and not using VS designers or strongly typed datasets)?

    Thanks in advance...

    Friday, February 3, 2006 2:35 AM

Answers

  • I read in my installed help files under "Adding Existing Constraints to a DataSet" the following sentence: "Foreign key constraint information is not included and must be created explicitly, as shown in Adding Constraints to a Table."

    For untyped datasets the bottom lines are that (1) by default the adapter.Fill method doesn't even load PK info; (2) you can load PK info from the database into your untyped dataset if you set the adapter's Missing SchemaAction to AddWithKey before calling Fill(); (3) or you can load PK by calling FillSchema explicitly. But in all cases, FK info is not loaded---as the sentence above says, you must create those relationships explicitly (with code).

    For typed datasets with relations and PK/FK constraints in VS, add them as data sources to your project, and then drag them on to a form, (I think) you get the PK/Relation info automatically.

    Further, if you double click on your .xsd file to bring up the dataset editor, and then right click each FK relationship and use "Edit Relationship" to enable "Both relation and Foreign Key Constraints", you can get all of the PK/FK/Relation info into your working dataset.

    I hope this info is correct. I will mark this entry as an answer to my question. If it is not correct, perhaps someone with a better understanding can add a corrective posting so that future readers get the right info. Thanks.

     

    Friday, February 3, 2006 5:36 PM
  • Having said all this and having tried a few examples, I see that even if you use the full enchilada of typed datasets, and enable PK/FK/Relation information to be generated into the typed datasets, you still don't get everything in the database.

    For example, you don't get Unique constraints in your dataset for columns in the database. My datasets could block me from inserting a bad record that went against FK relationships, but they did not block me from inserting multiple copies of a good FK record into the dataset. This was because I had no unique key constraint on a pair of the non-primary key columns.

    Of course when I pushed the dataset to the db, the database protested the unique constraint, and rejected the bad records.

    I conclude that I'll have to add the unique constraints to my datasets manually, even if I use typed datasets. (If I am wrong, please correct me...) thanks

     

    Friday, February 3, 2006 5:51 PM

All replies

  • I read in my installed help files under "Adding Existing Constraints to a DataSet" the following sentence: "Foreign key constraint information is not included and must be created explicitly, as shown in Adding Constraints to a Table."

    For untyped datasets the bottom lines are that (1) by default the adapter.Fill method doesn't even load PK info; (2) you can load PK info from the database into your untyped dataset if you set the adapter's Missing SchemaAction to AddWithKey before calling Fill(); (3) or you can load PK by calling FillSchema explicitly. But in all cases, FK info is not loaded---as the sentence above says, you must create those relationships explicitly (with code).

    For typed datasets with relations and PK/FK constraints in VS, add them as data sources to your project, and then drag them on to a form, (I think) you get the PK/Relation info automatically.

    Further, if you double click on your .xsd file to bring up the dataset editor, and then right click each FK relationship and use "Edit Relationship" to enable "Both relation and Foreign Key Constraints", you can get all of the PK/FK/Relation info into your working dataset.

    I hope this info is correct. I will mark this entry as an answer to my question. If it is not correct, perhaps someone with a better understanding can add a corrective posting so that future readers get the right info. Thanks.

     

    Friday, February 3, 2006 5:36 PM
  • Having said all this and having tried a few examples, I see that even if you use the full enchilada of typed datasets, and enable PK/FK/Relation information to be generated into the typed datasets, you still don't get everything in the database.

    For example, you don't get Unique constraints in your dataset for columns in the database. My datasets could block me from inserting a bad record that went against FK relationships, but they did not block me from inserting multiple copies of a good FK record into the dataset. This was because I had no unique key constraint on a pair of the non-primary key columns.

    Of course when I pushed the dataset to the db, the database protested the unique constraint, and rejected the bad records.

    I conclude that I'll have to add the unique constraints to my datasets manually, even if I use typed datasets. (If I am wrong, please correct me...) thanks

     

    Friday, February 3, 2006 5:51 PM