Generic data access code to stored procedures to return Enumerable?

Question

Instead of repeating the same ADO.net code with a different Enumerable, I want to make it more generic and reusable.

I have the following ADO.Net code to return a collection of objects:

public static IEnumerable<TasCriteria> GetTasCriterias()
    {
        using (var conn = new SqlConnection(_connectionString))
        {
            var com = new SqlCommand();
            com.Connection = conn;
            com.CommandType = CommandType.StoredProcedure;

            com.CommandText = "IVOOARINVENTORY_GET_TASCRITERIA";
            var adapt = new SqlDataAdapter();
            adapt.SelectCommand = com;
            var dataset = new DataSet();
            adapt.Fill(dataset);

            var types = (from c in dataset.Tables[0].AsEnumerable()
                         select new TasCriteria()
                         {
                              TasCriteriaId = Convert.ToInt32(c["TasCriteriaId"]),
                              TasCriteriaDesc= c["CriteriaDesc"].ToString()
                         }).ToList<TasCriteria>();

            return types;
        }

    }

Model:

 public class TasCriteria
    {
        public int TasCriteriaId { get; set; }
        public string TasCriteriaDesc { get; set; }
    }

What code is repeating? Show an example of the similar invocation, which should be covered by the generic implementation. Also show what you have by now and where you stand. — Edin, May 25 '14 at 17:50
Do all your models contain a `CriteriaId` and a `CriteriaDesc`? — Yuval Itzchakov, May 25 '14 at 18:08

DavidG · Answer 1 · 2014-05-25T18:38:50.740

If your stored procedure returns column names matching exactly properties in your class, you can use reflection like this code. I'll leave an exercise to add parameters to the stored procedure for someone else. (by the way, this is untested code, I wrote it off the top of my head)

    public static IEnumerable<T> GetStoredProcedure<T>(string procedure) where T : new()
    {
        var data = new List<T>();

        using (var conn = new SqlConnection(_connectionString))
        {
            var com = new SqlCommand();
            com.Connection = conn;
            com.CommandType = CommandType.StoredProcedure;

            com.CommandText = procedure;
            var adapt = new SqlDataAdapter();
            adapt.SelectCommand = com;
            var dataset = new DataSet();
            adapt.Fill(dataset);

            //Get each row in the datatable
            foreach (DataRow row in dataset.Tables[0].Rows)
            {
                //Create a new instance of the specified class
                var newT = new T();

                //Iterate each column
                foreach (DataColumn col in dataset.Tables[0].Columns)
                {
                    //Get the property to set
                    var property = newT.GetType().GetProperty(col.ColumnName);
                    //Set the value
                    property.SetValue(newT, row[col.ColumnName]);
                }

                //Add it to the list
                data.Add(newT);
            }

            return data;
        }

    }

So lets say you have a class like this:

public class TasCriteria
{
    public int TasCriteriaId { get; set; }
    public string TasCriteriaDesc { get; set; }
}

You would call the function like this:

IEnumerable<TasCriteria> criteria = GetStoredProcedure<TasCriteria>("IVOOARINVENTORY_GET_TASCRITERIA");

HuorSwords · Accepted Answer · 2014-05-25T18:51:47.837

0

An option would be to extract your specific code outside of your main method.

In example:

public static IEnumerable<T> GetCriterias(
                            string storedProcedureName,
                            Func<IEnumerable<T>> enumerateMethod)
{
    using (var conn = new SqlConnection(_connectionString))
    {
        var com = new SqlCommand();
        com.Connection = conn;
        com.CommandType = CommandType.StoredProcedure;

        com.CommandText = storedProcedureName;
        var adapt = new SqlDataAdapter();
        adapt.SelectCommand = com;
        var dataset = new DataSet();
        adapt.Fill(dataset);

        return enumerateMethod(dataset.Tables[0]);
    }
}

If your stored procedure requires parameters, you can overload your function as:

public static IEnumerable<T> GetCriterias(
                        string storedProcedureName,
                        Func<IEnumerable<T>> enumerateMethod,
                        SqlParameter[] parameters)

Using this code, you can define methods that matches de Func<> signature.

public static IEnumerable<TasCriteria> EnumerateTasCriteria(DataTable table)
{
    return 
        (from c in table.AsEnumerable()
        select new TasCriteria()
        {
            TasCriteriaId = Convert.ToInt32(c["TasCriteriaId"]),
            TasCriteriaDesc= c["CriteriaDesc"].ToString()
        }).ToList<TasCriteria>();
}

public static IEnumerable<DetailCriteria> EnumerateDetailCriteriaCriteria(
                                            DataTable table)
{
    return 
        (from c in table.AsEnumerable()
         select new DetailCriteria()
        {
        DetailCriteriaId = Convert.ToInt32(c["DetailCriteriaId"]),
        DetailCriteriaDesc = c["CriteriaDesc"].ToString()
        }).ToList<TasCriteria>();
}

Then, you can call your code as:

IEnumerable<TasCriteria> task = 
         GetCriterias<TasCriteria>("IVOOARINVENTORY_GET_TASCRITERIA", EnumerateTasCriteria);
IEnumerable<DetailCriteria> details =
         GetCriterias<DetailCriteria>("IVOOARINVENTORY_GET_DETAILSCRITERIA", EnumerateDetailCriteriaCriteria);

edited May 25 '14 at 18:51

answered May 25 '14 at 18:24

HuorSwords

2,225
1
21
34

i'm still learning ...In terms of separating them up in class files...what would be good practice? Naming conventions? – Chaka May 25 '14 at 18:37
This last question requires an specific answer itself, I think... In my opinion, this could be a good oportunity to apply [Repository pattern](http://martinfowler.com/eaaCatalog/repository.html). – HuorSwords May 25 '14 at 18:44
I was actually hinting that...All the examples of repository involve EF. – Chaka May 25 '14 at 18:46
One small request, if I have parameters? How would I take that into account. – Chaka May 25 '14 at 18:47
Ex: comm.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int)).Value = criteriaId; – Chaka May 25 '14 at 18:48
@Chaka what kind of parameters? For your stored procedure? – HuorSwords May 25 '14 at 18:48
Yes, if I want to grab one tasCriteria by Id for example. – Chaka May 25 '14 at 18:51
Updated answer. You can overload your signature to allow an array of parameters (or `List – HuorSwords May 25 '14 at 18:52
Btw, I have a bounty for this question if you have the time? – Chaka May 25 '14 at 21:37
http://stackoverflow.com/questions/23813905/re-factor-my-dal-code-to-a-domain-driven-design-or-more-modern-design-c-3-5 – Chaka May 25 '14 at 21:38

Generic data access code to stored procedures to return Enumerable?

2 Answers2