Why does using Microsoft Sql Server Management Objects (SMO) in the DAL require references to SMO dlls in a project which references the DAL?

Question

I've searched for an answer on google using:

"The type 'Microsoft.SqlServer.Management.Smo.Server' is defined in an assembly that is not referenced."

Why does using Microsoft Sql Server Management Objects (SMO) in the DAL require references to SMO dlls in a referenced project?

using sql smo in referenced projects

sql smo in layered solutions

sql smo reference requirements

and probably a few others and have not found a solution or explanation to this issue.

Admittedly, I'm only a journeyman googler, so if someone wishes to power level me and point the way to an existing resource, I'll gladly go spelunking from there.

Here's my set up:

I've got a layered solution: DAL, Business Logic, Services, UI. There's a hosts project that hosts the services. I'm actually using the VS2010 extension layerguidance.codeplex.com, which is quite nice, to set up all these projects. I'm using SQL Server 2008 Express and SMO v 10. All solution projects are referenced using Project References. All projects compile to a common top level Bin folder.

Now the problem:

Among the classes in the DAL I have an SmoTasks class which handles interfacing with SMO objects and a Utilities class which abstracts from SmoTasks and provides acces to its functions without requiring any SMO objects for parameters, so that referencing projects (read: Business Logic Layer) can interface using non-SMO types. All is well in the DAL, it compiles fine, the methods pass their tests - it feels good about its place in my world. Then in the BLL I have a component which handles using the Utilities class to perform database configuration for the application which will be exposed via the services. The BLL uses a project reference to the DAL and sees the DAL classes (a la intellisense) as expected. When I compile though, I get:

The type 'Microsoft.SqlServer.Management.Smo.Server' is defined in an assembly that is not referenced. You must add a reference to assembly 'Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'.

The code in BLL looks like this:

 public bool CreateTables(string connectionString)
    {

        bool result = default(bool);


        // Data access component declarations.
        Utilities utilities = new Utilities();

        // Step 1 - Calling CreateTables on Utilities.
        result = utilities.CreateTables(connectionString);

        return result;

    }

The line the error points to is:

result = utilities.CreateTables(connectionString);

I could, obviously, add the SMO references to the BLL and then the BLL would be happy, but that violates my design goal of loosely coupled projects. If I add the SMO assemblies to the BLL, it compiles and then referencing BLL objects in the services layer doesn't cause a complaint. My question is, why? More specifically: Why does the BLL need references to SMO when the Utilities class in the DAL already abstracts away the SMO types?

What I want is everything database related to live in the DAL (duh) and only business logic in the BLL (double duh). Is there another way to achieve this using SMO that I have overlooked?

Thank you for your valuable time and answers, I humbly await your responses

Edit: I've adjusted my solution based on suggestions by Chris, verified that I'm using project refs (I am), readded the references to SMO in the DAL using Muse.VSExtensions to add GAC reference, before I had been browsing and adding manually, then I went ahead and set Copy Local = True for those assemblies just to be doubly sure they're around... but I'm still stuck with this annoying compile error.

Answer 1

It's depressing to answer your own question with a mea culpa, "I'm an idiot"... but, well, I'm an idiot:

In Utilities there was an overload for the offending method which did contain an Smo.Server parameter. I removed that overload (an artifact from testing before refactoring) and voila, problem solved/idiocy confirmed! The interesting thing I learned here is that using the other methods of the Utilities class, which did not have overloads containing Smo objects, was absolutely fine, meaning even with a function in the Utilities class which required an Smo object for a parameter, as long as I didn't call that method or one of its overloads, the references resolved perfectly without a hitch. The new question I have, is why? Why does that overload's existence matter for reference resolution if I call another version of that function from a project higher in the dependency chain? Is there some internal loop where it goes over all versions of a function checking references if any version has been called...

Answer 2

I think this boils down to how things are referenced in your solution. So I'm going to take a couple guesses.

It sounds like your DLL references the DAL as an assembly instead of as a project reference.

During compile time Visual Studio copies everything it thinks is necessary to the projects BIN directory. If you reference an external DLL (DAL) then it will copy that DLL only to your BLL's BIN directory.

What you need to do is get it to copy the SMO assemblies as well OR have those SMO assemblies available through the GAC. Personally, I don't like GAC'ing things, so I'll ignore that.

There are three ways of doing this. The easiest is to simply add a reference to those other assemblies to your BLL. Obviously that's not what you want.

The second way is to reference the DAL project as a project reference. This will allow Visual Studio to detect the extra dependencies and copy them accordingly. This is also not exactly what you want as well.

The third way is to copy them as part of a build step. Right click on your BLL project and go to Build Events. In the Pre-build event command line put in the commands to copy the necessary SMO files to your BLL projects BIN directory.

You'll have to do this again for the main service project as well.