Re: Want to write your SQL statements and even stored procedures in pure C#?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Chad Z. Hower aka Kudzu wrote:

> "Frans Bouma [C# MVP]" <perseus.usenetNOSPAM@xxxxxxxxx> wrote in
> news:xn0e3jba0nyipg000@xxxxxxxxxxxxxxxxxx:
> > In sync with the database? Just like that? I don't think so. :)
>
> Yes it is. I think you are not understanding exactly whats going on.
> I'll be writing more articles soon. But this "system" is in use in
> several Fortune 500's, some of which are processing millions of
> transactions a day. But what Ive exposed in the article is just a
> peak at the DAL layer.

Chad, I know what I'm talking about, you can drop the marketing BS. If
you define a class in code with n fields, mapped on a given table T,
and the table changes (gets a new PK, field type changes, new fields
are added, or: 2 fields are removed), your class in code is out-of-sync
with the table it's mapped on. So you have to update the class in code.
That's not going to be automatic, either the system has to migrate the
meta-data and re-generate the class (as I do) or the developer has to
manually adjust the class.

You can't get away with "but my code will take care of the
differences" as that's not going to work with for example fields which
are removed from the table.

> > Either
> > you have to update class mappings based on a changed schema, or you
>
> They arent class mappings per se - its an object WRAPPER of the DB
> tables. When you embed SQL in your app and you change the DB you
> cannot find mismatches until run time. With this system, it
> regenerates the interfaces and all conflicts are found at comile time
> and then easily resolved from there.

Like I said, every O/R mapper takes care of that at compile time...
to some distinct. Even your code. For example, if you remove a field,
and you have queries defined which use that field, it won't compile,
but that's natural. Type safe query languages make sure errors like
that are found during compile time, but that's nothing new.

> > have to migrate the schema based on class changes. I can tell you,
> > that's far from 'automatic'.
>
> Because you are not seeing the whole picture.

haha, well I think I do, Chad.

> Until I finish the
> other articles, remember that what you have seen so far is just a
> peek at the DAL, nothing more. So your assumptions of how its used in
> a bigger picture sceanrio are invalid because you are assuming in
> your system that its a 1:1 replacement for whatever you use now.

Let me say this: in the past 3 years I've worked full time on one of
the market leading O/R mappers of .NET. You can be sure I've seen every
problem you try to solve as well. I've seen your codeproject article
and in there, I haven't seen anything new nor special. That's ok of
course, though don't try sell known stuff as new things, because that's
not what people here need.

You made a solution for the data-access problem, good. There are
others on the planet who have thought of that solution as well, and
perhaps way before you did.

> > Although I support dyn. sql of course, the problem I have with
> > your reasoning is that you claim your solution is truly
> > database
>
> Its fully DB independent. We have systems running on the older
> version which run on Oracle, SQL server, DB2, and Firebird. In fact
> the demo runs on Firedbird and SQL server, and they are fairly
> different databases. Firebird is closer to Oracle.

'DB independent' is only true in the scope of the abstraction level
the dal engine offers. For example, if you have a like-filter on
sqlserver in your application and you port your app to Oracle, you'd
better have a case insensitive flag in your dal engine or it won't work.

DB independent also suggests that it doesn't matter how the database
schema is constructed, but that's not true. There are always sacrifices
to be made: no bit/unique_identifier types on SqlServer if you want to
use Oracle as well as Oracle doesn't have equivalent types.

> > independent, though at the same time require things in the schema
> > (like a horrible sequence table in sqlserver... )
>
> First of all, if you read the article you would note that this is
> optional and not a requirement. Second of all - sequences are not
> horrible, identity fields are the item that is poor. Identity fields
> cause bottlenecks in transaction and multitable inserts, etc by
> forcing record inserts prematurely. Identity/autoincs are widely
> regarded as a not very good practice.

I was refering to the fact that a sequence table was required on
sqlserver to get autonumber field functionality.

By definition, sequences and identity columns are the same: identity
fields use an internal sequence (the table sequence) but in general
it's the same, with one exception: you can have multiple sequenced
fields per table, well not that you want to use that often, but you can.

I don't see how identity fields cause bottlenecks in transactions: for
sequences you too have to grab the new sequence value. And about
prematurely inserts: I guess you're referring to gaps in the sequence
if a transaction rolls back? With sequences you've the same thing, as
that's a logical outcome of the fact that once a value is generated, it
can't be rolled back as that would be unsafe.

> Given the choice of implementing autoinc/id fields in other DB's that
> use sequences, or implenting sequences in the others, its obvious
> which choice to use.

sequence objects require more maintenance (the source of a sequenced
value is not defined with the destination), and the 2nd db in the
world, DB2 offers both for example, so I don't think Identity is all
that bad compared to sequences.

(I'm not referring to the semantical issue of sequenced values as
PK's).

FB

--
------------------------------------------------------------------------
Get LLBLGen Pro, productive O/R mapping for .NET: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------
.

Relevant Pages

  • Re: Oracle vs MySql Performance
    ... Database Independence is core concept and there isn't a single project ... Actually Oracle has always conformed to ANSI-SQL though to a lower ... easier to negotiate with your RDBMS vendor if you have an exit option ... cached sequences of Oracle. ...
    (comp.databases.oracle.server)
  • Re: [PHP] Pragmatically changing a "Record Number"
    ... So to say it another way, I have a table that has 900 records in it, I've added 3 records, but then deleted 2 of those which puts the actual record count at 901 but my auto increment field starts at 904 on the next insert. ... Is there away with PHP that I can pragmatically change that value to the total records in the database more so then a representation of the actual record number? ... I've seen many people who wished to "fix" sequences like this before, usually because they believe there is something "wrong" with there being gaps in there. ...
    (php.general)
  • Re: Want to write your SQL statements and even stored procedures in pure C#?
    ... I'll be writing more articles soon. ... > your reasoning is that you claim your solution is truly database ... SQL server, DB2, and Firebird. ... Second of all - sequences are not horrible, identity fields are the item that is poor. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Spin calling John 100% identity (Human & Gekko)
    ... Unfortunately there are no other sequences to use ... When he has the filters snag ... probability that two random sequences will match is 1 in 4x 10^180. ... talking about the probability of a match existing in the database. ...
    (talk.origins)
  • Re: Spin calling John 100% identity (Human & Gekko)
    ... Unfortunately there are no other sequences to use in ... When he has the filters snag ... We could model this several ways, but the probability ... number of independent trials is much less than the size of the database. ...
    (talk.origins)