Data layer abstraction, what to do?

dnme

I'm building a small application using C# on .Net 2.0. The application uses a local Sql CE database. I have abstracted the data layer out to a DLL. This handles all the reads and writes to the database.

Here's where I have come to a slight halt. I have methods in my DAL library such as Execute, This is overloaded to take either a sql string or a SqlCeCommand object. Ideally I would like to use SqlCeCommands rather than strings of sql as SqlCeCommands allow me to use parameters which are safer, neater, easier to read and write etc. So from my application, I might save an object like this.....

        private int SaveResource (Resource r)
        {
            string sql = "UPDATE Resources SET " +
                    "ParentID=@ParentID,ProjectID=@ProjectID,Name=@Name,Type=@Type " +
                    "WHERE ResourceID = " + r.ResourceID;

            using (SqlCeCommand com = new SqlCeCommand(sql))
            {
                com.Parameters.AddWithValue("@ParentID", r.ParentID);
                com.Parameters.AddWithValue("@ProjectID", r.ProjectID);
                com.Parameters.AddWithValue("@Name", r.Name);
                com.Parameters.AddWithValue("@Type", r.Type);
                DAL.Execute(com); 
            }
        }

But if I wish to send a SqlCeCommand object from my application to my DAL library, then this means I have to add a reference to SqlCe in my application. This is something I am trying to avoid. Surely it would be best practice to restrict any knowledge of SqlCe to the Dal lobrary. My application should not need to know about it nor reference it.

So, what is the best way to procedd? Is there a better way to send the components of a given object to my DAL Library for updating?

mewso

I don't see the issue to be honest as this stuff is in the GAC. You are also accessing parameters which are part of System.Data so you are essentially creating an ado.net DAL which means referencing these things isn't really an issue. Using the DbCommand object I suppose is a better choice and probably what you are looking for if there is a chance you will switch to another ado.net provider.

Creating a completely provider agnostic DAL would mean lots of abstractions which is not necessary when you have so many free options from fully functioning orms, micro-orms and so on out there anyway.

dnme

Oh yea I know and appreciate all you are saying. But just messing around here as an academic exercise and wondering what others might do with similar structures.

The SqlCe reference is a unique little fella. You cant just pass it any old parameters as far as I know and the reference has to be imported specifically etc.

mewso

You can do it all in your DAL though rather than your application. I've written a simple DAL implementations before with AddSqlLine functions, AddParamater functions and then the Execute function gets the sql from an internal stringbuilder, creates the specific command object, adds parameters to it from the internal list of paramaters and executes. Your application simply uses the object. Simple example:-

public class Dal   {
    private SqlCeConnection _conn;
    private SqlCeCommand _cmd;
    private StringBuilder _sqlText;

    public Dal(string connectionString)   {
        _conn = new SqlCeConnection(connectionString);
        _cmd = new SqlCeCommand();
        _cmd.Connection = _conn;
        _sqlText = new StringBuilder();
        _parameters = new List<DbParameter>();
    }
  
    public void AddSqlLine(string sqlLine)    {
        _sqlText.Append(sqlLine);
    }

    public void AddParameter(string paramName, object paramValue)    {
        _cmd.Parameters.AddWithValue(paramName, paramValue)
    }

    public void Execute()    {
         _cmd.ExecuteNonQuery();
    }

    public void CloseConnection()    {
        _conn.Close();
    }
}

var myDalObject = new Dal("myconnectionstring");
myDalObject.AddSqlLine("UPDATE Resources SET ");
myDalObject.AddSqlLine("ParentID=@ParentID,ProjectID=@ProjectID,Name=@Name,Type=@Type ");
myDalObject.AddSqlLine("WHERE ResourceID = @ResourceId");
myDalObject.AddParameter("@ParentID", r.ParentID);
myDalObject.AddParameter("@ProjectID", r.ProjectId);
myDalObject.AddParameter("@Name", r.Name);
myDalObject.AddParameter("@Typr", r.Type);
myDalObject.AddParameter("@ResourceId", r.ResourceId);
myDalObject.Execute();
myDalObject.CloseConnection();

You can even make your dal object implement IDisposable closing your connection when disposing so you can wrap it in a using statement in your application.

shootermacg

Can I just say, unless you are looking to leave and still have to go back as a contractor, make all your sql no matter how trivial a stored procedure.
It's a trivial thing to change the contents of a stored procedure, but adding sql to your code means any small changes mean code releases, which I know everyone hates ^ ^. Not only that but if someone actually starts work on more dev and you release went tits up because of some sql, things will be starting to get a bit more complicated.

