|
Use an ORM and only revert to stored procedures where (and if) performance bottlenecks exist. The application will be cleaner, easier to debug, and you will develop it much faster. If you need more speed, then might want to consider alternatives to .NET. Now bring on the flames!
|
|
|
|
|
mutantdna wrote: easier to debug
Are you aware the Visual Studio allows you to put breakpoints in a SQL Server stored procedure and debug it just as you would C# or VB.Net code?
|
|
|
|
|
No I was not aware of this - so thanks for the tip. Does it also work for Oracle? MySQL, SQLite? Or is this specific to the MS product stack?
|
|
|
|
|
I'm not sure, I've only ever tried it with SQL Server. Though, if you want to research it, this might be a good place to start.
|
|
|
|
|
mutantdna wrote: Does it also work for Oracle? MySQL, SQLite?
My understanding is that it will only work for MSSQL. I believe that Microsoft has built in some debugging capability into the Management Studio engine that Visual Studio utilizes.
I wasn't, now I am, then I won't be anymore.
|
|
|
|
|
aspdotnetdev wrote: Are you aware the Visual Studio allows you to put breakpoints in a SQL Server stored procedure and debug it just as you would C# or VB.Net code?
Actually it gets better than that. You can write your stored procedures in actual C# or VB.Net if you want.
I've played with it but not really done more than that. Anyone jumped on this bandwagon? Any thoughts?
-Richard
Hit any user to continue.
|
|
|
|
|
Indeed, I guess that's called SQL Server CLR Integration. Could be useful, but I'd only use it when necessary, as that does create maintenance burden for the next guy who has to administer the database.
|
|
|
|
|
have you actually had this work? I've tried and couldn't get it to play nice.
|
|
|
|
|
Yeah, worked fine for me. There were some limitations though. Off the top of my head, I think I was unable to view data in table variables or temp tables (though you can create a cursor to loop through each value in the temporary table and set a breakpoint to inspect the resulting variables, one row at a time) and you apparently need to change your connection string so connection pooling isn't used.
|
|
|
|
|
Funny stuff.
Database related code should remain with the database. Just because in your experiences people don't seem to be able to write good clean sql code doesn't mean its to be avoided. Before a proc is committed to source control or production it should be reviewed just like you should with other code you write. Instead of complaining and avoiding the real world, offer your expertise to help others, offer your time to review and explain the hows and whys of your criticisms. Don't let the end product suffer just because some are not as capable as yourself. Make the best choice of technologies to use, decide on the best way to use them, educate yourselves and your peers.
|
|
|
|
|
ohmyletmein wrote: Instead of complaining and avoiding the real world, offer your expertise to help others, offer your time to review and explain the hows and whys of your criticisms. Don't let the end product suffer just because some are not as capable as yourself.
I have sort of a problem with your post. But you may have just picked me up wrong.
You seem to think that I think I'm some sort of expert looking down my nose at bad programmers. Far from it. I said explicitly I don't think I am qualified to put complex logic into stored procs.
You say that Data related code belongs in the Database. Well where to you draw that line? Ultimately it's all data related logic right? We build apps that manipulate Data.
What it comes down to is that languages like PL/SQL are fundamentally unsuited to representing complex logic.
Here's an example and this is code that comes from Oracle of all companies. I'm trying to change the date on a contract. I have to call a function in package. The log file for toggling that currency runs to over 40 PAGES if I paste it into word.
Other similar small changes produce log files that are over 100 pages. And it isn't becuase the logging is very detailed, it's because there is a shed load of business rules veing validated.
This logic is virtually impossible to debug. It might be possible to rewrite this logic in PL/SQL and improve it but I don't believe it could ever be represented as elegantly and as simply as it could be in proper high level programming language.
PL/SQL is for relatively simple querying and manipulation of data and rudimentary validation. When things get more complicated than that It might not be suitable at all, or if it is "possible" the level of skill requried is frankly beyond the majority of developers (including me).
-Rd
Hit any user to continue.
|
|
|
|
|
I agree that complex logic does not have a place in db code, but I also believe that the integrity of data written in he database needs to be ensured by extensive use of constraints, keys, and, most importantly, value validation code. The database should be viewed as a data store that knows just enough to maintain the integrity of the data housed in it, but actual data manipulation should be left to the consuming applications.
I wasn't, now I am, then I won't be anymore.
|
|
|
|
|
First of all, I have to say I agree with you completely concerning lack of high-quality tools for working with databases. I would like to point out that Red-Gate do have some very good database tools (although, mostly for MSSQL), including support for version control.
My issue with writing all the data access code in the code-base, rather than the database, is that you are still writing SQL! How ever you look at the issue, you still need to write SQL in order to manipulate database and extract/update data. The only difference is where it is written, and I don't believe that writing classes and functions are any better than writing sprocs.
If you think that only sprocs are copied in order to make small changes, or that only sprocs are prone to remain around for years for fear of deletion, why, check out The Daily WTF for multitudes of cases where the same (and worse) happens to code.
I think that the best conclusion that can be made from all that has been shared here, is: we need much better development tools for databases. Tools that make it much easier to write, test and debug SQL (and vendor-specific SQL-based languages). Tools that make it as simple as VS or Eclipse to search and view dependencies and references between database objects. Tools that make it as simple as one click to commit, update and merge version-controlled database objects, and I don't mean exporting a DDL file and versioning that.
Of course, this still doesn't mean that any developer that knows a little C#, VB.NET or Java, can now become an automatic expert at SQL, even if this does seem to be expected by most job advertisements.
|
|
|
|
|
I suspect that I used to work for that same globally known company.. and I've seen PL/SQL that would make a grown man sit down and ball like a baby. Think spaghetti code with a very inconsistent use of badly named exceptions (think some exceptions floating up to the top caller, where NO handler existed for it, others being thrown locally but and not handled, etc etc.). I literally had to comment every line of 20 odd routines to figure out that rats nest. I knew the guy that wrote it.. nice guy, but couldn't code for his own life.
That said, in agreement with other posters.. I've also seen such atrocities in other languages. I've been programming for 28 years professionally, and I've had two projects handed to me that were such utter messes that starting over was the only way to go. In both cases I was able to save about 20% of the code base from the gallows, and the rest were sent to their eternal rest.. In the latest (c++): 5 threads, no locks, sleeps used to affect a very bad semblance of synchronization, and the SAME code piece copied like 30 times (it checked device state to make sure that the next statements were going to have an effect). The code was so buggy that in the 20% saved, I had to dig bugs out of it that had been hidden by the more hideous (and spectacularly more fatal) race conditions.
Egad.
|
|
|
|
|
a Stored procedure is given a name and called from code, while ad query writes it's own query (select, update, insert, delete). Is there anything else we need to know about?
|
|
|
|
|
I know I posted an example of this a while ago, but here's another classic example, and I thought some of the other bits in this snippet may amuse you ...
For j = 1 To k
char = UCase(Mid$(myField, j, 1))
If InStr(delims, char) <> 0 Then
If i <> j Then
buffer = Mid$(myField, i, j - i)
GoSub addIndex ' Nooooooooooooooooooooooooooooooooooooooo
End If
i = j + 1
End If
Next j
If i <> j Then
buffer = Mid$(myField, i, j - i)
GoSub addIndex
End If
a bit later in the same routine...
addIndex:
If IsNull(buffer) Then Return
buffer = Left$(Trim$(buffer), 20)
If Len(buffer) = 0 Then Return
wordnum = libWordNo(Trim(utilRemoveCrap(buffer)))
If wordnum = 0 Then Return
If InStr(wordsNow, ";" & CStr(wordnum) & ";") = 0 Then
wordsNow = wordsNow & CStr(wordnum) & ";"
End If
If InStr(wordsThen, ";" & CStr(wordnum) & ";") <> 0 Then Return
retSet.AddNew
retSet![AccountNumber] = recSet![AccountNumber]
retSet![recordNumber] = recNum
retSet![wordNo] = wordnum
retSet.Update
Return
I should also mention that there's a variable in the same routine called wordNo, and one called wordNum.
Classy stuff.
That dull thudding you can hear in the distance is my head banging against the desk.
|
|
|
|
|
wordNo is probably a boolean. If the buffer contents are an actual word, then wordNo contains false. But if the buffer contetns are not a word, then wordNo contains true.
wordNum is what happens to you after reading all this code. My brain is now num(b). 
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
I've devised a new technique for dealing with this kind of code.
When el-coder-loco is at lunch, you sneak over and change some tiny part of the code. A plus to a minus, a True to a False, something small.
Then they spend the next six months trying to debug the tangled mess that they've created.
Keeping them out of your way.
-Rd
Hit any user to continue.
|
|
|
|
|
Wow! A fantastic tecnique! 
|
|
|
|
|
|
A great suggestion, but as he retired a while ago...
At least I have the good sense to clear up after me
|
|
|
|
|
Rob Grainger wrote: but as he retired a while ago...
Let that not be an obstacle. A banana in a tailpipe can give a guy many days of debugging fun.
Childish? perhaps, but you'll feel better.
-Rd
Hit any user to continue.
|
|
|
|
|
This anti-pattern is scattered all over the place.
bool bStandardMandatory = false;
bool CheckStandardMandatory()
{
if (cbReportDefinitions.SelectedItem != null)
{
var rep = cbReportDefinitions.SelectedItem;
bStandardMandatory = _presenter.Controller.CheckMandatory(rep, "Standard");
return bStandardMandatory;
}
return false;
}
The only other place that touches bStandardMandatory :
bStandardMandatory = CheckStandardMandatory();
Please tell me who teaches this stuff to people?
Can I cry now? 
|
|
|
|
|
What about the implementation in _presenter.Controller? Is it:
bool bMandatory = false;
bool CheckMandatory()
{
if (someCondition)
{
...;
bMandatory = _someMember.CheckMandatory(..., "Standard");
return bMandatory;
}
return false;
}
with only one other place that touches bMandatory:
bMandatory = CheckMandatory();
|
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
