public List <GetAnimalsResponse> getAnimals(string[] parameters)
{
SqlDataReader dr;
var list = new List <GetAnimalsResponse>();
try
{
using (var con = new SqlConnection(ConString))
using (var com = new SqlCommand())
{
com.Connection = con;
con.Open();
if (parameters == null)
{
com.CommandText = "SELECT Name, Type, AdmissionDate, LastName FROM Animal JOIN Owner ON Animal.IdOwner = Owner.IdOwner ORDER BY AdmissionDate DESC;";
}
else
{
com.CommandText = "SELECT Name, Type, AdmissionDate, LastName FROM Animal JOIN Owner ON Animal.IdOwner = Owner.IdOwner ORDER BY " + parameters[0] + " " + parameters[1] + ";";
}
dr = com.ExecuteReader();
while (dr.Read())
{
var animal = new GetAnimalsResponse();
animal.Name = dr["Name"].ToString();
animal.AnimalType = dr["Type"].ToString();
animal.DateOfAdmission = DateTime.Parse(dr["AdmissionDate"].ToString());
animal.LastNameOfOwner = dr["LastName"].ToString();
list.Add(animal);
}
return(list);
}
}
catch (SqlException exc)
{
throw new SqlServerException(exc.Message);
}
}
public GetAnimalsResponse GetAnimals(string orderBy)
{
GetAnimalsResponse response;
if (orderBy == null)
{
orderBy = "AdmissionDate";
}
using (var connection = new SqlConnection(connectionString))
using (var command = new SqlCommand())
{
command.Connection = connection;
command.CommandText = $"SELECT Name, Type, AdmissionDate, OWNER.LastName FROM ANIMAL,OWNER where ANIMAL.IdOwner=OWNER.IdOwner ORDER BY {orderBy}";
//command.Parameters.AddWithValue("orderBy", orderBy);
connection.Open();
using (var reader = command.ExecuteReader())
{
if (!reader.HasRows)
{
throw new Exception("Error brak danych");
}
while (reader.Read())
{
response = new GetAnimalsResponse
{
Name = reader["Name"].ToString(),
AnimalType = reader["Type"].ToString(),
DateOfAdmission = DateTime.Parse(reader["AdmissionDate"].ToString()),
LastNameOfOwner = reader["LastName"].ToString()
};
}
}
}
return(response);
}
public GetAnimalsResponse GetAnimals(string orderBy)
{
GetAnimalsResponse response;
if (orderBy == null)
{
orderBy = "DateOfAdmission";
}
using (var connection = new SqlConnection(_connectionString))
using (var command = new SqlCommand())
{
connection.Open();
command.Connection = connection;
command.CommandText = "select a.Name, a.AnimalType, a.DateOfAdmission, o.LastNameOfOwner "
+ "from Animal a inner join Owner o on o.IdOwner = a.IdOwner "
+ "order by @orderBy";
command.Parameters.AddWithValue("orderBy", orderBy);
using (var reader = command.ExecuteReader())
{
if (!reader.HasRows)
{
throw new Exception($"Some error occured");
}
response = new GetAnimalsResponse
{
Name = reader["Name"].ToString(),
AnimalType = reader["AnimalType"].ToString(),
DateOfAdmission = DateTime.Parse(reader["DateOfAdmission"].ToString()),
LastNameOfOwner = reader["LastNameOfOwner"].ToString()
};
}
}
return(response);
}
