Embedded SQL in C#

  • Thread starter Thread starter VictorReinhart
  • Start date Start date
V

VictorReinhart

Hi,
I am intersted in trying to reduce the cost of C# development, by
reducing the number of lines of code. In my opinion, as a business
developer, the biggest opportunity to reduce the number of lines of
code is in database access.

My reality is that I use relational databases a lot. Other constructs,
such as flat files, Web Services, arrays, etc I don't use very much.

My data is stored on relational databases. I think that's pretty
common, nowadays.

I have been using ADO.NET and was wondering about the future.

Will C# support truly embedded SQL in the future? For example, in
PowerBuilder, since 1992, you could code as follows:

Long ll_count
String ls_last_name
SELECT Count(*), max(last_name) INTO :ll_count, :ls_last_name USING
TXN1;

IF TXN1.SQLCODE<>0 THEN ...

In C#, it seems to take many more lines of code to do the same thing.

Further, in PowerBuilder, the above statement works for any relational
database. All you have to do is change the connection parameters.

It seems that in C#, you have to contend with named parameters for SQL
Server and Oracle, vs. positional parameters for ODBC and OLE-DB.
Further, you have to explicitly create every single parameter in C#,
while in the example above, the parameters are created for you, using
datatypes which match the datatypes you use. That is a lot simpler.

Also, in C#, you are forced to use multiple TRY-CATCH constructs after
each SQL statement is executed. And, the exceptions which you catch
are different for different databases. That's an awful lot of code.

PowerBuilder has TRY-CATCH too, but it is generally not used for
relational database access.

And, let's talk cursors. In PowerBuilder, COBOL, and a few other
languages, such as PL*SQL, you can explicitly declare, open, fetch, and
close cursors. Wouldn't it be great if you could do that in C#?

Cursors give you low-level control. They are simple. And, if you
fetch too much data, you can stop.

In PowerBuilder and COBOL, you can even 'fetch in batches'. This means
that instead of getting one row at a time from the database, you
actually grab, say, 1000 rows at a time. Fetching in batches can
improve runtimes by 20 to 1, if your database is over a Wide-Area
Network, or by 4 to 1 if it is on a Local Area Network, in my
experience.

I think Embedded SQL makes sense because maybe, just maybe, it would
not become obsolete, as have a few other technologies (RDO, DAO, ADO,
etc).

Also, I think Embedded SQL is inherently much simpler than all the
above.

For example, a severe performance problem in a classic ASP application
was impacting one of my customers. The ASP app used ADO. I have read
many books about ADO, but not a one of them explained to me how a
"keyset" worked. It turns out that a keyset reads *all* the keys from
your SQL table, and puts them into memory. Well, when your table has 3
million rows, this becomes untenable. But then, how many people know
about this fact?

The reality is that RDO, DAO, ADO and ADO.NET all do things "behind the
scenes" which make performance tuning difficult. You have to capture
the actual SQL they send to the database first, then tune.

I don't want to need ADO to do that for me. Wouldn't you rather just
have real embedded SQL?

Opinions?

VictorReinhart
 
DLinq in C# 3.0 is probably what you are after.

That said, most applications make use of stored
procedures versus sql. You wind up sending less
across the network when making database calls.
Proc compilation also offers performance gains
although even that is being minimized by more
efficient database processing of dynamic sql.

The other thing you need to keep in mind
is that some databases support features
not supported by other platforms. A
good example of this is SQL Server 2005
support for the .NET CLR from within
user-defined functions.

It isn't just a simple sql world anymore...

--
Robbe Morris - 2004/2005 Microsoft MVP C#
http://www.eggheadcafe.com/forums/merit.asp
 
How do you equate development costs with the number of lines of code? Are
your developers getting paid by the line?

Reducing development costs come more in architecture than the actual writing
of the code. If the classes are designed right, than maintenance can be
reduced. There is more than just the initial time to develop an app. There
is the cost of sustaining it and fixing bugs.

For instance, if is fully possible to write a large app with only one class.
Each operation might be a method and they could be spaghetti together from
the main method. This takes less code than designing many classes to
support the functionality.
 
VictorReinhart said:
Hi,
I am intersted in trying to reduce the cost of C# development, by
reducing the number of lines of code. In my opinion, as a business
developer, the biggest opportunity to reduce the number of lines of
code is in database access.

My reality is that I use relational databases a lot. Other constructs,
such as flat files, Web Services, arrays, etc I don't use very much.

