Multiple Connections for a Dataset

[Christine Y.]

Hi,

I have a method in a data access class (all in C#) where I'm trying to
put information from different connections into a single dataset to
return to the calling class. Any ideas on how I can do this? I only
know how to pull data from different tables using ONE connection. I
have data in a completely different connection that I need to pull
from. This is what I currently have:

Connection.Open();
myDataAdapter = new OracleDataAdapter();
myDataAdapter.SelectCommand = new OracleCommand( query, base.Connection
);
myDataSet = new DataSet();
myDataAdapter.Fill( myDataSet );

return myDataSet;


This fills my dataset using the query and only one connection. I don't
want to have to pass connection strings to the method that does this.
Any ideas would be greatly appreciated!

Christine

 

[Christine Y.]

Just to note, by multiple connections I mean different databases.

 

[W.G. Ryan - MVP]

Christine:

You can specify the connection string in the class itself and then pass in a
variable to indicate which one to use. Another way you can do this though
is to use the ChangeDatabase method.
http://www.knowdotnet.com/articles/handlingnullvalues.html
I can't find the documentation for Oracle but since the Oracle connection
implements IDBConnection I believe it will work there as well.

so basically, assuming the first approach, assume this...

String FirstDB= "FirstDBconnectionString";
String SecondDb= "SecondDBconnectionString";
//Probably best to store these in a config file, ideally encrypted, but
that's just a matter of preference, if they won't change often then you can
probably get away with hard coding them.

Now each time you call the method, right before you call open, you can use
Connection = new OracleConnection();
if(DBIndicator = DBToUse.FirstDb){
Connection.ConnectionString = FirstDb;
}
if(DbIndicator = TableToUse.SecondDb){
Connection.ConnectionString = SecondDb;
}
Where I'm going is that I'd create an enum (there are a few ways to get to
the same place and normally we want to shun hard coding, but since db names
don't change all that frequently, it probably won't be too big of a deal)
with a value for each of the Database names that I'd need. Then , create
string values to hold the ConnectionString for each db. Then, pass in an
enum value into your method that's filling the dataset. based on the enum
value, instantiate a new connection with the correct connection string, or
just reset the connection string, or use ChangeDatabase. Obviously the
repeated if statements aren't as elegant as a switch statement , but I did
it for clarity.

So now you just call fill on the dataset or the tables within the dataset.
CommandText, Parameters etc will need to be changed appropriately but I'm
just telling you something you already know ;-). For the record, have you
checked out the Enterprise Library
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/daab.asp)
- you may find it quite useful for the overall scenario you're dealing
with.

HTH,

Bill