Saving Large Set of Data

Posts   
 
    
Ramsha
User
Posts: 1
Joined: 19-Jul-2022
# Posted on: 19-Jul-2022 12:21:51   

Hi I need to save large Set of data like 20000 record in one go using LLBLGen Adapter what will be the best approach to use this?

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 20-Jul-2022 10:25:27   

Either use Batching

Or use System.Data.SqlClient.SqlBulkCopy

Example:

            List<IEntityPropertyProjector> entityCollectionProjection = EntityFields2.ConvertToProjectors(collectionToSave[0].Fields);
            var result = new DataTable();
            collectionToSave.DefaultView.CreateProjection(entityCollectionProjection, result);
            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(new DataAccessAdapter().ConnectionString))
            {
                bulkCopy.BulkCopyTimeout = 240;
                bulkCopy.DestinationTableName = "dbo.Products";

                try
                {
                    bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("ProductCode", "ProductCode"));
                    bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("Actual", "Actual"));
                    bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("Target", "Target"));
                    bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("Date", "Date"));
                    bulkCopy.WriteToServer(result);
               }