locked
How to I import data to an existing tsql table from an arraylist? RRS feed

  • Question

  • I have some fixed width files that I need to load and compare over time (slow changing dimension).  I have the file parsed, organized and saved into an ArrayList.  I want to inject the data from the ArrayList to a table as the base. How do I do that? The subsequent files would be loaded to a staging table for comparison. Thanks in advance!
    Saturday, March 21, 2015 5:06 AM

Answers

  • My inclination would be to use ssis to parse the data and import to a staging table directly.

    If you need to work code to database then I would consider entity framework and make whatever class you have in that arraylist an entity.

    Make that Arraylist a List instead.

    You can then just add them to the entity in an entity framework context and call savechanges to write them to the database:

            List<Person> people = new  List<Person>();
            foreach(Person p in people)
            {
                context.People.Add(p);
            }
            context.SaveChanges();

    Here a person is the equivalent to whichever object you have in that arraylist.

    context is an entity framework context.

    People is a table that each person is to be added to.


    Hope that helps.
    Recent Technet articles: Property List Editing; Dynamic XAML

    • Marked as answer by Caillen Monday, April 6, 2015 7:48 AM
    Sunday, March 22, 2015 11:10 AM

All replies

  • What is the size of the array list?
    1. Create a stored procedure in your DB
    2. From your code loop through the array and call the SP.

    Check this:

    http://stackoverflow.com/questions/1260952/how-to-execute-a-stored-procedure-within-c-sharp-program



    Fouad Roumieh

    Saturday, March 21, 2015 5:18 AM
  • The arraylist is dynamic.  The size can grow depending on the size of the file it is reading from.  Thanks!
    Sunday, March 22, 2015 3:00 AM
  • You can also have a look at sql bulk copy:

    https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy%28v=vs.110%29.aspx?f=255&MSPPError=-2147217396


    Fouad Roumieh

    Sunday, March 22, 2015 9:54 AM
  • Have you already decided how to represent the data in database? It can be a special table, where each element of ArrayList will be stored in a separate row. Or it can be an additional column in an existing table having the xml datatype, so that the whole ArrayList will be stored in a single cell as an XML.

    Sunday, March 22, 2015 10:06 AM
  • My inclination would be to use ssis to parse the data and import to a staging table directly.

    If you need to work code to database then I would consider entity framework and make whatever class you have in that arraylist an entity.

    Make that Arraylist a List instead.

    You can then just add them to the entity in an entity framework context and call savechanges to write them to the database:

            List<Person> people = new  List<Person>();
            foreach(Person p in people)
            {
                context.People.Add(p);
            }
            context.SaveChanges();

    Here a person is the equivalent to whichever object you have in that arraylist.

    context is an entity framework context.

    People is a table that each person is to be added to.


    Hope that helps.
    Recent Technet articles: Property List Editing; Dynamic XAML

    • Marked as answer by Caillen Monday, April 6, 2015 7:48 AM
    Sunday, March 22, 2015 11:10 AM