Linq To SQL Tutorial

Check out some of my other Linq to SQL posts:

This is a basic tutorial for using Linq To SQL introducing some of the concepts behind it.

For this tutorial I have created a simple database for a University.  I will probably use this database and expand on it for any future posts where a database is required.  Below you can see the structure of the database.

ERD

Create a new website called Universities and add a second project called DAL to the solution.  This is the Data Access Layer where we will create the Linq To SQL classes.

Universities Solution

Right-click on the DAL project and select Add > New Item.  From the dialog choose ‘LINQ to SQL Classes’ and call the file University.dbml.

New Linq to SQL file

The .dbml extension stands for Database Markup Language.  The file itself is essentially an XML file that describes the database which is used to generate our classes.  When you add your new dbml file the Object Relational Designer (O/R Designer) should launch which is a visual tool for creating your model.  There is also a command-line tool called SqlMetal which gives you further control over how your model is created.  In this instance we will use the O/R Designer.

Locate your database in the Server Explorer window (View > Server Explorer), and drag and drop the tables to use onto the surface of the O/R Designer.  The result should look like this:

Object Relational Designer

Save and close the the O/R desinger.  In solution explorer you should see the University.dbml file, expanding this shows two other files, University.dbml.layout and University.designer.cs.

Solution Explorer

Examine University.designer.cs and you will find your DataContext which inherits System.Data.Linq.DataContext, and your model classes which were generated from the dbml file.  These classes are all partial classes which allows you to easily build them out to add extra functionality which won’t be affected if you need to regenerate from the dbml.

At this point we are now ready to use the generated classes.  In the website create a reference to the DAL project, and to System.Data.Linq.

linqtosql_referencelinq

Create a simple form to add a new Student with a GridView to display the current students. It should look something like this:

Student Form

Firstly we want to bind the data from the Title table in the database to the Title DropDownList on the form.  Manually add some data to this table such as Mr, Mrs and Miss.  In the Page_Load event handler put a (!Page.IsPostBack) condition and call the following method:

private void PopulateTitles()
{
using (UniversityDataContext context = new UniversityDataContext())
{
ddlTitle.DataSource = context.Titles;
ddlTitle.DataTextField = "Name";
ddlTitle.DataValueField = "ID";
ddlTitle.DataBind();
}
}

This method first creates an instance of our DataContext to use.  We then set the DataSource of the DropDownList to context.Title which is of type System.Data.Linq.Table.  We can query this object further, but in this instace I want all rows from the table.  The DataTextField and DataValueField properties are set to the relevant properties of the Title object.  Run the application and the Title dropdown will be populated with the values from the database.

The next step is to create a new student and save it to the database.  Call the following method from the Save button event handler.


private void SaveStudent()
{
using (UniversityDataContext context = new UniversityDataContext())
{
Student newStudent = new Student()
{
TitleID = Convert.ToInt32(ddlTitle.SelectedValue),
Forename = txtForename.Text,
Surname = txtSurname.Text,
DOB = Convert.ToDateTime(txtDOB.Text),
EmailAddress = string.IsNullOrEmpty(txtEmail.Text) ? null : txtEmail.Text,
Phone = string.IsNullOrEmpty(txtPhone.Text) ? null : txtPhone.Text
};

context.Students.InsertOnSubmit(newStudent);
context.SubmitChanges();
}
}

We are creating a new Student object and setting the properties of that object based on the form values.  We are using an Object Initializer which allows setting the properties of the object without having to explicity envoke a constructor.  The penultimate line adds our newly created object to the table in a pending state, then calling the SubmitChanges method on the DataContext submits any pending changes in the context.

Now that there is data in our Student table it would be good to be able to see it.  Add the following method to our code and call it from the Page_Load event handler.


private void PopulateStudents()
{
using (UniversityDataContext context = new UniversityDataContext())
{
var students = from s in context.Students
select new
{
Title = s.Title.Name,
Forename = s.Forename,
Surname = s.Surname,
DOB = s.DOB.ToShortDateString(),
EmailAddress = s.EmailAddress,
Phone = s.Phone
};

gvStudents.DataSource = students;
gvStudents.DataBind();
}
}

This method uses a Linq query to assign an Anonymous Type using the var keyword.  We could bind context.Students to the GridView as we did with the Titles, but in this instance I want the Name of the Title for the student using context.Student.Title.Name and I also want to show the DOB without the time.  The Linq query is selecting all rows in the Students table, and creating a collection of the Anonymous Type setting each of its properties.  We then set the datasource of the GridView to this collection and call DataBind.

