My application iterates through the tables in my database. For each table that has a Primary Key, it needs to know the Primary Key columns.
I'm using OleDb's GetOleDbSchemaTable, which returns a DataTable like this:
Primay_Key_Name, Table_Name, Column_Name, Ordinal
PK_ZipCode, Cities, ZipCode, 1
PK_ZipCode, Cities, ZipPlus4, 2
PK_ProjectId, Projects, ID 1
PK_UsersId, Users, ID 1
The example shows 3 Primary Keys. There are 4 records because PK_ZipCode is a composite key with two columns, ZipCode and ZipPlus4.
I want to store this in a list, so that I don't have to hit the database later, when I loop through the tables to get the columns.
What type of list should I use? I watched an 8 hour PluralSight video on C# Collections, only to discover that strongly typed collections are now "legacy" and that I should be using Generic Collections instead.
To avoid watching another video, I've created an object to store the data. I'll store it in a List<primarykeyobj>.
public class PrimaryKeyObj
{
private string schema = null;
private List<string> columns = new List<string> { };
private string table = null;
public string Schema { get => schema; set => schema = value; }
public List<string> Columns { get => columns; set => columns = value; }
public string Table { get => table; set => table = value; }
public PrimaryKeyObj (string schema, string table, List<string> columns )
{
Schema = schema;
Table = table;
Columns = columns;
}
}
I'm currently doing it in 2 queries:
(1) returns a list of Primay Keys and creates my custom objects
(2) populates my object with the Primary Key's list of columns.
Step 1: Generate the list of PrimaryKeyObj custom objects:
dt = dbConnection.GetOleDbSchemaTable ( OleDbSchemaGuid.Primary_Keys ,
new Object[ ] { null , null, null } );
dv = dt.DefaultView;
dv.RowFilter = "TABLE_NAME NOT LIKE 'MSys%' and ORDINAL = 1";
dt = dv.ToTable ( );
var primarykeys = new List<PrimaryKeyObj> { };
var primarykeycolumns = new List<string> { };
for ( int i = 0 ; i < dt.Rows.Count ; i++ )
{
string schema = dt.Rows[ i ][ "TABLE_SCHEMA" ].ToString ( );
string table = dt.Rows[ i ][ "TABLE_NAME" ].ToString ( );
PrimaryKeyObj pko = new PrimaryKeyObj ( schema , table, primarykeycolumns );
primarykeys.Add ( pko );
}
This next part is where I'm stuck. Now that I have the list of Primary Keys, I need to populate each one with the primary key column names.
Step 2: Loop through the Primary Keys, locate the key in my List<primaykeyobj> and update that key with the column names.
dt = dbConnection.GetOleDbSchemaTable ( OleDbSchemaGuid.Primary_Keys ,
new Object[ ] { null , null , null } );
dv = dt.DefaultView;
dv.Sort = "TABLE_SCHEMA, TABLE_NAME, PK_NAME, ORDINAL asc";
dv.RowFilter = "TABLE_NAME NOT LIKE 'MSys%'";
dt = dv.ToTable ( );
for ( int i = 0 ; i < dt.Rows.Count ; i++ )
{
string schema = dt.Rows[ i ][ "TABLE_SCHEMA" ].ToString ( );
string table = dt.Rows[ i ][ "TABLE_NAME" ].ToString ( );
string column = dt.Rows[ i ][ "COLUMN_NAME" ].ToString ( );
PrimaryKeyObj PkObj = {Do something to return the PrimaryKeyObj that has this schema and table}
pko.Columns.Add ( column );
}
What I have tried:
I'm tried writing this as a Dictionary, and as a List<string> to hold the schema, a List<string> to hold the table but then got stuck on how to bring the columns into the fold. I've tried many other things, but it's after 4am, so please just ask me if you need any more information.