Saving Bulk Records in SQL Server

Database operations are costly. Especially if we deal with big number of data. Sometimes even timeout may happen in your application.

There is a better way we can handle this from code level which we can use SqlBulkCopy class to insert bulk data into SQL Server Tables.

You can read more about SqlBulkCopy class here.

The "WriteToServer" method can be used with a DataTable filled with all your data from the application.

Lets get to work;

 DataTable dtFiles = new DataTable();  
 string _conStringRead = string.Empty;  
 string _strPathToScan = string.Empty;  
 _strPathToScan = ConfigurationManager.AppSettings["folderPath"]; // reading folderpath  
 _conStringRead = ConfigurationManager.ConnectionStrings["Read"].ConnectionString; //reading connection string from App.Config file  
 dtFiles.Columns.Add("lbFileName"); // Adding Column to DataTable  
 foreach (var file in Directory.EnumerateFiles(_strPathToScan, "*", SearchOption.TopDirectoryOnly).Select(Path.GetFileName).ToList()) // Get files in folder  
           { //append them to DataTable  
             var toInsert = dtFiles.NewRow();  
             toInsert["lbFileName"] = file;  
             dtFiles.Rows.Add(toInsert);  
           }  
 using (SqlConnection sqlConRead = new SqlConnection(_conStringRead))  
           {//Bulk Insert  
              sqlConRead.Open();  
             var bulkCopy = new SqlBulkCopy(sqlConRead);  
             bulkCopy.DestinationTableName = "Your Table Name";  
             bulkCopy.ColumnMappings.Add("lbFileName", "lbFileName");  
             bulkCopy.WriteToServer(dtFiles);  
           }  

So the App.Config file looks like below;

 <connectionStrings>  
   <add name="Read" connectionString="Data Source=xxxx;Initial Catalog=DB;Persist Security Info=True;User ID=development;Password=development; Current Language=French;" providerName="System.Data.SqlClient"/>    
 </connectionStrings>  
 <appSettings>    
   <add key="folderPath" value="X:\FolderPath\"/>    
 </appSettings>  

Here I insert the list of files in the folder to a given table by matching it's columns with the DataTable columns at once. This is a great solution for dealing with large amount of data.

Try and you will enjoy this.

Happy Coding...