Any decent ADO.NET Helper utils out there?

Question

Hi all,

I am looking for some decent ADO.NET helper utility class to allow me to query an ado.net datasource. I am not looking for anything too fancy, but it has to support transactions. Is there anything out there?

P.S. I know the data access block will do that but I was looking for something a bit more independent to other components. like a simple library or something

Answer 1

Use a simple ORM like SubSonic ?

Answer 2

It depends on your definitions of "nothing fancy" and "simple", but BLToolkit does a pretty decent job of abstracting away boilerplate ADO.NET code that you don't really want to write manually. For instance,

public abstract class PersonAccessor : DataAccessor<Person>
{
    [SqlQuery("SELECT * FROM Person WHERE PersonID = @id")]
    public abstract Person GetPersonByID(int @id);
}

Person person = DataAccessor.CreateInstance<PersonAccessor>.
    GetPersonByID(2);

will fetch a Person object from the DB in just -- see that? -- 5-6 lines of code.

As for DataSets, here's how:

        using (DbManager db = new DbManager())
        {
            DataSet ds = db
                .SetCommand("SELECT * FROM Person")
                .ExecuteDataSet();

            Assert.AreNotEqual(0, ds.Tables[0].Rows.Count);
        }

Adding parameters is as simple as calling a method on the DbManager.

And you shouldn't be really afraid of ORMs.

Answer 3

If its just a light wrapper Why not just wrap the commands yourself?

eg:

    /// <summary>
    /// Executes a stored procedure or query, returns the number of rows effected.
    /// </summary>
    /// <param name="commandText"></param>
    /// <param name="commandType"></param>
    /// <param name="sqlParameters"></param>
    /// <param name="sqlTransaction"></param>
    /// <returns></returns>
    public static int ExecuteQuery(string commandText, CommandType commandType, List<SqlParameter> sqlParameters, SqlTransaction sqlTransaction)
    {
        if (sqlTransaction == null)
        {
            using (SqlConnection sqlConnection = new SqlConnection(GetConnectionString()))
            {
                sqlConnection.Open();
                using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.CommandType = commandType;
                    sqlCommand.CommandText = commandText;
                    if (sqlParameters != null)
                    {
                        foreach (SqlParameter sqlParameter in sqlParameters)
                        {
                            sqlCommand.Parameters.Add(sqlParameter);
                        }
                    }
                    return sqlCommand.ExecuteNonQuery();
                }
            }
        }
        else
        {
            SqlCommand sqlCommand = new SqlCommand(commandText, sqlTransaction.Connection, sqlTransaction);
            sqlCommand.CommandType = commandType;
            foreach (SqlParameter sqlParameter in sqlParameters)
            {
                sqlCommand.Parameters.Add(sqlParameter);
            }
            return sqlCommand.ExecuteNonQuery();
        }
    }

Answer 4

• Rhino.Commons' Use
• Fluent Ado.Net