none
Translate linq query result to DataTablie questrion RRS feed

  • Question

  • Hi guys,

    i finish it with Da924x help ,those web site is cool i learn a lot.

    I will write down how i trun linq query to datatable.

    First u need this compoment.put in a IQueryable Items,with generated type and out of datatable

    public static DataTable ToDataTable<T>( IQueryable items)
            {
                Type type = typeof(T);
    
                var props = TypeDescriptor.GetProperties(type)
                                          .Cast<PropertyDescriptor>()
                                          .Where(propertyInfo => propertyInfo.PropertyType.Namespace.Equals("System"))
                                          .Where(propertyInfo => propertyInfo.IsReadOnly == false)
                                          .ToArray();
    
                var table = new DataTable();
    
                foreach (var propertyInfo in props)
                {
                    table.Columns.Add(propertyInfo.Name, Nullable.GetUnderlyingType(propertyInfo.PropertyType) ?? propertyInfo.PropertyType);
                }
    
                foreach (var item in items)
                {
                    table.Rows.Add(props.Select(property => property.GetValue(item)).ToArray());
                }
    
                return table;
            }


    use linq query and a Dto class

    Dtoclass:

    private class GoodsShowDto {
    
              public   string ImgUrl { get; set; }
              public  long   GoodsId { get; set; }
              public  string GoodsName { get; set; }
    
    
            }


    IQueryable<GoodsShowDto> show= from im in context.tb_Images
                                               join go in context.tb_GoodsInfo 
                                               on im.GoodsID equals go.GoodsID
                                               select new GoodsShowDto {
                                                                      ImgUrl =im.ImgUrl,
                                                                      GoodsName =go.GoodsName,
                                                                      GoodsId =go.GoodsID
                                                                        }; 

    at last u can trun it LOL

    DataTable dt = new DataTable();
    dt= GoodsInfoLinq.ToDataTable<GoodsShowDto>(show);
    warn: this is noting useful but just for fun,u can use list<t> to bind derectly

    sorry for my engilsh i read well but poor at write xd

    Happy Coding!


    • Edited by BigXUXU Tuesday, April 24, 2018 6:40 AM
    Sunday, April 22, 2018 8:07 AM

Answers

  •                  with select new{I'm.imgurl,go.goodsname,go.goodsID},the <t> is a

    What you are doing is called a Linq projection. 

    http://csharp-station.com/Tutorial/Linq/Lesson02

    <copied>

    The process of choosing what parts of an object to select is called projection and the result of that operation is a projection.

    <end>

    You can create a projection using anonymous objects that you are doing now, which is shown in the tutorial.  

    You can also do a projection using a custom type, like a DTO, which custom type is also being shown in the tutorial.

    I think you want to create a List<T> of custom type to do something with it to work with your datatable logic. 

    Because other than that, I don't know what you are trying to do. :)

    You should go with DTO(s) using a List<T>, the SQL Command Objects, a Datareader  that was shown to you and abandon the whole datatable thing IMO. :)

    • Marked as answer by BigXUXU Monday, April 23, 2018 3:20 AM
    Sunday, April 22, 2018 12:33 PM

