Expaning on ExecuteNonQuery () in DataAccess class

kalel_4444 · July 1st, 2008, 12:10 AM

Hello Everyone..!,

I need to add the ability to set the site prefix (tbh_) in the web.config and pass it to the ExecuiteNonQuery() in the DataAccess class (helper method for all newly created SqlDalObj's).

The book discusses this in "Ch. 3/A Base Class for All Data Access Classes". It mentions prefixing tables & sp's (tbh_) so that the prefix represents a unique site, should multiple sites be required on the same database.

Problem is, it stopped short on demonstrating how to implement this. Specifically said in the book: " we won't fully implement the replaceable table name and stored procedure name substitution in this book in order to keep the SQL statements and stored procedures easier to understand and work with." But it seems that in the TheBeerHouse project, the prefix is included, but not the implementation isn't.

Can anyone give me an idea on configuring the web.config to include a placeholder for the parameter info "site1_, site2_, tbh_, etc" and have the ExecuteNonQuery() method accept it and pass it to all SqlDalObj's ?

Thank you everyone in advance,
Ronnie

retroviz · July 21st, 2008, 03:52 PM

Hi Ronnie,

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:

Code:

        <ConfigurationProperty("sitePrefix")> _
        Public Property SitePrefix() As String
            Get
                Return CStr(Me("sitePrefix"))
            End Get
            Set(ByVal value As String)
                Me("SitePrefix") = value
            End Set
        End Property

Next you can add to your web.config:

Code:

        <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 :D):

Code:

        Private _sitePrefix As String = ""

        Protected Property SitePrefix() As String
            Get
                Return _sitePrefix
            End Get
            Set(ByVal value As String)
                _sitePrefix = value
            End Set
        End Property

Before you change your helper methods go into your ArticlesProvider class and change your constuctor to include your siteprefix:

Code:

        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
        End Sub

now back in your DataAccess class you can change your helper methods. For example ExecuteReader():

Code:

        Protected Function ExecuteReader(ByVal cmd As DbCommand) As IDataReader
            cmd.CommandText = Me.SitePrefix + cmd.CommandText
            Return ExecuteReader(cmd, CommandBehavior.Default)
        End Function

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.