How do I clone data from a SqlDataReader keeping all Columns and Rows in a way that can be exported through a method?

Question

I'm currently trying to create a method that allows me to run any query on a connectionstring, and get the results returned in a readable way for my ASP.Net website. Because I need access to all rows and columns a query might need, I cannot simply return a string or an array of strings. If I return a SqlDataReader, I'm not able to read the data outside of the method, because the connection is closed.

This is what my method currently looks like.

private SqlDataReader QueryConnectionString (string query)
    {
        // New SQL Connection
        SqlConnection cnn;

        // New Connection String
        string connetionString = ConfigurationManager.ConnectionStrings["ConnectionStringName"].ConnectionString;

        // Instantiate the SQL Connection with Connection String
        cnn = new SqlConnection(connetionString);

        // Create a SqlCommand with the input Query
        SqlCommand command = new SqlCommand(query, cnn);

        cnn.Open();

        // Create a SqlDataReader and tie it to the query
        SqlDataReader reader = command.ExecuteReader();        

        cnn.Close();

        return reader;
    }

In my other methods I would have something like this

SqlDataReader reader = QueryConnectionString("SELECT * FROM tTable");

lblOutput.Text = reader.GetString(0);

But doing so gives me the error

System.InvalidOperationException: 'Invalid attempt to call CheckDataIsReady when reader is closed.'

I realize that returning a SqlDataReader is not an option. What can I return the data as so that other methods can read the data?

At the most basic level you could use the reader to fill a DataTable and return a DataTable (see DataTable.Load method) but the best thing to do is to have a specific object that represent your data record and fill a list of those objects. ORM are libraries that help you in this field converting your bare bone records in your domain objects — Steve, Apr 24 '19 at 22:31

haldo · Accepted Answer · 2019-04-25T11:12:20.473

You can return a DataTable which will preserve the columns and rows of the query. Instead of a SqlDataReader consider using SqlDataAdapter. One advantage is that the Fill method of SqlDataAdapter will open and close the connection for you.

var dt = new DataTable();    
using (var da = new SqlDataAdapter(query, cnn))
{      
    da.Fill(dt);
}

Your full method might look something like this:

private DataTable GetData(string query)
{
    // New Connection String
    string connetionString = ConfigurationManager.ConnectionStrings["ConnectionStringName"].ConnectionString;

    // Instantiate the SQL Connection with Connection String
    SqlConnection cnn = new SqlConnection(connetionString);

    // declare datatable
    var dt = new DataTable();    

    // create SqlDataAdapter 
    using (var da = new SqlDataAdapter(query, cnn))
    {      
        // fill datatable
        da.Fill(dt);
    }

    return dt;
}

You will then need to read from the DataTable. See my answer here for a method to read data. The method in the linked answer uses the Field<T> extension from DataSetExtensions to extract data. Please see example below:

// get data from your method
DataTable table = GetData("select * from MyTable");

// iterate over the rows of the datatable
foreach (var row in table.AsEnumerable())  // AsEnumerable() returns IEnumerable<DataRow>
{
    var id = row.Field<int>("ID");                           // int
    var name = row.Field<string>("Name");                    // string
    var orderValue = row.Field<decimal>("OrderValue");       // decimal
    var interestRate = row.Field<double>("InterestRate");    // double
    var isActive = row.Field<bool>("Active");                // bool
    var orderDate = row.Field<DateTime>("OrderDate");        // DateTime
}

To check if a DataTable is null/ empty see this answer. It can be as simple as:

if(table?.Rows?.Count() > 0) { ... }

score 2 · Answer 2 · answered Apr 24 '19 at 22:52

You can try this

IEnumerable<IDataRecord> GetRecords()
{
    using(var connection = new SqlConnection(@"..."))
    {
        connection.Open();

        using(var command = new SqlCommand(@"...", connection);
        {
            using(var reader = command.ExecuteReader())
            {
               while(reader.Read())
               {
                   // your code here.
                   yield return reader;
               }
            }
        }
    }
}

How do I clone data from a SqlDataReader keeping all Columns and Rows in a way that can be exported through a method?

2 Answers2