Reading CSV file into OLEDB vs SQLite into DbDataReader(System.Data.Common)

Anwind

Well-known member
Joined
Nov 6, 2021
Messages
48
Programming Experience
1-3
Hello

It's a question of memory efficiency in C#.NET VisualStudio2019.


In C#.NET, Reading CSV file into OLEDB vs SQLite into DbDataReader(System.Data.Common).

In C#.NET, Reading CSV file into OLEDB : Is it memory efficient?
In C#.NET, SQLite into DbDataReader(System.Data.Common): Is it memory efficient?

Which is more efficient ( Memory of PC ), Reading CSV file into OLEDB or SQLite into DbDataReader(System.Data.Common)?

Please Help me.
 
Last edited:
If I had to read a CSV like this:

C#:
Name,Height,Birthday,IsActive
John,180,1970-01-01,True
Jane,170,1980-01-01,False

I would add a reference to Sylvan.Data.Csv from nuget and have a code like:

C#:
using Sylvan.Data;
using Sylvan.Data.Csv;

using var csv = CsvDataReader.Create("path/to/csv.csv");
var people = csv.GetRecords<CsvPerson>().ToList();


record CsvPerson(string? Name, int Height, DateTime Birthday, bool IsActive) { public Person() : this(default, default, default, default) { } }

And then i'd take my People list and (probably) run it through AutoMapper to map it to my database Person entity, add those person to an EF context and save it - another couple of lines of code. The Person DB entity and context would have been created by quickly putting together a database in some query tool (I tend to use SQLS and SSMS, but Sqlite has its own ecosystem of such things) and then I would tell EF to analyse the database and write code from it; EF Core Power Tools VS extension makes this easy too.

We end up with a set of code, mostly written by tools in a few seconds, that we can just use like:

C#:
var newDbEntities = mapper.Map<CsvPerson, Person>(people); //Person is the DB entity type, CsvPerson is the record read from CSV. mapper is the configured AutoMapper that knows how to translate a CsvPerson to a Person object
context.People.AddRange(newDbEntities);
await context.SaveChangesAsync();

If I had millions of CSV records to do, I'd use a loop rather than ToList() so that I wasn't loading the whole file at once, and I'd look at the EFCore Bulk Extensions for batch loading of the data for a useful speed boost

How should I sort?:
CsvDataReader dr = CsvDataReader.Create("demo.csv");

var NameOrdinal = dr.GetOrdinal("Name");

IDataReader dataReader = dataReader.Select("Id", "Name").Where( n=>n.GetValue(NameOrdinal).ToSring() == "ABC" ).OrderBy(Id);


Does Sylvan have Sort = Order by function??

Please Help me

The return should not be an Array List.
 
Last edited:
OrderBy doesn't look like that; it takes a lambda that fetches/realizes the item that should be sorted

C#:
 listOfPeople.OrderBy(person => person.Age);
 
Recall that DbDataReader exposes IEnumerable. It's going to take a couple of extra steps to get an IEnumerable<T> for the LINQ OrderBy<T>() to be applicable.
 
If I change it to IEnumerable, can I change it back to IDataReader or DbDataReader or Sylvan CsvDataReader ?
If you were reading the Sylvan.Data documentation, you would know the answer to this.
 
Back
Top Bottom