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 <string> GetMetadataList(string inputQuery) { using (SqlConnection connection = DatabaseClient.GetConnection(xmlPath)) { connection.Open(); SqlCommand command = new SqlCommand(inputQuery, connection); SqlDataReader reader = command.ExecuteReader(); List <string> columns = new List <string>(); // Add column value columns.Add("1"); while (reader.Read()) { columns.Add(reader.IsDBNull(0) ? null : reader.GetString(0)); } return(columns); } }
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); } }