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