Update 2012-05-20: 

I am using the same SQLExpress database for Orchard content as well as my module, which is an existing website that I am migrating into Orchard as a module.  The existing site has its own tables/stored procs.

*******

I need to store a connectionstring in the web.config of my module as I am connecting to a separate database from the rest of Orchard.  I have my db connection working fine.  I have this in my module's web.config:

  <connectionStrings>

    <add name="BlahConnectionString"

         connectionString="Data Source=TONE-PC-WIN;Initial Catalog=BlahDB;Persist Security Info=True;User ID=useridblah;Password=passwordblah"

         providerName="System.Data.SqlClient" />

  </connectionStrings>

and this code to get the connection string value:

System.Configuration.Configuration rootWebConfig = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration("/ModuleName");

   System.Configuration.ConnectionStringSettings connString;

   if (rootWebConfig.ConnectionStrings.ConnectionStrings.Count > 0)

{

                connString = rootWebConfig.ConnectionStrings.ConnectionStrings["BlahConnectionString"];

                return connString.ConnectionString;

}

but it returns null.  If I look for it by index there is only one connectionstring returned (at 0 index) and it is for the main Orchard site.  

What am I missing here?

The module's web/config will apply to almost nothing. The section for connection strings, for instance, doesn't have any effect at this level. It would need to be in the top-level config, which is no good for a module. This is why web.config is used as little as possible in Orchard as a way to store app-specific or module-specific settings. Instead, you extend site settings for your module. There are plenty of examples of that in existing modules.

ok, so on further assessment I actually can just use the DataConnectionString property in Orchard.Web/App_Data/Sites/Default/Settings.txt.  How can I access that connection string?  I tried using ShellSettings but all the properties are null.  Is there another way to access this in a module?

Orchard.Environment.Configuration.ShellSettings shellSettings = new Orchard.Environment.Configuration.ShellSettings();

using (SqlConnection cn = new SqlConnection(shellSettings.DataConnectionString))

{

...

}

No, if you're going to use the Orchard connection string, it's a lot easier to grab the nHibernate session. You don't need the connection string at all for that. If you need a different connection string, store it in your own site settings. That will be available from any module.

Do you have a quick example of how to grab the nHib session?  Is there a session object of some sort that has the connection string?  I just realized my original question states that I need to connect to 2 separate databases - this is in fact not true so I'll edit the original post to reflect that.  I had forgotten that I configured this site to use SQLExpress, and not the SQL CE that it uses by default.  This same SQLExpress DB has tables/stored procs from an existing site that I am migrating into Orchard as a module. 

Look in the OrchardPo module.

I do not see this module anywhere?  Sorry if I'm missing the obvious here :S .. a bit new to the internals of Orchard.  

https://bitbucket.org/bleroy/orchardpo

Awesome - thanks Bertrand!