Newbie: Stored Procedures and Or Datasets ?

[Jules]

Hello,

I would like some clarification on how I can use Datasets and ADO.NET
with Stored Procedures. I have read various multi tier architecture
discussions on performing Object/ RDMS maintenance, and there seems to
be a concensous that Stored Procedures are the most effective means to
maintain overall database performance. (I.e. Stored procedures will
mena less database access, can be optimised by the DBMS or Database
Engineers, but at the expense of portability because SP are DBMS
specific.)

But I have been reading through ADO.NET and the use of DataSets does
seem very attractive on simplifying my data manipulation operaitons
outside of the database. However would I be correct in assuming that
although I can use ADO.NET to call into Stored Procedures, their use
is not really consistent with DataSet data access ? I guess I can have
mixtures, so long as I am sure I am not manipulating the same data.

Is there any advice or wisdom on the relative merits of ADO.NET
Datasets and Stored procedures ?

Thanks

Jules

 

[William Ryan eMVP]

Comments below:

--

W.G. Ryan, eMVP

Have an opinion on the effectiveness of Microsoft Embedded newsgroups?
Let Microsoft know!
https://www.windowsembeddedeval.com/community/newsgroups

Hello,

I would like some clarification on how I can use Datasets and ADO.NET
with Stored Procedures. I have read various multi tier architecture
discussions on performing Object/ RDMS maintenance, and there seems to
be a concensous that Stored Procedures are the most effective means to
maintain overall database performance. (I.e. Stored procedures will
mena less database access, can be optimised by the DBMS or Database
Engineers, but at the expense of portability because SP are DBMS
specific.)

--Performance is just one benefit. Security is another one as is
maintainability.

But I have been reading through ADO.NET and the use of DataSets does
seem very attractive on simplifying my data manipulation operaitons
outside of the database. However would I be correct in assuming that
although I can use ADO.NET to call into Stored Procedures, their use
is not really consistent with DataSet data access ?

No, you can use Stored Procedures just as easily as Dynamic Sql - in it's
simplest form the only difference is settin gthe Command Object's
CommandType Property . In practice there's a little more to it, but suffice
to say that from the client side, using a paramaterized dynamic sql
statement is virtually identical to calling a stored proc. The proc is
simply used to execute your query - the actual data is stored in a
dataset/datatable (or a DataReader if you chose)

I guess I can have

mixtures, so long as I am sure I am not manipulating the same data.

If I understand you correctly, then you can have mixtures period.

Is there any advice or wisdom on the relative merits of ADO.NET
Datasets and Stored procedures ?

If you're going to use any of the disconnected objects, you basically have
to use a DataSet/DataTable - there's really no alternative. You have these
objecst as well as the connected ones, DataReader, Command.ExecuteScalar
etc - all of these objects can operate with a Stored proc or a dynamic sql
statement (although you really should use parameterized sql statements if
you aren't using procs).

HTH,

Bill

 

[Jules]

Thanks for those replies, these have helped clarify my understanding
and choice of Stored procedures and Datasets.

Jules

 

[Guest]

Does anyone have a quick reference to using DataSets with stored procedures
and updating tables? Can I use a stored proc. with a DataSet the same way as
SqlDataAdapter.Update(dataSet) - i.e. pass the DataSet to the stored proc.
once - or do I have to iterate through the DataSet?

Thank you

 

[Guest]

Figured it out. See "Update using DataSet and Stored Procedure"