DbCommandBuilder issues

[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

 

[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)

 

[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