Click here to Skip to main content
15,502,293 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
What was it, about 2002 that .Net gave us the DataAdapter/DataSet and the DataReader. All we needed to know then was that the DataReader was fastest.
Well this is 14 years later in a corporate production environment after we're supposed to be using stored procedures after all the fighting over preventing SQL injection. ... This is a Windows Service by the way, no web attachment anywhere.
Sometimes we need to dynamically generate SQL, so there is no way to use a stored procedure, but in this case, I potentially could, maybe. It would be a hassle, but I could do it... with limits... because with the DataReader I stop reading rows when I have found enough to process, that have certain criteria. Later, this will become even more important in the future as different records are given different priority (planned feature). I suspect that when/if the DB Admin figures out I am using a DataReader with a SQL string literal, he's gonna flip out (he's picky).
So my question would be
(1) are there any new implications to consider about using the DataReader that I should pay attention to.
(2) How can I defend my decision that I need the flexibility, if the guy goes off?

Thanks, M

Hmmm. You can make a stored procedure that will take a SQL literal statement and run it, but that is still not the solution I need and that would really flip him out.

What I have tried:

Really, I have the stored procedure made, but defined it out. The DataReader gives me the flexibility to pick out the number of records I want that fit the criteria, then I stop.
Updated 4-Oct-16 12:00pm

The DataReader isn't "faster", it just defers the "download" of the records until you ask for them, which the DataTable / DataSet approach returns when the complete set of records have been assembled and returned to your code. The DataReader takes the same time - or even longer - to process all records because it spreads the time that the DataTable approach puts all up front. In addition, it requires a connection open for longer (the life of the Reader) and "hogs" Server resources which the DataTable approach releases immediately. Which is why your DBA is going to "flip out".

And if you only want the top twenty records, then only retrieve the top twenty rows using the SELECT TOP 20 MyColumn1, MyColumn2, ... syntax. That's efficient and faster!

we need to dynamically generate SQL, so there is no way to use a stored procedure
Since when? SP's can still use EXEC to execute dynamically generated SQL...
Share this answer
Michael Breeden 4-Oct-16 11:36am    
>>>it requires a connection open for longer (the life of the Reader) and "hogs" Server resources <<<
That is the key thing I was concerned with and why the code to use the DataAdapter is only commented out, not removed. I may even make which is used configurable.
As for "select top 20", this is processing to a fax number. I might get 100 records with the same fax number, but I only allow 3 in my system at a time. That means only 1 of 100 fax lines could be put in use until I get records with different fax numbers. I would need a statement something like "select top(100) faxnumber is distinct" ... may have to ask a DBA around here if that can be done... a group clause maybe... Hmmmmm.
I suspect that using an "exec" statement might torque someone as well.
Use EntityFramework, it should give you the things you need. Under the covers it works out the dynamic SQL needed to fulfil your request and executes for you, abstracting away all the actual data access stuff. It basically makes your SQL tables behave like objects\lists in your code (oversimplification, but you get the idea). It would mean ditching the SPs though.
Share this answer
Personally I think it is a bad idea to split your logic, and put some in your database layer and use stored procedures other than for performance reasons when processing a lot of rows, for a couple of reasons:
1) I makes development and changes harder and you have to "massage" your logic to work in stored procedures (which is not what it was designed for).
2) You are locked in to your vendor database and can't easily move to another.
3) At least in my tests using normal sql code was faster than using stored procs in any db post 2005.
Share this answer
Michael Breeden 4-Oct-16 11:39am    
>>>3) At least in my tests using normal sql code was faster than using stored procs in any db post 2005.<<<
Interesting, I had heard they were very similar, with possibly an edge to a stored procedure. Still, due to past risks of SQL Injection, some shops absolutely forbid using SQL Statements instead of Stored Procedures... I'm not even using parameters, so I am just hoping no one notices.
Mehdi Gholam 4-Oct-16 14:23pm    
There are known ways around sql injection attacks, but I prefer to have maximum development speed and flexibility which in the short term and long term comes down to development cost.
Jörgen Andersson 5-Oct-16 3:16am    
If you know what you're doing there is no difference between stored procedures and using plain SQL. And there shouldn't be.
Most common reason for SP to be faster is that the plan is cached, but if you use parameters WITH DEFINED SIZES, your plain SQL would also use the cache.
The most common reason for SP to be slower than plain SQL is when you are using dynamic queries. Then the compiled plan is good only for a certain set of parameters. One solution is to use the WITH RECOMPILE option when calling the SP.
Michael Breeden 5-Oct-16 6:16am    
Jörgen Andersson - Very interesting. I basically know most of this stuff, but you and some others know it far more solidly than I do and can say why it is so. It's funny that I am really not good at SQL, not even as good as many C# developers, but some good database people have on occasions told me very good stuff and I try to remember it.
A very simple approach to this is to use parameterized queries for your dynamic SQL
// brief example of what I use all over the place
public DataTable LoadRecords(DateTime pStartDate, DateTime pEndDate)
 DataTable results = new DataTable();
 // parameterized query
 string sqlStatement = "SELECT R.One, R.Two, R.Three FROM dbo.Records AS R WHERE R.Date BETWEEN @StartDate AND @EndDate;";

 using (SqlConnection conn = new SqlConnection(_connectionString))

  using (SqlCommand command = new SqlCommand(sqlStatement, conn))
   command.Parameters.Add("@StartDate", SqlDbType.DateTime).Value = pStartDate;
   command.Parameters.Add("@EndDate", SqlDbType.DateTime).Value = pEndDate;

   using (SqlDataReader reader = command.ExecuteReader())

 return results;

The command will automatically build the dynamic statement into a parameterized query which eliminates the SQL injection threat.
Share this answer
Michael Breeden 4-Oct-16 18:48pm    
Cool, this is great. It allows me to use parameters, but only when I want them. This is super adaptable.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900