I hope this tutorial gives a good overview of Linq To SQL for anybody new to it.  In the real world you would probably not access your object model directly from the page, but write a repository model that handles all the data access and DataContext.  I will write more about how to do this at a later date.

Posted on by Joe in C#, Linq

26 Responses to Linq To SQL Tutorial

  1. Joe T

    This is a very clean and useful introduction to LINQ covering aspects of how it works and can be used in the context of a standard web example. Great explanation and layout. Thank you for the same.

  2. Joe

    Hi Joe

    Thanks for your comment.

    I’ve been meaning to reorganise my Linq to SQL posts and write some more follow-ups to this. It’s just finding the time!

    Cheers
    Joe

  3. Dulari

    Hi,

    Useful information. If you can aslo eloberate the advantages over simple query language in example, it would be more clear for novice users like me.

    This tut exactly says what is linq and how can we use it, but i still havnt got the idea how its surpasses the regular way of querying which is more easy for me.

  4. Joe

    Hi Dulari

    There is nothing wrong with using stored procedures and TSQL, Linq to SQL is just an alternative that can be advantageous is certain situations.

    Some of the advantages include type safety, intellisense and debugging. Linq to SQL gives you an easy way to generate your objects from your database model, and as each object generated is marked as partial it is easy to extend the object to include business logic rules or hook in audit logging or anything similar.

    The main disadvantage is that the object model created is a one to one mapping of your data model which isn’t always dersired, although you can play around with the object model in the OR Designer.

    Have a look at this post to see how you can start to create a generic framework for your Linq to SQL classes to be able to perform simple CRUD operations without having to write additional code or stored procs for every table in your database.

    I’ve then extended the framework in this post to allow queries to easily be created on the fly using the dynamic linq library so that more complex queries can be generated without having to write additional code. Using this does loose the type safety provided when writing queries with Linq but the result is fairly cool.

    Linq to SQL is not ideal in all situations; it probably serves best in smaller applications, although I have used it for a large enterprise project which still worked extremely well.

    Joe

  5. Jim

    If I have my own data classes that I want to use and not use the ones generated by dbml, is there a way to de-couple the generated classes and couple the ones I created?

  6. Joe

    Hi Jim

    The generated classes contain a lot of extra stuff which is required for Linq to SQL to work. Using the generated models is kind of the whole point of Linq to SQL.

    What are you trying to achieve? If you just want to add extra business methods etc to the models then it’s simple as the generated classes are partial, so you can just create another partial class and add your own code.

    Joe

  7. Dean

    What is wrong with using data access guidance package of WSSF? It is quite neat – provides DTO objects and data access factory classes.

  8. Joe

    Hi Dean

    This is a Linq to SQL Tutorial just explaining how to use it. I’ve never used WSSF and I didn’t say there is anything wrong with it, or any other framework.

    Joe

  9. Saminathan

    Hi really it is very useful for Me

  10. Joe

    Hi Saminathan

    Glad it helped.

    Joe

  11. Naveed Anjum

    Amazing Tutorial for beginners….

  12. Pingback: Implementing DAL « Sam's Daily Life

  13. Eddy

    I liked your introduction to LINQ ,its very clear and straight forward,it was useful to me thanx.dont mind my poor English

  14. Izhar

    The sample code is showing the following error.

    The type or namespace name ‘UniversityDataContext’ could not be found (are you missing a using directive or an assembly reference?)

  15. Joe

    Izhar

    Did you make sure you DBML file is called University. If you view the code you should see the UniversityDataContext class.

  16. Sumit Thapar

    Very nice article…..clean and clear…..thank you so much

  17. shashank

    this is nice article.

  18. Abdhal

    plz tell about the Linq to sql method without creating DBML file… i heard that some mapping methods are using

  19. Rahul

    Very clearly shown.
    Is there a sample for Update and Delete. You have already shown Select and Insert.

  20. Pingback: Интеграция SharePoint и Navision | Внедрение Sharepoint / ECM

  21. Truls

    Thank you very much for this. This saved me a lot of time getting up to speed on this.

  22. Mayur Prajapati

    hi,,
    it’s really good tutorial for beginers…..
    it is very useful for me….

    thanks a lots……….

  23. Pingback: Linq to SQL with WCF Services « Sochinda's Blog

  24. ted

    can i get this code in visual basic?
    thanks

  25. Joe

    Ted

    I’ve not use visual basic for many years, but you should be able to find a converter online that should do a good enough job.

    Cheers
    Joe

  26. chris

    Hey, it would be handy if you could post the script to generate the database, great tut, thanks.

Add a Comment