O'Reilly logo

The Art of SQL by Peter Robson, Stephane Faroult

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Abstract Layers

It is a common practice to create a succession of abstract layers over a suite of software primitives, ostensibly for maintenance reasons and software reuse. This is a worthy practice and provides superb material for exciting management presentations. Unfortunately, this approach can very easily be abused, especially when the software primitives consist of database accesses. Of course, such an industrial aspect of software engineering is usually associated with modern, object-oriented languages.

I am going to illustrate how not to encapsulate database accesses with some lines from a real-life program. Interestingly for a book entitled The Art of SQL, the following fragment of C# code (of questionable sharpness...) contains only bits of an SQL statement. It is nevertheless extremely relevant to our topic, for deplorable reasons.

1 public string Info_ReturnValueUS(DataTable dt, 2 string codeForm, 3 string infoTxt) 4 { 5 string returnValue = String.Empty ; 6 try 7 { 8 infoTxt = infoTxt.Replace("'","''"); 9 string expression = ComparisonDataSet.FRM_CD 10 + " = '" + codeForm 11 + "' and " + ComparisonDataSet.TXT_US 12 + " = '" + infoTxt + "'" ; 13 DataRow[] drsAttr = dt.Select(expression); 14 15 foreach (DataRow dr in drsAttr) 16 { 17 if (dr[ComparisonDataSet.VALUE_US].ToString().ToUpper().Trim( ) 18 != String.Empty) 19 { 20 returnValue = dr[ComparisonDataSet.VALUE_US].ToString( ) ; 21 break; 22 } 23 } 24 } 25 catch (MyException myex) 26 { 27 throw myex ; 28 } 29 catch ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required