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 1880
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.co m
"Ronald S. Cook" <rc***@westinis .comwrote in message
news:O7******** ******@TK2MSFTN GP06.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.OutOfMem oryException 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.c omwrote in
message news:e1******** ******@TK2MSFTN GP05.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.co m
"Ronald S. Cook" <rc***@westinis .comwrote in message
news:O7******** ******@TK2MSFTN GP06.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.OutOfMem oryException 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.co m> 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.co m
"Ronald S. Cook" <rc***@westinis .comwrote in message
news:%2******** ********@TK2MSF TNGP04.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.OutOfMem oryException
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.c omwrote
in message news:e1******** ******@TK2MSFTN GP05.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.co m
"Ronald S. Cook" <rc***@westinis .comwrote in message news:O7******* *******@TK2MSFT NGP06.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******** ******@TK2MSFTN GP06.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 2000,
using an SqlConnection object to open the database and an SqlCommand
object to transfer my SQL text to the database.
Is this the "dynamic...
|
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 dynamic SQL string using a class
builder.
The pros I can see are:
It reduces the number of stored procs to admin.
Changes to the underlying table...
|
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 execute the stored procedure "SP_GETNAMES" on SQL server and have
it return the table, which could change over time as more return columns get
added of...
|
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...
| |
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 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...
|
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 proper tier to put it since is a data function
But then I've heard that writing SQL in my client .NET code might run just
as fast? Dynamic SQL or...
|
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 because it would mean one
fewer place to have things.
But, before we go that route we wanted to ask the question:
Is there any compelling reason why...
|
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?
|
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...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
| |
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...
|
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 protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
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...
|
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...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
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: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |