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.

flesh · Accepted Answer · 2008-11-20 20:03:18Z

14

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.

answered Nov 20, 2008 at 20:03

flesh

23.9k24 gold badges83 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

TylerH · Accepted Answer · 2020-03-02 16:47:36Z

9

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/a one-off.

edited Mar 2, 2020 at 16:47

TylerH

21.3k84 gold badges84 silver badges121 bronze badges

answered Nov 20, 2008 at 20:14

Steven A. Lowe

61.4k19 gold badges136 silver badges206 bronze badges

2 Comments

HMR Over a year ago

"I wouldn't hit you with a rolled-up newspaper for using one" stumbled on this one while googling for "bad design SqlDataSource" as I'm currently trying to work on a project using sqldatasource in at leas 15 different pages. Thank you Steven for making me laugh (+1 for that). Was looking for an authority source for not using them.

Jay Sullivan Over a year ago

Steven might not hit you for using SqlDataSource, but many others would. I would.

Jeremy Frey · Accepted Answer · 2008-11-20 20:23:08Z

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.

Mark Struzinski · Accepted Answer · 2008-11-20 20:22:16Z

2

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.

answered Nov 20, 2008 at 20:22

Mark Struzinski

33.6k37 gold badges109 silver badges137 bronze badges

Comments

Alexis Pigeon · Accepted Answer · 2012-11-22 12:01:48Z

1

SQL in aspx or aspx.cs are both really novice, bad approaches. Look up n-tier / n-layer design, seperation of concerns and any book on software design.

edited Nov 22, 2012 at 12:01

Alexis Pigeon

7,52811 gold badges42 silver badges46 bronze badges

answered May 12, 2011 at 0:33

En66

191 bronze badge

1 Comment

Jay Sullivan Over a year ago

+1 "any book on software design". Database code doesn't belong in views. Also, it makes it extremely difficult to debug. I like being able to debug mycode when something goes wrong with my database.

Rich M · Accepted Answer · 2010-07-15 17:33:51Z

0

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.

answered Jul 15, 2010 at 17:33

Rich M

3871 gold badge3 silver badges12 bronze badges

Comments

MGSeggerman · Accepted Answer · 2013-07-25 12:25:58Z

0

I've used SQLDataSources to fill a grid, needing an uncomplicated query. Using a stored procedure instead of putting the query right in the SQLDataSource solves the reusability problem. The examples from Telerik controls make use of data sources extensively, but that may be due to the a need for simplicity and not real-world constraints. Another use I have encountered was on a page placed on every site at a client not particularly concerned with security that enabled the staff to execute sql on the fly. It contained a simple SQL query - opening the page was an easy way to check if the database was accessible.

answered Jul 25, 2013 at 12:25

MGSeggerman

14 bronze badges

Collectives™ on Stack Overflow

Writing queries in code behind vs. SqlDataSource

7 Answers 7

Comments

2 Comments

Comments

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

Comments

2 Comments

Comments

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related