What coding tricks have you used to avoid writing more sql?

Question

This question was suggested by Kyralessa in the What is your most useful sql trick to avoid writing more sql?. I got so many good ideas to try from the last question, that I am interested to see what comes up with this question.

Once again, I am not keeping the reputation from this question. I am waiting 7 days, for answers, then marking it wiki. The reputation that the question has earned, goes into a bounty for the question.

Ground Rules:

• While it is certainly reasonable to write code, to move processing from SQL into the code to address performance issues, that is really not the point of the question. The question is not limited to performance issues. The goal is less simply less sql to get the job done.

• Communicate the concept, so that other users say "Oh Wow, I didn't know you could do that."

• Example code is very useful, to help people that are primarily visual learners.

• Explicitly state what Language you are using, and which dialect of SQL you are using.

• Put yourself in your readers shoes. What would they need to see right there on the screen in front of them, that will cause an epiphany. Your answer is there to benefit the reader. Write it for them.

• Offsite links are ok, if they appear after the example. Offsite links as a substitute for a real answer are not.

There are probably other things to make it nicer for the reader that I haven't thought of. Get Creative. Share knowledge. Have fun showing off.

[EDIT] - It looks like there hasen't been any activity in a while. 5 votes = 50, so there is the bounty, and it has been wikified.

Answer 1

Generate SQL SPs, Views, etc. from the metadata in INFORMATION_SCHEMA. That code generation can then be augmented with custom code.

If a number of SPs will do similar things, for ease of a single point of maintenance, I will generate dynamic SQL instead.

All these things results in less SQL code, and more code that is re-used, and so better tested - like any library.

Here's an example of code generation to avoid writing SQL

Answer 2

If you want to avoid writting SQL use an ORM such as nHibernate, or one of the Microsoft offerings Linq to SQL / Entity Framework

This is even better then using a generator since you won't need to rerun the generators, and if you use Fluent nHibernate you can enable Configuration via Convention and not even maintain a mapping file / class.

Answer 3

Language: C#/ VB.NET.

I currently can write a DB backed system without writing any SQL at all. My DAL uses the POJO's class definitions to generate SQL on the fly. Example:

SearchCriteria sc = new SearchCriteria();
sc.AddBinding("Customer_id", "ALFKI");
List<Entity> customers = SQL.Read(sc, new Customers());

The code above will return a list of Customer instances matching Customer_id to "ALFKI". The DAL connects to the db, builds the SQL, executes it, instantiates new objects, populate them and send them back. When you are done changing the objects, simply call

SQL.Write(customer);

to have all changed items updated back to the db - mind you, only the ones that changed and only the columns that changed.

Added bonus: it supports SQL Server, Oracle, Informix. Client code never has to change.

Answer 4

Where I work we've done several things to reduce SQL and to reduce the associated overhead of using SQL in Java. (We run Java with MSSQL, MySQL, and Oracle).

The most useful trick is to use Java's setObject method for binding parameters. This, combined with Varargs, lets you write a utility method for executing SQL:

DBUtil.execSQL(Connection con, String sql, Object... params)

Simply iterate over the parameters and use statement.setObject(index, param[index-1]). For nulls you use setNull(). We've extended this concept for queries, with a getResultSet method; the wrapped ResultSet object also closes its statement, making it easier to do resource management.

To reduce actual SQL code written, we have a query building framework that lets you specify a bunch of columns and their types, and then use this to automatically specify search criteria and output columns. We can easily specify joins and join criteria and this handles most of the normal cases. The advantage is that you can generate a report in about 10 lines of code, including different query parameters, sorting, grouping, etc. The code is too complex to include here.

I've also used Oracle's ALL_TABLES and ALL_TAB_COLUMNS tables to generate SELECT statements; another trick I've used is using the ResultSetMetadata to analyze the table:

ResultSet rs = DBUtil.getResultSet(con, "SELECT * FROM " + someTable);
ResultSetMetaData rsm = rs.getMetaData();

boolean first = true;
for (int i = 1; i <= rsm.getColumnCount(); i++) {
  String col = rsm.getColumnName(i).toUpperCase();
  // do something with the column name
}

This makes it easy to generate certain kinds of statements; in this case we have an active table and an archive table and we are moving records from one to the other. Without getting into a debate about using an archive table, the Java code I've written lets me modify the two tables without having to modify the archiving script.

