Automated Table Updates - Strategy Assistance Requested

Discussion in 'ASP .Net' started by Jeff, Dec 1, 2005.

  1. Jeff

    Jeff Guest

    I'm actually stuck! Can't believe it... so I'd appreciate some help.

    What I'm doing is enabling users of an ASP.NET 1.1 Web application to update
    a table in a SQL Server 2000 database. To make their life easier and to
    automate the enforcement of validation rules, I'm enabling users to maintain
    an Excel file on their local machine that has columns that match those in
    the table in the database. When the users want to update the table in the
    database, they upload the Excel file where I have logic that then validates
    the content and updates the table in the database. Specifically I need for
    the table in the database to exactly match the Excel file contents when this
    update operation is completed. The columns and data types won't be
    changing - only the data in the table.

    Upon receiving the Excel file on the Web server, I currently have logic that
    reads the Excel file contents into an untyped DataSet. Subsequent logic then
    validates the uploaded data. Only if all data data complies with the
    validation rules will the data then be transferred to the database.

    So far so good. But this is where I'm stuck.

    I want to next update the data in the database table with the data in the
    untyped DataSet - but I don't want to loop through every row (up to 2500
    rows) in the DataSet, as not all rows will be changed.

    The following strategy did not work (but shows the sort of thing I'd like to
    be able to do):
    1. Create/populate DataSet from Excel data (then validate it)
    2. Retrieve DataSet populated from SQL db table
    3. .Merge() the two DataSets
    4. Identify all the rows, and only the rows, that are different - per
    DataSet.GetChanges()
    5. Send all the changes and only the changes to the database via a
    DataAdapter's command objects (e.g., InsertCommand, UpdateCommand,
    DeleteCommand) - each of which would call a stored procedure and pass it
    parameters to do the actual update.

    This strategy does NOT work because merging the two DataSets does not
    automatically set the DataRowState value to whatever it would need to be in
    order to identify the modified/new/deleted rows (as the RowState of each row
    in each DataSet is of course"unchanged" -- meaning the GetChanges() method
    won't recognize any row as changed).

    Hopefully I'm just missing something obvious about the merge operation.
    PLease note that the [preserveChanges] argument to the Merge() method does
    nothing to help me here because, again, the original value of RowState is
    unchanged... so preserving that value is of no help.

    So, any ideas? I don't mind going with a completely different approach. I
    primarily want to avoid looping through *every* row and passing all values
    to a SP that then determines if an insert or update is required, and then
    have separate logic to handle deletes. That just seems like such an
    inefficient way to go.

    Thanks!
     
    Jeff, Dec 1, 2005
    #1
    1. Advertising

  2. You could send the whole dataset ( from step 1) to a storedprocedure as
    XML, and let that sp take care of it.

    The sp can update existing records, add new records and delete
    unnecessary records (propably without a cursor).



    ****************************************************************
    Tapio Kulmala

    "Those are my principles. If you don't like them I have others."

    - Groucho Marx
    ****************************************************************


    In article <>,
    says...
    >
    > The following strategy did not work (but shows the sort of thing I'd like to
    > be able to do):
    > 1. Create/populate DataSet from Excel data (then validate it)
    > 2. Retrieve DataSet populated from SQL db table
    > 3. .Merge() the two DataSets
    > 4. Identify all the rows, and only the rows, that are different - per
    > DataSet.GetChanges()
    > 5. Send all the changes and only the changes to the database via a
    > DataAdapter's command objects (e.g., InsertCommand, UpdateCommand,
    > DeleteCommand) - each of which would call a stored procedure and pass it
    > parameters to do the actual update.
    >

    --
     
    Tapio Kulmala, Dec 2, 2005
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Replies:
    1
    Views:
    535
    S. Justin Gengo [MCP]
    Feb 4, 2006
  2. Replies:
    6
    Views:
    362
  3. Ilias Lazaridis
    Replies:
    7
    Views:
    103
    Ilias Lazaridis
    May 8, 2005
  4. RF Rohrer

    Assistance Requested

    RF Rohrer, Apr 20, 2005, in forum: Javascript
    Replies:
    1
    Views:
    123
    Vladdy
    Apr 23, 2005
  5. Kiki

    "Automated" updates..

    Kiki, Jun 5, 2006, in forum: Javascript
    Replies:
    5
    Views:
    110
Loading...

Share This Page