SubSonic Multiple Databases ConnectionString or DataProviders
March 6th, 2009
So you have a SubSonic Project that you now need to connect to multiple Databases. In my case, I didn’t let SubSonic build the database infrastructure, but for my 2nd database, I just used and inline query. Why? Because I needed it done YESTERDAY…So it was just easier at the time.
To configure SubSonic to connect to the other database. I did the following.
Below in the XML…notice I have 2 connectionStrings and 2 providers
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<configSections>
<section name="SubSonicService" type="SubSonic.SubSonicSection, SubSonic" allowDefinition="MachineToApplication" restartOnExternalChanges="true" requirePermission="false"/>
</configSections>
<connectionStrings>
<add name="SQLDashboard" connectionString="Data Source=MYQSTMGTDB01; Database=SQLPerfmonStats; Integrated Security=true;"/>
<add name="MYDevicesDB" connectionString="Data Source=MYDevicesDB; Database=MYDevices; Integrated Security=true;"/>
</connectionStrings>
<SubSonicService defaultProvider="SQLDashboard" enableTrace="false" templateDirectory="">
<providers>
<clear/>
<add name="MYDevicesDB" type="SubSonic.SqlDataProvider, SubSonic" connectionStringName="MYDevicesDB"/>
<add name="SQLDashboard" type="SubSonic.SqlDataProvider, SubSonic" connectionStringName="SQLDashboard"/>
</providers>
</SubSonicService>
</configuration>
In my C# code I did the following using Subsonic.InlineQuery
String sql = @"select * from myTable";
IDataReader rdr = new SubSonic.InlineQuery("SACDevicesDB").ExecuteReader(sql);
DataTable dt = new DataTable();
using (rdr)
{
dt.Load(rdr);
}
return dt;
