c# how do you return dataset from sqldatareader?

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?开发者_如何学JAVA

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

if you can use DataAdapter subclass or use something as:

DataTable myTable = new DataTable();

myTable.Load(myCommand.ExecuteReader());

and then return DataTable to client.

IDataReader reader;
DataSet ds;

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

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.

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

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();

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");