none
DataTable.Find where multiple index exists on SqlServer table RRS feed

  • Question

  • Hello

    How does a DataTable handles a SqlServer  table having 2 primary unique indexes on two separate columns ? I have a table with separate indexes, one on col1 and one on col3. Using the debugger I can see that col1 has been selected as primary key for the DataTable. Why ?  Is it because col1 is defined before col3 ? What if I want to do a Find() on col3 ?

    Tx.

    Tuesday, October 30, 2012 1:29 PM

Answers

All replies

  • DataTable.Find does not hit the database (it has no database connection). It uses an in-memory index build on the primary key of the datatable. You can set the primary key before populating the data table.

    If you want to utilize indexes in the database you have to hit the database again with another database query. 



    Visual C++ MVP

    Tuesday, October 30, 2012 10:20 PM
  • Apparently FillSchema() is the culprit. It won't load all the constraints but only the first one it finds. There seems to be no way to instruct FillSchema() to create the primary key on col2 instead of col1 (I tried using "select col2, col1 from..." instead of "select * from ...", but it did not work). Found the answer here:

    http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/22f47eca-a135-47ab-b359-424c6c3fc4a3/

    Wednesday, October 31, 2012 1:01 PM
  • You know that you can programmatically set the DataTable's PrimaryKey to be more than one column, right? Just set it to an array of DataColumn[ ], like this:

    MyTable.PrimaryKey = new DataColumn[] { MyTable.Columns["col1"], MyTable.Columns["col2"] };


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Saturday, November 3, 2012 4:12 PM