Accessing Microsoft Commerce Server Connection Strings

• Download source - 478.37 KB

Introduction

If you are using Microsoft Commerce Server 2007/2009 (MSCS) and if you have one commerce starter site, then you will have 7 databases in your SQL Server. The databases are listed as follows:

1. SiteName_ProductCatalog
2. SiteName_Profiles
3. SiteName_Marketing
4. SiteName_MarketingLists
5. SiteName_Transactions
6. SiteName_TransactionConfig
7. SiteName_DataWareHouse

If you have multiple sites, datawarehouse resource may be shared among these sites. If we leave datawarehouse alone, each site may have 6 databases. Although you may share your profile DB among your sites, we may also separate each sites’ profile resource. Thus each site will have 6 databases if you do not create a datawarehouse, and use a separate profile for each one.

Every MSCS installation creates two DBs, namely “MSCS_Admin” and “MSCS_CatalogScratch”. MSCS_Admin DB stores settings of the MSCS. MSCS_CatalogScratch DB stores temporary tables of the system.

Background

Microsoft Commerce Server 2002/2007/2009 development experience is required in order to understand this article.

Data Retrieval

Having read all the information above, now we know that MSCS has an internal storage for connection strings for the active resources that the system uses. The commerce infrastructure’s CRUD operations is done through web services or the DLLs that Microsoft provides. If you use web services, we call this approach ServiceAgent approach, otherwise if you use commerce DLLs in order to CRUD underlying data model, we call it SiteAgent approach.

Another approach to query the underlying data model is directly connecting to the database and executing data retrieval queries for fast responses. Since SiteAgent and ServiceAgent use commerce server DLLs or services before querying the data model, these approaches are not as fast as directly connecting and executing queries to database. That’s why sometimes you may want to query the db directly in order to achieve best performance in your applications. Thus direct query is another option to get information from the underlying data stores.

Using More Than One Product Catalog Repository for Testing/Staging Purposes

You may have two productCatalog databases in order to use one for testing or staging purposes, active site may use productCatalog2 database. If the staging DB is ready to go for live you may switch the active product catalog resource from productCatalog to productCatalog2 by changing the connectionstrings from Commerce Server Manager windows application, after changing the connectionstrings you should recycle the application pool in order to refresh the caches for commerce system.

Consequences of Changing the ConnectionStrings Frequently

As a result of always changing the connectionstrings of productCatalog resource, your code may not be aware of the active product catalog. Thus you cannot hard code your conStr in your web.config file and always use the same conStr. Instead of using the same conStr, you have to go and search for the active conStr in your MSCS_Admin DB and find the active productCatalog conStr at that moment.

By finding the active productCatalog conStr from the MSCS_Admin DB, you may execute data retrieval queries fast. As an additional suggestion, you may use “(NOLOCK)” statement in your SELECT queries for the non-blocking queries, this will make your queries run faster.

History

• 11^th September, 2009: Initial post