30

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?

Improve this question
2
  • Use a DataTable if you only have one result set coming back. Use a DataSet if you have multiple. Commented Nov 4, 2010 at 18:22
  • According to a commenter here, 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. As long as you don't open the connection in your code, Fill will close the connection if an exception occurs. Commented Nov 4, 2010 at 18:25

7 Answers 7

Reset to default
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);
}
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

@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).
I believe the DataAdapter will do the connection opening and closing. Is it desirable to wrap that in a using statement as well?
@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).
@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.
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.

Improve this answer

3 Comments

This is a good idea if you only need a DataTAble and not the whole DataSet.
Generally dont'need to return all DataSet if a simple query return a single set of rows :)
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.
16 
IDataReader reader;
DataSet ds;

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

2 Comments

I can't understand why this answer got negatived. It does answer the question "get the dataset from myReader". Simple as that.
Excellent answer for Microsoft.Data.Sqlite which has no data adapter this works.
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.

Improve this answer

5 Comments

Doesn't have to be a DataSet - you can just fill a DataTable which will be more lightweight, if appropriate.
@DOK what should the dataset name be.?
@i am a girl - you just pass a DataTable to the .Fill method, instead of a DataSet. See: msdn.microsoft.com/en-us/library/…
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.
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.
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
Improve this answer

Comments

0

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();
Improve this answer

Comments

0

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");
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.