I've frequently run into situations where I need to retrieve data from a one of the Commerce Server databases in a production site. Unfortunately, some of this data is unavailable through the exposed runtime APIs.
Occasionally, there are methods available through a management context for accessing this information, but this requires reloading the configuration settings each time one of these contexts is instantiated (which can be really, really expensive).
The preferred option is therefore to work directly with these tables in ADO.Net. This is much more effecient than loading the management context objects, and allows the developer to do additional performance tuning if needed. The complication that this introduces is that it requires developers to maintain connection strings somewhere in the solution (either in a configuration file, or in the actual code). This can be a real annoyance when deploying code to a new environment, since the developer needs to go in and manually change the connection strings.
The solution to this is to read these connection strings right out of the Commerce Server configuration database. This minimizes time spent configuring your site, and all of your connection strings are now located in one central location.
If done properly, you can just package a pup file from one environment, deploy it to a completely different environment, and everything will work without any modifications to configuration.
This isn't a new technique, but it's not exactly well documented, and I thought I'd write something up to help out other developers.
The following code snippets will give you the OLEDB connection strings to each of the Commerce Server Resources from within a Runtime Context. You can programmatically trim off the provider if you want to use something else. Give these a try on the Starter Site or CSharp Site!
//Transactions:
CommerceContext.Current.Resources["Transactions"]["connstr_db_Transactions"].ToString()
//Transactions Config:
CommerceContext.Current.Resources["Transaction Config"]["connstr_db_TransactionConfig"].ToString()
//Product Catalog:
CommerceContext.Current.Resources["Product Catalog"]["connstr_db_Catalog"].ToString()
//Inventory:
CommerceContext.Current.Resources["Inventory"]["connstr_db_inventory"].ToString()
//Marketing Lists:
CommerceContext.Current.Resources["Marketing"]["connstr_db_Lists"].ToString()
//Marketing:
CommerceContext.Current.Resources["Marketing"]["connstr_db_Marketing"].ToString()
//Profiles (perhaps the most obscure).
CommerceContext.Current.Resources["Biz Data Service"]["s_BizDataStoreConnectionString"].ToString()
NOTE: You'll need to add a reference to the Microsoft Commerce Server Shared Types assembly, otherwise the CommerceResourceCollection type is unavialable.
Working with Commerce Server tables in ADO.Net
Roman Benko.
ScottyWakefield
The more sematically correct way of removing the Provider:
static string ConvertOdbcConnectionStringToSql(string connectionString)
{
OdbcConnectionStringBuilder connectionStringBuilder = new OdbcConnectionStringBuilder(connectionString);
connectionStringBuilder.Remove("Provider");
return connectionStringBuilder.ToString();
}