What do you guys think of abandoning stored procedures and writhing the SQL
in code?
I'm a little new to the debate and not sure I totally understand. From my
command object, I can just select the type to be "text" instead of "stored
procedure", type my SQL there and it performs just the same? When did
writing the SQL in code become just as good as using procs?
Is there a list of pros/cons someone can forward?
I've heard a little about LINQ coming in the next Visual Studio, so is
abandoning procs a trend anyway? My need however, is for now and does not
concern LINQ.
Thanks very much,
Ron 9 1877
On May 31, 4:23 pm, "Ronald S. Cook" <r...@westinis.comwrote:
What do you guys think of abandoning stored procedures and writhing the
SQL in code?
It's been discussed quite a lot - most recently in this thread: http://groups.google.com/group/micro...8da4f408f69806
Jon
Ron,
In addition to the thread that Jon pointed out, I would say that in the
case of some sort of persistence layer, where you have a defined way of
getting values to be stored to the database, dynamic sql would be a good
call in this case, as it would be tedious to write all the stored procedures
for all the different types you are persisting in your database.
However, if you are going to perform well-defined operations in your
database, beyond the simple CRUD operations, I would say to go with stored
procedures, as writing out the sql to perform these operations in code is
just redundant, and you don't get any checking against the statement until
runtime when you try to execute it. At least if you have the stored
procedures in SQL Server, you can see if you have some sort of error before
you run the code.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com
"Ronald S. Cook" <rc***@westinis.comwrote in message
news:O7**************@TK2MSFTNGP06.phx.gbl...
What do you guys think of abandoning stored procedures and writhing the
SQL in code?
I'm a little new to the debate and not sure I totally understand. From my
command object, I can just select the type to be "text" instead of "stored
procedure", type my SQL there and it performs just the same? When did
writing the SQL in code become just as good as using procs?
Is there a list of pros/cons someone can forward?
I've heard a little about LINQ coming in the next Visual Studio, so is
abandoning procs a trend anyway? My need however, is for now and does not
concern LINQ.
Thanks very much,
Ron
I just did a lttle test from code against a 3 million record database.
Test 1: calling proc
Test 2: text "SELECT * FROM MyTable"
Test 1 took 55 seconds. Test 2 gave me a System.OutOfMemoryException error
after 55 seconds.
Any thoughts on that? Everything was running local on my PC.
Thanks,
Ron
"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.comwrote in
message news:e1**************@TK2MSFTNGP05.phx.gbl...
Ron,
In addition to the thread that Jon pointed out, I would say that in the
case of some sort of persistence layer, where you have a defined way of
getting values to be stored to the database, dynamic sql would be a good
call in this case, as it would be tedious to write all the stored
procedures for all the different types you are persisting in your
database.
However, if you are going to perform well-defined operations in your
database, beyond the simple CRUD operations, I would say to go with stored
procedures, as writing out the sql to perform these operations in code is
just redundant, and you don't get any checking against the statement until
runtime when you try to execute it. At least if you have the stored
procedures in SQL Server, you can see if you have some sort of error
before you run the code.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com
"Ronald S. Cook" <rc***@westinis.comwrote in message
news:O7**************@TK2MSFTNGP06.phx.gbl...
>What do you guys think of abandoning stored procedures and writhing the SQL in code?
I'm a little new to the debate and not sure I totally understand. From my command object, I can just select the type to be "text" instead of "stored procedure", type my SQL there and it performs just the same? When did writing the SQL in code become just as good as using procs?
Is there a list of pros/cons someone can forward?
I've heard a little about LINQ coming in the next Visual Studio, so is abandoning procs a trend anyway? My need however, is for now and does not concern LINQ.
Thanks very much, Ron
Ronald S. Cook <rc***@westinis.comwrote:
I just did a lttle test from code against a 3 million record database.
Test 1: calling proc
Test 2: text "SELECT * FROM MyTable"
Test 1 took 55 seconds. Test 2 gave me a System.OutOfMemoryException error
after 55 seconds.
Any thoughts on that? Everything was running local on my PC.
Did the proc actually return the data to the client?
--
Jon Skeet - <sk***@pobox.com> http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too
Ronald,
How are you calling the stored procedure and the dynamic sql from the
client?
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com
"Ronald S. Cook" <rc***@westinis.comwrote in message
news:%2****************@TK2MSFTNGP04.phx.gbl...
>I just did a lttle test from code against a 3 million record database.
Test 1: calling proc
Test 2: text "SELECT * FROM MyTable"
Test 1 took 55 seconds. Test 2 gave me a System.OutOfMemoryException
error after 55 seconds.
Any thoughts on that? Everything was running local on my PC.
Thanks,
Ron
"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.comwrote
in message news:e1**************@TK2MSFTNGP05.phx.gbl...
>Ron,
In addition to the thread that Jon pointed out, I would say that in the case of some sort of persistence layer, where you have a defined way of getting values to be stored to the database, dynamic sql would be a good call in this case, as it would be tedious to write all the stored procedures for all the different types you are persisting in your database.
However, if you are going to perform well-defined operations in your database, beyond the simple CRUD operations, I would say to go with stored procedures, as writing out the sql to perform these operations in code is just redundant, and you don't get any checking against the statement until runtime when you try to execute it. At least if you have the stored procedures in SQL Server, you can see if you have some sort of error before you run the code.
-- - Nicholas Paldino [.NET/C# MVP] - mv*@spam.guard.caspershouse.com
"Ronald S. Cook" <rc***@westinis.comwrote in message news:O7**************@TK2MSFTNGP06.phx.gbl...
>>What do you guys think of abandoning stored procedures and writhing the SQL in code?
I'm a little new to the debate and not sure I totally understand. From my command object, I can just select the type to be "text" instead of "stored procedure", type my SQL there and it performs just the same? When did writing the SQL in code become just as good as using procs?
Is there a list of pros/cons someone can forward?
I've heard a little about LINQ coming in the next Visual Studio, so is abandoning procs a trend anyway? My need however, is for now and does not concern LINQ.
Thanks very much, Ron
Ronald S. Cook wrote:
What do you guys think of abandoning stored procedures and writhing the SQL
in code?
I'm a little new to the debate and not sure I totally understand. From my
command object, I can just select the type to be "text" instead of "stored
procedure", type my SQL there and it performs just the same? When did
writing the SQL in code become just as good as using procs?
Is there a list of pros/cons someone can forward?
I've heard a little about LINQ coming in the next Visual Studio, so is
abandoning procs a trend anyway? My need however, is for now and does not
concern LINQ.
In addition to what the other fellas said, an extra thing to consider is
whether your shop is using some type of ORM (such as NHibernate or
Ibatis.NET). In that case, it is typical to use inline SQL.
I prefer stored procedures, especially for medium to large projects. The
reason is maintanance. 1 times out of 2, you can fix a bug by sending
the customer a modified SQL script. You'd have to send a modified
binary, if your SQL was inline.
For projects where I am forced to use inline SQL, I typically have a
Queries subfolder in my application folder where I store all/most
queries each in a separate file. At runtime, I just read in the
particular file and execute the SQL within. If a bug crops up in the
SQL code, I can just change the file without having to recompile the
application.
Regards
I prefer sql in code because we often have many statements that are
similar but not exactly the same. In code it's easy to build a SQL
statement with conditional parts. In a proc you can do it, but it's
more confusing and you lose most of the advantages of using a stored
proc.
HTH,
Sam
------------------------------------------------------------
We're hiring! B-Line Medical is seeking .NET
Developers for exciting positions in medical product
development in MD/DC. Work with a variety of technologies
in a relaxed team environment. See ads on Dice.com.
On Thu, 31 May 2007 09:23:35 -0600, "Ronald S. Cook"
<rc***@westinis.comwrote:
>What do you guys think of abandoning stored procedures and writhing the SQL in code?
I'm a little new to the debate and not sure I totally understand. From my command object, I can just select the type to be "text" instead of "stored procedure", type my SQL there and it performs just the same? When did writing the SQL in code become just as good as using procs?
Is there a list of pros/cons someone can forward?
I've heard a little about LINQ coming in the next Visual Studio, so is abandoning procs a trend anyway? My need however, is for now and does not concern LINQ.
Thanks very much, Ron
"Ronald S. Cook" <rc***@westinis.comwrote in message
news:O7**************@TK2MSFTNGP06.phx.gbl...
What do you guys think of abandoning stored procedures and writhing the
SQL in code?
I'm a little new to the debate and not sure I totally understand. From my
command object, I can just select the type to be "text" instead of "stored
procedure", type my SQL there and it performs just the same? When did
writing the SQL in code become just as good as using procs?
Is there a list of pros/cons someone can forward?
Whether one uses a SQL Stored Procedure or in-line Dynamic SQL is based on
the needs of the solution. I have used both in a solution based on the needs
of the environment said application was to be executed. Or I have used one
or the other in a solution based on the environment said application was to
be executed. https://msdn2.microsoft.com/en-us/library/ms973918.aspx http://decipherinfosys.wordpress.com...erated-by-orm/
Hi,
For projects where I am forced to use inline SQL, I typically have a
Queries subfolder in my application folder where I store all/most
queries each in a separate file. At runtime, I just read in the
particular file and execute the SQL within. If a bug crops up in the
SQL code, I can just change the file without having to recompile the
application.
You risk code injection calls. You must had security layer to this
folder or otherwise bad things can happen.
I would suggest using Stored Procesdures since it's good for
maintanance (Deployment, optimization etc.) and the main reason (for
me) is security. Using inline SQL (or dynamic) you can't force
execution denial. Stored Procedures security (In SQL Server 2000 and
up) can be managed quite roughly, and you can modulate the calls for
security modulation.
Moty. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Guinness Mann |
last post by:
When you guys talk about "dynamic SQL," to what exactly are you
referring? Is dynamic SQL anything that isn't a stored procedure?
Specifically, I use ASP.NET to communicate with my SQL Server...
|
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
|
by: MattC |
last post by:
Hi,
I'm implementing a new Business Layer in one of our applications. I'm
toying with the idea of placing all the Create, Read, Update and Delete SQL
in the object in question and build a...
|
by: Brian Henry |
last post by:
How would I create a dataset dynamicly on the fly.. I looked at the example
in the MSDN article on datasets but it wouldn't work (the northwind example)
here's what i want to do...
I want to...
|
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...
| |
by: Burt |
last post by:
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...
|
by: Ronald S. Cook |
last post by:
I've always been taught that stored procedures are better than writing SQL
in client code for a number of reasons:
- runs faster as is compiled and lives on the database server
- is the more...
|
by: Ronald S. Cook |
last post by:
I've read a few posts on the stored procedure vs dynamic sql debate. I ran
a few performance test for myself and it appears to be a wash.
Given that, I'm leaning toward dynamic sql mostly...
|
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...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
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...
| |
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...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
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,...
|
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...
|
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...
|
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...
| |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |