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.
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. |
|
|
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.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.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.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. |
| Create a simple form to add a new Student with a GridView to display the current students. It should look something like this |
|
| |
|
 |
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";
}
}
|
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.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. |
