CODEWITHSUNDEEP
sqlreference
EvilTeach
EvilTeach

Reputation: 28837

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

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:

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.

Upvotes: 7

Views: 1134

Answers (9)

Mark Carpenter
Mark Carpenter

Reputation: 17775

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()

Upvotes: 0

devio
devio

Reputation: 37205

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.

Upvotes: 1

Vegard Larsen
Vegard Larsen

Reputation: 13037

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.

Upvotes: 2

mikelikespie
mikelikespie

Reputation: 5832

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

Upvotes: 0

Bill Martin
Bill Martin

Reputation: 4943

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.

Upvotes: 0

Mr. Shiny and New 安宇
Mr. Shiny and New 安宇

Reputation: 13908

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.

Upvotes: 1

Ot&#225;vio D&#233;cio
Ot&#225;vio D&#233;cio

Reputation: 74250

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.

Upvotes: 2

JoshBerke
JoshBerke

Reputation: 67068

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.

Upvotes: 10

Cade Roux
Cade Roux

Reputation: 89661

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

Upvotes: 2

Related Questions