public List <DataTuple> GetIllPersonsByYear(int startYear, int endYear)
{
using (SqlConnection connection = DatabaseClient.GetConnection(xmlPath))
{
connection.Open();
StringBuilder builder = new StringBuilder();
builder.Append("SELECT DISTINCT [No_], [First Name], a.[Last Name], a.Address, a.[Job Title] FROM [CRONUS Sverige AB$Employee] a ");
builder.Append("JOIN [CRONUS Sverige AB$Employee Absence] b ON a.No_ = b.[Employee No_] ");
builder.Append("WHERE b.[Cause of Absence Code] = 'SJUK' AND [From Date] BETWEEN @startDate AND @endDate");
string query = builder.ToString();
SqlCommand command = new SqlCommand(query, connection);
DateTime startDate = new DateTime(startYear, 1, 1);
DateTime endDate = new DateTime(endYear, 1, 1);
SqlParameter startDateParam = new SqlParameter("@startDate", System.Data.SqlDbType.DateTime);
startDateParam.Value = startDate;
command.Parameters.Add(startDateParam);
SqlParameter endDateParam = new SqlParameter("@endDate", System.Data.SqlDbType.DateTime);
endDateParam.Value = endDate;
command.Parameters.Add(endDateParam);
command.Prepare();
SqlDataReader reader = command.ExecuteReader();
List <DataTuple> tuples = new List <DataTuple>();
while (reader.Read())
{
DataTuple tuple = new DataTuple();
for (int i = 0; i < reader.FieldCount; i++)
{
tuple.Add(reader.GetName(i), reader.GetValue(i).ToString());
}
tuples.Add(tuple);
}
return(tuples);
}
}
public List <DataTuple> GetMetadata(string inputQuery)
{
using (SqlConnection connection = DatabaseClient.GetConnection(xmlPath))
{
connection.Open();
string query = inputQuery;
SqlCommand command = new SqlCommand(query, connection);
SqlDataReader reader = command.ExecuteReader();
List <DataTuple> tuples = new List <DataTuple>();
while (reader.Read())
{
DataTuple data = new DataTuple();
for (int i = 0; i < reader.FieldCount; i++)
{
data.Add(reader.GetName(i), reader.GetValue(i).ToString());
}
tuples.Add(data);
}
return(tuples);
}
}