LINQ to SQL is an API that allows querying relational databases. The samples in this post are based on the concepts of:
- Entity classes (which are instances of Entity Types). Such a class is a regular .NET class that is decorated with the attribute Table and its properties and fields with the attribute Column
- DataContext, is the channel for doing operations with the database; it is used like an ADO.NET connection; actually its constructor takes either a connection string or an ADO.NET connection
Considering the known Winner class from the previous posts,
public class Winner
{
public string Name { get; set; }
public string Country { get; set; }
public int Year { get; set; }
}
Decorating it with Table and column like this
[Table(Name = "Winners")]
public class Winner
{
[Column]
public string Name { get; set; }
[Column]
public string Country { get; set; }
[Column(IsPrimaryKey = true)]
public int Year { get; set; }
}
will create a direct mapping between Winner and the table call Winners, and between the fields Name, Country and Year of the class and the columns with the same name from the table. Both Table and Column have several properties. One of them is Name, which specifies the actual name of the table or column corresponding to the class or property. If the property Name is not specified, the same name as for the class and properties is used.
Assuming we have a SQL Server database, located at C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\UCL.mdf, with a table Winners that has three columns Year (which is also the primary key), Name and Country, and that this table is populated with the winners of UEFA Champions League, we could write the following code to retrieve and show the winners:
public void PrintWinners()
{
// creates a data context that takes the path of the database
DataContext dc = new DataContext(@"C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\UCL.mdf");
// retrieves a Table of Winner
Table winners = dc.GetTable();
// creates a sequence of winners ordered descending by the winning year
var result = from w in winners
orderby w.Year descending
select w;
// prints the sequence of winners
foreach (var w in result)
{
Console.WriteLine("{0} {1}, {2}",
w.Year, w.Name, w.Country);
}
}
First, we must instantiate a DataContext, passing as argument the path to the database. DataContext has a method GetTable, that returns a Table
2006 Barcelona, Spain 2005 Liverpool, England 2004 FC Porto, Portugal 2003 AC Milan, Italy 2002 Real Madrid, Spain 2001 Bayern Munchen, Germany 2000 Real Madrid, Spain 1999 Manchester Utd., England 1998 Real Madrid, Spain 1997 Borussia Dortmund, Germany 1996 Juventus, Italy 1995 AFC Ajax, Netherlands 1994 AC Milan, Italy 1993 Olympique de Marseille, France
It is however recommended that we use a so called strongly-typed version of DataContext. In other words, a derived class from DataContext that keeps as members all the table collections. In this case we don’t have to call directly GetTable<>();
public class UCLDataContext : DataContext
{
public Table Winners;
public UCLDataContext(string connection)
:
base(connection)
{}
}
The PrintWinnes function would have to change to:
public void PrintWinners()
{
// creates a data context that takes the path of the database
UCLDataContext dc = new UCLDataContext(@"C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\UCL.mdf");
// creates a sequence of winners ordered descending by the winning year
var result = from w in dc.Winners
orderby w.Year descending
select w;
// prints the sequence of winners
foreach (var w in result)
{
Console.WriteLine("{0} {1}, {2}",
w.Year, w.Name, w.Country);
}
}
Only querying the database is not enough. DataContext also allow us to submit changes to the database.
The following function shows how to add a winner to the table Winners:
public void AddWinner()
{
// creates a data context that takes the path of the database
UCLDataContext dc = new UCLDataContext(@"C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\UCL.mdf");
// adds a new winner to the table
dc.Winners.Add(new Winner { Name = "Manchester United", Country = "England", Year = 2007});
// submites the changes
dc.SubmitChanges();
}
If you want for instance to remove all the winners from Spain, we can do the following:
public void DeleteWinner()
{
// creates a data context that takes the path of the database
UCLDataContext dc = new UCLDataContext(@"C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\UCL.mdf");
// remove a sequence of winners
dc.Winners.RemoveAll(from w in dc.Winners
where w.Country == "Spain"
select w);
// submites the changes
dc.SubmitChanges();
}
