In continuation to this question: Database on data on the server

I deployed the base and wrote the program. I compared the speed of the program without inserting into the database and inserting the database and noticed that in the first case the program is completed faster than in the second case.

Insert carry out 1 entry. Is it possible to fill the database with a higher speed?

For example, this is how a class that adds files looks like:

public class FileProvider:BaseProvider { public static int Insert(int folder_id, FileInfo fileInfo) { using (var conn = new SqlConnection(ConnStr)) { conn.Open(); return conn.ExecuteScalar<int>(@"INSERT INTO dbo.[File](Folder_id,FileName,FileSizeMB) VALUES (@Folder_id,@FileName,@FileSizeMB) SELECT SCOPE_IDENTITY()", new { Folder_id = folder_id, FileName= fileInfo.Name, FileSizeMB = fileInfo.Length/1048576.0}); } } }
  • four
    conn.Open() execute in advance. bring up new SqlConnection(ConnStr) and this will significantly shorten the time - Senior Pomidor
  • @SeniorAutomator Then, probably, you still have to close the connection with handles? - iluxa1810
  • one
    Pass an array of what to insert into the method immediately. Now you have a connection for each call, it's long. - nzeemin 8:38 pm
  • one
    @nzeemin, I think that in this case it is simpler to transfer an open connection from outside - iluxa1810

2 answers 2

Here is a quick download. If you can submit an IDataReader to the SqlBulkCopy input - even better. But in this case, a slight modification is required, since SCOPE_IDENTITY() returned.

In this regard, you can create a temporary table (for example #Files ), using SqlBulkCopy to insert it into it. Then from it do an insert in the main table. Inserted id (along with FileName, if it is important to match them) send to OUTPUT

 INSERT INTO dbo.[File](Folder_id,FileName,FileSizeMB) OUTPUT INSERTED.id--, INSERTED.FileName SELECT Folder_id,FileName,FileSizeMB FROM #Files

You execute this command, read the result (list of id or id, FileName pairs) using SqlDataReader or using SqlDataAdapter .

    In this case, it is possible to make the InsertRange() method, which takes as input IEnumerable <T> , where T is a class with fields int folder_id, FileInfo fileInfo . Further look here:
    1) https://codereview.stackexchange.com/questions/3184/insert-multiple-rows-into-a-sql-table - write to the table in memory then to the database
    2) https://stackoverflow.com/questions/3913371/sqlbulkcopy-from-a-list - use a ready-made implementation of claim 1.