Bulk Insert or Another way to load a lot of data at once using C# and SQL server?

Question

I have an application where a user can upload files and associate the files with a project. The projects can be duplicated. Here is an example of an object:

Public class Project{
    Int ProjectId{get; set;}
    string ProjectName{get;set;}
    List<int> FileIds{get;set}
}

When I go to duplicate the project, say, what is the best way to associate the same files with a new project? I have two tables for projects and files. I have a relational table that has foreign keys to the project id and the file ids. When I create a new project id I want to bulk insert the file ids into the relational table. Is it just as good to iterate through the List and inert one at a time or is there a better way?

Answer 1

Either use SqlBulkCopy or a Table-Valued Parameter to a stored procedure.

Answer 2

If the project you're duplicating is already at the SQL Server by far the most efficient thing to do is to do the duplication at the SQL Server, just a simple:

INSERT INTO Files (ProjectId,FileId) SELECT 2 As ProjectId, FileId FROM Files WHERE ProjectId = 1

Where the New Project is ProjectId=2 and the old one is ProjectId = 1