I am working on a new personal project (I mentioned it in my videocast), I am building it in MVC, since it is a small project I wanted an easy to use and implement ORM. I chose LINQ to SQL after hearing several good things. It has been a real breeze to set up and use. So, without further ado, I have put together a tutorial on how to set up LINQ-TO-SQL.
First add a new Linq to SQL class to your project. Since I'm using MVC, I'm putting my Linq to SQL classes in my Model folder. However, you can put them wherever you want. When I add the classes I get the Object Relational designer telling me to drag over items from my server explorer to map them.
Now you need to add your SQL server to your project's Server Explorer. Right click on data connections and go to add a new connection. Put in the ODBC info for your server, and make sure to test your connection before closing. Now, if you expand your tables all you have to do is drag them over to the OR Designer.
You can very easily add relationships to your objects. I'm not sure if that would automatically happen if they are related in the DB. Someone will need to try and let me know. In order to add a relationship you right click on one of your tables, click "Add" and choose "Relationship." You can then specify the parent and child tables as well as what columns hold the relationship.
Next I'm going to build an interface to list my methods that I'm going to build to deal with my objects quickly. As you can see, mine are specific to my application, but I think they will be helpful to you anyway.
To create these methods, first I'm going to create an instance of the Data Context that was created with my .dbml. Then I'm going to use LINQ to query my objects for me and return them. I'm just making a basic Save method, and some small queries that I think I will need.
There you have it. That's how easy it is. I KNOW! You can download my source here. Now we have my model set up we can move to the rest of our MVC for web forms developers series. Stay tuned!!
Good job Sara! To answer your question, yes. The O/R Designer will automatically pick up any FK relationships defined in the DB. In fact, that is probably the better way to go -- in case you decide to go with NH or EF at some later point, the relationships will already be there for you.
ReplyDeleteGreat article!
ReplyDeleteDid you know that LINQ to SQL is placed into maintenance mode? The Entity framework will be the recommended solution for data access in .NET 4.0.
@erik -- L2S is a perfectly good solution, and it's not nearly as half-baked as the EF 1.0 is right now. Wouldn't even consider it until 2.0 comes out...
ReplyDeleteDmitry - I am a lazy developer who usually does my DB relationships right before production, lol, I trust myself with my data but not my users. Kids - don't try this at home!!!
ReplyDeleteGood call on the IDisposable. Thanks.
I find linq to sql to be a little bit of evil. You are using your database schema as your model objects. this seems crazy to me. In my mind i should model my domain first, then figure out how it maps to a 2d data schema. further, i'm not sure layer constraints can ever be met using this tool, you'll pretty much as you describe, HAVE to put persitance code in your model layer. YIKES!
ReplyDeleteI recently came across your blog and have been reading along. I thought I would leave my first comment. I love the way you write post!
ReplyDeleteSara, great site and awesome work. It is rare to find a very attractive woman in this industry so I was in shock when I found this blog. Keep up the great work.
ReplyDeleteVery eminent post, thanks for sharing useful information with us.
ReplyDelete