Database Access

Question

I want to write a new application that will use a DBMS. In the design stages of this application and weighing the options I found out that the there will be a lot of database accessing which means many queries. My biggest concern with developing this system is the unwieldy nature of SQL select, update, and delete statements scattered around in the code. Are there any language syntax extensions (or scripting languages) beyond Linq (which I don't like because of the awkward syntax) that are available today? I am well acquainted with Progress ABL (their 4GL whatever) for development and I love the syntax it has but its deployment model and dependencies for my type of application are to heavy and costly.

The system I'm requesting must be compatible with either PHP or C#.

The Database Management Systems that I'm considering to use are SQLite, MySQL, or MSSQL (Compact or Standard).

Answer 1

if you're using c# you could use something like Subsonic for database access. It will handle your Data Access and in a lot of cases you'll be able to avoid writing SQL throughout your code.

Answer 2

Any database frontend will prevent you from having SQL in your code, but there still needs to be data access somehow, and usually you end up with the proprietary junk you were upset about linq with. I have used Hibernate before, it is a great and powerful java library that talks to your database for you. If your tasks are small then raw access is not really a bad idea, having sql in your code is bad if the database is ever going to change though, which it probably will.

Answer 3

You might want to consider an ORM like nHibernate, which would work with or without LINQ.

Answer 4

just don't scatter SQL around in your code. write a proper model layer.

even if you're not using an MVC or ORM framework, using proven design approaches always pay up. simply write a list of the conceptual objects stored in your database, with all the operations you want to perform on these, and write all the functions for that in a single file (or a file for each conceptual object). the rest of the program shouldn't have a single SQL command in it, everything should be done using that model layer.