What is a SqlDataSource Control

Back when I was a fledgling .NET developer, I was taught that it is bad practice to utilize this control. However, as I gain more experience and look at the way other people develop their .NET applications, I am seeing these more often.

So, I have set out to gain an educated opinion on whether or not using a SqlDataSource is bad practice.

I have created a sample application that utilizes this control, using the AdventureWorks database, to test out different scenarios and see where it falters.

SqlDataSource

MSDN states that a SqlDataSource control is one that let’s the developer use a web server control to access data from a database. Seems simple enough.

It uses ADO.NET classes to interact with any database supported by ADO.NET. This includes:

• Microsoft SQL Server
• System.Data.OleDb
• System.Data.Odbc
• Oracle

That sound great! So I can connect to a multitude of databases via this control without writing any code. Note, anytime I see that claim “without writing any code”, I am skeptical.

The developer provides a connection string to connect to the database and the SQL statements or stored procedures that work with the data. SqlDataSource control opens the connection, executes the SQL statement or stored procedure, returns data if needed, and then closes it automatically.

You can also specify Update, Insert, and Delete commands for the SqlDataSource control.

Here is MSDN’s example:

<asp:SqlDataSource
          id="SqlDataSource1"
          runat="server"
          DataSourceMode="DataReader"
          ConnectionString = "<%$ ConnectionStrings:MyNorthwind%>"
          SelectCommand = "SELECT LastName FROM Employees">
</asp:SqlDataSource>

This will definitely make my life easier when it comes to wiring up one-offs for any databound control.

I have a few questions, though.

1. What happens when the business rules changes for my application and I need to update my SQL?
2. How do I Unit Test this?
3. Is there additional overhead involved in using a control like this?

What to do When the Business Rules Change

You have to update the SQL in the control and update your databound control. This could pose an issue if you use these in multiple places.

How do I Unit Test this?

I can’t! The SQL is in the markup. Then again, to be fair, it is difficult to write unit tests for web forms applications that do not utilize the MVP pattern.

Is there additional overhead when using this control?

Base on my research, it seems that there is.

If you do a Google search for “asp.net sqldatasource pros and cons”, there are several pages that discuss them.

This stack overflow post has some good points, but I would like to point out one here:

One disadvantage of the declarative way of doing the data binding is that the paging is not efficient. The gridview will pull all records from the database, and then it will only show you your page. [Zach Green]

I tested this with a table that had 19,972 rows and did not notice any lag when I navigated to new pages. However, since it is pulling all records from the database and then showing your page, I can definitely see where this would not be the best way to handle it.

I can also see where maintenance would be a problem. Imagine having 100 pages that used these controls! It would be much easier to abstract that out to a separate set of classes and handle the data in the code behind.

Conclusion

I really need to come up with better section names when I’m ending a post. “Conclusion” sounds too formal!

So, after all of this research, I would say that use a SqlDataSource when

1. Maintenance is not a major factor of the work you are doing.
2. You are creating a small application.
3. You are creating a mockup to show off to a client.

Do you use SqlDataSource in your applications? What issues have you faced when using them? Let me know in the comments!