Using a database link in a C# program
-
I'm a newbee in C# (barely 3 wks old). The problem is that I have two tables in two different databases running on different machines, I need to join these two tables in a query using a database link. Haven searched my libraries thoroughly. The best I came up with was to write the query in a stored procedure, which I called from the program (this is a very slow solution like you'll probaly guess). The VC#.NET documentation has extensive support for database programming which I have explored...but where is the database link object. Please help needed urgently.:rose: ...the mind is not a vessel to be filled but a fire to ignited
-
I'm a newbee in C# (barely 3 wks old). The problem is that I have two tables in two different databases running on different machines, I need to join these two tables in a query using a database link. Haven searched my libraries thoroughly. The best I came up with was to write the query in a stored procedure, which I called from the program (this is a very slow solution like you'll probaly guess). The VC#.NET documentation has extensive support for database programming which I have explored...but where is the database link object. Please help needed urgently.:rose: ...the mind is not a vessel to be filled but a fire to ignited
There's no such thing as a database link object. There is connection classes like
SqlConnection, but unless you plan on joining these results yourself, you can only use one in certain objects like aSqlDataAdapter. There is ways to link databases together in some RDBMS's like SQL Server. You can then use a stored proc or a view to query both databases using a syntax like "database.owner.object". In most cases, leaving this on the server provides faster access and centralized management of the query and the database links. You could then write a view like so:SELECT A.Name, A.Birthday, B.AccountID
FROM Table1 A JOIN db2.dbo.Table2 B ON A.ID = B.IDwhere db2 is a linked database. You can find more information about this in the documentation for your RDBMS like SQL Server. I also suggest you move this to the SQL forum if you continue this way. Otherwise, you'll need two
SqlConnectionobjects and two pairs ofSqlCommandandSqlDataReaderobjects (note, I'm assuming SQL here because many lower-end RDBMS's don't support database linking that would useSystem.Data.OleDbclasses). Then you'll have to read the results and programmatically join these together. This will most likely be much slower and more risky that allowing the database server to do so.-----BEGIN GEEK CODE BLOCK----- Version: 3.21 GCS/G/MU d- s: a- C++++ UL@ P++(+++) L+(--) E--- W+++ N++ o+ K? w++++ O- M(+) V? PS-- PE Y++ PGP++ t++@ 5 X+++ R+@ tv+ b(-)>b++ DI++++ D+ G e++>+++ h---* r+++ y+++ -----END GEEK CODE BLOCK-----
-
There's no such thing as a database link object. There is connection classes like
SqlConnection, but unless you plan on joining these results yourself, you can only use one in certain objects like aSqlDataAdapter. There is ways to link databases together in some RDBMS's like SQL Server. You can then use a stored proc or a view to query both databases using a syntax like "database.owner.object". In most cases, leaving this on the server provides faster access and centralized management of the query and the database links. You could then write a view like so:SELECT A.Name, A.Birthday, B.AccountID
FROM Table1 A JOIN db2.dbo.Table2 B ON A.ID = B.IDwhere db2 is a linked database. You can find more information about this in the documentation for your RDBMS like SQL Server. I also suggest you move this to the SQL forum if you continue this way. Otherwise, you'll need two
SqlConnectionobjects and two pairs ofSqlCommandandSqlDataReaderobjects (note, I'm assuming SQL here because many lower-end RDBMS's don't support database linking that would useSystem.Data.OleDbclasses). Then you'll have to read the results and programmatically join these together. This will most likely be much slower and more risky that allowing the database server to do so.-----BEGIN GEEK CODE BLOCK----- Version: 3.21 GCS/G/MU d- s: a- C++++ UL@ P++(+++) L+(--) E--- W+++ N++ o+ K? w++++ O- M(+) V? PS-- PE Y++ PGP++ t++@ 5 X+++ R+@ tv+ b(-)>b++ DI++++ D+ G e++>+++ h---* r+++ y+++ -----END GEEK CODE BLOCK-----
Thanks. I have already tried the first approach (joining the tables in a query that resides in a procedure on the server-side)- this turned out to be quite slow anyway. What I need is how to do this using either the OleDb or SqlClient (programmatically). Help still needed. ...the mind is not a vessel to be filled but a fire to ignited
-
Thanks. I have already tried the first approach (joining the tables in a query that resides in a procedure on the server-side)- this turned out to be quite slow anyway. What I need is how to do this using either the OleDb or SqlClient (programmatically). Help still needed. ...the mind is not a vessel to be filled but a fire to ignited
The client-side programmatic way is not going to be easy, and most likely will even be slower (think about it - an RDBMS server is typically beefed up to handle many queries where client machines usually aren't - and they both have to go through roughly the same procedure to join the results; the RDBMS is also better optimized for such operations). The easiest way (and similar to an action plan that an RDBMS would generate) would be to use two
SqlDataAdapters (I'll be using classes from theSystem.Data.SqlClientnamespace, but you most likely can do this in the other similar namespaces likeSystem.Data.OleDb) with two differentSqlConnections to get a couple ofDataSets. Then enumerate the rows in one of them, find rows to join in the other (seeDataTable.Select), and put them in a different table (which you can add programmatically - see the documentation on theDataSetclass). You could also make a newDataSetto hold both tables - or the cross product, whatever suits your needs. You can search CP and google for examples, but I doubt you're going to find much because an RDBMS on a decent server is going to handle this a lot better than a client.-----BEGIN GEEK CODE BLOCK----- Version: 3.21 GCS/G/MU d- s: a- C++++ UL@ P++(+++) L+(--) E--- W+++ N++ o+ K? w++++ O- M(+) V? PS-- PE Y++ PGP++ t++@ 5 X+++ R+@ tv+ b(-)>b++ DI++++ D+ G e++>+++ h---* r+++ y+++ -----END GEEK CODE BLOCK-----
-
The client-side programmatic way is not going to be easy, and most likely will even be slower (think about it - an RDBMS server is typically beefed up to handle many queries where client machines usually aren't - and they both have to go through roughly the same procedure to join the results; the RDBMS is also better optimized for such operations). The easiest way (and similar to an action plan that an RDBMS would generate) would be to use two
SqlDataAdapters (I'll be using classes from theSystem.Data.SqlClientnamespace, but you most likely can do this in the other similar namespaces likeSystem.Data.OleDb) with two differentSqlConnections to get a couple ofDataSets. Then enumerate the rows in one of them, find rows to join in the other (seeDataTable.Select), and put them in a different table (which you can add programmatically - see the documentation on theDataSetclass). You could also make a newDataSetto hold both tables - or the cross product, whatever suits your needs. You can search CP and google for examples, but I doubt you're going to find much because an RDBMS on a decent server is going to handle this a lot better than a client.-----BEGIN GEEK CODE BLOCK----- Version: 3.21 GCS/G/MU d- s: a- C++++ UL@ P++(+++) L+(--) E--- W+++ N++ o+ K? w++++ O- M(+) V? PS-- PE Y++ PGP++ t++@ 5 X+++ R+@ tv+ b(-)>b++ DI++++ D+ G e++>+++ h---* r+++ y+++ -----END GEEK CODE BLOCK-----
Heath, thanks for being helpful on this. I hope some database programmer out there has the way around this problem. I have a project in which the tables and every other database objects must be created programmatically(including the database link) and then written into two different databases and also queried later within the program. For now I'll just keep trying. Once again THANKS. ...the mind is not a vessel to be filled but a fire to ignited
-
Heath, thanks for being helpful on this. I hope some database programmer out there has the way around this problem. I have a project in which the tables and every other database objects must be created programmatically(including the database link) and then written into two different databases and also queried later within the program. For now I'll just keep trying. Once again THANKS. ...the mind is not a vessel to be filled but a fire to ignited
You're not going to find a magic bullet to solve this. I've already given you the answer. Check my history and profile - I know what I'm talking about. I also do A LOT of development with databases. That's the entire backend - both directly and indirectly using .NET Remoting - for our monstrous application that I designed. Creating a database completely through code is provider-specific. Yes, you can execute CREATE TABLE, CREATE VIEW, etc. commands using a
DbCommandclass (likeOleDbCommandorSqlCommand, etc.) but actually creating the database itself is entirly specific to the database. And there is no such thing as a "database link". If you're talking about joining two databases, this is again specific to the database engine. Instead - through code - you can make multiple connections, execute table, view, procedure, etc. statements on each one and copy data into all of them in a loop or something. There just isn't a pre-canned solution that's going to do this. Actually read the .NET SDK (especially being a newbie - don't guess about code), specifically theSystem.Data,System.Data.OleDb, andSystem.Data.SqlClientnamespaces. Read the topics about ADO.NET to understand what happens behind the scenes. If that still isn't enough, read about OLE DB prividers and how they provide generic functionality for ADO and ADO.NET, but anything specific to that provider is not always exposed through abstract layers, which ADO and ADO.NET are (they abstract the specific details away from the client code). As I also mentioned before, if you use the MSDE (free download, royalty free if you own the right products like VS.NET) or SQL Server, you can create a "database link" as you call it and perform replication, which is basically what you're describing. This can copy both database objects (like tables, views, etc.) and data from database to database, even across disparate networks.-----BEGIN GEEK CODE BLOCK----- Version: 3.21 GCS/G/MU d- s: a- C++++ UL@ P++(+++) L+(--) E--- W+++ N++ o+ K? w++++ O- M(+) V? PS-- PE Y++ PGP++ t++@ 5 X+++ R+@ tv+ b(-)>b++ DI++++ D+ G e++>+++ h---* r+++ y+++ -----END GEEK CODE BLOCK-----