Save objects to a database?

Question

So far in my .Net coding adventures I've only had a need to save information to files. So I've used XmlSerializer and DataContractSerializer to serialize attributed classes to XML files. My next project, however, requires that I save and retrieve information from a SQL server database. I'm wondering what my options are for doing this.

The current version of the app, which was not created by me, uses a lot of hard coded SQL commands. But now I'm trying to avoid doing anything where I have to read or write individual fields to or from the database or objects. I especially want to avoid a lot of hard coded SQL in my code. I like how the serializer classes just figure out how to read and write XML files based on the attributes and or public properties of the class. Is there something similar for a database rather then XML?

Answer 1

There is no generic object type when you deal with databases. Only tables and fields. The combination of these could make an object though. Your best bet is to use stored procedures if you are concerned with hard coded SQL on the client code.

I'm also mainly referring to the actual field types in a database. ORM's are a different story. If you want look into nHibernate if you want an object relational mapper that can help with INSERTs, SELECTs, etc.

Answer 2

Depending on the project an ORM like NHibernate might be what you're looking for. Something where you map your database information to classes and the ORM takes care of the inserts, deletes, and selects for you without hand-written SQL. This also allows for migration to a different database system without a ton of rewrite.

I say it depends on the project because other things come into play here like performance and how the data is actually structured.

Answer 3

Object Relational Mapping

There are bunch of products out there, most notorious one being NHibernate, there are couple of competing products offered by Microsoft in Linq 2 Sql and Entity Framework (you're supposed to use the later, but everyone uses the first as is waaaay simpler).

You can see a nice (although I suspect biased) comparison of ORM offerings at http://ormbattle.net/

Answer 4

I believe you're referring to Object Relational Mappers. These provide a wealth of functionality, including simple object CRUD plumbing.

Check out:

• NHibernate
• Entity Framework
• Linq to SQL

There are many others, but that'll get you going.

Answer 5

I think you should read up on Linq to SQL. This will allow you to work "primarily" with classes that are representations of your database tables and their relations.

DataContext context = new DataContext();
var obj = context.Table1.Single(row => row.Id == 1234);
obj.Name = "Test1234";
context.SubmitChanges();

This could be a good place to start to learn about Linq to SQL Hope this is what you are looking for.

Answer 6

I agree with (and prefer) the previous suggestions to use an ORM. Just to make sure you have a full menu of options here is another option. If you're comfortable with the XML representation, (de)serialization, etc... you could also look into using SQLXML. With that said, you should not use this to avoid doing proper database design although this can be totally reasonable for some solutions.