Using Diffgrams for multiple insert/update/delete

[Guest]

Hi,

I want to do multiple insert/update/delete of multiple rows using a single
stored procedure using diffgram. Can I please get a small sample/link for
achieving this. I just need the the stucture of the stored proc as I have the
diffgram generated successfully.

Advanced thanks.

 

[W.G. Ryan - MVP]

Samba - is it a must that you only use One stored procedure? This adds a
little complication to the scenario. If you must though, then which db are
you using, Sql Server (T-Sql) or Oracle (PL/Sql)?

BTW, if you use multiple procs (one for each crud command), you can just use
the Update method of the dataadapter and it would definitely make things a
lot easier. You stated one proc for a reason so I'm not trying to dodge your
question, i just wanted to double check on this b/c the solutions will be a
little different.

Essentially, you can pass in a parameter to the proc indicating the action
to take. then, you'll have to branch out the command for each. The params
should be the same although you may or may not be using them all at any one
time.

Finally, how do you want to use the Diffgram specifically? Are you planning
on just creating a dataset with it or is there anything special that you
need to do with it (the update scenario as a whole) other than just using
the diffgram for rowstate?

 

[Guest]

Hi Ryan,

Thank you very much for your reply. I'm using SQL Server 2000 (T-SQL). I've
lot of inserts, updates as well as deletes in one table. After this all of
them must be saved to the database in one single shot. So, I'm just creating
a dataset and once the changes are done (inserts, updates as well as
deletes), I'll call:

myDataset.GetChanges(DataRowState.Added Or DataRowState.Modified Or
DataRowState.Deleted).WriteXml(strWriter, XmlWriteMode.DiffGram)

This step gives out a diffgram Xml (which I suppose is correct). I'll be
passing tyhis to a T-SQL stored procedure like this:

ALTER PROCEDURE dbo.usp_ToolAttributeValueImportData
(
@UpdatedOrInsertedData NTEXT,
@DeletedData NTEXT
)
AS
BEGIN
DECLARE @hDoc INT

-- Updated records
IF @UpsertedData IS NOT NULL
BEGIN
EXEC sp_xml_preparedocument @hDoc OUTPUT, @UpsertedData
/*
Update the table (this is what I want to know)
*/
END
EXEC sp_xml_removedocument @hDoc
/*
Similarly for Insert and delete (I want to know this also)
*/

Thanks, once again.