What is the difference between using the DataContext class and SqlConnection?

Question

This may be a very vague question, but I think I really do not understand what is going on. I asked a question earlier , where I was told that an easy way to "bind data to objects" is to simply run SqlConnection (connectionString). The answer also included a comment in which I could get an idea of ​​L2S and Entity Framework, so I looked deeper into them. It seems that all you need to do with the DataContext object is pointing to the database. Why is SqlConnection an advantage?

What is the difference (or pros / cons) of using one of them? Is another "standard"? Another modern one?

PS I asked a lot of questions that not everyone should answer. I just wanted to add some clarity to my question and how much I really do not understand this topic.

Answer 1

SqlConnection is part of the base, source ADO.NET class library - part of the SQL Server of this library. This is the foundation of all data access in .NET.

With raw ADO.NET you are pretty bare bones and close to metal - you need to create your SQL queries and execute them, you get rows and columns very similar to a relational database. you.

Pros: really close to SQL, really powerful, better performance
Cons: more difficult to write, more glue, less type safety, closer connection with the basic structure of the database.

---

DataContext (Linq-to-SQL) or ObjectContext (Entity Framework) are higher-level abstractions - they sit on top of ADO.NET, but they (Linq-to-SQL or Entity Framework) offer the so-called ORM Features - you do not deal here with raw SQL statements and rows / columns, instead, these code generators will create for you an abstraction layer that is created from .NET objects. Each table in the database will be converted to a corresponding .NET class with properties for all columns in this table.

Also, with L2S and EF, you usually use LINQ to query - your queries are much larger than C # code, and L2s / EF handles the translation of those queries that you express in C # into the actual SQL statements that SQL Server will execute.

Pros: it’s much more convenient to work, it’s much better to handle (objects with properties or source rows / columns), security type, the ability to query with LINQ, higher dev performance
Cons: a different level means more translations, a hit on productivity, is not suitable for certain things (for example, mass operations)