473,880 Members | 1,813 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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

Apr 23 '07 #1
15 2292
The RPC (stored proc) path through SQL Server is a lot faster than the
language ("text SQL") path in most cases.

In addition, there are a number of very sophisticated things you can do
inside the database - transactions, UDFs, triggers, multistatement procs,
Table variables, cursors and so on that are difficult or impossible to do
with text sql even if it is parameterized.
My 2 cents.
Peter

--
Site: http://www.eggheadcafe.com
UnBlog: http://petesbloggerama.blogspot.com
Short urls & more: http://ittyurl.net


"Burt" wrote:
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

Apr 23 '07 #2
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]
- mv*@spam.guard. caspershouse.co m
"Burt" <bu*******@yaho o.comwrote in message
news:11******** **************@ q75g2000hsh.goo glegroups.com.. .
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

Apr 23 '07 #3
Burt wrote:
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
Due to SQL injection becoming a large problem to some databases, i would
suggest sticking with
stored procs. As all you have to do is run the stored procs you can disable
other query methods.
--
Regards JJ (UWA)

Apr 23 '07 #4
JJ,

Stored procedures don't necessarily prevent SQL injection attacks. It
is using the object model for passing parameters to a stored procedure which
prevents SQL injection attacks.

You can use that same object model to create parameterized dynamic sql
which is immune to injection attacks.

Conversely, you can actually execute the stored procedure by stringing
together a statement with the parameters (without using the object model)
and be vulnerable to an injection attack.

--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard. caspershouse.co m

"j1mb0jay" <ja**@aber.ac.u kwrote in message
news:11******** *******@leri.ab er.ac.uk...
Burt wrote:
>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

Due to SQL injection becoming a large problem to some databases, i would
suggest sticking with
stored procs. As all you have to do is run the stored procs you can
disable other query methods.
--
Regards JJ (UWA)

Apr 23 '07 #5
On Apr 23, 1:54 pm, "j1mb0jay" <j...@aber.ac.u kwrote:
Due to SQL injection becoming a large problem to some databases, i would
suggest sticking with
stored procs. As all you have to do is run the stored procs you can disable
other query methods.
Stored procedures offer no protection against sql injection attacks;
they are just as prone as dynamically built sql. The problem arises
from how you build the statement; if you are concatinating strings to
insert user supplied values, you are vunerable to sql injection
attacks. If you are instead leaving placeholders in the sql string,
and using something which implements IDataParameter to add the user
supplied values, you are immune from attacks.

Apr 23 '07 #6
develop it with inline and then move it to sprocs

for example.. using dreamweaver this strategy is actually quite nice

of course.. dreamweaver includes wizards for building webpages; MS
doesn't give a crap about web dev.. I mean.. Visual Interdev?
FrontPage? SharePoint?

Screw M$

On Apr 23, 10:08 am, Burt <burt_5...@yaho o.comwrote:
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

Apr 23 '07 #7
I agree with Peter. Another consideration is good app design/layering. Since
the format of the query is dependent upon the structure of the database, a
Stored Procedure can behave like a "function" that is called from the
database. If designed well, you can make changes to the underlying structure
of the database, leave the SP parameters the same, and the change will be
transparent to the application, thereby preventing you from having to
rebuild the application.

--
HTH,

Kevin Spencer
Microsoft MVP

Printing Components, Email Components,
FTP Client Classes, Enhanced Data Controls, much more.
DSI PrintManager, Miradyne Component Libraries:
http://www.miradyne.net

"Peter Bromberg [C# MVP]" <pb*******@yaho o.yabbadabbadoo .comwrote in
message news:E8******** *************** ***********@mic rosoft.com...
The RPC (stored proc) path through SQL Server is a lot faster than the
language ("text SQL") path in most cases.

In addition, there are a number of very sophisticated things you can do
inside the database - transactions, UDFs, triggers, multistatement procs,
Table variables, cursors and so on that are difficult or impossible to do
with text sql even if it is parameterized.
My 2 cents.
Peter

--
Site: http://www.eggheadcafe.com
UnBlog: http://petesbloggerama.blogspot.com
Short urls & more: http://ittyurl.net


"Burt" wrote:
>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


Apr 23 '07 #8
Thanks all. Nicholas you wrote:
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.
My next question was actually about the DAAB. At my current company
we're using it. At my last job, we just had a shared class called
"Data Access" which all programmers used. It was one level, and just
had some basic "GetDataReader" , "GetDataSet ", etc methods. The DAAB
sure does seem like a lot of overhead. Occasionally it will throw an
error which I'll have to Step through through a few Microsoft code
files to figure out. But I see there are benefits, like the one
above.