My data is stored on relational databases. I think that's pretty
common, nowadays.

I have been using ADO.NET and was wondering about the future.

Will C# support truly embedded SQL in the future? For example, in
PowerBuilder, since 1992, you could code as follows:
Click to expand...

Static Embedded SQL: No. DataSets and TableAdapters, or Code Generators or
ORM frameworks give you most of the benefits of embedded SQL without having
to have something official in the languages or framework.

For the future, a whole set of query constructs are being added to the .NET
languages which will allow simple and elegant expression of queries in .NET
laguages against relational, XML and object data sources. This project is
called LINQ:

http://msdn.microsoft.com/netframework/future/linq/default.aspx

A simple query against a relational source will look something like:

// establish a query context over ADO.NET sql connection
DataContext context = new DataContext(
"Initial Catalog=petdb;Integrated Security=sspi");

// grab variables that represent the remote tables that
// correspond to the Person and Order CLR types
Table<Person> custs = context.GetTable<Person>();
Table<Order> orders = context.GetTable<Order>();

// build the query
var query = from c in custs, o in orders
where o.Customer == c.Name
select new {
c.Name,
o.OrderID,
o.Amount,
c.Age
};

// execute the query
foreach (var item in query)
Console.WriteLine("{0} {1} {2} {3}",
item.Name, item.OrderID,
item.Amount, item.Age);

David
 
<<DLinq in C# 3.0 is probably what you are after. >>

I read the MSDN article: "The LINQ Project", September 2005. Thank
you for the suggestion, but It is not what I'm after. While the intent
is noble, I would rather have embedded SQL, for all the reasons above.
But if that was not enough, here are more:

a) Lack of Control over the SQL
What SQL does DLinq generate?
How to tell?
My experience is that when tuning problems happen, you have to see
and modify the SQL. For example, in SQL Server, you might need to add
the "nolock" keyword. How does one do this using DLinq? For Oracle,
how would one create a table which is like another table, and specify a
tablespace? Example:
create table hed_prov_az tablespace zhedhead
as select * from hed_prov_address where 1=2;
I don't know about your DBA's, but mine require me to specify a
tablespace when I create a table.

b) TRY-CATCH.
I rarely see anything in dot net which is a full example, with a
real TRY-CATCH. The way the code is written in the examples, your
application will crash with the first exception.

c) Which databases does it or will it support?
Embedded SQL should work for all databases.

d) It would be helpful to include examples using NULL Values.
Null values are common for things like termination dates. Yet, you
rarely see any examples.

<<most applications make use of stored procedures versus sql>>
I don't know of very many which use sp's. And sp's are a bad fit for
the application which targets multiple databases. We would rather use
SQL, thank you very much, instead of writing the same stored procedures
in three languages.

<<You wind up sending less across the network when making database
calls. >>
Maybe a trivial savings there, but development costs are a lot higher.

The reality is, SQL is here to stay, and is extremely useful.
Sometimes, you have to add hints to make it work, sometimes you need
database-specific code. But, in my opinion, what we really don't need
is software which hides the SQL.

I vote for embedded SQL.

VictorReinhart
 
<<It isn't just a simple sql world anymore... >>

That is why you can use Embedded SQL to:
1) Execute Stored Procedures which do not return result sets
2) Execute Stored Procedures which return result sets
3) Execute DDL (eg, Create Tables, Create Views)

Victor Reinhart
 
You can definitely write the code yourself and invoke Sql statements. I am
not quite sure about your relationship between cost and the number of lines.

Really, what you are talking about is imply hiding the access to the
database behind an access layer, so you might have something like
DAL.GetCustomer( int id ) and it would return a customer object. this would
essentially be the client version of a stored procedure that can cross
databases.
 
<<How do you equate development costs with the number of lines of
code?>>
There isn't an exact correlation. However, there is a gross
correlation. That is why we use high-level languages instead of
low-level languages, such as assembler. In fact, in my opinion, it is
best to use the highest level language possible as much as possible,
then only "drop-down" to other languages when absolutely necessary.

<<Are your developers getting paid by the line? >>
Of course not. But, we want to create the most value we can in a given
amount of time. It takes more time to write boiler-plate code than to
not write it. It takes more time to debug boiler-plate code. It takes
more time to read boiler-plate code. When the code is concise, time
and money are saved.

<<There is more than just the initial time to develop an app.>>
That is true, but time wasted is money wasted. The reality is that
money wasted developing an application can kill it before it is
completed. This is a common problem.

