Examples Of SqlDataSource In Production Code

I'm new to ASP.net and I'm currently reading ab开发者_运维问答out the SqlDataSource and running through some examples. I was talking to a co-worker who said it was 'fine for quick and dirty apps' but nobody would use it in a production/deployed environment.

I didn't really know where to look or ask for opinions on that. It's very different from what I'm used to (a data access layer / Business objects / UI).

Maybe this isn't an appropriate StackOverflow Question, but I didn't know where else to go. Does anyone have an opinion on this? Can someone provide a link or two of production/big/awesome websites that are using this approach?

In terms of best practices - what approach do you recommend for getting data out of a database to use in your website?

---

I've never seen performance issues using a SqlDataSource, but I've also never used it for a large website, just small businesses that get maybe 10-15 visits a day, or internal apps. There are a lot of things that it can make really easy (features like built-in caching, filtering, paging, etc.). The downside has more to do with maintenance than performance. Using a SqlDataSource means sprinkling in-line SQL throughout every page of your website, which is obviously not a best practice for enterprise development. It doesn't fit with the typical tiered development approach, because it forces you to embed both your data access and business logic into the presentation layer. So the deciding factor really isn't related to performance, it has more to do with complexity.

---

To do basic sql 'stuff' use a SqlConnection and a SqlDataAdapter
See: http://www.dotnetperls.com/sqldataadapter

You can also use SqlCommand (many tutorials - but to put you in the right direction) http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson03.aspx

Make sure you put SqlConnection in a using statement so the object is automatically disposed when the using statement goes out of scope.

Off the top of my head you can call a stored procedure as such using a data reader which does not put the results into a dataset but is read like a stream.

using (var connection = new SqlConnection(connectstring))
{
    using (SqlCommand command = new SqlCommand("dbo.SomeProc", connectString))
    {
        command.CommandType = System.Data.CommandType.StoredProcedure;
        command.CommandTimeout = 1000;
        command.Parameters.Add(new SqlParameter("@ParamName", "someValue");

        using(var reader = command.ExecuteReader())
        {
            while (dataReader.Read())
            {
                string someValue = dataReader["SomeColumnName"];
            }
        }
    }
}

---

I don't have a lot to add to what Joel says, but this is one of those questions where the more answers it gets, the more helpful it becomes.

I have 3 sites in production that still use SqlDataSource controls. They have been going since early 2006. They are about to be overhauled and moved across to MVC, but that's not because they suffer performance issues. It's because the customer wants them refreshed and extended after 5 years. They are not huge busy sites, but SqlDataSources would work in those just as well as anywhere else. They are simply wrappers around ADO.NET. They are probably a lot more performant than most ORMs.

I don't have inline SQL in those sites. The DataSource controls call stored procedures. In that respect, the Data Access layer exists and is in one place - the database itself.