DbCommandBuilder issues

6 Dec 2006 3:01 PM

Philippe Bertrand

1. DbCommandBuilder should detect when a column appears twice in the select
list and issue an error.

For example:
   SELECT id, c_bit, c_bit FROM adocmdbld

gives
   "UPDATE adocmdbld SET c_bit = ?, c_bit = ? WHERE ((id = ?) AND ((? = 1
AND c_bit IS NULL) OR (c_bit = ?)) AND ((? = 1 AND c_bit IS NULL) OR (c_bit
= ?)))"
   "INSERT INTO adocmdbld (c_bit, c_bit) VALUES (?, ?)"
   "DELETE FROM adocmdbld WHERE ((id = ?) AND ((? = 1 AND c_bit IS NULL) OR
(c_bit = ?)) AND ((? = 1 AND c_bit IS NULL) OR (c_bit = ?)))"

2. DbCommandBuilder should detect when select has only read-only primary key
columns

For example:
   SELECT id FROM adocmdbld
(where id is an autoincrement column)
gives:
   UPDATE adocmdbld WHERE ((id = ?))
   INSERT INTO adocmdbld DEFAULT VALUES
   DELETE FROM adocmdbld WHERE ((id = ?))

Thanks,
Philippe

6 Dec 2006 4:52 PM

William (Bill) Vaughn

I've been discussing the merits and issues of the CommandBuilder for some
time so see the archives and my article at
http://www.betav.com/msdn_magazine.htm. While the latest incarnation is
better than the last, it still is pretty simplistic as you have discovered.
Many developers have moved to SPs for CRUD so these issues are mitigated.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

Show quote

"Philippe Bertrand" <my.n***@ianywhere.com> wrote in message
news:el6m10$hml$1@newswest.sybase.com...
> 1. DbCommandBuilder should detect when a column appears twice in the
> select list and issue an error.
>
> For example:
>   SELECT id, c_bit, c_bit FROM adocmdbld
>
> gives
>   "UPDATE adocmdbld SET c_bit = ?, c_bit = ? WHERE ((id = ?) AND ((? = 1
> AND c_bit IS NULL) OR (c_bit = ?)) AND ((? = 1 AND c_bit IS NULL) OR
> (c_bit = ?)))"
>   "INSERT INTO adocmdbld (c_bit, c_bit) VALUES (?, ?)"
>   "DELETE FROM adocmdbld WHERE ((id = ?) AND ((? = 1 AND c_bit IS NULL) OR
> (c_bit = ?)) AND ((? = 1 AND c_bit IS NULL) OR (c_bit = ?)))"
>
>
> 2. DbCommandBuilder should detect when select has only read-only primary
> key columns
>
> For example:
>   SELECT id FROM adocmdbld
> (where id is an autoincrement column)
> gives:
>   UPDATE adocmdbld WHERE ((id = ?))
>   INSERT INTO adocmdbld DEFAULT VALUES
>   DELETE FROM adocmdbld WHERE ((id = ?))
>
> Thanks,
> Philippe
>

7 Dec 2006 4:58 AM

Cor Ligthert [MVP]

Philipe,

I do not share the opinion of Bill Vaughn about commandbuilders. Well used
they are pretty fine.

However, try to avoid autoidentifiers and non parameterized queries.

For the identifier is in my idea the best the Global Unique ID (GUID) to
create as new(C#) or New(VB) Guid.

On this page you see the simple use of simple paramized SQL transact code
(be aware that this holds the main difference between SQLClient and OleDB
and that the information about this on MSDN is now already for very many
years not accurate).

http://www.vb-tips.com/dbpages.aspx?ID=550279ec-6767-44ff-aaa3-eb8b44af0137

Our website is under construction pretty soon the Tips will become higher on
the page.

I hope this helps,

Cor

Show quote

"Philippe Bertrand" <my.n***@ianywhere.com> schreef in bericht
news:el6m10$hml$1@newswest.sybase.com...
> 1. DbCommandBuilder should detect when a column appears twice in the
> select list and issue an error.
>
> For example:
>   SELECT id, c_bit, c_bit FROM adocmdbld
>
> gives
>   "UPDATE adocmdbld SET c_bit = ?, c_bit = ? WHERE ((id = ?) AND ((? = 1
> AND c_bit IS NULL) OR (c_bit = ?)) AND ((? = 1 AND c_bit IS NULL) OR
> (c_bit = ?)))"
>   "INSERT INTO adocmdbld (c_bit, c_bit) VALUES (?, ?)"
>   "DELETE FROM adocmdbld WHERE ((id = ?) AND ((? = 1 AND c_bit IS NULL) OR
> (c_bit = ?)) AND ((? = 1 AND c_bit IS NULL) OR (c_bit = ?)))"
>
>
> 2. DbCommandBuilder should detect when select has only read-only primary
> key columns
>
> For example:
>   SELECT id FROM adocmdbld
> (where id is an autoincrement column)
> gives:
>   UPDATE adocmdbld WHERE ((id = ?))
>   INSERT INTO adocmdbld DEFAULT VALUES
>   DELETE FROM adocmdbld WHERE ((id = ?))
>
> Thanks,
> Philippe
>