<<For instance, it is fully possible to write a large app with only one
class>>
I don't understand. I have about 200 web application pages to create.
How is that done using only one class?
 
<<You can definitely write the code yourself and invoke Sql statements.With some difficulty, yes. However, the point is that truly embedded
SQL would make this much, much easier. For example, while I could
build my own SQL string and submit it, what happens if my key has a
special character, such as a single quote? The answer is that by
default, it would fail.

Unfortunately, it's at least 2 lines of code just to create a parameter
in C#. Then, you have to make sure to make the proper kind of
parameter (a named parameter or a positional parameter). Then, you
have to reference the parameter properly in the SQL statement. It's
way more work than in other languages, such as PowerBuilder.

My other alternative is to write my own function which I have to call
for every single data value which goes into my SQL. It might take that
key with the single quote and double-up the single quote or escape it,
depending upon the database. That's not very productive either.

With PowerBuilder, I don't have to worry about these complexities.
Since it supports embedded SQL, this works, even if emp_name = "O'Hara"

Select salary_wkly INTO :ldc_salary from emp_master where emp_name =
:ls_name;

Wow, one line of code. Easy to understand. Easy to write. Easy to
debug. And, I know exactly what is sent to the database. I can copy
and paste this SQL statement into a query tool and unit test it. That
is fast.
 
<<simple and elegant expression of queries >>

Declare C1 Cursor for select c.Name,o.OrderID,o.Amount,c.Age from custs
c, orders o where o.Customer = c.Name;
String ls_orderID
Decimal ldc_amt
Long ll_age

Open C1;
DO
Fetch C1 into :ls_orderID, :ldc_amount, :ll_age;
IF sqlca.sqlcode<>0 THEN EXIT
MessageBox('Row',ls_orderID + ' ' + String(ldc_amount) + ' ' +
String(ll_age))
LOOP WHILE TRUE
Close C1;

Well, let's compare. The LINQ example is 16 lines of code, my example
is 11. That's pretty close, I like the number of lines of code.

But there is one huge difference: I can copy and paste my SQL statement
and paste it into Query Analyzer to unit test it. How does one unit
test the SQL when using LINQ?

Victor Reinhart
 
There is a big difference between lower- and higher-level languages. If an
app takes days to write in assembly, then it might take hours to write in
c#.

Sometimes the extra effort put upfront can pay off. This is something that
is often overlooked. Sometimes architectures do require extra effort and
testing, but when done, can easily be consumed by other parts of the code,
and when boiler-plates are tested, then anything consuming them can have a
high degree of confidence rather than writing the same or similar code each
time it is needed.

I may not know your exact circumstances, but I do not see how saving a few
minutes by writing one line to talk to the database rather than a few lines
is going to affect the budget. But then that is just me.

