ADO.NET is bad practice?

Question

I read an article on MSDN a few months ago and recently started using the following code snippet to execute ADO.NET code, but it seems to me that this might be bad. Can I react or is this perfectly acceptable?

private void Execute(Action<SqlConnection> action)
{
    SqlConnection conn = null;
    try {
        conn = new SqlConnection(ConnectionString);
        conn.Open();
        action.Invoke(conn);
    } finally {
        if (conn != null && conn.State == ConnectionState.Open) {
            try {
                conn.Close();
            } catch {
            }
        }
    }
}

public bool GetSomethingById() {
    SomeThing aSomething = null
    bool valid = false;
    Execute(conn =>
    {
        using (SqlCommand cmd = conn.CreateCommand()) {
            cmd.CommandText = ....
            ...
            SqlDataReader reader = cmd.ExecuteReader();
            ...
            aSomething = new SomeThing(Convert.ToString(reader["aDbField"]));
        }
    });
    return aSomething;
}

Answer 1

What is the point of doing this when you can do it?

public SomeThing GetSomethingById(int id) 
{
    using (var con = new SqlConnection(ConnectionString)) 
    {
        con.Open();
        using (var cmd = con.CreateCommand()) 
        {
            // prepare command
            using (var rdr = cmd.ExecuteReader()) 
            {
                // read fields
                return new SomeThing(data);
            }
        } 
    }
}

You can promote code reuse by doing something like this.

public static void ExecuteToReader(string connectionString, string commandText, IEnumerable<KeyValuePair<string, object>> parameters, Action<IDataReader> action) 
{
    using (var con = new SqlConnection(connectionString)) 
    {
        con.Open();
        using (var cmd = con.CreateCommand()) 
        {
            cmd.CommandText = commandText;
            foreach (var pair in parameters) 
            {
                var parameter = cmd.CreateParameter();
                parameter.ParameterName = pair.Key; 
                parameter.Value = pair.Value; 
                cmd.Parameters.Add(parameter);
            }
            using (var rdr = cmd.ExecuteReader()) 
            {
                action(rdr);
            }
        } 
    }    
}

You can use it as follows:

//At the top create an alias
using DbParams = Dictionary<string, object>;

ExecuteToReader(
    connectionString, 
    commandText, 
    new DbParams() { { "key1", 1 }, { "key2", 2 } }),
    reader => 
    {
        // ...
        // No need to dispose
    }
)

Answer 2

IMHO, this is really bad practice, because you create and open a new database connection for each operator you perform.

Why is that bad:

• ( ): , , , .

• , , . : ? ? , , sql .

Answer 3

. SqlUtilities , . , .

EDIT: , (, ;))

SQLUtilities

public delegate T CreateMethod<T> (SqlDataReader reader);
public static T CreateEntity<T>(string query, CreateMethod<T> createMethod, params SqlParameter[] parameters) {
   // Open the Sql connection
   // Create a Sql command with the query/sp and parameters
   SqlDataReader reader = cmd.ExecuteReader();
   return createMethod(reader);
   // Probably some finally statements or using-closures etc. etc.
} 

private SomeThing Create(SqlDataReader reader) {
    SomeThing something = new SomeThing();
    something.ID = Convert.ToIn32(reader["ID"]);
    ...
    return something;
}

public SomeThing GetSomeThingByID (int id) {
    return SqlUtilities.CreateEntity<SomeThing> ("something_getbyid", Create, ....);
}

, Create, CreateCollection Create.

. LINQ . , ADO.Net.

Answer 4

.

, Action<SqlConnection>, . lambdas, .

Answer 5

Well, in my opinion, check what you are doing before going through it. Something that works does not mean that it is the best and good programming practice. Check and find a specific example and the benefits of using it. But if you are considering using for large projects, it would be nice to use frameworks like NHibernate. Since there are many projects based on it, for example, http://www.cuyahoga-project.org/ .