Very well put. And if I could add one more personal preference... I do
not use parameters because then I can not see the actual SQL that's
going out. I just assemble the queries myself from scratch. Of course
that means I have to have a few functions to add slashes and format
dates properly, but that doesn't take long to set up. And it future
proofs the code against any changes that might me made to the connecter
as happened recently. I didn't have to change a single line of code,
although I did update all the date fields in my database to default to
0001-01-01 instead of 0000-00-00.
Let me explain why: I make heavy use of dates in some of my tables. A
recordset comes back and gets loaded into class objects by looping
through each row in the record set and extracting one field at a time.
Recently, when I added some more date fields, it was taking 1 1/2
seconds to convert 10 rows into a corresponding array of objects. I
could sew a scarf in the time it took to load a small table. The
problem turned out to be that zero dates were coming back from the
database as 0/0/00 (or something like that) rather than 0000-00-00. And
the exact format depended on the current language settings of the user.
So I couldn't just test for "0/0/00" and convert that to
DateTime.MinValue, and I couldn't test for "0000-00-00". Because 0/0/00
is not a valid date format in .NET, it was going through my try catch
statement, and that's where all the slowdown was. By changing all the
zeros to ones in the database, my application sped right up again.
Notice that none of the recent discussions regarding dates affected me
because I do not use the MySQL datatypes that come with the connector.
Again, this future proofs my code.
Jordan Sparks
-----Original Message-----
From: SGreen@stripped [mailto:SGreen@stripped]
Sent: Wednesday, December 22, 2004 6:36 AM
To: streamlake@stripped
Cc: dotnet@stripped
Subject: RE: About float fields
If you are worried about concurrency. you could add a TIMESTAMP field to
your tables. It works like this. When you read the record you also read
the TIMESTAMP value of the last time the record was updated. When you
attempt to commit your changes into the database, compare that datetime
to
the one currently in the record. If it doesn't match, you know that
someone (or something) already changed the record since you started
reading it.
There are several ways to detect the change:
an UPDATE of 0 records
or - a transaction wrapped around a read check of the current
date
followed by an update statement (InnoDB)
or - a LOCK/UNLOCK wrapper around a read check of the date with
UPDATE.
How you handle edit conflicts is rather application specific. Your
options
can be summarized as:
1) Forget the changes you are attempting to make and start over with the
new information
2) Merge your changes with those already made (requires field by field
comparisons)
3) Overwrite the existing changes with your changes.
I concur with James, I don't databind any elements. I found that
database
automation only works reliably for the most trivial of cases and I
firmly
believe that I have saved time by constructing my own SQL statements.
Not
only do I *know* that it will be an optimal statement, I won't have to
worry too much about a component upgrade automagically writing platform
specific statements because it's trying to optimize for MS Access or MS
SQL Server and not MySQL.
My recommendation too, use the connections and recordsets for reading
data
and manage your own inserts and updates through direct SQL statements.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
streamlake@stripped wrote on 12/22/2004 03:33:22 AM:
> Thanks James,
> I'm beginning to think that your suggestion looks to be a correct
solution...
> CommandBuilder lets me save some coding but it doesn't look
> particularly flexible, or maybe I don't know it well enough... :-)
>
> The same happens with automatic data binding with TextBoxes
> (Binding, BindingContext,
> Parse and Format events, etc), it doesn't look much flexible apart
from
> basic operations, or at least it doesn't make me reduce code size.
>
> Any other suggestions for the float problem?
> Frank
>
>
> >-- Messaggio Originale --
> >From: "James Moore" <banshee@stripped>
> >To: "'Frank'" <streamlake@stripped>, <dotnet@stripped>
> >Subject: RE: About float fields
> >Date: Tue, 21 Dec 2004 17:32:51 -0800
> >
> >
> >My personal solution is to not use automatically generated SQL (ie,
ditch
> >CommandBuilder.) I find that for the simple case building the
> >commands myself is simple anyway, and in the more complex case
> >CommandBuilder
doesn't
> >do what I need. Not really a solution to your problem, but it's
something
> >to think about.
> >
> > - James
> >
> >
>
>
> __________________________________________________________________
> Tiscali Adsl 2 Mega Free: l'adsl piu' veloce e' gratis! Naviga libero
> dai costi fissi con Tiscali Adsl 2 Mega Free, l'adsl Free piu' veloce
> in Italia. In piu', se ti abboni entro il 7 gennaio 2005, navighi
> gratis fino al 31 marzo. E il costo di adesione e' GRATIS.
> http://abbonati.tiscali.it/adsl/
>
>
>
>
> --
> MySQL on .NET Mailing List
> For list archives: http://lists.mysql.com/dotnet
> To unsubscribe:
http://lists.mysql.com/dotnet?unsub=1
>
