Update using DataSet & Stored Proc.

[Guest]

Can I pass a DataSet to a Stored Procedure (in Sql Server 2000)?

If yes, Do I just specify the parameters and let the SqlDataAdapter handle
passing the rows of data from the DataSet into the Stored Procedure?

Have you a good reference I can go to?

Thank you.

 

[Mary Chipman]

The problem is that a stored procedure can't accept a DataSet or array
as an input parameter to a stored procedure. You'll have to take
another approach -- using DTS, XML, or parsing concatenated strings
inside the body of the stored procedure.

--Mary

 

[Guest]

Hi,

As mentioned you can not pass the Data Set as parameter. Other options could
be passing the complete data as XML String and use the Pre-Defined XML
supported stored procedures to read the data and execute the SQL Statements.

HTH,
Sangam

 

[Guest]

Maybe I should have worded my question differently - I was looking for info
on how to use a stored procedure to handle updates from a DataSet.

I found I just have to specify CommandType.StoredProcedure to the
SqlCommand. I then specified the input parameters for the stored proc. and
apply SqlDataAdapter.Update(DataSet). The stored proc then determines where
and how to update my tables.

Thanks anyway for your time.

 

[Mary Chipman]

That's a little different from your original question, which implied
that you wished to update all the rows in the dataset in a single,
atomic transaction, which is not an uncommon scenario. By using the
dataadapter, each row will update independently in a separate
transaction. So if you ever need to have all rows commit or all rows
roll back as an atomic operation, you'll have to use one of the other
techniques mentioned. Hope that clarifies things

--Mary

 

[Guest]

Yes. Thank you.

That's a little different from your original question, which implied
that you wished to update all the rows in the dataset in a single,
atomic transaction, which is not an uncommon scenario. By using the
dataadapter, each row will update independently in a separate
transaction. So if you ever need to have all rows commit or all rows
roll back as an atomic operation, you'll have to use one of the other
techniques mentioned. Hope that clarifies things

--Mary