KIT206 Tutorial 8 – Communicating with Data Sources
KIT206 Software Design & Development KIT506 Software Application Design & Implementation
1/4
Tutorial 8: Data Sources and LINQ
Purpose: To practise the use of LINQ for filtering and searching data structures, and to communicate
with the HRIS case study’s MySQL database.
Outline
The Language Integrated Query (LINQ) feature of C# (and also Visual Basic) allows common collection
processing tasks to be written in a consistent form within the host language while being applicable to
a variety different data sources: arrays, Collection types, XML documents and databases (Microsoft’s
SQL Server natively and others with suitable third-party libraries such as LinqConnect [this is not an
endorsement of that particular product, merely an example]).
In the absence of a suitable third-party library, communicating with a MySQL database requires that
we fall back on classes for issuing native SQL statements expressed as strings and the general-purpose
and hence awkward-to-use MySqlDataReader and MySqlDataAdapter classes to read the results of
queries. In the future you will often use technologies like Entity Framework (with .NET) and Hibernate
(with Java) to map between your system’s classes and database tables, but whenever you are faced
with a legacy database system you will need to fall back on the approach described here.
The following tasks are based on the last two tutorials. Either load your project from the last tutorial
or download the sample solution as a starting point.
1 LINQ to Objects
C# provides considerable flexibility in querying data structures. In addition to the traditional approach
of writing a for or foreach loop, collection objects may be queried using short lambda expressions
(a topic we do not cover due to time constraints), or by using LINQ. LINQ queries are typically longer
than the equivalent operation using a lambda expression, but they are also more easily parsed by a
human reader.
1.1 Filtering with LINQ
In the previous C# tutorials you filtered a list of Employee objects by instantiating a new list and using
a foreach loop to iterate over a master list and to select the objects matching some condition
(previously it was Employee gender). Locate the Filter() method where you wrote that code and
replace it with the following, which you are to complete:
var filtered = Some LINQ expression
return new List
Task: Replace Some LINQ expression with an actual LINQ expression that selects Employee objects
whose gender matches the value passed to the method. Your tutor will provide assistance and, once
everyone’s had an opportunity to attempt this, go through a possible solution with the group.
1.2 Assignment experimentation: Who’s rostered on to work now?
This task may be left until after the tutorial if you believe you will not be able to get much of Part 2
done before the end. Note, however, that task 2.3 requires that the following is completed first.
Create a new class called RosterItem that contains auto-generated properties called Day (a
DayOfWeek), Start and End (both TimeSpan objects). As you can probably guess, each RosterItem
represents a period of time when an Employee may work.
KIT206 Software Design & Development KIT506 Software Application Design & Implementation
2/4
Add a method with the following header:
public bool Overlaps(DateTime sometime)
Using any resources available to you (MSDN, IntelliSense [the pop-up help the editor provides], etc.)
implement this method so that it returns true if the given date time represents the same day of the
week and is after Start but before End.
Add a ToString() method that returns a string that combines the Day, Start and End time (they have
their own ToString() methods that will be called automatically if they are concatenated with a string).
1.2.1 Create some RosterItems
For testing purposes, instantiate and populate a List of RosterItems in Main(). Include at least five
objects, making sure that some have the same day as today, some have start and end times that
overlap the time now (this is actually not essential) and some definitely overlap with the time now.
1.2.2 Filter the list of RosterItems
Create a LINQ expression that finds all the RosterItems that overlap the time right now. (Note that for
testing purposes you could also create a DateTime that you know falls within one of the RosterItems’
time frame; you don’t have to wait until the current system time matches one of the entries.)
Make sure you add some code to display the filtered list to check that it works.
When you’re satisfied, consider commenting out that testing code before moving to Task 2.
2 Communicating with a MySQL database
The tasks in this section rely on the following database connection information:
Database, User Id, and Password: kit206 Data Source: alacritas.cis.utas.edu.au
Refer to the Module 6 presentation slides and accompanying SqlCommandDemo.cs file for the
relevant code for establishing a connection to a MySQL database, issuing a basic query, and reading
the result. The remainder of the tutorial will assume you have these materials available.
2.1 Connecting to the Employment & Research Database
Task: Modify the Agency class so that it has a (static) MySqlConnection field called conn. Add this
method to Agency, which will allow different methods within it to obtain a reference to conn when
needed. You won’t need to have as much line wrapping in your code.
private static MySqlConnection GetConnection() {
if (conn == null) {
string connectionString =
String.Format(“Database={0};Data Source={1};User Id={2}; Password={3}”,
db, server, user, pass);
conn = new MySqlConnection(connectionString);
}
return conn;
}
Note that, in your assignment, if your bridge to the database is implemented by an object, and not by
class methods in an abstract class, then code like this would go in the constructor (and the check if
conn is null could be omitted).
KIT206 Software Design & Development KIT506 Software Application Design & Implementation
3/4
2.2 Reading Staff members into Employee
You will have recognised that the Employee class developed in recent tutorials is similar to whichever
class you have in your assignment to represent a staff member. In fact, because we are taking
complete control over the mapping from database to application object, there’s no reason why we
can’t treat it that way as part of this learning exercise.
Task: Create a new method in Agency called LoadAll(), with the same return type as the existing
Generate() method. Using SqlCommandDemo.ReadData() as a guide, implement this method so that
it selects the id, given_name and family_name columns from the staff table. The relevant changes
are:
• Near the start of the method call GetConnection() to ensure that conn exists; you may assign
the result to a local MySqlConnection variable if you wish
• Modify the SQL select statement string
• Declare a new List of Employee objects
• Each time through the loop over the result set, instead of writing the retrieved data to the
console, create a new Employee, setting:
o its ID to rdr.GetInt32(0) (assuming you selected id first)
o its Name to the concatenation of rdr.GetString(1) and rdr.GetString(2)
Remember to return the new list of Employee objects at the end.
Modify Boss so that it calls LoadAll() instead of Generate() and run the program to test.
2.3 Consultation is work too
Add a new method to Agency called LoadRosterItems() that will query the database for all
consultation times for a given Employee (i.e., staff) ID passed as a parameter and return a List of
RosterItems describing those blocks of time.
2.3.1 Constructing the query
Follow the pattern from 2.2, but modify your query code to be the following:
MySqlCommand cmd = new MySqlCommand(“select day, start, end from
consultation where staff_id=?id”, conn);
cmd.Parameters.AddWithValue(“id”, id);
The indicates where the line has been wrapped to fit the page here; do not include it in your code.
What does the ?id mean? Although we could have simply appended the integer id the method
accepted as a parameter to the query string, it is generally better to separate the unchanging parts of
a query from those parts that will change. When communicating with a MySQL database ?name is a
template parameter with the identifier name.
2.3.2 Creating the RosterItem
Given the query above, and a MySqlDataReader called rdr, the Start and End properties of RosterItem
can be set to rdr.GetTimeSpan(1) and rdr.GetTimeSpan(2), as in
new RosterItem{ Start=rdr.GetTimeSpan(1), End=rdr.GetTimeSpan(2) };
To do a quick test that this works, modify Main() to call Agency.LoadRosterItems() with a staff ID
you know is in the database and then display each one on the console.
KIT206 Software Design & Development KIT506 Software Application Design & Implementation
4/4
Alternatively (and optionally), if you’re feeling confident and have time right now, add a
List
of RosterItems for each Employee in turn, then modify the Employee ToString() method to include
each of its RosterItems. Although this takes longer, it makes one of the later tasks easier.
Whichever approach you chose, test your program to view some loaded RosterItem objects.
2.3.3 Converting strings to Enums
Currently the loaded RosterItems have a start and end time, but no day. We happen to know that the
System.DayOfWeek type is an enumeration of the values Sunday through Saturday, and also that the
day column of the consultation table only contains strings with the values “Sunday” through
“Saturday”. Surely it should be easy to convert the string value to the Enum value.
Oddly, whereas C# allows many things to be expressed more succinctly than in Java, converting a
string to an Enum is ridiculously complicated in C# (and trivial in Java). So, add this general purpose
method to Agency to assist in the conversion:
public static T ParseEnum
{
return (T)Enum.Parse(typeof(T), value);
}
Then, given a string s, it can be converted to a value of type DayOfWeek via:
DayOfWeek day = ParseEnum
Modify the code that creates each RosterItem to use the ParseEnum method. (Note that, apart from
the fiddly conversion from database string value to Enum value, Enums do make the rest of your code
more robust.)
2.3.4 Is a staff member busy right now?
Now that you can load a collection of RosterItems for a given Employee you can determine if an
Employee is busy right now (or at a date and time of your choosing). There are two alternative options
for completing this task, both of which are valid but would be used in different situations:
Option 1: If you have added a WorkTimes property to Employee and loaded roster/consultation times
into it, add a method to Employee called BusyNow() that returns a bool indicating if any of those
RosterItems overlaps the current time. Use a LINQ expression to select each RosterItem that overlaps
Use the Count() method on the result of the LINQ expression to see if there were any overlapping
times. To test this approach, modify Employee.ToString() to include the result of BusyNow().
Option 2: Alternatively, write a new method in Agency that uses a “SELECT COUNT(*)…” query to
perform the same test directly on the database. The method should accept either an Employee or an
integer ID.
3 For your assignment
Look for similarities between today’s exercises and the functions required of your assignment
application. Adapt your approaches to suit the needs of the assignment.
4 Not covered: disconnected data and DataSets
The potential scope for this unit is extremely large. One of the approaches to loading data from a
database into a DataSet object, is covered briefly in Module 6 but is not essential for implementing
the assignment, so is not covered in this tutorial. Be aware that it is an alternative that can make some
data processing tasks easier, especially when modifying the contents of a database.
Outline
1 LINQ to Objects
1.1 Filtering with LINQ
1.2 Assignment experimentation: Who’s rostered on to work now?
1.2.1 Create some RosterItems
1.2.2 Filter the list of RosterItems
2 Communicating with a MySQL database
2.1 Connecting to the Employment & Research Database
2.2 Reading Staff members into Employee
2.3 Consultation is work too
2.3.1 Constructing the query
2.3.2 Creating the RosterItem
2.3.3 Converting strings to Enums
2.3.4 Is a staff member busy right now?
3 For your assignment
4 Not covered: disconnected data and DataSets