Wrox Programmer Forums
BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0
This is the forum to discuss the Wrox book ASP.NET 2.0 Website Programming: Problem - Design - Solution by Marco Bellinaso; ISBN: 9780764584640
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old July 1st, 2008, 12:10 AM
Authorized User
Join Date: Mar 2008
Posts: 62
Thanks: 1
Thanked 3 Times in 3 Posts
Send a message via Yahoo to kalel_4444
Default Expaning on ExecuteNonQuery () in DataAccess class

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,

Old July 21st, 2008, 03:52 PM
Authorized User
Join Date: Jan 2007
Posts: 72
Thanks: 0
Thanked 0 Times in 0 Posts

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:

        <ConfigurationProperty("sitePrefix")> _
        Public Property SitePrefix() As String
                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:

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

        Private _sitePrefix As String = ""

        Protected Property SitePrefix() As String
                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:

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

        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.

Similar Threads
Thread Thread Starter Forum Replies Last Post
ExecuteNonQuery not giving proper result Lalit Pradhan ASP.NET 2.0 Professional 6 July 24th, 2008 02:00 AM
Warning: DataAccess.cs jimibt BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 1 March 22nd, 2007 09:33 AM
VB.NET ExecuteNonQuery() command Lofa SQL Server 2000 1 September 20th, 2005 10:03 AM
OleDbCommand.ExecuteNonQuery(); kaz VS.NET 2002/2003 1 December 7th, 2003 04:29 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.