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

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Chad Z. Hower aka Kudzu wrote:

> "Frans Bouma [C# MVP]" <perseus.usenetNOSPAM@xxxxxxxxx> wrote in
> news:xn0e3kb543ucwy001@xxxxxxxxxxxxxxxxxx:
> > 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.
>
> But it is automatic. And its not something unique to this system -
> you merely regenerate the wrappers. The wrappers are isolated from
> any user code.

that's not automatic, you have to re-run a tool. What if I rename a
table, or a field? Then my code breaks as the wrapper class gets a new
name. That's not what a developer wants.

> > 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.
>
> No, you miss the point. Nothing magic fixes up the differences. What
> it does is by early binding - you detect all problems at compile time
> rather than hoping to find them at run time. Whats unique about this?

nothing.

> Well nothing really from most other similar solutions - except that
> nearly every other solution is only early bound on some aspects, but
> filters etc fail because they use strings.

no they don't. A lot of the O/R mappers out there don't use strings or
offer an object based query system as well.

> > Let me say this: in the past 3 years I've worked full time on
> > one 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.
>
> If you say its not new - please show me another O/R that is fully
> early bound, filters and all. Ive seen very few, andt those that
> I've seen have quite an ackware syntax.

LLBLGen Pro does, and so do a lot of others. For example Genome uses a
similar approach as you do (with overloaded C# operators).

> > 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.
>
> Please point me to one that is 100% early bound, and has a "native"
> type syntax.

#define 'native'. SQL is set-based, C# is imperative, non-set based.
So what's native? SQL-like, C# like or a language/system which
represents what the SQL elements mean semantically?

> > 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.
>
> They are similar yes, but identity fields cannot be obtained until
> after an insert.

so? You've to insert the PK side of an FK first anyway, or do you
require that there aren't any FK's?

> > 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.
>
> Read any of the many articles here regarding the issues with identity
> fields - they describe the complexities the add much better than I
> can. As far as bottlnecks - they can create bottlenecks by forcing
> inserts of master rows prematurely and thus forcing premature locks
> or even transaction begins.

Best practises say that you should FK constraints on live databases.
FK constraints force you to insert the PK side of the FK first anyway,
then sync the value with the FK side and insert the FK side. That's not
tic-tac-toe indeed: you have to sort the objects which have to be
inserted first, and you have to implement synchronization logic so when
I do:

CustomerEntity newCustomer = new CustomerEntity();
newCustomer.CompanyName = "Solutions Design";
//...

OrderEntity newOrder = new OrderEntity();
newOrder.OrderDate = DateTime.Now;
//...
newCustomer.Orders.Add(newOrder);
adapter.SaveEntity(newOrder);

I get the graph saved in the right order: first Customer, then order,
pk of customer, an identity, properly synced with newOrder.CustomerID,
and all in a single transaction.

Where are the bottlenecks? What's inserted prematurely?

> > sequence objects require more maintenance (the source of a
> > sequenced
> > value is not defined with the destination), and the 2nd db in the
>
> Trivial compared to the complexity identify fields inject into code.

I don't see how. Unless you don't use FK constraints.

> > (I'm not referring to the semantical issue of sequenced values
> > as PK's).
>
> Im not referencing that either. I dont care what the values are, my
> issue with identity fields is that they are not available utnil after
> an insert, and looking at cross DB platforms, sequences are much
> easier to simulate than identity fields.

The insert problem isn't a problem, as when FK constraints are used,
you have to insert the PK side of the FK constraint first anyway, so
even with a sequence you first have to insert the PK side, then the FK
side.

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: Want to write your SQL statements and even stored procedures in pure C#?
    ... every O/R mapper takes care of that at compile ... > you want to use Oracle as well as Oracle doesn't have equivalent ... > fields use an internal sequence but in general ... but identity fields cannot be obtained until after an insert. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: How to generate unique document number ?
    ... Do not use the cache on a sequence. ... transaction, or an error may occur, causing the transaction to ... By creating an autonomous transaction - still possible to ... get missing numbers. ...
    (comp.databases.oracle.server)
  • Re: Deadlock on an identity field table
    ... Thanks David, that changed us from 90% transaction failure on 16 threads to ... 1280 transactions a minute on the same thread count with no sequence problems ... >> The result is used to provide unique keys for multiple tables. ...
    (microsoft.public.sqlserver.programming)
  • Re: Table Locking in .NET Transaction
    ... can lead to major performance problems. ... have no problems with gaps in the sequence, use a table with an identity ... > number table as part of a larger transaction taking care ... I plan on using a Holdlock ...
    (microsoft.public.sqlserver.programming)
  • Re: Theoretical Basis for SELECT FOR UPDATE
    ... select sumfrom account into memory_variable; ... the database was in an inconsistent state. ... are a single atomic transaction. ... then I do not see a problem with sequence. ...
    (comp.databases.theory)