Sunday, February 15, 2009

Stored Procedures: Make them or break them

I've been thinking about the use of stored procedures lately.

Some questions that popped up in my head: What are the advantages/disadvantages? When and how should they be used? Will they extinct with LINQ to SQL becoming more and more popular? Why should you store your queries in your database, instead of in your code..

I asked the opinion of a few ASP.NET/.NET professionals. Hope you find this interesting!

Kris Van Der Mast (Blog)
Since I'm a consultant I have to go with the specifications of my clients. Most still use stored procedures though I see a shift occurring. My current client allows us to use Linq To Sql for select statements (generated by L2S) but for update, delete and insert statements they require us to make sprocs since they put security on it on a database level. My former client allowed us to use NHibernate (winforms project) but for 3-4 special occasions we used sprocs.

SyntaxC4 (Profile)
I do use Stored Procedures on a regular basis. I develop mostly in DotNetNuke, which has an extensive use of stored procs. I have read blogs, and forum posts that suggest that stored procedures shouldn't be used as they can be a performance bottleneck, but i believe they are an excellent way of promoting SQL Abstraction.

I do however try to preserve the number of stored procedures I write. I normally create one stored procedure to handle both insert and updates to the a particular table, one for deleting, and one to select (sometimes i do multiple select statements within one stored proc if it makes sense).

To work away from bottlenecks, i try to use subqueries in my stored procedures instead of using a view to query against. unless of course a view is more efficient.

TATWORTH (Profile)
It is not much a case of would I use stored procedures as when would I not use them! Occasionally for searching, dynamic TSQL needs to be generated and for this I would use parameterized TSQL. Otherwise I use stored procedures all the time. I do have stored procedures for generating from the table definition stored procedures plus wrapper code. This allows very rapid development of the data layer.

You can find the current release, in the CommonData project at http://www.CodePlex.Com/CommonData.

The use of stored procedures (or the occasional parameterized TSQL) is in my opinion one of a series of essential steps to produce robust applications.

Tom Peeters (a co-worker)
I prefer to (read ‘always’) use stored procedures when I’m developing front end applications.

The ones who find creating stored procedures is intolerable overhead, I want to give some advantages..

1. They are modular, I’d prefer to troubleshoot a stored procedure than an embedded query buried within many lines of GUI code.
2. They are tunable. Changes can be made to the stored procedures --in terms of join methods, differing tables, etc.-- that are transparent to the front-end interface.
3. Stored procedures abstract or separate server-side functions from the client-side. It is much easier to code a GUI application to call a procedure than to build a query through the GUI code.
4. Stored procedures are usually written by database developers/administrators. Persons holding these roles are usually more experienced in writing efficient queries and SQL statements. This frees the GUI application developers to utilize their skills on the functional and graphical presentation pieces of the application. If you have your people performing the tasks to which they are best suited, then you will ultimately produce a better overall application.

In short, queries are best handled via stored procedures. While the initial development overhead is greater, you will more than make up for the investment down the line.

Looks like a lot of people are still very fond of stored procedures. The arguments make a lot of sense!

What are your thoughts on stored procedures?

Tuesday, February 10, 2009

Being a developer in India

Nowadays all you here is: hey let's outsource our call center, our IT department, our developer team.. to India. I asked Mudassar Ahmed Khan a few questions about how it's like being a developer in India.



Me: How is it like to be a developer in India?
MAK: Quite bad..
1: You get into heavy competition from day one when you join a Computer Science course.
2. Companies set percentage criteria for young graduates. If you are below those criteria you will have a hard time finding a job.
3. If you get into the company, the company decides what you will work on.
4. For Multinationals you need to pay security amount which is refunded when you complete a certain period in the company the period has a range 1-4 years depending on the company.

Me: How do you make up for the lack of experience? Work harder, be more passionate, ...?
MAK: In Multinationals you can't be more passionate because you have a lot of restrictions. But you get a fair amount of money too.
In Midsized and smaller companies, you'll get good work but at the cost of less money. But here too you can't be passionate since the Team Leaders and Project Managers mostly don't allow you to do so.
So it's the company which decides much rather than the individual. It's very rare that you get into a company that allows you to use ideas like mine.

Me: What is the general feeling about outsourcing? Is everyone just content that it is great for the economy, or do people have doubts?
MAK: It has increased the IT in India and many companies take up outsourced work from other countries. Recently the recession affected many companies and they have removed many employees. So it has both positive and negative effects.

Me: Do you think India is a threat to Europe?
MAC: I have no idea in that perspective.

Are there other Indians who wish to share their thoughts?

.NET Podcasts

I'm going to share the podcasts I listen to while commuting. Not enough developers realise they exist, but they are such a great way to use your time useful while being stuck in a trafficjam.





I realise that there are other podcasts available as well.. If you have links to other podcasts, feel free to share!

Update: Scott Hanselman published a list of Podcasts as well. And there are a lot more out there then I was aware of. Take a look at this article on his blog!

Tuesday, February 3, 2009

Allow scrolling in a MultiLine TextBox when Enabled="False"

I came across this problem today. We have a MultiLine TextBox in which typing should be disabled but where the scrollbars should be enabled.

I tried setting the Enabled property to False. But this had the side effect of not being able to use the scrollbars anymore. So I set the Enabled property to True and set the ReadOnly property to True. This worked, but there was no more visual difference between an editable TextBox and a non-editable. The background of the text was White and the text Black, so I set the ForeColor property to Gray.

Anyways here is the code.



And this is the result.