Have been looking into this myself. Marco's comment about "designing the datastore to support this type of architecture from the beginning" would certainly be better if starting from scratch.
The helper methods used in the DataAccess class do make it quite simple to perform stored procedure name substitution as you can change the name of the stored proc before actually calling the standard ExecuteNonQuery() etc. method.
However the problem I have come across is that to pass the table name as a parameter in your stored procedure you have to declare a new variable within your stored proc to contain your full SQL command and then substitute in your table name variable. See http://www.visualbuilder.com/viewpag...04&pageorder=1
for an example.
This isn't that bad for basic stored proc's but I think it would be more work and a less clean approach than duplicating the existing stored procedures and changing the prefix (im sure you could even script this duplication).
To get this working I first added a new Configuration Property within my ArticlesElement in ConfigSection.vb
/cs. I have done this at the provider level rather than globally for maximum flexibility:
Public Property SitePrefix() As String
Set(ByVal value As String)
Me("SitePrefix") = value
Next you can add to your web.config:
<articles providerType="Retro.Web.DAL.SqlClient.SqlArticlesProvider" pageSize="10" rssItems="10" sitePrefix="rw_"/>
(obviously sustituting for your own prefix)
Then you need to add a new property to your DataAccess class (sorry for the vb
Private _sitePrefix As String = ""
Protected Property SitePrefix() As String
Set(ByVal value As String)
_sitePrefix = value
Before you change your helper methods go into your ArticlesProvider class and change your constuctor to include your siteprefix:
Public Sub New()
Me.ConnectionString = Globals.Settings.Articles.ConnectionString
Me.EnableCaching = Globals.Settings.Articles.EnableCaching
Me.CacheDuration = Globals.Settings.Articles.CacheDuration
Me.SitePrefix = Globals.Settings.Articles.SitePrefix
now back in your DataAccess class you can change your helper methods. For example ExecuteReader():
Protected Function ExecuteReader(ByVal cmd As DbCommand) As IDataReader
cmd.CommandText = Me.SitePrefix + cmd.CommandText
Return ExecuteReader(cmd, CommandBehavior.Default)
Now providing on your SqlArticlesProvider (or forums, store etc.) you have removed the prefix from the stored procedure names, the SqlCommand object's passed to your helper methods will have a CommandText property of your default procedure name i.e. GetArticles.
Using the setting in web.config the helper method adds on the prefix and executes rw_GetArticles.
I hope this helps you. You could also add the prefix within the SqlArticlesProvider class but I felt this was a cleaner approach.
If someone can offer a better solution for table name substitution im sure that would be of great help to many people.
Whilst using SiteID values within each of your tables and passing these as variables to one set of stored procedures may be deemed a better solution, using separate groups of stored procedures seems easier to set up and I presume would require less processing both within your app and your sql server.