[pgsqlclient-checkins] SF.net SVN: pgsqlclient: [55] trunk/pgsqlclient/source/PostgreSql/Data/Schema/PgForeignKeyColumns.cs

[<car...@us...>]

Revision: 55
Author:   carlosga_fb
Date:     2006-03-27 04:13:59 -0800 (Mon, 27 Mar 2006)
ViewCVS:  http://svn.sourceforge.net/pgsqlclient/?rev=55&view=rev

Log Message:
-----------
?\194?\183 Finished initial implementation of the Foreign Key Columns schema

Modified Paths:
--------------
    trunk/pgsqlclient/source/PostgreSql/Data/Schema/PgForeignKeyColumns.cs
Modified: trunk/pgsqlclient/source/PostgreSql/Data/Schema/PgForeignKeyColumns.cs
===================================================================
--- trunk/pgsqlclient/source/PostgreSql/Data/Schema/PgForeignKeyColumns.cs	2006-03-27 12:13:20 UTC (rev 54)
+++ trunk/pgsqlclient/source/PostgreSql/Data/Schema/PgForeignKeyColumns.cs	2006-03-27 12:13:59 UTC (rev 55)
@@ -88,7 +88,23 @@
         protected override DataTable ProcessResult(PgConnection connection, DataTable schema)
         {
             DataTable foreignKeyColumns = schema.Clone();
+            string sql =
+                "SELECT " +
+                    "column_name " +
+                "FROM information_schema.columns " +
+                "WHERE "+
+                    "table_catalog=current_database() AND " +
+                    "table_schema=@tableSchema AND " +
+                    "table_name=@tableName AND " +
+                    "ordinal_position=@ordinalPosition";
 
+            PgCommand selectColumn = new PgCommand(sql, connection);
+            selectColumn.Parameters.Add("@tableSchema", PgDbType.Text);
+            selectColumn.Parameters.Add("@tableName", PgDbType.Text);
+            selectColumn.Parameters.Add("@ordinalPosition", PgDbType.Text);
+
+            selectColumn.Prepare();
+
             foreach (DataRow row in schema.Rows)
             {
                 Array tableColumns              = (Array)row["CONSTRAINT_TABLE_COLUMNS"];
@@ -98,22 +114,40 @@
                 {
                     DataRow foreignKeyColumn = foreignKeyColumns.NewRow();
 
+                    // Grab the table column name
+                    selectColumn.Parameters["@tableSchema"].Value   = row["TABLE_SCHEMA"];
+                    selectColumn.Parameters["@tableName"].Value     = row["TABLE_NAME"];
+                    selectColumn.Parameters["@ordinalPosition"].Value = Convert.ToInt16(tableColumns.GetValue(i + 1));
+
+                    string tableColumn = (string)selectColumn.ExecuteScalar();
+
+                    // Grab the referenced table column name
+                    selectColumn.Parameters["@tableSchema"].Value   = row["REFERENCED_TABLE_SCHEMA"];
+                    selectColumn.Parameters["@tableName"].Value     = row["REFERENCED_TABLE_NAME"];
+                    selectColumn.Parameters["@ordinalPosition"].Value = Convert.ToInt16(referencedTableColumns.GetValue(i + 1));
+
+                    string referencedTableColumn = (string)selectColumn.ExecuteScalar();
+
+                    // Create the new foreign key column info
                     foreignKeyColumn["CONSTRAINT_CATALOG"]          = row["CONSTRAINT_CATALOG"];
                     foreignKeyColumn["CONSTRAINT_SCHEMA"]           = row["CONSTRAINT_SCHEMA"];
                     foreignKeyColumn["CONSTRAINT_NAME"]             = row["CONSTRAINT_NAME"];
                     foreignKeyColumn["TABLE_CATALOG"]               = row["TABLE_CATALOG"];
                     foreignKeyColumn["TABLE_SCHEMA"]                = row["TABLE_SCHEMA"];
                     foreignKeyColumn["TABLE_NAME"]                  = row["TABLE_NAME"];
-                    foreignKeyColumn["COLUMN_NAME"]                 = "";
+                    foreignKeyColumn["COLUMN_NAME"]                 = tableColumn;
                     foreignKeyColumn["REFERENCED_TABLE_CATALOG"]    = row["REFERENCED_TABLE_CATALOG"];
                     foreignKeyColumn["REFERENCED_TABLE_SCHEMA"]     = row["REFERENCED_TABLE_SCHEMA"];
                     foreignKeyColumn["REFERENCED_TABLE_NAME"]       = row["REFERENCED_TABLE_NAME"];
-                    foreignKeyColumn["REFERENCED_COLUMN_NAME"]      = "";
+                    foreignKeyColumn["REFERENCED_COLUMN_NAME"]      = referencedTableColumn;
 
                     foreignKeyColumns.Rows.Add(foreignKeyColumn);
                 }
             }
 
+            // CleanUp
+            selectColumn.Dispose();
+
             return foreignKeyColumns;
         }
 


This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.