none
DataTable PrimaryKey columns do not belong to this table

    Question

  • I have 2 issues/questions related to DataTable.PrimaryKey: (1) why didn't a "new" DataTable I created via a DataView of another "source" DataTable not automatically pick up the PrimaryKey of the source DataTable; and (2) in attempting to get around problem 1 by explicitly assigning a PrimaryKey to the new DataTable, why do I get a "PrimaryKey columns do not belong to this table" runtime error, referring to the newDataTable, when I try to assign the PrimaryKey of the sourceDataTable to it?  If I try get around problem 1 by assigning the new PrimaryKey using new DataColumn[] and referencing the primary key column by name, it works.

    Here's the code:

    DataView sourceDataView = new DataView(sourceDataTable);

    sourceDataView.RowFilter = "Type = 6"; sourceDataView.Sort = "Level ASC";

    DataTable newDataTable = sourceDataView.ToTable(); // (1) Why doesn't newDataTable inherit the PrimaryKey from sourceDataTable?

    // Setting the PrimaryKey this way works

    newDataTable.PrimaryKey = new DataColumn[] { newDataTable.Columns["PKColumnName"] };

    // (2) Setting the PrimaryKey this way generates the "PrimaryKey columns do not belong to this table" runtime error on the 3rd statement. The more streamlined 4th and 5th statements generate the same error, not surprisingly. 

    DataColumn[] keys = new DataColumn[1];

    keys = sourceDataTable.PrimaryKey;

    newDataTable.PrimaryKey = keys;

    newDataTable.PrimaryKey = new DataColumn[] { sourceDataTable.PrimaryKey[0] };

    newDataTable.PrimaryKey = sourceDataTable.PrimaryKey;

     

    • Edited by Cincy Steve Saturday, February 04, 2012 6:06 PM
    Saturday, February 04, 2012 5:55 PM

Answers

  • Steve --

    Sorry for not trying it out before suggesting it. I usually do, but was pressed for time yesterday when I posted my reply. And I was going to initially suggest using the .Clone() method, which turns out to be the proper way to handle this. I tested it this time. ;0)

    DataColumn[] keys = sourceDataTable.PrimaryKey.Clone() as DataColumn[];
    DataColumn[] keysNew = new DataColumn[keys.Length];
    for (int i = 0; i < keys.Length; i++)
    {
    	keysNew[i] = newDataTable.Columns[keys[i].ColumnName]; 
    }
    newDataTable.PrimaryKey = keysNew;
    
    

    As to your other question, I'm not sure why you need to filter a DataTable in order to use the .Find() method. Since PrimaryKeys are unique and the .Find() method works only on PrimaryKeys, then why would you need to limit its search by filtering your sourceDataTable? What is the scenario in your app where you need this functionality? I'm not questioning your need to do this, I'm just trying to understand your "workflow" in case another suggestion might be made as to how to handle it...


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    • Marked as answer by Cincy Steve Tuesday, February 07, 2012 7:41 PM
    Monday, February 06, 2012 5:08 PM
  • Thanks for the explanation of your architecture, Steve. It makes perfect sense to be handling your data the way you're doing it. 

    From a lot of the posts I read on these forums, it's refreshing to see someone who is new to .NET properly separating UI and DAL!!  I can't tell you how exasperating it is to me to see posts from people who just don't get that concept!!

    Good luck with your first project!!


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com

    • Marked as answer by Cincy Steve Tuesday, February 21, 2012 8:04 PM
    Tuesday, February 07, 2012 7:17 PM

