Strongly typed .net -> sql data access ?

Question

I know what i want but not sure of the name and required tools so here goes...

I have a few different sql tables.

I want a tool that will generate an object in my .net project for each table, with each column a property of this object. (i.e. strongly typed, no datatables)

I want to be able to call select, insert, update and delete methods in my .net project and possibly pass an object (i.e. 'Person') to my insert() method that will insert it into the sql table.

Im not sure whether this is a data access layer, and object-relational mapping or linq to sql, or a mishmash of both.

Any advice appreciated.

Answer 1

LINQ to SQL, nHibernate, and any of a number of other ORMs will do all that you require. I believe that there is also LINQ to nHibernate if you want to go the ORM route. Personally, I've been ok with LINQ to SQL alone, using the LINQ to SQL Classes designer to generate my entities. I prefer using the IQueryable extension methods over the LINQ syntax, but that's more of a personal preference. You will need to use data context methods for insert and delete, though.

Answer 2

Linq-to-SQL will allow you to drag'n'drop your database tables into your project in Visual Studio, and create exactly the kind of strongly-typed model you're looking for.

It has limitations - it doesn't do many-to-many associations (or rather, it does, but your association table will end up as a rather odd but necessary part of your model), and the visual designer isn't particularly friendly when it comes to reflecting changes to the underlying database.

That said, it's lightweight, it's simple, it creates partial classes so you can add your own logic and methods without breaking the autogenerated model, and it works.

NHibernate's greatest strength is that it doesn't require a 1:1 correspondence between your database schema and your domain model - it'll map one class to many tables, multiple columns to separate entities, and so on. This is extremely useful, but it obviously comes at a cost in terms of complexity, and it sounds like you can live without that extra complexity for now.

Answer 3

What you want is an implementation of the Active Record pattern.

There are many ORMs that will give you that implementation, including, but not limited to:

• NHibernate (through Castle Active Record)
• Entity Framework
• Subsonic
• LinqToSql

For choosing an ORM, you should look at this other question since this question has been asked and answered many times before:

http://stackoverflow.com/questions/1377236/nhibernate-entity-framework-active-records-or-linq2sql

You can also build your own Active Record implementation, but that isn't worth a whole lot outside of as a learning exercise.