Re: Inline SQL or stored procs for my C# windows app?

Burt,

Well, it kind of is one of those "no right answer" questions. It always
depends on context.

If you have a routine that is going to be called over and over again,
then there really is no reason to not have it in a stored procedure.

Yes, you will have to migrate these if you move to another database, but
honestly, how hard is it to move a stored procedure from one database to
another? If you have dynamic sql, then you are going to run into the same
problem, you will have to change the dynamic sql generator (or strings for
the sql) to make sure you are not using any database-specific features.

Just because you are using dynamic sql doesn't mean that you are not
using database-specific features in the dynamic sql.

Honestly, I would use something like the Data Access Application Block
in the Enterprise Framework that is offered by Microsoft. It will help
mitigate using stored procedures and changing from one database to another
(you won't have to worry about changing how you call stored procedures from
managed code, for example), and you can use the object model exposed by it
to generate dynamic sql that will work across any database that has a
provider for the DAAB, assuming you don't use any database-specific features
in your SQL.

Personally, I think that dynamic sql has a place, but for established
logic which will be called repeatedly, stored procedures are the way to go.
For me, I would use dynamic sql for things like persisting an object model
to the database, but not performing multi-statement logic against the
database.

Hope this helps.


--
- Nicholas Paldino [.NET/C# MVP]
- mvp@xxxxxxxxxxxxxxxxxxxxxxxxxxx


"Burt" <burt_5920@xxxxxxxxx> wrote in message
news:1177348117.001857.213900@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I'm a stored proc guy, but a lot of people at my company use inline
sql in their apps, sometimes putting the sql in a text file, sometimes
hardcoding it. They don't see much benefit from procs, and say anyway
they're are db specific- what if we change from SQL Server to Oracle
one day?

What say you experts? Is this one of those "no right answer"
questions?

Thanks,

Burt



.

Relevant Pages

  • Re: SQL Statement in C#
    ... This is mainly MS-SQL thinking, with other databases ... they do not think this way dynamic SQL is the prime method. ... people who have a problem with stored procedures have it with CRUD ... statements, if you are doing something that multiple database pulls, ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Newbie: Stored Procedures and Or Datasets ?
    ... > be a concensous that Stored Procedures are the most effective means to ... > mena less database access, can be optimised by the DBMS or Database ... you can use Stored Procedures just as easily as Dynamic Sql - in it's ... statement is virtually identical to calling a stored proc. ...
    (microsoft.public.dotnet.framework.adonet)
  • Dynamic SQL in Stored Proc
    ... I have a database setup so that NO users have READ, ... Dynamic SQL and they have stopped working. ... error message when executing one of the stored procs. ...
    (microsoft.public.sqlserver.security)
  • Re: SQL Injection Prevention
    ... I was pointing that you're not considering it in isolation but with the ... I think it's much more likely that an application developer would build the ... sql string from input that the database developer would do it in dynamic sql. ...
    (microsoft.public.sqlserver.server)
  • Re: SQL Injection Prevention
    ... I was pointing that you're not considering it in isolation but with the ... I think it's much more likely that an application developer would build the ... sql string from input that the database developer would do it in dynamic sql. ...
    (microsoft.public.dotnet.security)