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!
***************************


"NorCan" wrote:

> 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
>

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?
--
Miha Markic [MVP C#] - RightHand .NET consulting & development
www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/
SLODUG - Slovene Developer Users Group www.codezone-si.info

"Cowboy (Gregory A. Beamer) - MVP" <(E-Mail Removed)> wrote
in message news:F0D3B3E6-FB0F-4BD1-A8D3-(E-Mail Removed)...
>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!
> ***************************
>
>
> "NorCan" wrote:
>
>> 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
>>

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

Miha Markic [MVP C#] wrote:
> 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?

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

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/
SLODUG - Slovene Developer Users Group www.codezone-si.info

"NorCan" <(E-Mail Removed)> wrote in message
news:%237%(E-Mail Removed)...
> 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
>
> Miha Markic [MVP C#] wrote:
>> 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?