Wrox Programmer Forums

Need to download code?

View our list of code downloads.

| FAQ | Members List | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old July 1st, 2008, 12:10 AM
Authorized User
 
Join Date: Mar 2008
Location: New York, NY, USA.
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,
Ronnie


  #2 (permalink)  
Old July 21st, 2008, 03:52 PM
Authorized User
 
Join Date: Jan 2007
Location: , , .
Posts: 72
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 04:38 AM.


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