SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Trying to incorporate a Database Abstract Layer...


Trying to incorporate a Database Abstract Layer...

Author
Message
Gabriel P
Gabriel P
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1080 Visits: 947
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.
dmbaker
dmbaker
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1345 Visits: 3583
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.



Rod
Rod
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7002 Visits: 2175
I work in a small shop (just 2 of us), and so having direct access to tables & views is SOP. Sorry.

Kindest Regards,Rod
Connect with me on LinkedIn.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search