Populate a DataSet using Context - Entity Framework 4

Question

I have some Data returning from a Context. Data has being pulled by spCmsCategoriesReadHierarchy.

I need take all Data from Context and populate my DataSet. My final goal is to populate a TreeView Control with DataSet object.

Any ideas? Thanks for your time and patience.

using (TestHierarchyEntities context = new TestHierarchyEntities())
{
    int n = 0;
    Int16 sl = 1;
    ObjectParameter nn = new ObjectParameter("nn", typeof(int));

    // Create a Data Set where adding the result of context 
    DataSet dataSet = new DataSet("myDataSet");

    foreach (CmsCategory categories in context.spCmsCategoriesReadHierarchy(n,sl,nn))
    {
    }
}

Why are you using EF if you need data sets? Seems like a waste of time. — , Jan 07 '11 at 14:12
Will I think you are right! Do you know how to bind a TreeView using the context (EF)? thanks — GibboK, Jan 07 '11 at 14:16

score 5 · Accepted Answer · answered Jan 07 '11 at 14:08

5

Sure, you can manually copy the data over from the object to a data row within the dataset.

DataSet dataSet = new DataSet("myDataSet");
dataSet.Tables.Add(new DataTable());
//Setup the table columns.

foreach (CmsCategory categories in context.spCmsCategoriesReadHierarchy(n,sl,nn))
{
    DataRow row = dataSet.Tables[0].NewRow();
    row["A"] = categories.A;
    row["B"] = categories.B;

    dataSet.Tables[0].Rows.Add(row);
}

If you are not looking to explicitly copy over the properties to the data row, you can also use reflection to do the copying.

answered Jan 07 '11 at 14:08

Brian Mains

50,520
35
148
257

Hi Brina thanks. As i wrote my idea is to populate a TreeView control with a DataSet. Because I use EF 4 would be possible to bind to TreeView directly to EF without use any DataSet? I really appreciate your help on this! Are you able to provide a piece of code to start with? – GibboK Jan 07 '11 at 14:23
Sure, you can always bind with EF objects, are you using the ASP.NET tree view? If so, you can easily translate the inner loop, and instead of creating a DataRow object, you create a TreeNode object. If using the datasource approach, I'm not sure 100% about the hierarchical binding approach, but it seems like since each EF object contains its relationships as properties for that entity, you could easily recreate the hierarchical structure. – Brian Mains Jan 10 '11 at 02:19
Thanks Brina, for sure I will try and post the code for other references. Thanks for now – GibboK Jan 10 '11 at 05:51

score 2 · Answer 2 · answered Nov 03 '20 at 11:57

This method will call a stored procedure in your database and fill a data set with the result:

var dataSet = new DataSet();
using ( var sqlCmd = context.Database.Connection.CreateCommand() )
{
    sqlCmd.CommandType = CommandType.StoredProcedure;
    sqlCmd.CommandText = "spCmsCategoriesReadHierarchy";
    // sqlCmd.Parameters.Add( new SqlParameter( "@Parameter", value ) );
    // sqlCmd.Parameters.Add( new SqlParameter( "@Error", null ) { Direction = ParameterDirection.Output, Size = -1 } );

    // Define the data adapter and fill the dataset 
    using ( DbDataAdapter da = new SqlDataAdapter() )
    {
        da.SelectCommand = sqlCmd;
        da.Fill( dataSet );
    }
    // resultDetail.Error = sqlCmd.Parameters["@Error"].Value.ToString();
}

The comments show how to pass parameters bi-directionally.

p.s.: We're using Entity Framework 6. I don't know if this would work on EF4.

score 0 · Answer 3 · edited May 23 '17 at 12:10

This method will convert a list of objects to a datatable. It was given as an answer to this question.

/// <summary>
/// Create data table from list.
/// https://stackoverflow.com/questions/18746064/using-reflection-to-create-a-datatable-from-a-class
/// </summary>
public static DataTable CreateDataTable<T>(IEnumerable<T> list)
{
    Type type = typeof(T);
    var properties = type.GetProperties();      

    DataTable dataTable = new DataTable();
    foreach (PropertyInfo info in properties)
    {
        dataTable.Columns.Add(new DataColumn(info.Name, Nullable.GetUnderlyingType(info.PropertyType) ?? info.PropertyType));
    }

    foreach (T entity in list)
    {
        object[] values = new object[properties.Length];
        for (int i = 0; i < properties.Length; i++)
        {
            values[i] = properties[i].GetValue(entity);
        }

        dataTable.Rows.Add(values);
    }

    return dataTable;
}

Rather than just copying code and pasting here as an answer, it would be better if you tailored it more to the OP's question. For example, OP asked for a DataSet, and this returns a DataTable. It would also be nice if you included a way to go from OP's code snippet to the code you provide--for example, "Replace your `foreach` statement with `CreateDataSet(context.spCmsCategoriesReadHierarchy(n,sl,nn))`" or something like that. — David, Apr 07 '17 at 19:53

Populate a DataSet using Context - Entity Framework 4

3 Answers3

Linked