Petapoco with Datatable

Question

I am new in Petapoco,Can I retrieve table data as Datatable using stored procedure in petapoco ORM ? I tried in google but I didn't get any answer from google.Any one know that?

you can use sql statement like "exec sp_name @@param1 = @0" and give your parameters — Laurent Lequenne, Jul 06 '17 at 14:41
You can do this, but note that the idea of PetaPoco is to give you strongly typed objects to work with; if you want a DataTable, you can use standard ADO.NET methods to do so. — asherber, Oct 22 '18 at 02:01

score 1 · Answer 1 · answered Oct 17 '18 at 06:56

1

step 1:

    public DataTable ListToDataTable(IList list)
    {
        var dt = new DataTable();
        if (list.Count <= 0) return dt;

        var properties = list[0].GetType().GetProperties();
        foreach (var pi in properties)
        {
            dt.Columns.Add(pi.Name, Nullable.GetUnderlyingType(pi.PropertyType) ?? pi.PropertyType);
        }

        foreach (var item in list)
        {
            DataRow row = dt.NewRow();
            properties.ToList().ForEach(p => row[p.Name] = p.GetValue(item, null) ?? DBNull.Value);
            dt.Rows.Add(row);
        }
        return dt;
    }

step 2:

    var list = db.Fetch<dynamic>(sql);
    var dt = ListToDataTable(list);

answered Oct 17 '18 at 06:56

Jack CQ

21
2

Thanks for your answer.But the same code is not working for me.I created some changes in your code.Then Its working fine.The below is the changes. – achu Oct 17 '18 at 07:36
var dt = new DataTable(); if (list.Count <= 0) return dt; var obj = list[0]; var eoAsDict = ((IDictionary)obj); eoAsDict.ToList().ForEach(p => dt.Columns.Add(p.Key)); foreach (var item in list) { var eoAsDict1 = ((IDictionary)item); DataRow row = dt.NewRow(); eoAsDict1.ToList().ForEach(p => row[p.Key] = p.Value ?? DBNull.Value); dt.Rows.Add(row); } return dt; – achu Oct 17 '18 at 07:58

score 0 · Answer 2 · answered Jul 06 '17 at 18:23

Yes, you can execute stored procedures using Petapoco.

Example:

var result = db.Fetch<dynamic>(";EXEC GetSomething @@Year = @0", 2017);

Petapoco returns a List<T> and I recommend you to use List instead of Datatable, but if you want you can convert your list to a Datatable

Petapoco with Datatable

2 Answers2