Best way to access a SQL Server database using C# .Net

Question

I am new to .NET and have heard about several different ways of querying a SQL Server databse such as ADO.NET and the entity framework.

Can anyone give me some advise about the best way to go for new applications?

Thanks for any help or suggestions.

Answer 1

Here is an example using EF with code generation from the database (for a real app you probably want to generate your DB from the code, instead):

1. Right click on your project >> Add >> New Item >> ADO.NET Entity Data Model.
2. Pick a name for your entities i.e. MyEntities.edmx, click Next
3. Select "Generate from database"
4. Configure a 'New Connection' if there is not one already there. Next.
5. Select the Tables, Views, and SPROCs you want included in the entities. Finish.

You will see a file MyEntities.edmx added to your project. You can open it in design view to see a diagram of your entities and relationships. Note that each entity should have a primary key - the easiest way to do this is to add an ID - auto increment field to each table, or a GUID column. Anyway now you can query your db like this:

// assuming a "Product" table, which has an entity pluralized to "Products"

MyEntities db = new MyEntities();

var cheapProducts = db.Products.Where(p => p.Price > 30); // var is IEnumerable<Product>

Answer 2

Entity Framework is the easiest, and its built in.

All you have to do is important your model, and then just update simple classes, that are automatically mapped to your db.

Its just like updating normal objects, but at the end you call SaveChanges.