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

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!

Answer 1

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

Answer 2

The arraylist is dynamic.  The size can grow depending on the size of the file it is reading from.  Thanks!

Answer 3

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

Answer 4

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.

Answer 5

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