I need to work on this poorly designed db where a new group of data are established by adding a new db to the server. So I have to fetch data from multi dbs, I wonder is there a way to get all thoes group data through one connection? I am using c#.
views:
46answers:
2
+1
A:
(aside: is it possible to influence the design? This sounds in part like what database schema names are designed for...)
It is sometimes possible (for example in SQL Server by using database.schema.object notation, or even server.database.schema.object), but I don't recommend it as the same approach won't fit all use-cases, and it breaks a lot of best-practice guidelines. I would strongly recommend simply generating the connection string per database, perhaps using DbConnectionStringBuilder, or more specific variants like SqlConnectionStringBuilder.
You could disable pooling on the dynamic connections if you are worried about having too many open (pooled) connections (but you would then need to handle connection management yourself; personally I'd be tempted to leave pooling enabled until I can prove there is a problem).
Marc Gravell
2009-07-23 08:05:25
if generating connection string for each db, does that mean i have to create as many connections as the number of dbs? sounds kind of inefficient to me, there might be more than hundreds of groups.
Mr peak
2009-07-23 08:53:32
Wow; hundreds of dbs? Yes... there is an inefficiency there, but I think that is in the *design*. You could **try** the database.schema.name approach, but at own risk...
Marc Gravell
2009-07-23 09:08:27
thanks for sorting this out.
Mr peak
2009-07-23 10:52:49
A:
if all the databases are on the same server, you could just query them using the full name from a connection to whatever the main database is. You may need to build all code dynamically with this design though as you don't know in advance what database you need to connect to.
select * from database1.dbo.mytable m
join database2.dbo.mytable m2 on m.id = m2.id
HLGEM
2009-07-23 17:52:20