All replies

  • tips

    my purpose is query with linq from sqlserver and translate to datatable,

    then i can use datatable to bind PageDatasourse,

    then i use PageDatasourse to bind GridList copoment 

    finally ican bind data with container.dataItem

     <asp:Image ID="ImageGoods" runat="server" imageurl='<%# DataBinder.Eval(Container.DataItem,"ImgUrl") %>'

    Sunday, April 22, 2018 8:16 AM
  • How can i buid a class as linq sql table class with my wish?

     

    Sunday, April 22, 2018 8:26 AM
  • You should learn how to use a custom object like a DTO in a collection List<T> that is also bindable to a control's datasource. 

    https://dzone.com/articles/reasons-move-datatables

    http://lauteikkehn.blogspot.com/2012/03/datatable-vs-list.html

    https://www.codeproject.com/articles/1050468/data-transfer-object-design-pattern-in-csharp

    https://www.codeproject.com/Articles/26743/Using-LINQ-to-Objects-in-C

    <copied>

    Example 5 – Working with a Custom Type

    <end>

    https://docs.microsoft.com/en-us/dotnet/csharp/programming-guide/classes-and-structs/auto-implemented-properties

    You should learn how to use SQL Command Objects with a datareaader

    https://www.c-sharpcorner.com/UploadFile/c5c6e2/working-with-command-object/

    You can see one example where you see the ExecuteReader Method example in the above link.

    The example I am showing is using the ADO.NET Entity Framework, but the concept is the same what you would use where you populate the List<DTO> within the datareader While Loop in the above example link and return the List<DTO> that is bindable to a control.

    public List<DTOStudent> GetStudents()
            {
               
                var dtos = new List<DTOStudent>();
    
                using (var context = new CUDataEntities())
                {
      
                    var students = context.Students.ToList();
    
                    foreach(var stud in students)
                    {
                        var dto = new DTOStudent
                        {
                            StudentID = stud.StudentID,
                            FirstName = stud.FirstName,
                            LastName = stud.LastName,
                            EnrollmentDate = stud.EnrollmentDate
                        };
    
                        dtos.Add(dto);
                    }
                }
    
                return dtos;
            }
    

    About this query are just trying to get im.ImgUrl only?

     var image = from im in context.tb_Images
                            join go in context.tb_GoodsInfo on im.GoodsID equals go.GoodsID
                 //         select im;   with one its ok ican use tb_image for T
                            select new { im.ImgUrl,go.GoodsName,go.GoodsID};
                            //select like this how can i get the class T?

    Sunday, April 22, 2018 9:30 AM
  • Hello,

    Look at CopyToDataTable.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Sunday, April 22, 2018 10:28 AM
    Moderator
  • thanks first watch second :D
    Sunday, April 22, 2018 11:16 AM
  • var image = from im in context.tb_Images
                            join go in context.tb_GoodsInfo on im.GoodsID equals go.GoodsID
                 //         select im;   with one its ok ican use tb_image for T
                            select new { im.ImgUrl,go.GoodsName,go.GoodsID};
                            //select like this how can i get the class T?

    with this query, var image is a Iqueryable<t> type variable.

                         with select IM which means select * from tb_Images the <t>equals <tb_Images>linq provide that class.

                         with select new{I'm.imgurl,go.goodsname,go.goodsID},the <t> is a <'a> 

         i want to know how to construct the class represent for that<'a>.

        so i can use it to my first method  turn linq query to datatable.

    I know use ado.net to query ,DataAdapter(_)back with dataset,

    I am learning linq,so i am trying query with linq and turn it to datatable .

    i will learn DTO First :D

    Sunday, April 22, 2018 11:38 AM
  •                  with select new{I'm.imgurl,go.goodsname,go.goodsID},the <t> is a

    What you are doing is called a Linq projection. 

    http://csharp-station.com/Tutorial/Linq/Lesson02

    <copied>

    The process of choosing what parts of an object to select is called projection and the result of that operation is a projection.

    <end>

    You can create a projection using anonymous objects that you are doing now, which is shown in the tutorial.  

    You can also do a projection using a custom type, like a DTO, which custom type is also being shown in the tutorial.

    I think you want to create a List<T> of custom type to do something with it to work with your datatable logic. 

    Because other than that, I don't know what you are trying to do. :)

    You should go with DTO(s) using a List<T>, the SQL Command Objects, a Datareader  that was shown to you and abandon the whole datatable thing IMO. :)

    • Marked as answer by BigXUXU Monday, April 23, 2018 3:20 AM
    Sunday, April 22, 2018 12:33 PM
  •        

    You should go with DTO(s) using a List<T>, the SQL Command Objects, a Datareader  that was shown to you and abandon the whole datatable thing IMO. :)


    There's nothing wrong with using DataSet/DataTable (that's considered a DTO too!) ... IMO ;)

    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Sunday, April 22, 2018 4:43 PM
    Moderator
  •        

    You should go with DTO(s) using a List<T>, the SQL Command Objects, a Datareader  that was shown to you and abandon the whole datatable thing IMO. :)


    There's nothing wrong with using DataSet/DataTable (that's considered a DTO too!) ... IMO ;)

    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    I abandon using datasets and datatables when I left COM programming behind and crossed over to .NET back in year 2001, and I cringe anytime I see someone using them. :)
    Sunday, April 22, 2018 7:47 PM
  • i am learning  coding by some old program book , writed in chinese,   published in year 2010 ...x.x

    maybe its too old..  i will go to work soon, learning harder than before.its fun i like coding :D

    i really love dataset/datatable, but it seems like its time to say goodbye with them.

    thank you  for answer.

    Monday, April 23, 2018 3:33 AM
  • with thats website, you create a '程序猿' which means a guy don't like girls but like program...xd
    Monday, April 23, 2018 3:49 AM
  • i really love dataset/datatable, but it seems like its time to say goodbye with them.

    I see no reason to "say goodbye" to DataSet/DataTable. There are plenty of developers who believe that DataSets are more flexible and easier to use than Entity Framework kinds of DTOs. I think it's all a matter of what you're used to and what your prefererences are.

    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Monday, April 23, 2018 5:02 AM
    Moderator
  • with thats website, you create a '程序猿' which means a guy don't like girls but like program...xd
    What do you mean by that? I'm a "girl" ... (in quotes, because I should really say I'm a woman!)

    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Monday, April 23, 2018 5:04 AM
    Moderator
  • i really love dataset/datatable, but it seems like its time to say goodbye with them.

    As explained in a prior link......

    https://dzone.com/articles/reasons-move-datatables

    <copied>

    LINQ queries can be used on both DataTables and collections. But the experience of writing the queries on generic collections is better because of IntelliSense support provided by Visual Studio

    Generic collections are easier to serialize and de-serialize, so they can be easily used in any service and consumed from a client written in any language.

    ORMs are becoming increasingly popular, and they use generic collections for all data operations.

    Mocking DataTables in unit tests is a pain, as it involves creating the structure of the table wherever needed. But a generic collection needs a class defined just once.

    <end>

    IMO, if you want to learn and know all that you can do with .NET programming C# or VB, then you will consider doing Web programming and Service Oriented Architecture (SOA) programming where you'll seldom find datasets and datatables being used.

    If you want to learn, then get the C# version of the DoFactory source code and learn. 

    http://www.dofactory.com/net/design-patterns

    Monday, April 23, 2018 6:04 AM
  • thanks for answer!

    i will learn more then decide whats i should use.

    its just a joke about ‘程序猿’ .in my understand the reason is----program warn me where i am wrong, girls don't warn,they just ignore .untill the  'bugday'.

     


    • Edited by BigXUXU Monday, April 23, 2018 8:04 AM
    Monday, April 23, 2018 8:02 AM
  • its just a joke about ‘程序猿’ .in my understand the reason is----program warn me where i am wrong, girls don't warn,they just ignore .untill the  'bugday'.

    Ah, OK ... I get it now ... LOL!

    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Monday, April 23, 2018 1:30 PM
    Moderator
  • :D

    thanks again about u guidance

    now I have a new question about asp.net webform

    here is the thread

    https://social.msdn.microsoft.com/Forums/en-US/a4bd822c-2303-4e54-bb42-ab466d9d1b9d/how-does-responsewrite-works?forum=netfxbcl

    if u have times,thanks a lot!!!

    Friday, April 27, 2018 8:12 AM
  • :D

    thanks again about u guidance

    now I have a new question about asp.net webform

    here is the thread

    https://social.msdn.microsoft.com/Forums/en-US/a4bd822c-2303-4e54-bb42-ab466d9d1b9d/how-does-responsewrite-works?forum=netfxbcl

    if u have times,thanks a lot!!!

    ASP.NET solutions can be discussed at the ASP.NET forums.

    https://forums.asp.net/

    Friday, April 27, 2018 9:49 AM