LINQ Part 1 - Filtering & Sorting Object Lists
LINQ Part 3 - Filtering and Sorting XML

LINQ Part 2 - Filtering & Sorting Using Data from the Database

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

Comments

Feed You can follow this conversation by subscribing to the comment feed for this post.

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment

Your Information

(Name and email address are required. Email address will not be displayed with the comment.)