Re: Newbie: Stored Procedures and Or Datasets ?

From: William Ryan eMVP (dotnetguru_at_comcast.nospam.net)
Date: 07/12/04 

Date: Mon, 12 Jul 2004 13:38:34 -0400

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
"Jules" <Roseanna80@hotmail.com> wrote in message
news:80bc211e.0407120842.6264a446@posting.google.com...
> 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
>
> Thanks
>
> Jules

Relevant Pages

  • Re: Database Model - Class, objects and interaction
    ... I was just proving stored procedures can call views. ... stuff like security and logging. ... So given 100,000 users, you would create database accounts for each? ... Part of our system's integrity was its reliability, and reliability is often assisted by simplicity. ...
    (comp.object)
  • Re: Database Model - Class, objects and interaction
    ... Stored procedures should only be used as a last resort if you cant use ... I've not said that views shouldn't be created, only that they shouldn't be exposed as the interface to the database. ... But business rules can be more complicated than simply defining overdue invoices. ...
    (comp.object)
  • Re: Infinite Loops and Explicit Exits
    ... the whole payroll system would be dependent on proper database ... I design large systems the same way. ... Stored procedures ARE application code. ... any language, including Cobol. ...
    (comp.lang.cobol)
  • Re: Identity Column
    ... the end use is using Crystal Reports - where is the magic? ... The user will connect using Active Directory from their desktop to the database server or do please explain the magic.... ... Using stored procedures you permission on the stored proc only; the user can only execute the stored procedure with the parameters defined, there is no select * from to get all the information in your database. ...
    (microsoft.public.sqlserver.programming)
  • Re: Infinite Loops and Explicit Exits
    ... The behaviour could only be changed by changing the database. ... >>designing proper databases and using stored procedures on them, ... are database dependent and not available in COBOL. ... But with extended SQL you simply write the procedures in SQL ...
    (comp.lang.cobol)