I have a form on the client side with two drop-down menus, he chooses a year and a type of cover there.

enter image description here

In the controller, I collect this data. And from this I make a query into the table. It is logical that if nothing is selected, then I load all the data.

string sql_count = string.Format("SELECT count(*) FROM labirint"); string sql_pages = string.Format(" ORDER BY id ASC LIMIT "+ skip +", 10"); string sql_all = string.Format("SELECT * FROM labirint"); string sql_result = string.Format(sql_all + sql_pages); var v = dc.Database.SqlQuery<labirint>(sql_result).AsQueryable(); recordsTotal = dc.Database.SqlQuery<int>(sql_count).First(); var data = v.ToList(); dc.Dispose(); return Json(new { draw = draw, recordsFiltered = recordsTotal, recordsTotal = recordsTotal, data = data }, JsonRequestBehavior.AllowGet);

This is how it looks when the table is loaded by default. Now when they choose a year. I'm doing a string splice.

  if (!string.IsNullOrEmpty(year)) { int year_int = int.Parse(year); string sql_year = string.Format(" WHERE year = " + year_int + ""); sql_result = string.Format(sql_all + sql_year + sql_pages); sql_count = string.Format(sql_count + sql_year); }

The result is like this

 string sql_count = string.Format("SELECT count(*) FROM labirint"); string sql_pages = string.Format(" ORDER BY id ASC LIMIT "+ skip +", 10"); string sql_all = string.Format("SELECT * FROM labirint"); string sql_result = string.Format(sql_all + sql_pages); if (!string.IsNullOrEmpty(year)) { int year_int = int.Parse(year); string sql_year = string.Format(" WHERE year = " + year_int + ""); sql_result = string.Format(sql_all + sql_year + sql_pages); sql_count = string.Format(sql_count + sql_year); } var v = dc.Database.SqlQuery<labirint>(sql_result).AsQueryable(); recordsTotal = dc.Database.SqlQuery<int>(sql_count).First(); var data = v.ToList(); dc.Dispose(); return Json(new { draw = draw, recordsFiltered = recordsTotal, recordsTotal = recordsTotal, data = data }, JsonRequestBehavior.AllowGet);

Everything works great. But most likely I wrote it all crooked and not beautiful. The problems start when I want to sort by cover. I chose for example the year 2013 and I want to select all the books with a hard cover or vice versa. How can I do it? Perhaps there will be a third field with dropbox.

  • Sorry, but you do not have a clearly formulated question. But there is only a visually shaped process of internal self-criticism and reflection. - Bulson
  • @Bulson I chose for example the year 2013 and I want to select all the books with a hard cover or vice versa. How can I do this? - shatoidil
  • @Bulson That's the question. where does self-criticism? - shatoidil
  • If you want flexibility, it is better to use Linq in conjunction with the Entity Framework. And stop, dc is DbContext ? - koks_rs
  • one
    Sewing parameters into the sql query is a very bad practice, on the one hand, it degrades performance, since each such query will have to be analyzed again by the server (parameterized sql queries can be taken from the hash), secondly, it allows sql injections. Those. instead of WHERE year = "+ year_int +" "it's better to make SqlQuery (" select * from labirint WHERE year = @year ", new SqlParameter (" @ year ", year_int)) - Primus Singularis

2 answers 2

  var where = new List<string>(); var query_params = new List<SqlParameter>(); var limit_sql = "LIMIT @skip, 10"; query_params.Add(new SqlParameter("@skip", int.Parse(skip))); if (!string.IsNullOrEmpty(year)) { where.Add("year = @year"); query_params.Add(new SqlParameter("@year", int.Parse(year))); } if(условие) { where.Add(...); query_params.Add(...); } var whereSql = where.Any() ? " WHERE " + string.Join(" AND ", where) : ""; var sql_result = select_sql + where_sql + order_sql + limit_sql; var data = dc.Database.SqlQuery<labirint>(sql_result, query_params.ToArray()).ToList();

    After WHERE year = " + year_int + "" should be AND oblojka = 'tverdaya' . But it's better not to write SQL queries yourself, use ORM or, if it's too slow for you, at least DbExtensions or something lightweight Dapper

    • one
      Dapper does not generate queries, it only mapping does. It is worth writing requests, especially if productivity is necessary, ORM does not solve all tasks. - Primus Singularis
    • then the stored procedure. Because if you form queries on the mind, most likely, everything will be reduced to forming SQL lines based on expression trees. Take an example in question. In the form of three filtering fields and already, the code, to put it mildly, not very. You can adjust with concatenation of strings, you can forget the shielding of values, and if the joins go, write it is gone. - osharper