Re: Writing updates from a wide DataTable

I would feel uncomfortable too--at one point in time you might want to
restrict IO rights to specific columns or trip over a column that can't be
updated at all.

If you roll your own UpdateCommand, you don't have to set columns you don't
want to set and you can still use a SP. The UpdateCommand can define all 50
Parameters but pass NULL or your own "flag"(you might want to SET some
columns to NULL) that could be used in the SP logic that says not to SET
this column.

Creating your own UPDATE statement on the fly is what ADO classic did--based
on changed columns (one of the Update Criteria property settings). It's not
a bad idea, but moves more schema-dependent logic to the client than I would
typically like to do.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
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.
__________________________________

"Nigel Norris" <nospam@xxxxxxxxxx> wrote in message
news:e$Xv0t5kFHA.1948@xxxxxxxxxxxxxxxxxxxxxxx
>I have a DataTable with a large number of colums - 50 or more. I'm
>following the recommendations to use stored procedures to apply updates to
>the database, and using the Enterprise Llibrary. So I end up with a stored
>procedure with 50+ parameters, and an Update statement that updates the
>same number of columns. In a typical use case only a few columns will
>actually be updated, but my DAL generally doesn't know which - it just gets
>a DataSet with some changes.
>
> Everything works fine, but I feel uncomfortable with this solution. I
> don't like rewriting all those columns I haven't changed. I don't like
> shipping all this unchanged data around.
>
> Should I feel uncomfortable? Are there better ways of handling this kind
> of situation?
>
> Thanks...
>
> --------
> Nigel Norris
>


.

Relevant Pages

  • Re: Writing updates from a wide DataTable
    ... >restrict IO rights to specific columns or trip over a column that can't be ... > Microsoft MVP ... >>following the recommendations to use stored procedures to apply updates to ... and an Update statement that updates ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Grant Execute on Proc
    ... users without CREATE TABLE rights can still create ... "Anand" wrote in message ... > User "A" have Execute Privilege on all stored ... > Inside one of the stored procedures a table gets created ...
    (microsoft.public.sqlserver.security)
  • Re: Rename Extended stored procedures
    ... My advise would be to revoke rights and create stored procedures that wrap ... where XP's allow overloaded parameter type passing ... This posting is provided "AS IS" with no warranties, and confers no rights. ... > I've read on some sql security sites that dropping stored procedures is ...
    (microsoft.public.sqlserver.security)
  • Re: When to use Stored Procedures!
    ... > I am still new to Sql Server & lovin' it. ... you can revoke all rights ... Stored procedures have other benefits as well, ...
    (microsoft.public.sqlserver.server)
  • Re: Granting GRANT permissions
    ... Creators of stored procedures (standard users with CREATE PROCEDURE rights) ... can grant permissions on their own procedures to other users. ... "Josh N." ...
    (microsoft.public.sqlserver.security)