All replies

  • Hi Steve,

    DataView.ToTable() doesn't work that way (obviously, as you found out). It simply creates a new DataTable based on the filtered data. I'm pretty sure it doesn't copy any of the constraints from the original DataTable.

    #2 might work if you used the .CopyTo() method of the PrimaryKeys array (it doesn't work the way you did it, because that array is still considered part of the sourceDataTable). I haven't tried this code, but it might do the trick:

    sourceDataTable.PrimaryKey.CopyTo(newDataTable.PrimaryKey, 0);
    

     


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Sunday, February 05, 2012 4:58 PM
  • Bonnie -

    Thanks for the suggestion.  I tried it and got the following runtime error: "Destination array was not long enough. Check destIndex and length, and the array's lower bounds."  I tried to prime the PrimaryKey in the newDataTable by first assigning it a new DataColumn[1] (I have a single column primary key), but that didn't help.  I check and the source PrimaryKey is in fact a single row array, but the target PrimaryKey remains empty up to the point of the error.  I'm stumped on how to get rid of the CopyTo error.  I hope it's not a simple oversight on my part.

    Your broader point about DataView.ToTable() not retaining certain features of the original DataTable prompts me to ask a broader question.  How should I go about filtering and sorting a source DataTable so that the result can be searched based on values in the primary key field?  As you can tell from my code, I was envisioning filtering/sorting via a DataView which I would then turn back into a DataTable on which I could do a Find().  I can make that work via my brute force way of designating the PrimaryKey for the filtered/sorted DataTable, but that doesn't seem as elegant as it should.  I've looked at filtering/sorting via the DataTable.Select(filterString, sortString) method, but that doesn't yield a DataTable that I can subsequently process with Find().

    Thanks, Steve

    Monday, February 06, 2012 3:51 PM
  • Steve --

    Sorry for not trying it out before suggesting it. I usually do, but was pressed for time yesterday when I posted my reply. And I was going to initially suggest using the .Clone() method, which turns out to be the proper way to handle this. I tested it this time. ;0)

    DataColumn[] keys = sourceDataTable.PrimaryKey.Clone() as DataColumn[];
    DataColumn[] keysNew = new DataColumn[keys.Length];
    for (int i = 0; i < keys.Length; i++)
    {
    	keysNew[i] = newDataTable.Columns[keys[i].ColumnName]; 
    }
    newDataTable.PrimaryKey = keysNew;
    
    

    As to your other question, I'm not sure why you need to filter a DataTable in order to use the .Find() method. Since PrimaryKeys are unique and the .Find() method works only on PrimaryKeys, then why would you need to limit its search by filtering your sourceDataTable? What is the scenario in your app where you need this functionality? I'm not questioning your need to do this, I'm just trying to understand your "workflow" in case another suggestion might be made as to how to handle it...


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    • Marked as answer by Cincy Steve Tuesday, February 07, 2012 7:41 PM
    Monday, February 06, 2012 5:08 PM
  • Bonnie -

    Thanks for the update.  As you said, it works great.

    As to why I want to do this at all, it's a judgement call about the implementation.  I have a UserControl that contains a ListBox that I need to populate with a subset of the data from one of my DataSet DataTables.  In order to maintain maximum separation between the UIL and DAL, the UI calls a data model object that returns the filtered DataTable.  Subsequently, the UI may need to Find a row in the filtered DataTable, which is why I want the PrimaryKey set.  I know I could have the data model object return the entire DataTable and then have the UI do the filtering via a BindingSource or a local DataView, but that requires the UI to know about the filtering criteria/syntax, which I'm trying to avoid.

    This is my first .Net app.  It's been a process of discovery to decide how best to integrate .Net's native data binding with "application architecture" models such as MVC, MVVM, etc that seem to offer advantages, though with their trade-offs too.  I hope that helps explain.

    More exploration, learning and refactoring to come.  In any case, thanks for your help.

    Steve 

    Tuesday, February 07, 2012 6:06 PM
  • Thanks for the explanation of your architecture, Steve. It makes perfect sense to be handling your data the way you're doing it. 

    From a lot of the posts I read on these forums, it's refreshing to see someone who is new to .NET properly separating UI and DAL!!  I can't tell you how exasperating it is to me to see posts from people who just don't get that concept!!

    Good luck with your first project!!


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com

    • Marked as answer by Cincy Steve Tuesday, February 21, 2012 8:04 PM
    Tuesday, February 07, 2012 7:17 PM