|
In the first part of this LINQ series I've shown how to filter and sort an object list:
var winners = from r in racers
where r.Wins > 3
orderby r.Wins descending
select r;
foreach (Racer r in winners)
{
Console.WriteLine("{0}, {1}", r.Name, r.Wins);
}
Now I'm showing the same result from racers stored inside the database. Racers should be filtered according to the number of race wins, and sorted.
The traditional way is by creating a connection using an ADO.NET connection class, defining the SELECT statement using the SqlCommand class and defining a SELECT statement including WHERE and ORDER BY. With a data reader all records that are already sorted can be read.
SqlConnection connection = new SqlConnection(@"server=localhost\yukon;database=Formula1;trusted_connection=true");
SqlCommand command = connection.CreateCommand();
command.CommandText = "SELECT Name, Wins From Racers WHERE Wins > 3 ORDER BY Wins DESC";
connection.Open();
SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
{
Console.WriteLine("{0}, {1}",
reader.GetString(0),
reader.IsDBNull(1) ? 0 : reader.GetInt32(1));
}
reader.Close();
How can the same be achieved usind DLINQ?
Formula1Database db = new Formula1Database(@"server=localhost\yukon;database=Formula1;trusted_connection=true");
var winners = from r in db.Racers
where r.Wins > 3
orderby r.Wins descending
select r;
foreach (Racer r in winners)
{
Console.WriteLine("{0} {1}", r.Name, r.Wins);
}
Compare the code accessing the database with the code from the first part of the article accessing an object list. The same syntax is used accessing object lists and data from the database.
To get the data from the database, the Formula1Database class derives from the base class DataContext. With the constructor a connection string is passed to the base class. The class also contains a field that maps to the Racers table in the database returning Racer objects.
public class Formula1Database : DataContext
{
public Formula1Database(string fileOrServerOrConnection)
: base(fileOrServerOrConnection)
{
}
public Table<Racer> Racers = base.GetTable<Racer>();
}
The mapping to the Racers table in the database is defined with [Table] and [Column] attributes:
[Table(Name="Racers")]
public class Racer : IComparable<Racer>
{
//...
[Column(Id=true)]
private int id;
private string name;
[Column]
public string Name
{
get { return name; }
set { name = value; }
}
private string car;
[Column]
public string Car
{
get { return car; }
set { car = value; }
}
private int wins;
[Column]
public int Wins
{
get { return wins; }
set { wins = value; }
}
//...
Part 3 will show how filtering and sorting can be done using XML data.
Christian
|