c# how do you return dataset from sqldatareader?

Question

I have this in a public class:

SqlConnection myConnection = new SqlConnection("Data Source=hermes;database=qcvalues; Integrated Security=SSPI;");
myConnection.Open();
SqlDataReader myReader = null;
SqlCommand myCommand = new SqlCommand(InitializeQuery(), myConnection);
myReader = myCommand.ExecuteReader();

I need the datasource of a control to get the dataset from myReader.

Unfortunately this is difficult to do because the control is on a form (a separate class). how would I return myReader dataset into the datasource property of the control on my form?

Use a DataTable if you only have one result set coming back. Use a DataSet if you have multiple. — Dismissile, Nov 04 '10 at 18:22
According to a commenter [here](http://www.velocityreviews.com/forums/t99983-does-sqldataadapter-fill-always-close-the-connection.html), who shows the code for DataAdapter.Fill there if you want to read it, the Fill method has its own try-finally which closes the connection so you don't have to. See also [this SO question](http://stackoverflow.com/questions/2472854/does-dataadapter-fill-close-its-connection-when-an-exception-is-thrown). As long as you don't open the connection in your code, Fill will close the connection if an exception occurs. — DOK, Nov 04 '10 at 18:25

Justin Niessner · Accepted Answer · 2010-11-04T18:09:07.953

45

You don't. Use a DataAdapter instead:

var ds = new DataSet();

using(var conn = new SqlConnection(connString))
{
    conn.Open();
    var command = new SqlCommand(InitializeQuery(), conn);
    var adapter = new SqlDataAdapter(command);

    adapter.Fill(ds);
}

edited Nov 04 '10 at 18:09

answered Nov 04 '10 at 17:59

Justin Niessner

242,243
40
408
536

1

@i am a girl - It would work. On the other hand, this is what DataAdapter is for (getting an in-memory representation of your data and putting it into a container). – Justin Niessner Nov 04 '10 at 18:05
1

I believe the DataAdapter will do the connection opening and closing. Is it desirable to wrap that in a using statement as well? – DOK Nov 04 '10 at 18:08
@DOK - Yes. Even if the adapter wraps the open/close calls, there's still nothing to dispose of the instance when you're done with it (for instance, in case of an exception). – Justin Niessner Nov 04 '10 at 18:09
@JustinNiessner What's wrong with Sanjib Bose's answer ? Seems like we can load Dataset from DataReader that way. The reason I don't want to use DataAdapter is - it does not have async implementation. – Jay Shah Jun 06 '19 at 03:03

score 26 · Answer 2 · answered Nov 04 '10 at 17:59

26

if you can use DataAdapter subclass or use something as:

DataTable myTable = new DataTable();

myTable.Load(myCommand.ExecuteReader());

and then return DataTable to client.

answered Nov 04 '10 at 17:59

pyCoder

503
3
9

1

This is a good idea if you only need a DataTAble and not the whole DataSet. – DOK Nov 04 '10 at 18:04
3

Generally dont'need to return all DataSet if a simple query return a single set of rows :) – pyCoder Nov 04 '10 at 18:08
1

I wanted to take advantage of Command.ExecuteReaderAsync() for async/await yet still return a DataTable. This looks like the best way to accomplish that. Thanks. – Sean B Jan 07 '16 at 15:16

score 16 · Answer 3 · edited Jun 01 '17 at 18:57

16

IDataReader reader;
DataSet ds;

while (!reader.IsClosed)
   ds.Tables.Add().Load(reader);

edited Jun 01 '17 at 18:57

Samvel Petrosov

7,580
2
22
46

answered Jun 01 '17 at 09:53

Sanjib Bose

171
1
2

2

I can't understand why this answer got negatived. It *does* answer the question "get the dataset from myReader". Simple as that. – Cesar Jan 21 '18 at 14:50
Excellent answer for Microsoft.Data.Sqlite which has no data adapter this works. – dgxhubbard Nov 15 '20 at 00:05

score 5 · Answer 4 · edited Oct 26 '12 at 15:53

5

Instead of returning a SqlDataReader, you can change your code so that it returns a DataSet.

SqlConnection myConnection = new SqlConnection("Data Source=hermes;database=qcvalues; Integrated Security=SSPI;");
DataSet dst = new DataSet();
SqlDataAdapter dap = new SqlDataAdapter(InitializeQuery(), mConnection);
dap.Fill(dst, "DataSetName");

One of the neat things about this approach is that Fill opens and closes the database connection for you.

edited Oct 26 '12 at 15:53

noelicus

14,468
3
92
111

answered Nov 04 '10 at 18:01

DOK

32,337
7
60
92

1

Doesn't have to be a DataSet - you can just fill a DataTable which will be more lightweight, if appropriate. – AdaTheDev Nov 04 '10 at 18:11
@DOK what should the dataset name be.? – Alex Gordon Nov 04 '10 at 18:13
@i am a girl - you just pass a DataTable to the .Fill method, instead of a DataSet. See: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter.fill(v=VS.90).aspx – AdaTheDev Nov 04 '10 at 18:15
It only needs to be a DataSet if you have multiple DataTables, and especially if you have relations between those tables (such as primary key-foreign key). That may be more common in desktop applications, where data is kept around for a while on the client. If you only have one DataTable, you don't need a DataSet. – DOK Nov 04 '10 at 18:18
The DataSet name can be anything or nothing. If you have more than one of them, it can be helpful to give it a name. Same thing applies to DataTables. So, these names are optional. – DOK Nov 04 '10 at 18:19

score 2 · Answer 5 · edited Oct 30 '19 at 13:10

2

If your SelectCommand is stored procedure, the Fill method of the adapter will rise exception.

In these cases you can use:

DataTable dt = new DataTable();
dt = sdr.GetSchemaTable();
dt.Constraints.Clear();
dt.BeginLoadData();
dt.Load(sdr);
//dt.EndLoadData(); // Enables constraints again

edited Oct 30 '19 at 13:10

Prashant Pimpale

10,349
9
44
84

answered Nov 06 '13 at 13:21

d.popov

4,175
1
36
47

score 0 · Answer 6 · answered Apr 13 '20 at 05:22

the below snippet works fine for me on SqlServer:

public DataSet executeProcedure(String procudureName, params SqlParameter[] sqlParameters)
{
    return executeSqlCommand(procudureName, CommandType.StoredProcedure, sqlParameters);
}
public DataSet executeSql(String commandText, params SqlParameter[] sqlParameters)
{
    return executeSqlCommand(commandText, CommandType.Text, sqlParameters);
}
public DataSet executeSqlCommand(String commandText, CommandType Commandtype, params SqlParameter[] sqlParameters)
{
    DataSet myset = new DataSet();
    using (var command = Database.GetDbConnection().CreateCommand())
    {
        command.CommandText = commandText;
        command.CommandType = Commandtype;
        foreach (var _kv in sqlParameters)
        {
            DbParameter _dbpara = command.CreateParameter();
            _dbpara.ParameterName = _kv.ParameterName;
            _dbpara.Value = _kv.Value;
            command.Parameters.Add(_dbpara);
        }
        Database.OpenConnection();
        DbDataAdapter adapter = DbProviderFactories.GetFactory(Database.GetDbConnection()).CreateDataAdapter();
        adapter.SelectCommand = command;
        adapter.Fill(myset);
    }
    return myset;
}

the DbDataAdapter instance can be retrieved from DbProviderFactories class.

DbDataAdapter adapter = DbProviderFactories.GetFactory(Database.GetDbConnection()).CreateDataAdapter();

score 0 · Answer 7 · answered Aug 06 '22 at 22:24

I added the following method into my DataContext class:

public async Task<DataSet> ExecReturnQuery(string query)
    {
        using (var command = this.Database.GetDbConnection().CreateCommand())
        {
            command.CommandText = query;

            this.Database.OpenConnection();

            using (var result = await command.ExecuteReaderAsync())
            {
                // get DataSet from result
                var ds = new DataSet();
                ds.Load(result, LoadOption.OverwriteChanges, "Table");

                // returning DataSet (instead of DbDataReader), cause can't use DbDataReader after CloseConnection().
                this.Database.CloseConnection();

                return ds;
            }
        }
    }

Then I call it from any class like so:

DataSet dataSet = await _dataContext.ExecReturnQuery("SELECT * FROM MyTable");

c# how do you return dataset from sqldatareader?

7 Answers7

Linked