CODEWITHSUNDEEP
c#entity-frameworkcollectionsdatatableienumerable
Hesoti
Hesoti

Reputation: 87

DataTable iteration gives unwanted data

I am iterating through a DataTable using SqlDataAdapter and storing it in a list. Here is my code.

public IEnumerable<DataRow> GetRecord()
{
    var table = new DataTable();
    using (var da = new SqlDataAdapter("SELECT * FROM mv_tbl", "ConnectionString"))
    {
        da.Fill(table);
        List<DataRow> list = new List<DataRow>();
        foreach (DataRow r in table.Rows)
        {
            list.Add(r);
        }

        return list;
    }
}

This gives me a result which has unnecessary data also. Here is the result.

[{"RowError":"",
"RowState":2,
"Table":[{"mv_id":2,
"mv_name":"Up",
"mv_link":"link",
"user":"[email protected]",
"db_tstamp":"2020-01-01T01:50:00"},
{"mv_id":3,
"mv_name":"8 Below",
"mv_link":"link",
"user":"[email protected]",
"db_tstamp":"2020-01-01T02:49:00"}],
"ItemArray":[2,"Up","link","[email protected]","2020-01-01T01:50:00"],
"HasErrors":false},
{"RowError":"",
"RowState":2,
"Table":[{"mv_id":2,
"mv_name":"Up",
"mv_link":"link",
"user":"[email protected]",
"db_tstamp":"2020-01-01T01:50:00"},
{"mv_id":3,
"mv_name":"8 Below",
"mv_link":"link",
"user":"[email protected]",
"db_tstamp":"2020-01-01T02:49:00"}],
"ItemArray":[3,"8 Below","link","[email protected]","2020-01-01T02:49:00"],
"HasErrors":false}]

My expected result is.

[{"mv_id":2,
    "mv_name":"Up",
    "mv_link":"link",
    "user":"[email protected]",
    "db_tstamp":"2020-01-01T01:50:00"},
    {"mv_id":3,
    "mv_name":"8 Below",
    "mv_link":"link",
    "user":"[email protected]",
    "db_tstamp":"2020-01-01T02:49:00"}]

Here is a screenshot from the database.

Table Records screenshot

My original question is here.

How can I return a list of rows of dynamic table?

Where am I making a mistake.?

Upvotes: 0

Views: 154

Answers (2)

Bharati Mathapati
Bharati Mathapati

Reputation: 119

I think you are trying to serialize your list (GetRecord return result).

But before serialize you need to pass datatable to below function (in your case you are returning IEnumerable<DataRow>, try passing datatable) and convert it into List. Now try serializing this List<dynamic>.

public  List<dynamic> ConvertToDynamic(DataTable dt)
{
        var lstOfDynamic = new List<dynamic>();

        foreach (DataRow row in dt.Rows)
        {
            dynamic dyn = new  System.Dynamic.ExpandoObject();

            foreach (DataColumn col in dt.Columns)
            {
                var dic = (IDictionary<string, object>)dyn;
                dic[col.ColumnName] = row[col];
            }

            lstOfDynamic.Add(dyn);
        }

        return lstOfDynamic;
    }

Hope this will help you.

Upvotes: 1

Anu Viswan
Anu Viswan

Reputation: 18155

You could convert the DataTable to a collection of Dictionary<string,object> (EnumerableRowCollection<Dictionary<string,object>)

var result = dt.AsEnumerable()
                    .Select(row => dt.Columns
                                     .Cast<DataColumn>()
                                     .ToDictionary(column => column.ColumnName,column => row[column]));
return result;

This would also produce the desired Json return from Web API.

Alternatively, you could return the DataTable itself or make use of collection of anonymous types or user defined class.

Upvotes: 3

Related Questions