User1120430333 posted
They give read only permission all databases. I decided to create local sql database specifically used for this dashboard application only. This will separate production and also avoid lock issues and connection issues. I created separate services
to import data from production to my staging database and process the data from staging to my dashboard database.
One should not be having lock issues and connection issues. The only way there would be such issues is if one doesn't know how to do database access using an ORM like ADI.NET Entity Framework or just plain old ADO.NET with SQL Command objects with inline
T-SQL or sprocs and using either one effectively.
I am using only read not other operations (Edit, Update and Delete). So if i directly use ado.net with stored procedures, any performance difference?
There is no advantage here with you making stored procedures, because ADO.NET EF's database engine uses the internal MS SQL Server stored procedure to submit generated T-SQL to the database engine for execution.
I am thinking like EF will store database in the memory and it less performance that ado.net. But will get advantage of linq to sql
Yes, EF will use Linq to return materialized objects in a List<T> from the database query that was done by the EF engine generating T-SQL that is submitted to the MS SQL Server database engine via the MS SQL Server internal stored procedure for
execution with the results retuned to the program as a collection of objects in a List<T>.
Then Linq-2-Objects can be used on the List<T> of objects in memory that has nothing to do ADO.NET or ADO.NET EF at that point. However, the following is true in an EF situation if the list of objects are disconnected from the EF connection,
because otherwise, if one does a Linq query using EF that retrieves 100, 000 records/objects and then goes into a loop on the 100, 000 objects in the list while the EF connection is not closed leaving the objects in a connected state
in the list, then that's 100,000 reads again as EF goes back to the database to read each object from the database as it iterates objects in the loop. That's 200,000 reads from the database when there should have been only 100, 000 reads from the database.
Please help to decide this. EF is fine or ado.net is better in this case?
What's the purpose of using ADO.NET Entity Framework IMO if all one is doing in running sprocs from EF that one made. If one uses ADO.NET with SQL Command objects and sprocs, then one still has to put the results in a container like a List<T> that
EF is doing or one uses a datatable.
Where is the advantage of one doing it manually as opposed to EF doing it in this situation?
https://dzone.com/articles/reasons-move-datatables
There is nothing wrong in using stored procedures for specific business needs, but to use them for simple CRUD operations with a database when EF can do it with no trouble, I'll go EF every time. :)
