Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Trying to incorporate a Database Abstract Layer... Expand / Collapse
Posted Tuesday, July 9, 2013 9:15 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, December 1, 2016 10:04 PM
Points: 245, Visits: 933
I have a goal of creating a Database Abstract Layer (stored procedures and maybe views) as long term project, in order to allow the flexibility in modifiying the table structure, access and location of tables. However many of the developers I work with use these database "wizard" tools (E.g Entity Framework, LINQ2sql) that encourage direct table use and make it difficult to alter the structure of the database schema. If I present the idea of the DAL, i'm worried the biggest push back will be that they will no longer be able to use these tools as effectively as they use to, and will make their development less efficient. Another problem is they have the mindset that the ER diagram = the database diagram, and for advance optimization of data access, many times that is not the most effective model.

I wanted to know if anyone worked in a place that used EntityFramework, Linq2Sql, etc in a database environment that does not allow direct access to tables, and how well do they work with just Stored procedures and/or views.
Post #1471693
Posted Wednesday, July 10, 2013 8:51 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 10:39 AM
Points: 544, Visits: 3,412
I think that "database abstraction layer" isn't really what you think it is, or at least your view is incomplete. Microsoft's Entity Framework, as well as other ORM tools, are "database abstraction layers".

The purpose of a database abstraction layer is to provide an interface to your data store -- it isolates the data store behind a consistent API, and you code against that API instead of directly against the data store. In theory, by using a DAL, you don't have to care about the actual data store, you code against the DAL and the DAL handles all the gory details of accessing the data store, and it "just works". In reality it's rarely that simple, but that's the goal at least.

In the sense that stored procedures and views in a database can provide a consistent API, they could be considered a DAL, but generally that's not what a programmer would consider to be a DAL.

Whether or not you restrict database access to sprocs and views, you'll still have issues with altering the *structure* of the database -- it won't make things any easier, because a structure change will usually require a correspondeing model change in code, because a structure change changes the interface in the DAL. But using sprocs and views gives you much more flexibility to alter the underlying *logic* for data storage and retrieval without requiring model changes in code (as long as a change to the logic does not change the interface). Sprocs and views also help with management of security.

That said, current ORM tools (including current version of Entity Framework) can work with stored procedures and views. I'm doing it myself right now, in fact (EF 4.x...accessing SQL Server 2012 database via stored procs and views, working fine so far but it takes a bit of extra effort). Other ORM tools have similar capabilitites from what I understand but I don't have any experience with anything else at the moment.

Post #1472190
Posted Thursday, July 11, 2013 8:20 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 7:49 AM
Points: 1,069, Visits: 1,888
I work in a small shop (just 2 of us), and so having direct access to tables & views is SOP. Sorry.

Kindest Regards,

Connect with me on LinkedIn.
Post #1472602
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse