Embedded SQL in C#

[David Browne]

Re: Embedded SQL in C#


"VictorReinhart " <victora.reinha rt@phs.com> wrote in message
news:1135893781 .390492.92280@o 13g2000cwo.goog legroups.com...[color=blue]
> <<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.[/color]

Yes it is.
[color=blue]
>
> So, there is going to be a lot of C# code declaring parameters, etc.[/color]

Which Visual Studio or any number of tools can automate for you.
[color=blue]
>
> 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.
>[/color]

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

[Robbe Morris [C# MVP]]

Re: Embedded SQL in C#

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

"VictorReinhart " <victora.reinha rt@phs.com> wrote in message
news:1135889401 .646903.287540@ g49g2000cwa.goo glegroups.com.. .[color=blue]
> <<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_addres s 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
>[/color]

[Chris Priede]

Re: Embedded SQL in C#

Hi,

VictorReinhart wrote:[color=blue]
> if that SQL statement is failing, do you see
> the benefit of using copy and paste?[/color]

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.

--
Chris Priede

[VictorReinhart]

Re: Embedded SQL in C#

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

Peter Rilling wrote:[color=blue]
> You might want to try the Data Access Application Block from Microsoft.
> This wraps many of the common code functionality.
>
> http://msdn.microsoft.com/library/de.../html/daab.asp
>
>
> "VictorReinhart " <victora.reinha rt@phs.com> wrote in message
> news:1135893781 .390492.92280@o 13g2000cwo.goog legroups.com...[color=green]
> > <<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
> >[/color][/color]

[Jon Skeet [C# MVP]]

Re: Embedded SQL in C#

VictorReinhart <victora.reinha rt@phs.com> wrote:[color=blue]
> 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.[/color]

<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.

--
Jon Skeet - <skeet@pobox.co m>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too

[VictorReinhart]

Re: Embedded SQL in C#

<<stored procedures are validated at the time of creation or change. >>
It is true that in C#, there is no validation of SQL when it is
compiled. In PowerBuilder, however, there is. You will get a compiler
warning for any embedded SQL which has a database syntax error, for
example, incorrect column or table name. This can also be turned off.
This is another advantage of embedded SQL. The same applies for stored
procedure calls.

If your C# code which calls a stored procedure has the wrong number or
type of arguments, do you get a warning at compile time?

<<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. >>

Well, in PowerBuilder, if you compile your application, all SQL errors
in embedded SQL show up as warnings. True, not all my SQL is embedded
-- some is dynamic and most is in datawindows, but the point is that it
is possible for the C# language to be more tightly integrated with
relational databases than it is.

In my opinion, using embedded SQL, given the syntax checking at compile
time, it would actually be much easier to maintain than to maintain
stored procedures. For example, to unit test my embedded SQL, just
copy and paste into SQL*Plus and test it. How do you test your stored
procedures?

[Jon Skeet [C# MVP]]

Re: Embedded SQL in C#

VictorReinhart <victora.reinha rt@phs.com> wrote:[color=blue]
> In my opinion, using embedded SQL, given the syntax checking at compile
> time, it would actually be much easier to maintain than to maintain
> stored procedures. For example, to unit test my embedded SQL, just
> copy and paste into SQL*Plus and test it. How do you test your stored
> procedures?[/color]

I unit test mine with DbUnit and JUnit. That way they're still
automated, unlike cutting and pasting with SQL*Plus...

--
Jon Skeet - <skeet@pobox.co m>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too

[VictorReinhart]

Re: Embedded SQL in C#

<<There are all sorts of database factory patterns that would enable
multiple database support>>
That is true. I looked at NHibernate, which is a factory. It looks
like it has a lot of merit but there seems to be a substantial up-front
effort to make it work, and lots of lines of code.

Embedded SQL can be verified at compile-time. Do you know of any way
to catch an SQL error at compile time in C#? An example is invalid
column name, invalid table name, or SQL syntax error.

PowerBuilder provides an option to report SQL errors at compile time.
It is extremely helpful to find an error at compile time rather than at
runtime. And, it is very nice to be able to validate all SQL in the
entire application in a compile, if desired. This compile-time error
detection, plus the easy ability to copy and paste the SQL for unit
testing, saves a lot of time when developing an application. Also, it
makes for far fewer lines of code, which are much easier to understand.

Victor Reinhart

[Jon Skeet [C# MVP]]

Re: Embedded SQL in C#

VictorReinhart <victora.reinha rt@phs.com> wrote:[color=blue]
> <<There are all sorts of database factory patterns that would enable
> multiple database support>>
> That is true. I looked at NHibernate, which is a factory. It looks
> like it has a lot of merit but there seems to be a substantial up-front
> effort to make it work, and lots of lines of code.
>
> Embedded SQL can be verified at compile-time. Do you know of any way
> to catch an SQL error at compile time in C#? An example is invalid
> column name, invalid table name, or SQL syntax error.
>
> PowerBuilder provides an option to report SQL errors at compile time.
> It is extremely helpful to find an error at compile time rather than at
> runtime. And, it is very nice to be able to validate all SQL in the
> entire application in a compile, if desired. This compile-time error
> detection, plus the easy ability to copy and paste the SQL for unit
> testing, saves a lot of time when developing an application. Also, it
> makes for far fewer lines of code, which are much easier to understand.[/color]

Does that mean you can only use absolutely standard SQL? What if you
want to use SQL which is T-SQL or P/L SQL specific? The language (and
the compiler) would have to have detailed knowledge of all the
databases you want to use. That sounds like a bad idea to me.

To be honest, the idea of a general-purpose *language* knowing about
SQL directly doesn't sound very nice to me - any more than I like the
idea of the language itself knowing about, say, XML or URLs.

--
Jon Skeet - <skeet@pobox.co m>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too

[Robbe Morris [C# MVP]]

Re: Embedded SQL in C#

The compilation would be caught at the stored procedure level.

Again, your design architecture preference is much different
than most developers and best practices suggest.

Your whole argument pretty much falls on deaf ears...

--
Robbe Morris - 2004/2005 Microsoft MVP C#

http://www.eggheadcafe.com/forums/merit.asp

"VictorReinhart " <victora.reinha rt@phs.com> wrote in message
news:1135960067 .695548.104770@ g47g2000cwa.goo glegroups.com.. .[color=blue]
> <<There are all sorts of database factory patterns that would enable
> multiple database support>>
> That is true. I looked at NHibernate, which is a factory. It looks
> like it has a lot of merit but there seems to be a substantial up-front
> effort to make it work, and lots of lines of code.
>
> Embedded SQL can be verified at compile-time. Do you know of any way
> to catch an SQL error at compile time in C#? An example is invalid
> column name, invalid table name, or SQL syntax error.
>
> PowerBuilder provides an option to report SQL errors at compile time.
> It is extremely helpful to find an error at compile time rather than at
> runtime. And, it is very nice to be able to validate all SQL in the
> entire application in a compile, if desired. This compile-time error
> detection, plus the easy ability to copy and paste the SQL for unit
> testing, saves a lot of time when developing an application. Also, it
> makes for far fewer lines of code, which are much easier to understand.
>
> Victor Reinhart
>[/color]

[carion1]

Re: Embedded SQL in C#

Seems like the solution is to use PowerBuilder if it provides the
functionality you want/need.

--

Derek Davis
ddavis76@gmail. com

"VictorReinhart " <victora.reinha rt@phs.com> wrote in message
news:1135955847 .399301.14390@o 13g2000cwo.goog legroups.com...[color=blue]
> <<stored procedures are validated at the time of creation or change. >>
> It is true that in C#, there is no validation of SQL when it is
> compiled. In PowerBuilder, however, there is. You will get a compiler
> warning for any embedded SQL which has a database syntax error, for
> example, incorrect column or table name. This can also be turned off.
> This is another advantage of embedded SQL. The same applies for stored
> procedure calls.
>
> If your C# code which calls a stored procedure has the wrong number or
> type of arguments, do you get a warning at compile time?
>
> <<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. >>
>
> Well, in PowerBuilder, if you compile your application, all SQL errors
> in embedded SQL show up as warnings. True, not all my SQL is embedded
> -- some is dynamic and most is in datawindows, but the point is that it
> is possible for the C# language to be more tightly integrated with
> relational databases than it is.
>
> In my opinion, using embedded SQL, given the syntax checking at compile
> time, it would actually be much easier to maintain than to maintain
> stored procedures. For example, to unit test my embedded SQL, just
> copy and paste into SQL*Plus and test it. How do you test your stored
> procedures?
>[/color]