CODEWITHSUNDEEP
c#sql
Juan Portela
Juan Portela

Reputation: 3

How to return SQL DataReader data

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?

Upvotes: 0

Views: 834

Answers (3)

Fabian Bigler
Fabian Bigler

Reputation: 10895

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;
}

Upvotes: 0

user645280
user645280

Reputation:

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.

Upvotes: 0

evanmcdonnal
evanmcdonnal

Reputation: 48076

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.

Upvotes: 3

Related Questions