Writing queries in code behind vs. SqlDataSource

Question

I always have this notion that writing SQL queries in the code behind is not good compared to writing it using a SqlDataSource

SqlDataAdapter ad = new SqlDataAdapter("SELECT * FROM Categories", myConnection);

DataSet ds = new DataSet();

ad.Fill(ds, "Categories");

myGridView.DataSource = ds;

myGridView.DataBind();

vs.

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
  ConnectionString="<%$ ConnectionStrings:myConnection %>"
  SelectCommand="SELECT * FROM Categories" />

I feel using SqlDataSource is secure, easy to maintain. Is my concern true? Please justify.

Answer 1

I wouldn't write SQL queries in code behind full stop. How about a data access layer?

What happens if you want to change your backing store? You're going to have to re-write all your code-behind.

What happens where you need to use the data in more than one place? You duplicate code.

You need think seriously about how you're architecting your solution before writing SQL queries in your code behind. Think about separation and maintainability long before you question the 'security' of SqlDataSource objects. Seriously.

Answer 2

SQL queries in the code-behind and SQL queries in a SqlDataSource are pretty much equivalent

they're both about the same security-wise; as for easier to maintain, SqlDataSource may be a bit easier in most cases

a data-access layer is preferred, but SqlDataSource is sometimes a good-enough expediency. I wouldn't hit you with a rolled-up newspaper for using one if you didn't already have a data-access layer and it was for something simple/one-off ;-)

Answer 3

In my experience, SQLDataSource is good for a quick one-off page to display data and perhaps edit it. But once you get into any kind of complicated scenario (and I always have), it breaks down pretty quickly. Maintainability is a nightmare using both SQLDataSource and straight SQL in the code behind. I've been burned by SQLDataSource many times.

I would at the very least write a Data Access Layer as a separate assembly that you can call into. This will give you a pluggable way to change it out if you need to. Even better would be a data access solution such as NHibernate or LinqToSql, which handles the plumbing for you and prevents you from having to write the whole thing yourself.

Answer 4

Neither of your methods are more or less secure than the other. Since your aspx pages are compiled just as your code behind pages are, you don't run the risk of accidentally exposing your SQL statements or database structure simply by using SqlDataSources. However, security isn't your main problem here -- it's maintainability.

Lots of people complained when Microsoft released SqlDataSources as part of .NET 2.0: we believe it encourages and reinforces bad habits.

For any type of project that's larger than a single intranet page, you should look into its well-behaved big brother, the ObjectDataSource. In using an ODS, you're almost constrained into developing a separate model for your data, away from your view.

Answer 5

I agree with Jeremy. Try using the ObejctDataSource in conjunction with the SqlHelper class.

Anthony :-) www.codersbarn.com

Answer 6

DataSource controls are great for most things. They support paging in grids and serverside caching and may save trips to the database. However one downfall is that if you are doing anything complicated with db transactions, you wont be able to use a transaction across more than one sqldatasource, at least not easily.

because of pooling, two datasources could have different connections and there is no easy way to assign the transaction object before the commands execute.