How to get a stored proc to reference a DB from outside its current execution scope

Question

I have 2 DB's, Building and Contact, and a stored proc that executes from the Building DB, building_sp.

building_sp needs to update a table, TblContact, within Contact, and they way I have been referencing it is by

[Contact].dbo.[TblContact]

Since the Contact table can be named arbitrarily, I need to remove this dependency.

The options NOT available to me are

Moving the stored proc logic to code (ie a .NET controller, where the Contact DB name could be set in a web service config file).
Storing the Contact DB name in a meta table/row in the Building DB.
Pass in the a string variable containing the Contact DB name into the building_sp.

Any suggestions or help on this will be appreciated, even just rough ideas or partial answers. Thanks.

bjnr · Answer 1 · 2013-12-16T18:13:51.760

1

I would choose option1: having 2 DataAccess components, one for Building, one for Contact and let a .NET component (controller) to invoke operation on DALs within a transaction (see TransactionScope class).

Why?

Later, you might decide to move those 2 databases on different machines
Avoid low coupling
Later you might have a third DB invoked, so you have 2 pass 2 DB names or to access many DBs from your SP
You might need to call other services (e.g. sending a mail) and it is more natural to do this kind of operations in .NET
Respect open/close principle: if contact update logic changes, there will be no need to touch Building logic, so less impact, less time involved in testing, lower chances to produce regressions
I let the others to add other reasons here...

edited Dec 16 '13 at 18:13

answered Dec 16 '13 at 18:03

bjnr

3,353
1
18
32

The reason this is not an option is simply b/c I don't feel like, or have the time to, reimplement the stored proc. I've only done transactions in code on a SqlLite DB, but not yet on a MSSql DB. – samus Dec 16 '13 at 18:08
My connection to the Building DB is already specified in a web config, and building_sp is infact fired from a controller using this connection. Could I maybe add another line to this config file to specify the Contact DB, maybe as a string literal, or a connection of sorts, and then reference this "config variable" or connection from the building_sp ? – samus Dec 16 '13 at 18:36
usually you have a section called connection strings: http://stackoverflow.com/questions/5642474/setting-up-connection-string-in-asp-net-to-sql-server – bjnr Dec 16 '13 at 18:45
Is it possible to make a version of my building_sp that has variable names in place of where the hard-coded reference is, and then execute it as dynamic sql, passing into it the reference string as an argument (ie, SELECT * FROM @ContactDBRef). I'd need to use option 3 to use this though. – samus Dec 16 '13 at 18:50

score 0 · Answer 2 · answered Dec 17 '13 at 15:12

I ended up storing the Contact DB name in a web.config app setting

<configuration>
   …
  <appSettings>
    …
    <add key="ContactDBName" value="ContactDataBase"/>
  </appSettings>
  …
</configuration>

I then read this into a global static string from Application_Start()

public class WebApiApplication : System.Web.HttpApplication
{
    public static string ContactDBName;

    protected void Application_Start()
    {
        ...        
        ReadContactDBNameFromConfig();
    }

    protected void ReadContactDBNameFromConfig()
    {
        System.Configuration.Configuration rootWebConfig = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration("/WebServiceName");
        System.Configuration.KeyValueConfigurationElement contactDBNameElement = rootWebConfig.AppSettings.Settings["ContactDBName"];

        ContactDBName = contactDBNameElement.Value;
    }
}

I also stored the stored procedure into a string, using the "place holder" literal "{0}" for the DB name.

public static string BUILDING_SP = 
@"BEGIN TRANSACTION 
    ...
    UPDATE [{0}].[dbo].[TblContact]
    SET param1 = @Param1,
        param2 = @Param2,
        ...
    WHERE record_id = @RecordID
    ...     
COMMIT";

I then use String.Format to set the place holder with the DB name

string sql = String.Format(BUILDING_SP, WebApiApplication.ContactDBName);

This string is then executed via a SqlCommand object.

How to get a stored proc to reference a DB from outside its current execution scope

2 Answers2