Another more relevant point is DALs should preferably be self contained (this is a basic OO premise), so by all means pass parameters and strings to it but let it handle the actual database related work, the calling app shouldn't be half arsing it.

dnme

shootermacg wrote: »

Can I just say, unless you are looking to leave and still have to go back as a contractor, make all your sql no matter how trivial a stored procedure.
It's a trivial thing to change the contents of a stored procedure, but adding sql to your code means any small changes mean code releases, which I know everyone hates ^ ^. Not only that but if someone actually starts work on more dev and you release went tits up because of some sql, things will be starting to get a bit more complicated.

Another more relevant point is DALs should preferably be self contained (this is a basic OO premise), so by all means pass parameters and strings to it but let it handle the actual database related work, the calling app shouldn't be half arsing it.

Perhaps have a read of the thread!

1. Sql Ce (no stored procs)
2. Academic exercise, just messing around

shootermacg

dnme wrote: »

Perhaps have a read of the thread!

1. Sql Ce (no stored procs)
2. Academic exercise, just messing around

Oh my bad, apologies ^ ^

Colonel Panic

I know it's a moot point, but why would you want to use stored procs in lieu of SQL queries and why would you need to change them? And why is changing them better than updating software? Would you still track stored procs in source control? How would you debug them? Would you unit test the logic? What if some smart arse DBA wanted to "optimize" it for you?

Half the time people use stored procs where triggers and constraints on the database would do the same job, the other half, they try to use them in some procedural way when you should use a proper high level programming language instead.

The only real argument for moving stuff to stored procedures would be performance reasons and doing that outright stinks of premature optimization.

shootermacg

Colonel Panic wrote: »

I know it's a moot point, but why would you want to use stored procs in lieu of SQL queries and why would you need to change them? And why is changing them better than updating software? Would you still track stored procs in source control? How would you debug them? Would you unit test the logic? What if some smart arse DBA wanted to "optimize" it for you?

Half the time people use stored procs where triggers and constraints on the database would do the same job, the other half, they try to use them in some procedural way when you should use a proper high level programming language instead.

The only real argument for moving stuff to stored procedures would be performance reasons and doing that outright stinks of premature optimization.

Its a case of simple economics, it takes 1 person to rollback changes to a stored procedure.

In the case of a code release we need to employ initial testers, business testers, the server admin, and set time aside for regression testing.

You also have topackage up the code, transfer it to a secure location, ensure file access for the parties concerned. All of this happening outside of business hours when you really want to be at home ^ ^. Then there all the paperwork involved with the change management.

A lot of issues can arise in code releases, I've had incomplete file transfers, registry issues and morons who cant follow simple deployment instructions on critical systems.

Use stored procedures, they make life easier.

Colonel Panic

Easier is not always better. Nothing you've said addresses the negative aspects of using stored procedures!

Still, if it works for you, so be it!

Giblet

I use Stored Procs, and versioning is simply scripting changes, either the SP, or entire database itself. You shouldn't be doing logic in your SPs, just simple CRUD either 1:1 to an object or container, or batching for use in several (This is especially true if you are using an ORM that depends on SPs). It's also quite difficult for a dev to deploy script changes over code in a lot of cases, but this of course depends on whether or not you've given all devs access to sa or a locked down account. Again, they should only be used for simple CRUD, nothing else. If you are writing logic that goes beyond a "branch on exist" then you are going too far.

markpb

shootermacg wrote: »

Its a case of simple economics, it takes 1 person to rollback changes to a stored procedure. In the case of a code release we need to employ initial testers, business testers, the server admin, and set time aside for regression testing.

You also have to package up the code, transfer it to a secure location, ensure file access for the parties concerned. All of this happening outside of business hours when you really want to be at home ^ ^. Then there all the paperwork involved with the change management.

What I read from your post is that you follow process for code changes but lash DB changes in willy nilly? That's not a benefit of code over SPs, it's a benefit of being crazy!

Colonel Panic wrote: »

The only real argument for moving stuff to stored procedures would be performance reasons and doing that outright stinks of premature optimization.

Giblet wrote: »

You shouldn't be doing logic in your SPs

I think this boils down to opinion - there are always situations where putting logic in the DB makes sense. Personally I dislike deploying DBs compared to deploying code but I still see the benefit in using the DB more if it makes sense.

For one, there are performance reasons for using SPs. We use them almost entirely for the core part of our transactional system because it keeps the logic close to the data.