CODEWITHSUNDEEP
c#.netwinforms
Alex Gordon
Alex Gordon

Reputation: 60691

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?

Upvotes: 30

Views: 106934

Answers (7)

YMH
YMH

Reputation: 3093

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

Upvotes: 0

Guokas
Guokas

Reputation: 830

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

Upvotes: 0

d.popov
d.popov

Reputation: 4255

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

Upvotes: 2

Sanjib Bose
Sanjib Bose

Reputation: 171

IDataReader reader;
DataSet ds;

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

Upvotes: 16

DOK
DOK

Reputation: 32831

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.

Upvotes: 5

Justin Niessner
Justin Niessner

Reputation: 245399

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

Upvotes: 45

pyCoder
pyCoder

Reputation: 501

if you can use DataAdapter subclass or use something as:

DataTable myTable = new DataTable();

myTable.Load(myCommand.ExecuteReader());

and then return DataTable to client.

Upvotes: 26

Related Questions