Another trick we use is to use constants for all our table and column names. This makes typing out SQL a little tedious but it allows us to (among other things) generate SQL easily for tables with similar or identical construction. Since we use constants to define the column names the code actually enforces that the the identical columns have the same name. Using constants also lets you find references to a particular column, thus allowing you to examine other SQL statements that may be related to the work you are doing. This lets us re-use SQL from other modules, instead of blindly re-writing the same statement again.

Answer 5

I recommend creating shared methods that focus on task, and use simple SQL elements, without having to write sql. I use Subsonic for my data access to MS SQL. However, you could make it non- database specific, such as a DAL. These examples could be customized to an ORM or however you access data. I'd recommend to create a static class that narrow's it down to a specific task.

For example, if you have a datagrid to populate and you know a view, table, stored proc to populate it from, create a function similar to the following c# code:

public static void BindDataGridViewWhere(DataGridView dgv, string tablename, string selectList, string whereClause)
{
    Query qQuery = new Query(tablename);
    qQuery.SelectList = selectList;
    qQuery.WHERE(whereClause);
    DataSet dsDGV = qQuery.ExecuteDataSet();
    dgv.DataSource = dsDGV.Tables[0];
    dgv.RowHeadersVisible = false;
}

Then, in page init or something like that, simple 1 line call to this method passing the datagridview in with the where statement, what you want to appear and how in the select, and the whereclause and you're bound.

BindDataGridViewWhere(dgvCars, "tbl_Cars", "CarName", "Color, mdl as Model", "Color = 'blue'");

This works great for any object that you do a lot of binding with, such as dropdownboxes, listboxes, datagridviews, anything else. Then, for others that don't fit this model, have a method that just returns a dataset. That way if you need to interact with it before displaying it, you can do additional business logic,etc.

I like this approach because if you want to switch data frameworks, you have 1 place to make the change. You can build a screen very quickly this way.

Answer 6

I have used outlet-orm which is an awesome ORM solution that doesn't generate. I think out of all the tricks I have done in the past this one has been by far the most convenient and time saving.

Answer 7

I would highly recommend SQLAlchemy if it's possible for you to use a python environment.

The other thing I tried is writing my own schema generator for testing our systems at work. This was more to generate different combinations of queries to attempt to crash the system. Basically, I defined a pseudo deparse tree, with dictionaries and lists

SQLAlchemy

Here's a code snippet

>>>for row in session.query(User, User.name).all():
...    print row.User, row.name
SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password FROM users []
<User('ed','Ed Jones', 'f8s7ccs')> ed
<User('wendy','Wendy Williams', 'foobar')> wendy
<User('mary','Mary Contrary', 'xxg527')> mary
<User('fred','Fred Flinstone', 'blah')> fred

Answer 8

I rolled my own ORL (Object-Relational Mapper) in PHP for MySQL. It really simplifies everything that has to do with changing the database, and it works very well for simple situations.

It consists of a base class that you can inherit from. You subclass it easily:

<?php
class CSomething extends CDatabaseObject
{

}

// create a new Something
$oSomething = new CSomething();
$oSomething->somevariable = 'blah';
$oSomething->Save();

// fetch an old Something by primary key
$oSomething = new CSomething(1);
// .. and delete it
$oSomething->Delete();
?>

It automatically figures out the indexes in the table, and the primary key. If it is required, you can of course tell the class these things if it does something bad.

You can do basic searches by specifying the WHERE clause of the SQL (so it isn't entirely SQL free). Since it nows about the data types of the fields, parameterized queries are simple.

Of course, it can't do everything I need, but it saves a lot of development time and code.

Answer 9

In one of my projects, I use a meta-model (tables, columns, relations) which adds information to the built-in sys* views.

Part of the data in my meta-model is used to generate logging triggers for insert/update/delete, and to implement cascading deletes in the instead-of delete triggers. With approx. 100 tables the generated code for these triggers is about 12.000 lines of TSQL code.

An SP generates a C# data structure which compares the live database schema with my development database schema to make sure upgrades went ok.

Recently the meta-model even allowed me to generate C# code for delete validation (i.e. can't delete record if depending records exist) in FormView-based Asp.Net forms.

Answer 10

Using built-in .NET features, such as Expression Columns (VB example shown) to data bind multiple columns to be displayed at once:

ds.Tables(0).Columns.Add(
    New DataColumn("CustomDescription", GetType(String), 
                   "LastName + ', ' + FirstName + ' - ' + 
                   WorkEmail"))
ResultsListBox.DataSource = ds
ResultsListBox.DataTextField = "CustomDescription"
ResultsListBox.DataValueField = "EmployeeID"
ResultsListBox.DataBind()

Answer 11

There's nothing wrong with SQL. Use the right tool for the right job.