Wednesday, February 29, 2012

Using Mercurial with TFS

TFS is not TEH DEV-VIL. It is a conceptually brilliant, one product tend over all the facets of getting ideas turned into code. The brilliance of the implementation of each individual portion (source control, wiki, collaboration tool, development process management tool, build server, QA support/defect tracker, kitchen in-sink garbage disposal) is, unfortunately debatable, but at least the theory of TFS is smart.

Now, the source control component is what it is. Something designed to get companies to (for the love of everything good in the world) quit using Visual Source Safe. No less, no more. Just look at how it operates.

It isn't the most terrible thing in the whole wide world (file_new1.cs.bak.old.2 is the most terrible thing in the whole wide world), but it really hates you if you're trying to work disconnected from the TFS server. Maybe TFS 11 will bring some love in that arena.

I started with a bog-standard installation of TortoiseHg, enabled the rebase extension and added in the MakeWriteable extension. Pay attention to the version numbers that the extension is compatible with. I had to pull the latest version of MakeWriteable out of its source repository, the version compatibility chart on selenic.com was out-of-date when I started (it should be fine now), so just pay attention.

Also, you want to install the TFS Power Tools. I don't know why these don't ship with the TFS client distribution, because they make using TFS much, much better.

I decided to write scripts in PowerShell 2.0, mostly because you don't have to install anything...if you're using an up-to-date OS.

I put this script in my "C:\Users\<username>\" directory, to set up the work environment.
$Env:Project = "C:\src\TeamProject\Main\project"
$Env:AnotherProject = "C:\src\TeamProject\Main\AnotherProject"
$Env:FxCop = "C:\Program Files (x86)\Microsoft Visual Studio 10.0\Team Tools\Static Analysis Tools\FxCop"
$Env:DotNetFramework = "C:\Windows\Microsoft.NET\Framework64\v4.0.30319"
$Env:NotePadPlusPlus = "C:\Program Files (x86)\Notepad++"
$Env:VisualStudio = "C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE"
$Env:WindowsSDK = "C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin"

$Env:Path = $Env:Path + ";" + $Env:FxCop +
 ";" + $Env:DotNetFramework +
 ";" + $Env:NotePadPlusPlus +
 ";" + $Env:VisualStudio +
 ";" + $Env:WindowsSDK

Set-Location "C:\Work"

Notice that I have a directory, "C:\Work" that is separate from "C:\src". Don't do your Mercurial controlled work within the directory scope that TFS is using locally. Visual Studio gets completely befuddled by that and tries to add new files to TFS for you. Not helpful.

I created a shortcut on my desktop that targets the following:
%SystemRoot%\system32\WindowsPowerShell\v1.0\powershell.exe -NoExit -file C:\Users\<username>\DevelopmentEnvironment.ps1

Despite the path saying "WindowsPowerShell\v1.0", it runs PowerShell 2.0. Also, -NoExit must preceed -file, or else PowerShell -WillExit. Whoever works on PowerShell, thanks for that.

Anyway, double-click the shortcut, and you get powershell console with your development environment properly configured.

I got latest on the TFS-controlled project directory in C:\src\<project>, then created a Mercurial repo using TortoiseHg. This takes a while if the project is of any size. Whoever works on Mercurial, thanks for that. (Equal opportunity whiner.)

I eventually ended up with the following .hgignore file for the repository:
syntax: glob
obj\*
*.user
*.suo
_ReSharper*
TestResults*
bin\*
Bin\*
*Database\sql
*.schemaview
*.dbmdl
*.sdf
We're using Mrs. Test (MSTest), so I had to exclude *.sdf files. I don't know (in detail) what *.sdf files contain, but Mrs. Test will dutifully make them for you. You don't want them in your source repository.

You should commit the .hgignore with your initial hg commit, in the TFS controlled directory for your project. Next, clone your TFS controlled Mercurial repository to somewhere else. I chose to clone "C:\src\<project>" to "C:\Work\<project>" to get the working Mercurial repository clear of TFS & Visual Studio's watchful eye. At this point, no work will be done in "C:\src" at all. All work will be done in "C:\Work".

I created the following pull script in "C:\Work\project" based on the work Eric Hexter published.

$projectName = "Project"
$ProjectRoot = "C:\src\TeamProject\Main"
$DVCSRoot = "C:\Work"

