How to return SQL DataReader data

Question

I am new to SQL Server so sorry if my question seems trivial but I have not been able to find a good answer to this or at least one that I can utilize.

My problem is that I need to use sql datareader to get table GUIDs which I will use later to insert entries into another database. Closing the datareader disposes of all information inside of it and I have not been able to find how to return the datareader values so that I can call them later in my code. How do you return or store datareader values to be called upon later?

You have to keep the reader open while using the data or else you will need to store it in a temp location (again before closing it). You could also just use a datatable. — Mike Cheel
– Mike Cheel, Commented Jul 2, 2013 at 17:04
create a List<Guid> and add the Guids to that and return it, it'll be easier to work with — Jonesopolis
– Jonesopolis, Commented Jul 2, 2013 at 17:04
read them and put in any suitable container, like List<T>(any other generic collection or just array) — Guru Stron
– Guru Stron, Commented Jul 2, 2013 at 17:04

evanmcdonnal · Accepted Answer · 2013-07-02 17:05:00Z

3

Rather than returning the data reader add all of the values to a collection. Here you'll want a List<T> or T[] (array). This should contain objects that model the results of your query. If you're only returning the Guids then it would be List<Guid> or Guid[].

In general, all of your db interactions should be in one layer of the application. It's at that point that you want to deserialize query results. Elsewhere in your code you should deal with native C# types rather than a query result that is still pending deserialization. Here you generally just want methods that take whatever data is necessary to form a query and return an object or a collection of objects which model the query results.

answered Jul 2, 2013 at 17:05

evanmcdonnal

48.5k17 gold badges111 silver badges119 bronze badges

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

1 Comment

Juan Portela Over a year ago

Thanks for the speedy help. I ended up using ebyrob's suggestion but your suggestion is more general and should help other readers.

user645280 · Accepted Answer · 2013-07-02 17:15:07Z

0

You could just store it to a DataTable:

SqlDataReader dr = null; // something you already have
DataTable dt = new DataTable();
dt.Load(dr);

Now all the rows and columns should be available in the DataTable until you're done using it.

Note: DataTable lives at: System.Data.DataTable.

answered Jul 2, 2013 at 17:15

user645280

2 Comments

Juan Portela Over a year ago

Thanks this one worked out the best for me, it helped me bypass a lot of the work that the other options needed. Although I think using a more general List<> option would apply to more users.

user645280 Over a year ago

@JuanPortela Interesting that you notice this is useful but think a "more general" POCO solution would work for others. These 3 lines of code work for pretty much every SQL query with a result set. Dissecting and storing the result into lists of (probably user objects) requires custom code for each query (not to mention creating the storage types)

Fabian Bigler · Accepted Answer · 2013-07-02 17:19:21Z

0

How I would do it (In the data-layer):

public List<Guid> GetAllIds()
{
    var cmd = new SqlCommand(yourConnectionString, "SELECT yourIDColumn from YourTable")

    var ids = new List<GUID>();
    Using(var reader = cmd.ExecuteReader())
    {
      while (reader.Read())
      {
        ids.Add(reader.GetGuid(0))
      }
    }
  return ids;
}

answered Jul 2, 2013 at 17:19

Fabian Bigler

10.9k6 gold badges52 silver badges74 bronze badges

Collectives™ on Stack Overflow

How to return SQL DataReader data

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related