Data Access Layer for application

Question

I am working on a retail application where currently I'm using raw SQL like

insert into some_table values (Textbox1.Text, Textbox2.Text, ...)

and

update some_table set some_column = value

for save and update, but the problem is I need to write this everywhere that a save or update is needed.

Is there a better solution for handling CRUD operations, like a separate class to contain data access logic which would use methods in the data access layer to perform CRUD operations?

• JeffO: When you say "I need to write it everywhere when save and update arise" I'm not sure what you mean. You can have one method Customer.Save with the only 'update customertable' script in your entire app or is it you don't want to write separate sql to update each table/entity like: customer, invoice, receipt, return, etc?
• Mussammil: @JeffO, I meaned it every different form exactly like Customer ,Invoice,Receipt etc

Answer 1

Generally speaking, you are supposed to bottleneck where your actual CRUD operations are contained and use one interface to deal with it. Since this is the sort of thing that tends to change often as you need to develop more CRUD operations, you risk that you'd have to change each and every call to a method that you've changed or at the least, rethink how it gets called in the context of how you've changed it.

If you're talking about a small project, this is not such a big deal, however in larger projects, this gets very tedious very quickly and thus good structure becomes paramount. What I recommend you do is make an object representative of the operations you're performing on the database. In other words, if I have CRUD operations for writing "Student" table, then you create class Student which handles the actual CRUD calls. At that point, to update information pertaining to Student, you'd only have to, say, implement a method "Save" which updates the database using the information it currently contains.

At this point, you no longer have to worry about providing a Database connection, session, whether or not it should insert or update. Callers merely have to call "Save" and your class worries about that sort of logic. You'll find that not only does this simplify your code, but it allows you to wall off logic pertaining to the database from the rest of your program, thus keeping it cleaner in general.