Best Practices for accessing data in .NET (non MVC)

Question

I am using .NET 3.5, and looking at old code done by someone else and trying to add security and update it.

What are the best practices for accessing data in a web forms project?

Currently I am changing the code to use SQL parameterization, like so:

using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[ConfigurationManager.AppSettings["defaultConnection"]].ConnectionString))
{
    using (SqlCommand myCommand = new SqlCommand(sql.ToString(), conn))
    {
        myCommand.Parameters.AddWithValue("search1", mySearchVar);
        ...

I know SQL parametization is important, but I see other people using stored procedures? Is they other ways, best practices to follow?

Answer 1

If it's not just small refactoring and you have time to rewrite your data access layer, use some ORM:

NHibernate

Entity Framework

Dapper.NET (Stackoverflow ORM)

BLToolkit

Answer 2

There is nothing wrong with using ADO.NET. It's what drives all the ORM solutions in .NET.

Yet it seems .NET developers are running in droves to jump on the ORM bandwagon. ORMs are just one of many tools in the data access toolbox.

In the early 2000's ORMs took the Java world by storm. Store procedures were shunned. It was ORM or nothing. A half decade later Java developers realized that the best solution uses both an ORM and stored procedures, each has strengths.

Use the best tool for the job. ORMs can automate much of the CRUD from the application. Stored procedures are good for adding abstraction, adding a layer of security and optimizing area's that need to be highly performant.

Choose the best tool for the job.