$tfs = "C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\tf.exe"

function pull {
 cd $ProjectRoot\$projectName
 &$tfs get . -r
 hg commit -A -m "from tfs"
 cd $DVCSRoot\$projectName
 hg pull --rebase
}

pull
The modifications I made to Eric's pull script are mostly superficial, except I am telling TFS to "get . -r", which says get the current directory, and recursively get anything in the child directories below the current. In my experience, TFS just wants to "get" the whole Team Project unless you tell it otherwise. This may or may not be OK, depending on how you use your TFS rig.

This is the push script I cobbled together in "C:\Work\project", again derived from Eric's work.
$projectName = "project"
$ProjectRoot = "C:\src\TeamProject\Main"
$DVCSRoot = "C:\Work"

$tfpt = "C:\Program Files (x86)\Microsoft Team Foundation Server 2010 Power Tools\TFPT.EXE"
$tfs = "C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\tf.exe"

hg push
cd $ProjectRoot\$projectName
&$tfpt scorch /noprompt /recursive /exclude:.hgignore`,.hg`,*.ps1 .
hg update -C -y
&$tfpt online /adds /deletes /recursive /exclude:.hgignore`,.hg`,_ReSharper*`,bin`,obj`,*.user`,*.suo`,*.ps1`,*.log* .
&$tfs checkin
cd $DVCSRoot\$projectName
The scorch command was modified to work starting on the current directory (notice the stealth "current directory" dot on the end) and its child directories.

