How do I determine the relationships between these tables?

  • Thread starter Thread starter NorCan
  • Start date Start date
N

NorCan

Hi

I'm writing an application that will update a database with data from a
legacy application. The data is provided as small XML fragments, one for
each insert/update/delete required. For example:

<COMPANY>
<INSERT>
<COMPANY_ID value='269'/>
<COMPANY_NAME value='Acme Inc'/>
</INSERT>
</COMPANY>

<EMPLOYEE>
<INSERT>
<EMPLOYEE_ID value='1008'/>
<COMPANY_ID value='269'/>
<EMPLOYEE_NAME value='Bill Gates'/>
</INSERT>
</EMPLOYEE>

The root element is the name of the table, the next is what action to
take (insert, update or delete), then follows the actual data.

After reading all of these fragments into a dataset I have a dataset
with several tables, but no relations or constraints. This is a problem
when it comes to updating the database, where this is in place.

The main problem, however, is that the tables involved can vary (it's
not always the COMPANY and EMPLOYEE tables, it can be any tables,
sometimes more than two). This means that I can't hard-code anything, or
make any assumptions on the table names or their relations.

So once I have this dataset with all the data, how do I figure out the
order in which I should update the tables? My plan was to retrieve the
necessary relationship and constraint information from the database, but
how do I do that?

I may also need a way to determine which columns are the primary key(s)
of the tables involved. Is it possible to get this from a query, without
querying the system tables?

PS: The database is a FireBird database and I'm using an OdbcDataAdapter
to connect.


Thanks in advance.

-Frode
 
I am not sure you really have an "on the fly" need, but it can be done via
ADOX. In .NET, you will need to use Interop. ADOX is a COM library that
installs with the MDAC (which will be installed on your machine due to a .NET
requirement).

I had some R&D code with ADOX, but I am not sure where it is now. If I find
it (against a Cache database), I can post it. It will not be Firebird, but
the basic methodology is the same:

1. Set up a proper conn string
2. Connect
3. Query schema

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************
 
Hi Gregory,

This functionatlity in .net 1.x is wrapped in
OleDbSchema.GetOleDbScemaTable.
If you are using ODBC then you are stuck with Firebird specific DDL (data
definition language).
OP: Why aren't you using Firebird's native or OleDb provieder?
 
Thank you for your response, I will check this out further to see of it
meets my needs.

I have just started at this job, and was asked to stick to ODBC because
they don't want the code to be tied to a specific database provider, in
case they decide to replace Firebird in the future.


Regards,
Frode
 
You have two other options then:
- use a factory pattern and use database specific managed providers (most
powerfull)
- use OleDb which is a better choice over Odbc I would say
 
Back
Top