The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Generic SQL builder

We build our SQL statements in our Data Access Layer (DAL). At the moment we build each SQL statement in a dedicated method (C#).

Here is a very simplified example of one method;

private string SQLExample(Data Data)
{
  return string.Format("SELECT * FROM {0} INNER JOIN {1}
                        ON {2} = {3} WHERE {4} = '{5}'",

                        FieldNames._TABLENAME,
                        FieldNames.COLUMN1,
                        FieldNames.COLUMN2,
                        FieldNames.COLUMN3,
                        FieldNames.COLUMN4,
                        Data.Data);
}

You can see that the above method only return the SQL that we will later apply to the database. We have methods for each different function required.

What I would like to have is a generic SQL builder that will be able to contruct different SELECT, INSERT, UPDATE, DELETE SQL commands.

The problem is that some SQL commands need to have 5 elements and other 10, 8 or 3. etc etc.

How does everyone construct SQL in a tidy, efficient manner?

I welcome your input.
See Qual
Sunday, August 17, 2008
 
 
stuff without joins is easy

we have a table definition object for each table (which we build from the database metadata using a script to generate the classes)

then the base dal code, uses that table definition to generate insert, update, delete statements for each table

when it comes to multiple tables, they tend to be selects only, so we just hardcode them, as required
bumperbox
Sunday, August 17, 2008
 
 
This doesn't answer your question, but it could be very important for your application scalability and security.  Search for "bind variables" and "sql injection".

In my experience, managing generic sql's will become overly complex.  I think reusable sql is more important to have than generic sql.
Chi Hoang
Sunday, August 17, 2008
 
 
I agree with Chi.

One approach I use is to define the types of things you are going to do.  For example "Display Customer's Shopping Cart".  The database part of that is a procedure call to the db and that call returns the objects needed.  In this example, it is probably a Customer object and their Items to be purchased.  In the stored procedure the relevant data is retrieved and passed back up.  For example a CustomerShoppingCart might consist of
CustomerInfo (eg Name, id, address, etc.),
Shoppingcart  items (a shopping cart item is the SKU, the Description, price, quanity, etc.  So you would have 0 to N of these)

Then the Program layer above deals with how to display that.

THe definitions of what the stored procedure do are around the functionality and the details of how the data is stored and arranged are invisible to the application.  If we need to alter the schema, we can without impacting the code. (as long as the functionality is the same.)

We use Oracle and present the code with user defined types and collections of types.  We store those types in traditional tables.  We could have tables of types, but there are challenges with that approach that don't gain us anything. (and would cost us a lot )

Also each type has a dump method.  The dump method returns a formatted string of the members of the type.  This is useful when catching unexpected errors.  In the stored procedure error handler we can call the types dump function and store the results in an error table and return the error to the application.  This helps a lot when something unexpected occurs and you want to reproduce the error to fix it.
Jim Send private email
Sunday, August 17, 2008
 
 
+1 Chi

If that is how you create SQL then you potentially have far bigger problems than working out how to create SQL in an elegant manner.
Arethuza
Monday, August 18, 2008
 
 
<< If that is how you create SQL then you potentially have far bigger problems than working out how to create SQL in an elegant manner. >>

Well do you care to enlighten me ... or do you just throw around statements like that all the time?
See Qual
Monday, August 18, 2008
 
 
The others have said it but they haven't really been explicit so I will be.

DO NOT RETURN SQL STATEMENTS AS STRINGS. IT CAN'T BE DONE IN A SECURE MANNER!

Concatenating strings together to make SQL statements results in SQL injection vulnerabilities. I just can't understand why this isn't well known by now. Why do we have to keep repeating this over and over again out here and everywhere else on the Internet?

Your method should return an object that represents a SQL "command". This includes command text like "select * from customers where cust_id = ?" along with a collection of SQL parameters to be applied wherever there are question marks in the command string. Returning a simple string to represent a database query simply doesn't cut it.
kill me now
Monday, August 18, 2008
 
 
And before someone goes off on how you can just "escape quote marks" I will respond by saying that it isn't enough. Parameterized SQL is the only way to stop SQL injection.
kill me now
Monday, August 18, 2008
 
 
++ Kill me now

In absolute agreement.  Also more performant and scalable. And recommended by 10 out of 10 RDBMS's.  Whiter brighter teeth, fresher breath.  Okay, maybe not the last 2.
Jim Send private email
Monday, August 18, 2008
 
 
==> How does everyone construct SQL in a tidy, efficient manner?


I use a code generator (CodeSmith) with a highly specialized set of templates that generates me, automagically, 95% of the stored procedures I need, and 99% of the C# code I need for all data access.

Easy. Tidy. Efficient.
Sgt.Sausage
Monday, August 18, 2008
 
 
Look at using an ORM like nHibernate, it will drastically cut down the number of queries you need to write. It's the easiest way to handle database refactoring without worrying about lots of code changing.

The code generation/stored procedure approach tend to become maintenance nightmares so only use them if it's a small project.
flukus
Wednesday, August 20, 2008
 
 
"The code generation/stored procedure approach tend to become maintenance nightmares so only use them if it's a small project. "

Agreed. I've never seen a code generated DAL that wasn't a maintenance nightmare. I'd like to see what Sgt. Sausage calls "Tidy" and "Efficient".  ;)
dood mcdoogle
Wednesday, August 20, 2008
 
 
I was just looking into NHibernate. We are developing a C# app that will access a FireBird Database.

Is this suitable for NHibernate. I am seeing stuff about Java on their website?

Sorry, but I have only just been looking at the site 1 min ago and am very green behind the ears on NHibernate.

Your thoughts!

Thanks also for the suggestion.
See Qual
Thursday, August 21, 2008
 
 
Hibernate is a java project. nHibernate is the .net version.

Not sure about firebird, you would have to check the documentation.
flukus
Thursday, August 21, 2008
 
 
@ kill me now

> Parameterized SQL is the only way to stop SQL injection.

Utter cobblers. It is simply ONE WAY of several, not the ONLY way.
Tony Marston Send private email
Wednesday, August 27, 2008
 
 
"Utter cobblers. It is simply ONE WAY of several, not the ONLY way."

Maybe you'd like to enlighten us on the other ways then? I didn't know that there were "several" other legitimate ways that always worked.
uggh
Wednesday, August 27, 2008
 
 
"Maybe you'd like to enlighten us on the other ways then? I didn't know that there were "several" other legitimate ways that always worked."

If you are checking all of the variables that are coming from a website and into your database for characters that would cause an injection attack (and removing or escaping them), How is this any more insecure than bound queries?
marvin lee
Thursday, September 04, 2008
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz