public IEnumerable <Result> GetRange(int idStart, int idEnd)
{
//Подключение к БД.
SqlDataReader reader = null;
SqlConnection myConnection = new SqlConnection();
myConnection.ConnectionString = connectionString;
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandType = CommandType.Text;
//Команда выборки.
sqlCmd.CommandText = "SELECT * FROM Workers WHERE WorkerID BETWEEN " + idStart.ToString() + " AND " + idEnd.ToString();
sqlCmd.Connection = myConnection;
myConnection.Open();
reader = sqlCmd.ExecuteReader();
List <Result> result = new List <Result> {
};
while (reader.Read())
{
if (String.Compare(reader.GetValue(3).ToString(), "time", true) == 0)
{
//Расчет средней зарплаты у рабочих с повременной ставкой
TimePayWorker tp = new TimePayWorker(Convert.ToDecimal(reader.GetValue(4)));
decimal f = tp.AveragePay();
//Запись в коллекцию.
result.Add(new Result(Convert.ToInt32(reader.GetValue(0)),
reader.GetValue(1).ToString(),
reader.GetValue(2).ToString(),
reader.GetValue(3).ToString(),
f.ToString()));
}
else
{
result.Add(new Result(Convert.ToInt32(reader.GetValue(0)),
reader.GetValue(1).ToString(),
reader.GetValue(2).ToString(),
reader.GetValue(3).ToString(),
Convert.ToDecimal(reader.GetValue(4)).ToString()));
}
//Сортировка коллекции по заданному критерию
result.Sort(delegate(Result result1, Result result2)
{
int l = result2.Sum.CompareTo(result1.Sum);
if (l == 0)
{
l = result1.WorkerName.CompareTo(result2.WorkerName);
}
return(l);
});
}
return(result);
}
public IEnumerable <string> GetSum()
{
SqlDataReader reader = null;
SqlConnection myConnection = new SqlConnection();
myConnection.ConnectionString = connectionString;
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandType = CommandType.Text;
sqlCmd.CommandText = "SELECT * FROM Workers";
sqlCmd.Connection = myConnection;
myConnection.Open();
reader = sqlCmd.ExecuteReader();
decimal sum = 0.00m;
while (reader.Read())
{
if (String.Compare(reader.GetValue(3).ToString(), "time", true) == 0)
{
TimePayWorker tp = new TimePayWorker(Convert.ToDecimal(reader.GetValue(4)));
decimal t = tp.AveragePay();
sum = sum + t;
}
else
{
sum = sum + Convert.ToDecimal(reader.GetValue(4));
}
}
return(new string[] { sum.ToString() });
}
public IEnumerable <Result> GetWorkers()
{
SqlDataReader reader = null;
SqlConnection myConnection = new SqlConnection();
myConnection.ConnectionString = connectionString;
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandType = CommandType.Text;
sqlCmd.CommandText = "SELECT * FROM Workers";
sqlCmd.Connection = myConnection;
myConnection.Open();
reader = sqlCmd.ExecuteReader();
List <Result> result = new List <Result> {
};
while (reader.Read())
{
if (String.Compare(reader.GetValue(3).ToString(), "time", true) == 0)
{
//Расчет средней зарплаты у рабочих с повременной ставкой
TimePayWorker tp = new TimePayWorker(Convert.ToDecimal(reader.GetValue(4)));
decimal f = tp.AveragePay();
//Запись в коллекцию.
result.Add(new Result(Convert.ToInt32(reader.GetValue(0)),
reader.GetValue(1).ToString(),
reader.GetValue(2).ToString(),
reader.GetValue(3).ToString(),
f.ToString()));
}
else
{
result.Add(new Result(Convert.ToInt32(reader.GetValue(0)),
reader.GetValue(1).ToString(),
reader.GetValue(2).ToString(),
reader.GetValue(3).ToString(),
Convert.ToDecimal(reader.GetValue(4)).ToString()));
}
//Сортировка коллекции по заданному критерию
result.Sort(delegate(Result result1, Result result2)
{
int l = result2.Sum.CompareTo(result1.Sum);
if (l == 0)
{
l = result1.WorkerName.CompareTo(result2.WorkerName);
}
return(l);
});
}
//Сохранение 3х самых высокооплачиваемых работников к XML файл.
//Файл разсположен в каталоге программы.
Result[] workers = new Result[] { result[0], result[1], result[2] };
XmlSerializer ser = new XmlSerializer(typeof(Result[]));
FileStream file = new FileStream("Workers.xml", FileMode.Create, FileAccess.Write, FileShare.None);
ser.Serialize(file, workers);
file.Close();
return(result);
}
