Import CSV File Records Into SQL Server Database In .NET Core

Introduction:

In this article, we will learn to insert CSV file records into the database with a minimum amount of code.

These days, CSV files are the most widely used file extension.
The majority of websites supporting this format with millions of data exporting possibilities include Shopify, Ali Express, and Amazon.

Prerequisite:

1. Basic knowledge of .net core

2.required SQL knowledge

Now let’s start coding…

first, you need to create a table in SQL database with some column where you want to add records.

I have created table MysteryBoxCodesEntity with four column:

ID,Code,PrizeId,PromotionId

Now in visual studio add following function as import code:

public async Task ImportMysteryCodes(string CodeFiles, int PromotionId, int PrizeId)
       {
           var removableList = await _applicationDbContext.MysteryBoxCodesEntity.Where(r => r.PrizeId == PrizeId && r.PromotionId == PromotionId).ToListAsync();
           _applicationDbContext.MysteryBoxCodesEntity.RemoveRange(removableList);
           await _applicationDbContext.SaveChangesAsync();
           var mainConvertBytes = Convert.FromBase64String(CodeFiles.Split("base64,")[1]);
           Stream stream = new MemoryStream(mainConvertBytes);
           CsvParserOptions csvParserOptions = new CsvParserOptions(true, ',');
           CsvUserDetailsMapping csvMapper = new CsvUserDetailsMapping();
           CsvParser<CodeReader> csvParser = new CsvParser<CodeReader>(csvParserOptions, csvMapper);
           var result = csvParser
                        .ReadFromStream(stream, Encoding.ASCII)
                        .ToList()
                        .Select(r => new MysteryBoxCodesEntity
                        {
                            Code = r.Result.Code,
                            PrizeId = PrizeId,
                            PromotionId = PromotionId
                        }).ToList();
           await _applicationDbContext.MysteryBoxCodesEntity.AddRangeAsync(result);
           await _applicationDbContext.SaveChangesAsync();
       }

Here, the CodeFile parameter has a list of records in the base64 string.

OUTPUT: