C# How do I take my sqlDataReader offline?

Question

I have a memory leak, due to not closing the connection properly. This is due to using a global function to access the database (with different sql strings), but I pass back an sqldatareader. I cant close this in the method, nor the connection to the DB, as it closes access to the data! And it doesnt close properly from outside this method. :(

Is there Anyway way I can take the desired table, that the sqldatareader grants access to, offline. So that I can close all the connections, but still access the table.

Note, Different tables are returned so different fields exist. I dont want to have to duplicate code each time I try and connect.

private SqlDataReader OpenDataStream(String sql)
{
    SqlCommand sqlComm = new SqlCommand();
    sqlComm.Connection = new SqlConnection();
    sqlComm.Connection.ConnectionString = @"Myconnectionstring";
    sqlComm.CommandText = sql;
    sqlComm.Connection.Open();
    SqlDataReader data = null;
    data = sqlComm.ExecuteReader();

    return data;

    // Closing data here, or connection, results in returned object inaccessable.
}

or maybe a valid working way of closing it all down outside the method (after I have accessed what I need)?

possible duplicate of [Return DataReader from DataLayer in Using statement](http://stackoverflow.com/questions/850065/return-datareader-from-datalayer-in-using-statement) — nawfal, Feb 11 '13 at 18:04

score 3 · Answer 1 · answered Oct 27 '11 at 08:42

You could just return a DataTable instead of a SqlDataReader. This will fill the table with your data and you can close the connections before the method ends its execution.

private DataTable GetDataTable(String sql)
{
    SqlDataAdapter da = new SqlDataAdapter(sql, connection);
    DataSet ds = new DataSet();
    da.Fill(ds);
    return ds.Tables[0];
}

This might be worth a read.

score 3 · Accepted Answer · answered Oct 27 '11 at 08:47

3

Try the DataTable.Load Method method:

private DataTable OpenDataStream(String sql)
{

    DataTable dt = new DataTable();

    SqlCommand sqlComm = new SqlCommand();
    sqlComm.Connection = new SqlConnection();
    sqlComm.Connection.ConnectionString = @"Myconnectionstring";
    sqlComm.CommandText = sql;
    sqlComm.Connection.Open();
    SqlDataReader data = null;
    data = sqlComm.ExecuteReader();

    dt.Load(data);

    data.Close();

    return dt;
}

answered Oct 27 '11 at 08:47

Tim

28,212
8
63
76

Don't forget to close connection, using(var sqlComm.Connection.ConnectionString = @"Myconnectionstring"){} is preferred way – estinamir Mar 06 '23 at 19:06

score 2 · Answer 3 · answered Oct 27 '11 at 08:42

2

Don't return the reader, return a populated DataTable instead.

answered Oct 27 '11 at 08:42

Neil Barnwell

41,080
29
148
220

score 1 · Answer 4 · edited May 23 '17 at 12:19

1

You could consider using using something like a DataSet that provides off-line access to your data. Some examples here - http://msdn.microsoft.com/en-us/library/ms971499.aspx

or

This question provides a number of methods for taking the data out of a datareader and storing it in memory - How can I easily convert DataReader to List<T>?

edited May 23 '17 at 12:19

Community

1
1

answered Oct 27 '11 at 08:43

ipr101

24,096
8
59
61

C# How do I take my sqlDataReader offline?

4 Answers4