Convert SqlDataReader to a List of unknown types because of dynamic query

Question

Friends, I have a dynamic sql query that I would like to execute and return a list. From most of my internet searches on goole, I found that the type of the list has to be known to convert the sqldatareader to a list. How can I do that when I do not know what query will be executed.

Execute(String query)
{
  SqlConnection con=new SqlConnection(connection);//connection from elsewhere
  SqlCommand cmd = new SqlCommand(query);
  cmd.connection=con;
  con.Open();
  SqlDataReader result=cmd.ExecuteReader();
  //How to convert result to a list when i do not know the table structure 
}

Now I want to convert the result to a list. However I do not know the details of the table which the query works on.The query can vary, it can query any table. So under these conditions how can I convert the result to a list. Is it possible?

This leaves me with one simple question, if I do not know the table details, then what will be the entries of the list or it will be a list of what?

To answer that question, I ask myself another question, is it possible to have a list where each list entry will correspond to one row of the SqlDataReader result?

So I can use a List < DataRow > x =result.Select().ToList(), but I do not want to use this either.

I want each entry of the list to map to each row of the result and at the same time be composed of the atomic datatypes which make a row.

Eg Select studentname,studentid from student; This will return me a SqlDataReader result and I want to construct a list whose type contains a string and an int and then populate the list with the rows of the result.

When the query changes to select marks,subject,grade from marks where studentid=1432, then I want to construct a list whose type contains int,string,char and then fill the list with the rows of the result.

Is there a way to do it?

PS The user knows how to pick the values if I return a list as detailed above, since he created the query and he knows how many columns to expect.

can't you use a SqlTableAdapter ? you'll get a collection of DataRow, whichever is the schema of the result — Steve B
– Steve B, Commented Dec 6, 2011 at 9:19

Marc Gravell · Accepted Answer · 2011-12-06 09:27:33Z

3

If the caller will know the type, make is a generic method, and create a T per row...

Or, since dapper-dot-net already does that:

var list = connection.Query<T>(command [, args]).ToList();

If the caller can't know the T either, you could use ExpandoObject to populate a List<dynamic>, casting the expando to IDictionary<string,object> to populate it with key/value pairs.

Or, since dapper-dot-net already does that:

var list = connection.Query(command [, args]).ToList();

Then the caller can use:

foreach(var item in list) {
    Console.WriteList("{0}, {1}, {2}", item.marks, item.subject, item.grade);
}

answered Dec 6, 2011 at 9:27

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

david Over a year ago

Gravell, Super is the word! Thanks a lot, this is really helpful to achieve what I wanted. I also learnt something new today, Dapper dot net and ExpandoObject.

Collectives™ on Stack Overflow

Convert SqlDataReader to a List of unknown types because of dynamic query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related