The online command was modified to detect deletes (it doesn't do this by default), operate on child directories, and to not try to check in a ton of different things. Notice that this command includes the stealth "current directory" dot on the end as well.

If anyone figures out how to make these scripts execute as Mercurial hooks, I'm interested. It would be nice to just use TortoiseHg and not have to drop out to the PowerShell just to pull and push.

Hope this helps someone!

Friday, June 18, 2010

Running Mercurial on Windows Without CGI

In the past, the only real way to run python apps in IIS has been through CGI. FastCGI doesn't work because of an implementation flaw in Python 2.x script libraries that basically prevents FastCGI from functioning on Windows (supposedly this is fixed in Python 3.x). ISAPI wasn't really an option, either, since there was not really a good enough implementation of the WSGI server interface standard for IIS.

By the way, for those of you who haven't been keeping up with the inner workings of Python (that would be most of us Windows folks), WSGI (Web Server Gateway Interface) is a Python standard defined in PEP 333 (PEP is an acronym for Python Enhancement Proposals). It details how Python web applications/Python web frameworks should interface with web servers.

Things have change a little in the months since I wrote my original post on running Mercurial on Windows. Someone who has a great deal of dedication has been working on a WSGI interface for IIS since before my last post, and has now gotten it to a point where it works marvelously with Mercurial. Thanks to Jason R. Coombs and his team for all their hard work on the isapi-wsgi project.

On with the show


Installing Mercurial as a server using isapi-wsgi is SO much easier than fiddling with CGI that it was actually a joy. These instructions assume Mercurial 1.5.1. Keep in mind that since you don't have anything on Windows as cool as the FreeBSD Ports and Packages Collection to build stuff and manage dependencies for you that you will have to make sure you match the version of Mercurial to the correct version of Python yourself. IIS needs to be already installed and functioning.
  1. Install the Python 2.5.6 binary installer package.
  2. Install the latest of Mark Hammond's indispensable Python for Windows package, for Python 2.6 .
  3. Install the binary package for isapi-wsgi. 0.4.2 is the one that you want, since it now supports SSL properly. isapi_wsgi.py will be installed in the 'site-packages' directory of your Python installation...assuming you take the defaults. You should take the defaults.
  4. Go here and follow the instructions in the Testing section to make sure you didn't mess it up.

  5. Go to Selenic and download the source code for Mercurial. Its in a tarball, so you'll probably want to install 7-Zip at this point, because Microsoft obstinately refuses to include tar and gzip support directly in the Windows Explorer...and really why should they since 7-Zip is free, and does exactly that.

  6. Once you've extracted this thing somewhere (or not, you can navigate the archive in 7-Zip and drag-drop the file we need out), find the contrib\win32 directory. Copy hgwebdir_wsgi.py to a directory from which you want to serve Mercurial. I chose C:\inetpub\wwwroot\hg. It doesn't have to be in inetpub, you could use a virtual directory. Don't put your Mercurial repositories in this directory. You'll confuse Mercurial.

  7. On IIS 6 and above, create a new App pool for your Mercurial directory if your IIS installation is doing ANYTHING other than Mercurial. Make sure you make your Mercurial directory into a Application in IIS, and ensure the Application is assigned to the App pool you created for Mercurial. In IIS 5.1, you may want to set the Application Isolation as High "(Isolated)". I mean, the Python interpreter is native code. It could crash and take your entire IIS process down with it. You probably don't want that.

  8. Edit your copy of hgwebdir_wsgi.py. Make the top of it look kinda like this:
    # Configuration file location
    hgweb_config = r'c:\inetpub\wwwroot\hg\hgweb.config'
    
    # Global settings for IIS path translation
    path_strip = 0   # Strip this many path elements off (when using url rewrite)
    path_prefix = 1  # This many path elements are prefixes (depends on the
                     # virtual path of the IIS application).
    
    hgweb_config is pretty simple, it tells the script where to find its config file. path_prefix should be 0 if hgwebdir_wsgi.py is in the root of the web, 1 if its one directory down (server/hg), 2 if you get to it by server/directory/hg... you should get the idea. I don't know what path_strip does, but it sounds risque, so I didn't mess with it.

  9. Run the hgwebdir_wsgi.py script
    python hgwebdir_wsgi.py
    to make a shim dll for Mercurial. All this really does is copy a file from the Python Win32 Extensions package to where the script resides and names it appropriately. Which is nice because you don't have to figure it out for yourself.

  10. Create your hgweb.config file. This is all mine has in it.
    [paths]
    / = c:\repos\*
    If you read my last post, then yes, there is just one star this time.

  11. Create an ISAPI script mapping to the shim dll that the script generated. In IIS 5, you just create a script mapping to the dll using .* as the extension (clear the check box that says "Check that files exist", or IIS will generate hate messages). In IIS 6 and above, you will want to create a wildcard script map to that shim dll.

  12. Create a test repository in c:\repos\ (or where ever). If it shows up in the list on http://server/hg, have you a cold one. Good job. If it doesn't, you may have messed up your hgweb.config, maybe your permissions on c:\repos\. Remember, the identity that IIS (or the IIS worker pool) is running under will need full control access to your repository directory. If you don't see anything at all (or 401's and 404's and bears, oh my!), you may have messed up your script map in IIS or the editing the hgwebdir_isapi.py script.

SSL and web security both worked well through IIS. One thing to keep in mind is that the Hg client doesn't care a thing about Windows Integrated Security, so if you want to secure your repos, you'll have to use SSL+Basic Authentication.

If you find yourself having to diagnose your setup, iisreset is your friend. Because you're loading things into the IIS process space, and IIS isn't very likely to reload things just because you changed them, you may find that you have to restart it when you make certain configuration changes.

I found setting up Mercurial with isapi-wsgi to be easier than with CGI. It seems snappier, and less fussy about certain operations that I had trouble with in CGI. I imagine if FastCGI were to ever work with Python on Windows, it would actually perform better than ISAPI, due to the nature of threading within the Python interpreter, but for now, we already have something better than CGI, so that's what I'll stick with.

Issues I've seen

On a 64 bit Windows 2008 R2 server, I ran into a bit of trouble with Mercurial not being able to find its binary bits. See Mercurial has some modules in it that were written in C, because they're really hard core and mathy and they needed some extra speed to make Mercurial as fast as it really should be. In order to fix the "unable to locate module" business, I downloaded the Mercurial source, and lo, the authors had written pure Python versions of the C modules that were giving me grief. I tossed the pure python modules on the PYTHONPATH, and everything worked just fine.

I'm going to research this particular issue again, because I've learned a little more about how Python loads C code. It sure would be nice to know how to get Mercurial running at full tilt in IIS 7.5.

Tuesday, April 20, 2010

How to debug an ISAPI Extension with Visual C++ 2010 Express on Windows XP

I had a great deal of trouble figuring out how to debug an ISAPI extension I'm writing, and unfortunately, most of the information on the internet about ISAPI has gotten a little stale since the advent of .Net for web development.

On Windows, native code is moving further into the realm of witchcraft. Because witchcraft is scary, the Visual Studio team hid all the powerful witchery in 2010 C++ Express Edition. To turn all the really useful stuff back on, you have to go to Tools > Settings and select "Advanced Settings". Now you can get at the "Attach to process..." feature.

That wasn't what hung me up. I followed the instructions in the MSDN library, which basically say "Attach debugger to Inetinfo.exe, enjoy." But I got no joy. I got "The breakpoint will not currently be hit. No symbols have been loaded for this document." in the interactive debugger.

So, I enabled symbol download in Visual C++ by navigating through Debug > Options and Settings... and clicked on Symbols in the tree view on the left. Check the box next to Microsoft Symbol Servers, created a folder to hold whatever it downloads and put that path in the Cache symbols in this directory: box, and I was ready to go. Except it didn't help me out.

I loaded the Windows SDK, I loaded the Debugging Tools for Windows (which are inconveniently in a 650mb iso with the Windows DDK), I installed depends.exe (which apparently does not come with the Windows SDK anymore), I installed SysInternals ProcMon.exe. Of all the hundreds of megabytes of junk I downloaded and installed, none of the tools told me what I needed to know.

I'll save you the effort of installing all that stuff and just tell you that you need to put your IIS Applications into Application Protection: Low(IIS Process). If you don't, the ISAPI dll you are trying to debug gets loaded into some dllhost.exe process. If you leave the IIS Application set to Medium(Pooled) or High(Isolated), you will have to attach your debugger to one of the dllhost.exe processes. It is harder to figure out which dllhost.exe to attach the debugger to, so set it Application Protection to Low, THEN follow the instructions on the MSDN Library.

So, long story short, I was attaching the debugger to the wrong process. And you can't debug code that isn't loaded into memory. It seems a shame that that is the case, because if it weren't, then native code really would be witchcraft.



Sunday, June 7, 2009

Running Mercurial on Windows

Mercurial, if you haven't played with it yet, is one of the easiest, fastest source control mechanisms to hit Windows in recent years. If you have heard of Mercurial (or Hg, as some folks will type it, the symbol on the periodic table of elements for mercury), you may think I misspoke with that "Windows" bit, since only those long-haired open source hippie Linux people use that freebie distributed version control. Not true! As a matter of fact, for your own personal projects, Hg is probably one of the easiest, fasted version control mechanisms you can use for local-only development.


Getting started

The basic reference material you will need for Hg is available in the Mercurial wiki at http://www.selenic.com/mercurial/wiki/. Just so you don't have to read it, I'll continue below.

On your local Windows box, you will want to install TortoiseHg. If anybody has ever used the fine TortoiseSVN on Windows, you will be immediately familiar with the direct Windows Explorer integration with source control. Some of the more recent versions of TortoiseHg (0.7.6 is the latest as of this writing) include a full installation of the command-line version of Mercurial, saving Windows uses the hassle of finding and running another installer. You will eventually come up with a need for the command-line version of Hg, and you will want to become familiar with its simple commands anyway.

For Visual Studio users (Standard edition and above, sorry Express edition users. Out in the cold as usual.), you can install VisualHg to get that sweet, sweet development environment integration that the users of Visual Source Safe and TFS currently enjoy in their development experience. Once installed, you can enable the source control integration within Visual Studio by going to Tools->Options, then choosing "Source Control" in the tree view, and changing the "Current source control plug-in:" combo box.


As a side note, if you're still using VSS...please stop. SVN and Mercurial are, at the very least free, and are far less likely to corrupt your source repository. Also, if you aren't paying for each and every seat license of VSS (even if you put it on a server and share it), you're stealing from MS at the rate of about $390 a seat. Don't place your business and your code at risk by using VSS.


Beyond local development


Despite what some other ill-informed (yet admittedly old) blogs have implied, Mercurial can be hosted in IIS on Windows. Nobody's set out step-by-step instructions all in one place, but that is about to change.


For all versions of Windows


  1. Install Mercurial 1.2.1 on the server. The version that comes with TortoiseHg is not sufficient, it will not drive the CGI application.

  2. Install Python 2.5.4 on your server. Mercurial 1.2.1 components are compiled against that version of Python. Any other version of Python will cause a "Bad Magic Number" error on the server.

  3. Create a directory somewhere to hold the Mercurial source tree. Open a cmd window and navigate to whichever directory you want this source tree to be contained within, then type in
    hg clone http://selenic.com/repo/hg
    (robbed from Stack Overflow). This command will create a directory named hg, then download all the code in the Mercurial source repository into the hg directory. What we're after here is is the hgwebdir.cgi file, which is...a Common Gateway Interface app. Duh. And I'll bet you thought that cgi was dead.

  4. Create a directory on the file system (separate from any other web app files you have on your web server) to map in as a Virtual Directory in IIS. I used C:\inetpub\hgcgi\. Make sure this directory has read access in NTFS for the service account IIS (or your IIS app pool for Vista, XP x86-64, Server 03 and Server 08) is running under.

  5. Copy the hgwebdir.cgi file into your virtual-directory-to-be.

  6. You also want to extract the contents of the Library.zip file contained in your Mercurial installation directory to your virtual-directory-to-be. If you installed TortoiseHg 0.7.6, you will find this within the TortoiseHg installation directory.

  7. Copy the entire "templates" directory from the Mercurial installation directory to a "templates" directory within the virtual-directory-to-be.

  8. Create a file named hgweb.config. This is absolutely nothing like an Asp.Net web.config. Its more like an ini file from the days of old. And you have to actually add text to it. You want something that looks like this:

    [paths]
    MySourceCode = C:\Repositories\**
    [web]
    style = monoblue

    The [paths] segment is in the form = local path. You can list out repository locations manually, or you can do as above, and have it take all of the repos in a location. And yes, the ** is correct. Most of the other documentation around the web is unix-style fs specific, and suggest using the [collections] section as opposed to the [paths] section, but don't be surprised when it doesn't work on Windows. I never had any luck with using the [collections] section.
    And yeah, you want to change the style. Because you can.

  9. Once you get your repos to show up in the Mercurial cgi script, you can go into the repo's .hg directory, find the hgrc file therein and configure it. The syntax is the same as hgweb.config, and it allows you to set settings for the repository itself. Such as the style. Because you can. Details on the config file are here.


That's everything you must do that is common to all versions of Windows.


IIS 7 (Windows Vista, Server 08)

Just like everything else that is related to Vista, Microsoft changed the whole UI for the IIS Manager. This is good, because IIS Manager (just like the Component Services snap-in) in Server 2003 is a fussy, buggy, expletive-inducing wreck. IIS 7 does give you the ability to install Asp.Net "filters" (called a "Managed Handler" in IIS Parlance, called an HttpHandler in Asp.Net parlance) directly into IIS. Kinda cool really, since writing ISAPI filters requires that icky native code that seems to bamboozle so many people. Maybe someone will write a nice Managed Handler for Mercurial in the future, but for now, we have to use CGI.

  1. Install IIS. In Vista, its Control Panel->Programs and Features in "Classic View" or Control Panel->Programs in the new Vista view. Click on "Turn Windows features on or off." Expand "Internet Information Services", then select "Web Managment Tools" to install IIS Management Console. Select "World Wide Web Services", then expand "World Wide Web Services", then expand "Application Development Features" and make sure "CGI" is selected.

  2. Add your virtual directory for Mercurial. That's the one that has hgwebdir.cgi in it. Right click a web site (I just mapped it into the "Default Web Site", but any will do.), and choose "Add application" You could "Add virtual directory" and then upgrade the virtual directory to an application, but the virtual directory mapping has to be an "application." In the dialog, I set the alias to hg and the Physical path to "C:\inetpub\hgcgi". There is a nice "Test settings..." button here that will help you determine if your application directory is configured property. I never did get my authorization warning to go away, even though the CGI script would run.

  3. Map in the Python interpreter as a script engine. Click on your new application/virtual directory. In the center pane, double-click "Handler Mappings." Normally, if you wanted to execute Python scripts on the server, you would map the interpreter to *.py. But we're running a cgi app through the python interpreter. Click on "Add Script Map..." and put in *.cgi for the Request Path. Put
    C:\Python25\python.exe -u %s %s
    in for Executable. Give your mapping a name. I used Python 2.5. The %s %s mess above are substitution symbols (like printf(), or string.Format()) for arguments passed to the python interpreter that tell it what script it is executing on behalf of IIS. After that, click the "Request Restrictions..." button, and choose the "Access" tab. Pick the "Execute" option, so you can "Execute" the cgi application. Click OK to save. A dialog will pop up that says "Do you want to enable this ISAPI application?", which is a bit bizarre since you are enabling a CGI application, but you should say Yes.

  4. Enable the script engine for your application/virtual directory. Right click your new "Python 2.5" handler mapping, and select "Edit feature permissions...", and make sure all of the checkboxes there are checked.


Windows Server 2003/XP


  1. Buy Server 2008. I hear that if you get it on subscription, you get free upgrades. If you can't talk your boss into paying for the next scheduled maintenance on Balmer's V12 Vantage (and if he doesn't really have one, he probably should), make sure IIS is installed on the Server. Control Panel->Add or Remove Programs, then Add/Remove Windows Components. Select Application Server, and you should be set.

  2. Add your virtual directory for Mercurial. That's the one that has hgwebdir.cgi in it. Right click a web site (I just mapped it into the "Default Web Site", but any will do), and choose New->Virtual Directory. In the dialog, I set the alias to hg and the Physical path to "C:\inetpub\hgcgi". By default, in Server 03, virtual directories are mapped in as "Web Applications," but if it isn't set up that way, you can right click the virtual directory, and click the "Home Directory" tab. On the bottom of that tab, in the "Application settings" section, there should be a button labeled "Add." Once you click it, the virtual directory becomes a "Web Application." Also, while you're there, make sure the "Execute permissions:" drop down says "Execute."

  3. Map python in as the cgi script handler. Do this by right clicking the virtual directory, then selecting the "Home Directory" tab. In the "Application settings" section, click the "Configuration" button. On the "Mappings" tab, click the "Add..." button. For "Executable", put in the following:
    C:\Python25\python.exe -u "%s %s"
    And make sure you put the double quotes in exactly as shown. This is because IIS Manager is and won't take it without them. In "Extension", put ".cgi". Clear the checkbox that says "Verify that file exists", set the checkbox that says "Script engine."

  4. Enable Python as Web Service Extension (Server 03/XP x64). In IIS Manager, right-click the Web Service Extension to "Add a new Web service extension...". You can name the extension anything you want, I chose "Python 2.5". Click the "Add..." button. In "Path to file", put C:\Python25\python.exe -u "%s %s". It must exactly match the ".cgi" mapping on your virtual directory, or it won't work. Check "Set extension status to Allowed".


Point your browser to http://server/HgVDir/hgwebdir.cgi/ and enjoy. You should now be able to use Mercurial against that virtual directory. To secure your source control repository, you should set up SSL on your server, and mark your hg virtual directory to require SSL. Then set the virtual directory to use Basic security, as this is what the hg client tools support. Basic-over-SSL is pretty secure, since the SSL encrypted connection is established well before the Basic security credential exchange takes place.

To create new repos on the server, you'll want to clone them to the server from your workstation machine. The easiest way to do this is to hg serve from your workstation, then log onto the server and pull your new repo into the right place on the server from your workstation. After that, go into .hg in your server-side repo and configure your hgrc file. Once that is complete, you can synchronize your server repo with your workstation repo any time you wish.

And just in case you're wondering, the -u switch on the python interpreter places the interpreter in "unbuffered" mode, so results are sent to IIS immediately with no delay. Supposedly, for CGI, it improves performance.

Give hg a shot. Its effective and I've had quite a bit of success with it. Maybe you'll like it, too.



Saturday, May 30, 2009

Connection per request with Ado.Net

Say you have a service layer component that has reference to a DAL component. Say that the service layer calls two different methods on the DAL component, and that those methods need to be bound in one single transaction. How do you achieve this within the confines of a web application?

Since transactions are the domain of the service layer (business logic components...whatever), this should be pretty easy, right? Just start a transaction one way or another, call your methods, commit/rollback, and move right along. The devil is really in the details on this, however.

For the sake of this discussion, pretend that this code exists in the system:

'Highly Contrived Example
Public Class Stuff
Dim _Id As Long
Public Property Id As Long
Get()
Return _Id
End Get
Set(value As Long)
_Id = value
End Set
End Property

Dim _Thingy As Thingy
Public Property Thingy As Thingy
Get()
Return _Thingy
End Get
Set(value as Thingy)
_Thingy = value
End Set
End Property
End Class

Public Class Thingy
...Whatever goes here
End Class

Public Class DaoBase
Dim _Connection As IDbConnection
Public Property Connection As IDbConnection
Get()
Return _Connection
End Get
Set(Connection As IDbConnection)
_Connection = Connection
End Set
End Property
End Class

'This is a terribly designed component
Public Interface IDAO
Function SaveStuff(arg As Stuff) As Stuff
Sub UpdateThingyOnStuff(arg As Stuff)
End Interface

Public Class ServiceComponent
Dim _DAO As IDAO
Public Property DAO As IDAO
Get()
Return _DAO
End Get
Set(value As IDAO)
_DAO = value
End Set
End Property

Public Sub DoSomethingCool(ByVal someStuff As Stuff)
Using txn As New System.Transactions.TransactionScope
someStuff = DAO.SaveStuff(someStuff)
DAO.UpdateThingyOnStuff(someStuff)

txn.Complete
End Using
End Sub
End Class


I will be discussing the implementation of the IDAO interface.

In a web app, you don't really have any state between requests, except that which you can stuff into Session or stuff into a cookie. You can't really place a database connection object into Session. Well, you can, but when you have to switch from in-memory Session to, say SQL Session state, you're screwed. Nevermind having to figure out what to do about fixing the connection if it breaks down on you. And it scales like crap, too, because sessions may take a long time to become "abandoned" as far a the server is concerned, so you end up with database connections being held open that are idle.

You surely can't cram a connection object into a cookie, so that means that you have to open and close database connections within the scope of a single web request.

The wrong way

AKA, the way I used to do it.


Imports System.Data.SqlClient

Public Class DAO
Implements IDAO

Public Function SaveStuff(arg As Stuff) As Stuff
Using conn = New SqlConnection
conn.Open

Dim cmd = new SqlCommand
'Setup the command object here

cmd.ExecuteNonQuery
End Using
End Function

Public Sub UpdateThingyOnStuff(arg As Stuff)
Using conn = New SqlConnection
conn.Open

Dim cmd = new SqlCommand
'Setup the command object here

cmd.ExecuteNonQuery
End Using
End Sub
End Class

The problem with this DAL is that the connection doesn't stay open during the transaction. Using System.Transactions as I have done here, the ambient transaction elevates to a Distributed Transaction when you open the new connection in the second DAL call. This means you have to actually enable and configure the MSDTC, and it degrades performance.

If you use ADO.Net transactions instead of System.Transactions, you still have the same problem, except that you now have to keep up with a transaction object. With ADO.Net transactions, you could be creative and retain the connection when you pass in a transaction object, but you still have to come up with some strategy to close the connection when your transaction ends. You could place a Close() method on your DAL components that your service layer components could call, but it just seems so...hacky.

The right way

Open your database connection at the beginning of your web request and close it at the end.

...inside global.asax

Protected Sub Application_BeginRequest(sender As Object, e As EventArgs)
'Database vendor-agnostic code.
Dim connectionString = System.Configuration.ConfigurationManager.ConnectionStrings("sampleDbConnString")
Dim provider = System.Data.Common.DbProviderFactories.GetProvider(connectionString.Provider)
Dim connection = provider.CreateConnection

connection.Open

'The Context.Items collection is a per-request information store.
Context.Items("DbConnection") = connection
End Sub


Protected Sub Application_EndRequest(sender As Object, e As EventArgs)
Dim connection = DirectCast(Context.Items("DbConnection"), System.Data.IDbConnection)

If connection IsNot Nothing Then connection.Close
End Sub


Doing the above leaves only the question of how to get reference to the connection into the DAL components. Since you end up having to construct your DAL components at some point anyway (say, Page_Load), you could simply fetch the connection object off the Context.Items collection and place it on the DAL component.

...in Page_Load somewhere...
me.Service = New ServiceComponent
me.Service.DAO = New DAO
me.Service.DAO.Connection = Context.Items("DbConnection")


This gets you out of DTC elevation with System.Transactions, and you don't have to worry about opening or closing database connections anymore. With Ado.Net transactions, you do still have to fiddle with transaction enlistment, so you still have to pass in transaction objects (good reason to not use it, System.Transactions is easier to use).

For most common web apps, using connection-per-request and System.Transactions improves code quality and reduces the amount of code you have to write. That's good for everybody.