- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
SqlConnection con = new SqlConnection(connectionString);
string sql = "SELECT idea.Id, idea.Small_text, idea.Full_text, idea.Example_text, ";
sql = sql + " ul.FullName as Avtor, ref.Name as Status, even.date_act, (select count (*) from events ev where ev.id_action=2 and ev.id_target=idea.Id and ev.type_target=1) as CountComm, (select count (*) from events ev where ev.id_action=57 and ev.id_target=idea.Id and ev.type_target=1) as Voit, (select max(ev.date_act) from events ev where ev.id_action in (1,2,4,5,7,8,9,10,58,60,61,62,63,64,65,66,67,68,69,70,71,72,74) and ev.id_target=idea.Id and ev.type_target=1) as Last_action";
sql = sql + " FROM Idea idea, UserLists ul, Refbook ref, Events even ";
sql = sql + " WHERE idea.Avtor=ul.Id and idea.Status_id = ref.Id and even.id_action=1 and even.type_target=1 and even.id_target=idea.id ";
if (id >= 0)
sql = sql + " and idea.Status_id=" + id;
if (id_avtor >= 0)
sql = sql + " and idea.Avtor=" + id_avtor;
sql = sql + " ORDER BY Last_action desc";
SqlCommand cmd = new SqlCommand(sql, con);
cmd.CommandType = CommandType.Text;
List<Idea> Ideas = new List<Idea>();
using (con)
{
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Idea idea = GetIdeaInfo(Int32.Parse(reader["Id"].ToString()));
Ideas.Add(idea);
}
reader.Close();
return Ideas;
}
Принял еще один проект. ASP.NET MVC4. Автор уехал в Новую Зеландию.
Даже на T-SQL можно писать по-мускульному.
SELECT `idea`.`Id`, `idea`.`Small_text`, `idea`.`Full_text`,` idea`.`Example_text`