locked
How to copy only data (all rows at once) without structure from data table to another RRS feed

  • Question

  • Currently, clone() copies only structure and copy() both data and structure. But I want to copy all rows at once without structure.

    Any suggestions will be helpful.

    Thanks

    Harish


    Harish

    Thursday, September 6, 2018 10:31 AM

All replies

  • Hello,

    • How much data is involved e.g. how many rows, how many columns and in regards to columns will they all need to map from table to table?
    • Are columns to be mapped all the same type e.g. string to string, date to date etc?
    • Is this to be an automated process or some interaction with a user?
    • Which version of the .NET Framework is being used? 

    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

    Thursday, September 6, 2018 11:32 AM
  • There will be at least 30000 rows. Ultimately, I want to change data type of one column keeping other columns as it is with all the rows of data. 

    Harish

    Friday, September 7, 2018 4:26 AM
  • Some thoughts, still with (as I feel) limited information from you.

    As soon as mapping one or more columns is involved there is no straightforward method to do this.  

    One idea is to first create a list, do any conversions then take the results and put them into another DataTable. In the following example I take a DataTable, create a list of category, convert one column. Next take the list and create a DataTable. It will take time to do all the processing thinking 30,000 records so don't expect speed.

    Class for conversion

    Public Class Category
        Public Property Id As Long
        Public Property Name As String
        Public Property DisplayOrder As Integer
        Public Property IsActive As Boolean
    End Class

    Create DataTable from code below this code.

    Public Shared Function ConvertToDataTable(Of T)(list As IList(Of T)) As DataTable
        Dim table As New DataTable()
        Dim fields() As FieldInfo = GetType(T).GetFields()
    
        For Each field As FieldInfo In fields
            table.Columns.Add(field.Name, field.FieldType)
        Next
        For Each item As T In list
    
            Dim row As DataRow = table.NewRow()
            For Each field As FieldInfo In fields
                row(field.Name) = field.GetValue(item)
            Next
    
            table.Rows.Add(row)
    
        Next
    
        Return table
    
    End Function

    Processing

    Public Sub Demo()
        ' dt is the source data table
        Dim list = dt.AsEnumerable().
                Select(Function(dr) New Category With
                            {
                                .Id = Convert.ToInt64(dr.Field(Of Double)("ID")),
                                .Name = dr.Field(Of String)("Name"),
                                .DisplayOrder = Convert.ToInt32(dr.Field(Of Double)("Display Order")),
                                .IsActive = (Math.Abs(dr.Field(Of Double)("Active") - 1) < 10)
                            }).ToList()
    
        ' new data table 
        Dim d2 As DataTable = ConvertToDataTable(Of Category)(list)
    End Sub

    Of course there are other angles to approach this but my time is limited and the other methods I can think of more likely than not will not speed things up.

    Finally note, if the resulting DataTable is going to be pushed to a database table and by chance it was SQL-Server there are bulk copy methods and merge methods in one single command which would be a fast process.

    EDIT Just realized this is not vb.net

    public static DataTable ConvertToDataTable<T>(IList<T> list)
    {
        var table = new DataTable();
        var fields = typeof(T).GetFields();
    
        foreach (FieldInfo field in fields)
        {
            table.Columns.Add(field.Name, field.FieldType);
        }
        foreach (T item in list)
        {
    
            var row = table.NewRow();
            foreach (FieldInfo field in fields)
            {
                row[field.Name] = field.GetValue(item);
            }
    
            table.Rows.Add(row);
    
        }
    
        return table;
    
    }

    .

    public class Category
    {
    	public long Id {get; set;}
    	public string Name {get; set;}
    	public int DisplayOrder {get; set;}
    	public bool IsActive {get; set;}
    }

    .

    public void demo()
    {
        var list = dt.AsEnumerable()
            .Select(dr =>
                new Category
                {
                    Id = Convert.ToInt64(dr.Field<double>("ID")),
                    Name = dr.Field<string>("Name"),
                    DisplayOrder = Convert.ToInt32(dr.Field<double>("Display Order")),
                    IsActive = Math.Abs(dr.Field<double>("Active") - 1) < 10 ? true : false
                }
            ).ToList();
        DataTable d2 = ConvertToDataTable<Category>(list);
    }


    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


    • Edited by KareninstructorMVP Friday, September 7, 2018 11:31 AM Added C# (left VB.NET)
    • Proposed as answer by CoolDadTx Friday, September 14, 2018 1:48 PM
    Friday, September 7, 2018 11:16 AM
  • Some thoughts, still with (as I feel) limited information from you.

    As soon as mapping one or more columns is involved there is no straightforward method to do this.  

    One idea is to first create a list, do any conversions then take the results and put them into another DataTable. In the following example I take a DataTable, create a list of category, convert one column. Next take the list and create a DataTable. It will take time to do all the processing thinking 30,000 records so don't expect speed.

    Class for conversion

    Public Class Category
        Public Property Id As Long
        Public Property Name As String
        Public Property DisplayOrder As Integer
        Public Property IsActive As Boolean
    End Class

    Create DataTable from code below this code.

    Public Shared Function ConvertToDataTable(Of T)(list As IList(Of T)) As DataTable
        Dim table As New DataTable()
        Dim fields() As FieldInfo = GetType(T).GetFields()
    
        For Each field As FieldInfo In fields
            table.Columns.Add(field.Name, field.FieldType)
        Next
        For Each item As T In list
    
            Dim row As DataRow = table.NewRow()
            For Each field As FieldInfo In fields
                row(field.Name) = field.GetValue(item)
            Next
    
            table.Rows.Add(row)
    
        Next
    
        Return table
    
    End Function

    Processing

    Public Sub Demo()
        ' dt is the source data table
        Dim list = dt.AsEnumerable().
                Select(Function(dr) New Category With
                            {
                                .Id = Convert.ToInt64(dr.Field(Of Double)("ID")),
                                .Name = dr.Field(Of String)("Name"),
                                .DisplayOrder = Convert.ToInt32(dr.Field(Of Double)("Display Order")),
                                .IsActive = (Math.Abs(dr.Field(Of Double)("Active") - 1) < 10)
                            }).ToList()
    
        ' new data table 
        Dim d2 As DataTable = ConvertToDataTable(Of Category)(list)
    End Sub

    Of course there are other angles to approach this but my time is limited and the other methods I can think of more likely than not will not speed things up.

    Finally note, if the resulting DataTable is going to be pushed to a database table and by chance it was SQL-Server there are bulk copy methods and merge methods in one single command which would be a fast process.

    EDIT Just realized this is not vb.net

    public static DataTable ConvertToDataTable<T>(IList<T> list)
    {
        var table = new DataTable();
        var fields = typeof(T).GetFields();
    
        foreach (FieldInfo field in fields)
        {
            table.Columns.Add(field.Name, field.FieldType);
        }
        foreach (T item in list)
        {
    
            var row = table.NewRow();
            foreach (FieldInfo field in fields)
            {
                row[field.Name] = field.GetValue(item);
            }
    
            table.Rows.Add(row);
    
        }
    
        return table;
    
    }

    .

    public class Category
    {
    	public long Id {get; set;}
    	public string Name {get; set;}
    	public int DisplayOrder {get; set;}
    	public bool IsActive {get; set;}
    }

    .

    public void demo()
    {
        var list = dt.AsEnumerable()
            .Select(dr =>
                new Category
                {
                    Id = Convert.ToInt64(dr.Field<double>("ID")),
                    Name = dr.Field<string>("Name"),
                    DisplayOrder = Convert.ToInt32(dr.Field<double>("Display Order")),
                    IsActive = Math.Abs(dr.Field<double>("Active") - 1) < 10 ? true : false
                }
            ).ToList();
        DataTable d2 = ConvertToDataTable<Category>(list);
    }


    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


    R-I-D-I-C-U-L-O-U-S !

    This code is a humongous and astronomic rubbish# code.

    And more, it only copies  a specific DataTable: one that mimics the type named Category.

    Notice, you are using a DataTable variable named dt (whose steps to create it don't exist) as a source to be copied into a new DataTable variable, named dt2.

    And you create a List<> to only then, copy the list into a new DataTable:

                DataTable 1 => List<> => DataTable 2

    R-I-D-I-C-U-L-O-U-S !

    All this can be done in a pretty small line of statement, by calling a single method!

    Sunday, September 9, 2018 2:35 AM
  • hello,

    @ritehere42, the code Kareninstructor gave you is a good starting point to resolve the issue, also you should do some efforts to adapt the code to your use case;

    finally: please respect the effort done by others to help.

    Good Coding; 


    Sunday, September 9, 2018 10:33 AM
  • hello,

    @ritehere42, the code Kareninstructor gave you is a good starting point to resolve the issue, also you should do some efforts to adapt the code to your use case;

    finally: please respect the effort done by others to help.

    Good Coding; 


    So, you are saying that th Instructor, after writing 1,000,000 lines of statements, has just got the starting point... ?

    This is very... very... very offensive to the Instructor!

    Please, be respectiful...

    Friday, September 14, 2018 7:07 AM
  • life is a big story, write good lines or better to shut up; 

    I'm already a trainer and to be honest, I learned a lot from  Kareninstructor, and with all her contributions she doesn't need any recognition from a beginner,  other than doing the same and sharing knowledge;

    "Any fool can write code that a computer can understand. Good programmers write code that humans understand". Martin Fowler.

    Good Coding;


    Friday, September 14, 2018 9:55 AM
  • life is a big story, write good lines or better to shut up; 

    I'm already a trainer and to be honest, I learned a lot from  Kareninstructor, and with all her contributions she doesn't need any recognition from a beginner,  other than doing the same and sharing knowledge;

    "Any fool can write code that a computer can understand. Good programmers write code that humans understand". Martin Fowler.

    Good Coding;



    >> life is a big story, write good lines or better to shut up;

    You accept 1,000,000 lines
    When just 1 line is enough
    Thus, your life is short besides
    Dull, stupid and rough
    Your ignorance and incompetence are huge
    You'd better shut up you

    >> I'm already a trainer and to be honest, I learned a lot from Kareninstructor, and with all her contributions she doesn't need any recognition from a beginner, other than doing the same and sharing knowledge;

    If you are a trainer
    Then, by definition you are dishonest
    Definitely, you are a learner
    Pretending to be earnest

    >> "Any fool can write code that a computer can understand. Good programmers write code that humans understand". Martin Fowler.

    The code you are supporting has 1,000,000 lines, and you don't understand...
    I can write better code (without hardcoding anything) in just 1 small line, and you don't understand...
    Therefore, according to Martin Fowler (you cited him), you are not human...
    What are you, then...?

    P.S. #1: Don't reply to me; instead do to the moderator! Everything I wrote and you liked, you can say you authored; you can take with you, yes, tak'em all... up you.

    P.S. #2: The Instructor wrote 1,000,000 lines of code... ! ... and you say it's JUST a starting point... !
    Definitely, you are offending the Instructor!
    Why are you criticizing someone, when Martin Fowler (you cited him) says you are not human...?
    You'd rather starting to be respectiful!!!

    Friday, September 14, 2018 11:58 PM
  • @Michel Tayllor, moderator

    Why have you proposed the reply above, as correct answer?

    The Instructor is using 1,000,000 lines of code, when just 1 SMALL line is enough!

    Moreover, the 1,000,000 lines of code uses a hardcoded type for making the copy!!

    This means that the mentioned code NEEDS to be re-implemented for a different DataTable!!!

    Making a copy of a DataTable, requires just 1 small line of code!

    Why are you supporting a code so clearly wrong?

    Is it because you don't understand template? Notice: you moved to another forum, my answer to a question about template, that you weren't able to answer; 2 days later, a friend of yours (or yourself thru an alter-ego) posted a similar question, and you, then, posted a reply identical to my answer in the question you moved away.

    Is it because you don't understand IEnumerable? Notice: you posted a code that "tries" to index a variable of type IEnumerable<T>.

    Is it because you don't know what LINQ is? Notice: you posted a reply stating that code using LINQ methods, is not a LINQ query.

    Is it because you don't know what are query, query execution and deferred execution? Notice: you posted a reply containing a query and executed once; and you said the 2nd time it wouldn't be necessary to do the calculation again (!?) because it happened before (!?); all that would be necessary, would to enumerating again (!?!?!?!?).

    Is it because... (there are many others...)



    • Edited by ritehere43 Saturday, September 15, 2018 12:18 AM quoting
    Saturday, September 15, 2018 12:17 AM