how to fill DataTable from stored procedure

Question

I am trying to execute a stored procedure with parameters, and get the results into a DataTable; but after execution, I get this:

this is the screen

I can get IDataRecord but I can't IDataReader - why? What do I miss? Thanks

public DataTable ExecuteProcedureTest(string storedProcedure, Dictionary<Filter, object> filterValue)
{
    var parameters = GetParams(filterValue);

    var sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["testconn"].ConnectionString);

    var cmd = sqlConnection.CreateCommand(); 

    SqlDataReader reader;
    IDataReader dataReader;
    IDataRecord[] dataRecords;

    var table = new DataTable();

    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = storedProcedure;

    foreach (var item in parameters)
    {
        cmd.Parameters.Add(item);
    }

    try
    {
        sqlConnection.Open();
        reader = cmd.ExecuteReader();

        dataRecords = reader.OfType<IDataRecord>().ToArray();
        table.Load(reader);
    }
    catch (Exception ex)
    {
        throw ex;
    }
    finally
    {
        sqlConnection.Dispose();
    }

    //var count = dataRecords.Count();
    return table;
}

This call dataRecords = reader.OfType<IDataRecord>().ToArray(); read all records. There is nothing left in the reader for table.Load(reader); to work with. — rene
– rene, Commented Dec 25, 2017 at 14:39
So that is example when i try everything what i know. So is the same result when i remove that line of code. And please stop giving negative point before then user answer on your comment :S :S — Baba.Kata
– Baba.Kata, Commented Dec 25, 2017 at 14:50

Brian Rogers · Accepted Answer · 2017-12-25 19:22:54Z

1

If your goal is to fill a DataTable from a SqlCommand, use a SqlDataAdapter instead of a SqlDataReader. Here is a handy extension method:

public static class SqlExtensions
{
    public static DataTable ExecuteDataTable(this SqlCommand command)
    {
        DataTable table = new DataTable();
        using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
        {
            dataAdapter.Fill(table);
        }
        return table;
    }
}

Then from your code you can use it like this:

try
{
    sqlConnection.Open();
    table = cmd.ExecuteDataTable();
}
finally
{
    sqlConnection.Dispose();
}

answered Dec 25, 2017 at 19:22

Brian Rogers

130k31 gold badges315 silver badges315 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

how to fill DataTable from stored procedure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related