I should clarify:  in this alternate design, there would not necessarily  
be any reason for the buildings-specific class to be derived from the base  
StoredProcedureBase class.  It would be a stand-along static class, unless  
you decide that the base class can also be static, in which case it might  
still make sense to derive from that class.

Pete

On Apr 6, 12:48 am, "Peter Duniho" <NpOeStPe...@nnowslpianmk.com>
wrote:

That hadn't occurred to me, but you're right. (Sometimes, I get so
blinded by the details that I forget about some of the more common
sense issues.) It makes sense that this class, which invokes a stored
procedure to retrieve buildings, would know what a Building looks
like. The coupling between data model classes and the stored procedure
classes to retrieve them from the databases is likely an acceptable
coupling (provided that it doesn't break the private data barrier).

Actually, they don't. The database connections are handled by a
separate class, ConnectionGenerator (a factory). Further, the
ConnectionGenerator class doesn't know the settings; those are stored
in the application's configuration file, and passed to the
ConnectionGeerator during startup. GetOpenConnection() is one override
that uses the default settings to return an open database connection
to the caller. The retrieval of a database connection always takes
place *outside* of the stored procedure class, nominally in in a data
access class (BuildingDAC, for instance). The stored procedure classes
themselves always take either a SqlConnection or a SqlTransaction as
an argument to the Execute method, which is then forwarded to the base
class constructor so that the internal Command object can be properly
configured.

Now that's a valid concern. Here's where I was coming from.

I noticed early on that .NET doesn't provide a standard object to
simplify access to stored procedures. But the steps to invoke one are
typically the same (shown here for retrieving a set of data with a
data reader):

1.)  Create a connection
2.)  Create a command
3.)  Create a parameters
4.)  Assign the SQL statement
5.)  Set the commandtype
6.)  Execute the command and get the data reader
7.)  Do your work with the reader
8.)  Dispose of the reader
9.)  Dispose of the command
10.) Dispose of the connection

That's why I wrote the class to wrap this stuff. And it made a huge
difference. I didn't have to rewrite that same, monotonous code for
stored procedures everywhere in my code. And if a parameter changed in
a sproc, it was a lot easier to propagate the changes. But I digress.

The first version of my stored procedure base class didn't use the
Private Constructor/Public Execute model; instead, it used
instantiable stored procedure classes, with one property for every
parameter. In that model, however, you had to write a lot of code to
represent a stored procedure. And the code to invoke the stored
procedure was bloated as well.

For instance, the DAC layer function looked like this:

Public Function GetBuildings(ByVal siteId As Integer) As
BuildingCollection

   Dim buildings As BuildingCollection
   Dim building As Building
   Dim connection As SqlConnection
   Dim procedure As GetBuildingsProcedure

   Try
     connection = ConnectionGenerator.GetOpenConnection()
     procedure = New GetBuildingsProcedure(connection)
     procedure.SiteId = siteId
     reader = procedure.ExecuteQuery()
     buildings = New BuildingCollection
     Do While reader.Read()
       bulding = New Building
       Store(reader("AcceptsBuilding"), False,
building.AcceptsAdmissions)
       Store(reader("Description"), False, building.Description)
       Store(reader("ID"), -1, building.ID)
       Store(reader("SiteId"), -1, building.SiteId)
       Store(reader("Name"), String.Empty, building.Name)
       buildings.Add(building)
     Loop
   Finally
     Disposer.DisposeOf(connection)
   End Try

   Return buildings

 End Function

(This is a poor example, since this stored procedure only takes one
parameter. We have some stored procedures that take a lot of
parameters, especially those that update data in the database.) The
stored procedure class would then simply pass the parameters through
to the Parameters collection on the internal Command object in the
base class.

Through refactoring, I found that there were several occasions where I
wanted to invoke the same procedure from several different DAC
classes. (This has proven to be the case for many stored procedures in
the system.) The steps required to invoke the procedure were the same.
The logical place to put the code was in the StoredProcedure class
itself.

Now here's the interesting bit: *Not all stored procedure classes in
this system use the newer model.* To resolve the issue, a new base
class was written (StoredProcedureBase) that derives from the older
stored procedure base class (the incorrectly named StoredProcedure).
Both classes are abstract (marked MustInherit in VB). The newer stored
procedure classes inherit from StoredProcedureBase. The older stored
procedure classes still use the older StoredProcedure class, and must
be instantiated.

However, when it comes time to add new stored procedures to the
system, it takes a hell of a lot less time to add them using the newer
model. There's a lot less code to write, simply because I don't have
to write a property for each parameter, nor do I have to write code to
populate the properties from within the DAC layer. The Execute method
validates the parameters and throws exceptions if any of the
parameters are invalid.

So yes, it's a class. The base class (StoredProcedure) maintains
state, but the derived classes (StoredProcedureBase and
GetBuildingProcedure) do not. But the class abstracts away the details
of the setup of the call. The static (Shared) Execute method invokes
the private constructor to ensure that the stored procedure class is
created correctly, and then disposed of properly. That, right there,
is a huge boon: it ensures that Dispose is called on the stored
procedure and that the internal Commmand is properly garbage
collected.

It's a long, messy story. I just wanted to make sure that the class
instances were properly created, that the parameters were set up
right, that the Execute method returned typesafe values, and that the
internal objects were always garbage collected.

Thanks very much for your feedback! It's appreciated!

Mike

Hiya sloan,

Thanks for the pointer. I actually looked at both of these solutions.
Unfortunately, the vast majority of the code in question was generated
by another tool. There are 600 or so classes generated to do *exactly*
what you're describing. That is, get the data from the stored
procedure, shuffle it into a typesafe object (or typesafe collection),
etc. Essentially, every time the database schema changed, the tool
would regenerate the stored procecure classes, the data model classes,
and the data access classes so that the code stayed in synch with the
database. (It used base classes for the generated code, and provided
stub derived classes for you to provide extensions.)

Now, I don't mind the typesafe nature of the classes. Far from it. I
*like* the typesafety. I also *like* the fact that I don't have
DataSets all over my application. (Let's not get into that; suffice it
to say that I think a DataSet is overkill when you know exactly what
you want out of the database.)

Anyway, the application is now two years old, and the amount of
generated code makes a change of architecture on that scale
impractical. Inserting a derived class into the inheritance chain
wasn't, and it solved a good number of problems. The code is fast,
typesafe, and very efficient at garbage collection.

Now, future projects, built from scratch...that's a different story.
But those applications will likely be done in .NET 2.0 anyway.

Mike