Click here to Skip to main content
15,878,230 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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.
Posted
Updated 7-Jan-18 3:52am

1 solution

I'm not sure i understand you correctly, but...

If you want to get list of tables and their columns with information about primary keys, you have to change your PrimaryKeysObj this way:
C#
public class PrimaryKeyObj
{
 	private string schema = string.Empty;
	private string sPK = string.Empty;
    private List<string> columns = new List<string>();
    private string table = string.Empty;

    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 string PrimaryKey { get => sPK; set => sPK = value; }

    public PrimaryKeyObj (string schema, string table, string sPrimaryKey, List<string> columns )
    {
        Schema = schema;
        Table = table;
		PrimaryKey = sPrimaryKey;
        Columns = columns;
    }
}


This should help you to store entire information.
For example:
C#
List<PrimaryKeyObj> primarykeys =
    dt.AsEnumerable()
    .GroupBy(x=>x.Field<string>("TABLE_NAME"))
    .Select(grp => new PrimaryKeyObj
    (
        grp.Select(a=>a.Field<string>("TABLE_SCHEMA")).First(),
        grp.Key,
        grp.Select(a=>a.Field<string>("PRIMARY_KEY_NAME")).First(),
        grp.Select(c=> c.Field<string>("COLUMN_NAME")).ToList()
    )).ToList();


Now, your primarykeys should store the list of PrimaryKeyObj.
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900