Your main thread did not mention that you were using a website. But, since
you mentioned it, you can write a website with no physical pages and one
class (or a few classes). You could write something like an HttpHandler or
HttpModule (can't remember which) that intercepts the page requests. Rather
then letting ASP.NET handler the request, you could encapsulate all the
behavior and rendering within the class and just stream the content to the
browser. Therefore you could create all 200 pages with only a single class
to handler the requests. No code-behinds or other utilities.
 
<<Sometimes the extra effort put upfront can pay off.>>
Maybe, but in my experience, the extra effort usually blows the budget.
My focus is to not spend that effort if possible. That's why embedded
SQL makes sense.

<<I do not see how saving a few minutes by writing one line to talk to
the database rather than a few lines is going to affect the budget. >>
Let me explain. If a SQL statement has only two tables and 3 columns,
it might be pretty close using LINQ or Embedded SQL. But more often,
it is a 3 or 4 table join, and/or 6 to 12 columns. Now, if that SQL
statement is failing, do you see the benefit of using copy and paste?
You could copy the query from your source code, then paste it into
SQL*Plus or Query Manager, then test the SQL. Time is money. How do
you do this using LINQ? How could you know what SQL a complex query in
LINQ generates?

<<you can write a website with no physical pages and one class (or a
few classes).>>
How does one do this, and also lay out the design so that you can
visually see how the pages will look? Visual layout is very important
to improve productivity.
 
<<I may not know your exact circumstances, but I do not see how saving
a few
minutes by writing one line to talk to the database rather than a few
lines
is going to affect the budget. But then that is just me.>>

My application has about 250 tables. That's not unusual. I have
worked on numerous business applications with 250 to 400 tables or
more. With that many tables, there is going to be a lot of SQL to
insert, delete, update and select from all those tables.

That is very, very common.

So, there is going to be a lot of C# code declaring parameters, etc.

Further, these tables are frequently joined. Frequently, these are
non-trivial joins, with 3 to 6 tables or even more. Sometimes, we even
join views. Very often, we use a combination of outer joins with inner
joins. Very often, there are bugs in these queries. Also, these
queries tend to require maintenance -- adding colums, adding tables,
changing the WHERE clause.

In my applications, without exception, the SQL interface is hugely
important.

"A few lines" turns into "quite a few lines per query" times hundreds
or thousands of queries.

That affects the budget big-time.

Victor Reinhart
 
VictorReinhart said:
<<I may not know your exact circumstances, but I do not see how saving
a few
minutes by writing one line to talk to the database rather than a few
lines
is going to affect the budget. But then that is just me.>>

My application has about 250 tables. That's not unusual. I have
worked on numerous business applications with 250 to 400 tables or
more. With that many tables, there is going to be a lot of SQL to
insert, delete, update and select from all those tables.

That is very, very common.
Click to expand...

Yes it is.
So, there is going to be a lot of C# code declaring parameters, etc.
Click to expand...

Which Visual Studio or any number of tools can automate for you.
Further, these tables are frequently joined. Frequently, these are
non-trivial joins, with 3 to 6 tables or even more. Sometimes, we even
join views. Very often, we use a combination of outer joins with inner
joins. Very often, there are bugs in these queries. Also, these
queries tend to require maintenance -- adding colums, adding tables,
changing the WHERE clause.
Click to expand...

Which is one of the reasons embedding non-trivial SQL in applciation code,
whether using dynamic SQL or static embedded SQL is a bad idea. Non-trivial
SQL statements belong in the database, not the application. An application
should be coded against a simple service layer in the database consisting of
single table and view access and stored procedures. If you need to join
multiple tables and do non-trivial SQL, it's much easier to code, debug,
maintain and port it at the database tier.

I'm pretty liberal about allowing SQL in an application. My simple rule for
SQL in the application tier is simple: no joins. If you need a join, code
it in a view, procedure, UDF, etc and call it from client code. The need
for client code to join indicates that your data model is exposed with too
much granularity, and your application has too much knoledge about the
details of your relational schema design.

Embedding SQL in the application simply violates the seperation of tiers,
technologies and developer skill-sets which is the foundation of enterprise
application architecture. It works for simple applications, but in
substantial enterprise applications with lots of tables, lots of business
rules, lots of technical roles, etc, it just won't do.

David
 
Hi,
if that SQL statement is failing, do you see
the benefit of using copy and paste?
Click to expand...

Just as a side note -- this is one of the significant advantages of using
stored procedures over embedded SQL statements: stored procedures are
validated at the time of creation or change.

Another advantage is some degree of encapsulation of the database innards.
Personally, I'd hate to be the one to hunt down and fix who-knows-how-many
embedded SQL statements in a large project (the size of those to which you
refer in another post), should there ever be a need to alter the database
design.
 
b. is not true. You can either handle specific exceptions or
trap a generic one which will still return the problem in
the database.

c. I think you'll find that most architects take a much
different design route than you do in regards
to the database access layer, stored procedures,
and proprietary features of the database you are after.

There are all sorts of database factory patterns
that would enable multiple database support.

I think you are trying to argue a point about embedded
or dynamic sql that most high end developers have largely
abandoned. They tend to build systems that will scale
well and perform well. Many use code generators
to write the code for them so they can optimize
speed to the database and work in OOP designs.

Whether DLinq catches on or not remains to be seen.

--
Robbe Morris - 2004/2005 Microsoft MVP C#
http://www.eggheadcafe.com/forums/merit.asp
 
Thank you for the article. It is interesting, but it does not target
all the databases I use. Also, the article says it does not yet
support .NET Framework 2.0. I guess my concern, too, is that after
investing time and money in one of these factories, who knows if it
will ever be upgraded for the future?

Victor Reinhart
 
VictorReinhart said:
I am intersted in trying to reduce the cost of C# development, by
reducing the number of lines of code. In my opinion, as a business
developer, the biggest opportunity to reduce the number of lines of
code is in database access.
Click to expand...

<snip>

You might want to look into NHibernate:
http://www.nhibernate.org

I don't have any experience with NHibernate itself, but Hibernate (its
Java equivalent) is excellent, and has taken a lot of the work of data
access away in the project I've just been involved in.
 
Back
Top