Burt

Apr 23 '07 #9
On Apr 23, 1:08 pm, Burt <burt_5...@yaho o.comwrote:
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
I'll tell you what's been working for me for years: dynamically
generating SQL from my object model. Which implies first of all that I
do have an object model. I use two functions (static to boot because I
don't want to create objects more than necessary) :

- Read (which generates SELECT)
- Write (which generates either INSERT, UPDATE or DELETE).

I get 90% of my app working this way; the other 10% is custom sql or
stored procedures that I write after a lot of performance profiling.
If performance is ok, no sprocs - and complete database independence.
Also, I found it pretty hard to inject SQL because all of it is
generated. And I get most of the benefits of stored procedures since
of course I use parameterized prepared statements. I only update
columns that I actually changed in the objects, which increases
concurrency (two users are able to change different columns of the
same row at the same time). I don't need to pass any parameters to
stored procedures. My systems have never been so clean and
uncluttered. Life is good.

I would stay away from the DAAB but the microsoft buffs may disagree
(reasonable people really disagree over this very topic). Also, if you
sold your soul to a database vendor, well, maybe better stick with
it.

Otavio

Apr 23 '07 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
460
by: LineVoltageHalogen | last post by:
Greetings All, currentley there is a heated discussion in my place of work over which method is better/more efficient for simple selects. Background: 1.) Simple Application that uses sql server for backened db. 2.) The application is only inserting and selecting data from the db. 3.) The developers want to use sp_executesql for simple selects and the dba's want to use a stored proc. >From my reading it seems that sp_executesql has a...
6
3108
by: Dave | last post by:
1) I know that we can define an external proc to be Fenced or NotFenced on "CREATE PROCEDURE" command. I don't see the FENCED / NOT FENCED option on "Create Procedure" for SQL stored procs. Is it always "NotFenced" for SQL stored procs by any chance ? We are in the process of migrating a SQL server app to DB2 with 2000 stored procs and the DB2 server keeps crashing too often during stored procs execution. 2) I have read somewhere...
5
2145
by: Rhino | last post by:
This question relates to DB2 Version 6 on OS/390. Can a (COBOL) stored procedure on this platform do file I/O, i.e. write to a sequential file? I am trying to debug a stored procedure. As far as I know, DB2 stored procedures cannot do terminal I/O on any operating system but I know that (Java) stored procedures in Windows/Linux/Unix can write to files and I have done this many times.
7
1728
by: trint | last post by:
My boss wants me to use stored procedures, but I thought somehow that using c# that ADO.Net was better. Any help is appreciated. Thanks, Trint
45
3449
by: John | last post by:
Hi When developing vb.bet winform apps bound to sql server datasource, is it preferable to use SELECTs or stored procedure to read and write data from/to SQL Server? Why? Thanks Regards
4
5062
by: Wendy Elizabeth | last post by:
I have the following questions about VB.NET interfacing with sql server 2000: 1. I have heard that VB.NET can run with inline SQL. Can you show me how to use inline sql to access a sql server 2000 database? 2. How do you setup stored procedures using Visual basic.net? 3. What is better to use and why, either inline sql or stored procedures? 4. Can you execute a visual basic.net program from a DTS package? If so, show would you accomplish...
1
1720
by: kentk | last post by:
Is there a difference in how SQL Server 7 and SQL 2000 processes SQL passed from a program by an ADO command object. Reason I ask is I rewrote a couple applications a couple years ago were the SQL statements were inline. I basically took the SQL statements and put them into stored procs, were there were variables in the code I used SQL parameters in the stored procs. I got some amazing performance results by switching to stored procs. ...
4
3147
by: PJackson | last post by:
I have been given the task of taking a 3,200 line COBOL stored procedure and duplicating the same functionality in UDB 7.2 on the Windows platform with a procedural SQL stored procedure. I have fiddled with procedural SQL stored procs on UDB, but mostly short,trivial ones. Looking for good advice, links, etc. on the best approach to this. There doesn't seem to be the concept of subroutines within a stored proc, so I am guessing one main...
8
2637
by: Frank Calahan | last post by:
I've been looking at LINQ and it seems very nice to be able to make queries in code, but I use stored procs for efficiency. If LINQ to SQL only works with SQL Server and stored procs are more efficient, what use is LINQ to SQL, other than to have a simpler way to call my stored proc?
0
9926
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10715
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10811
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9550
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7951
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7105
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5778
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5973
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4596
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.