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.