Upload generic list to database

Question

How to upload genetic List<T> to SQL database.

I think it really depends on how you're going to use the data later on... — user2366842, Dec 30 '14 at 15:25
I want to create like a template for this - for change incoming type immediately, without a lot of changes. Up side way it's too huge — Hurricane, Dec 30 '14 at 16:09
I can't add an answer, but it depends on how the list will be used AND how large it is. In either case I can't see using a `DataTable` to be of any benefit outside of it being rather easy. If the list is small then either the XML that you have is fine or doing a CSV and using a SQLCLR splitter is quite fast (the [SQL#](http://www.SQLsharp.com) library, which I created, has `String_Split` and `String_Split4k` in the Free version). If you have a large list then check out using TVPs: http://stackoverflow.com/questions/19957132/pass-dictionarystring-int-to-stored-procedure-t-sql/25815939#25815939 — Solomon Rutzky, Dec 30 '14 at 16:16
Why on hold - _"Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking."_ — CodeCaster, Dec 30 '14 at 16:28

score 2 · Accepted Answer · answered Dec 30 '14 at 15:18

2

Having done it both ways, my money is on DataTable.

See http://msdn.microsoft.com/en-us/library/ex21zs8x%28v=vs.110%29.aspx

// Create a table with some rows. 
        DataTable newProducts = MakeTable();

        // Create the SqlBulkCopy object.  
        // Note that the column positions in the source DataTable  
        // match the column positions in the destination table so  
        // there is no need to map columns.  
        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
        {
            bulkCopy.DestinationTableName = 
                "dbo.BulkCopyDemoMatchingColumns";

            try
            {
                // Write from the source to the destination.
                bulkCopy.WriteToServer(newProducts);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }

answered Dec 30 '14 at 15:18

James

119
1
8

1

What does this do and how does it help OP? – CodeCaster Dec 30 '14 at 15:18
The code shown takes a data table and pushes it to a database. I just copied the relevant code from the link. My understanding of his question is what's the best way to move data from an application to a database. – James Dec 30 '14 at 15:35
I'm not asking what SQL Bulk Copy does, I'm asking how your answer helps OP or how to integrate it with their (non-shown) code. You don't explain the former _or_ the latter. – CodeCaster Dec 30 '14 at 15:37
The only thing unclear is how OP intends to implement the solution. The question of "which is better" is always "it depends". I merely offered my observation that having done both parameterized sql procedures and DataTable push from C# I found the latter to be "better" in my experience. – James Dec 30 '14 at 15:46
1

James: I believe @CodeCaster is saying that more context is needed. If the list is large then a DataTable is not the best choice as it duplicates the list in memory first. Or, if the list is needed to be used in a JOIN (think of using an INNER JOIN to filter instead of an IN list as that requires Dynamic SQL) then this solution won't work at all, even if it is efficient. – Solomon Rutzky Dec 30 '14 at 15:57
Yes, that. It is unclear what _exactly_ OP wants to do - the question is not specific enough. You shouldn't attempt to answer those questions, because many, long answers can be written while only a few apply. It's a waste of effort. OP must show effort, then specific answers can be given accordingly. – CodeCaster Dec 30 '14 at 16:30

Upload generic list to database

1 Answers1