Guys, how to do the search itself, I know the queries and all that. Help me, is it possible to write a beautiful condition if there are several types of search? or I’ll have to, as I started, this is somewhat (very much) else if.

//По году else if (numericUpDownMonth.Value == 0 && textBoxArticle.Text.Equals("") && textBoxFIOSuspect.Text.Equals("")) { conn.Open(); SqlCommand cmd = new SqlCommand(string.Format("Select ID AS 'П/н', NumberCriminalCases AS '№ у/д' , Article AS 'Статья', DateIntination AS 'Дата возбуждения у/д', Subdivision AS 'Подразделение', FIOEmployee AS 'ФИО Сотрудника', NumberCRB AS '№ КУСП', DateCRB AS 'Дата КУСП', Fabula AS 'Фабула', FIOSuspect AS 'ФИО подозреваемого', LimitationDate AS 'Срок' From dbo.ListOfCriminalCases Where YEAR(DateCRB) = {0}", numericUpDownYear.Value), conn); DataTable dt = new DataTable(); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(dt); dgvArchive.DataSource = dt; conn.Close(); numericUpDownMonth.Value = 0; tabControlSearch.Visible = false; } //По месяцу else if (numericUpDownYear.Value == 0 && textBoxArticle.Text.Equals("") && textBoxFIOSuspect.Text.Equals("")) { conn.Open(); SqlCommand cmd = new SqlCommand(string.Format("Select ID AS 'П/н', NumberCriminalCases AS '№ у/д' , Article AS 'Статья', DateIntination AS 'Дата возбуждения у/д', Subdivision AS 'Подразделение', FIOEmployee AS 'ФИО Сотрудника', NumberCRB AS '№ КУСП', DateCRB AS 'Дата КУСП', Fabula AS 'Фабула', FIOSuspect AS 'ФИО подозреваемого', LimitationDate AS 'Срок' From dbo.ListOfCriminalCases Where Month(DateCRB) = {0}",numericUpDownMonth.Value), conn); DataTable dt = new DataTable(); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(dt); dgvArchive.DataSource = dt; conn.Close(); numericUpDownMonth.Value = 0; tabControlSearch.Visible = false; } //По году и месяцу else if(textBoxArticle.Text.Equals("") && textBoxFIOSuspect.Text.Equals("")) { conn.Open(); SqlCommand cmd = new SqlCommand(string.Format("Select ID AS 'П/н', NumberCriminalCases AS '№ у/д' , Article AS 'Статья', DateIntination AS 'Дата возбуждения у/д', Subdivision AS 'Подразделение', FIOEmployee AS 'ФИО Сотрудника', NumberCRB AS '№ КУСП', DateCRB AS 'Дата КУСП', Fabula AS 'Фабула', FIOSuspect AS 'ФИО подозреваемого', LimitationDate AS 'Срок' From dbo.ListOfCriminalCases Where YEAR(DateCRB) = {0} and Month(DateCRB) = {1}", numericUpDownYear.Value, numericUpDownMonth.Value), conn); DataTable dt = new DataTable(); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(dt); dgvArchive.DataSource = dt; conn.Close(); numericUpDownMonth.Value = 0; tabControlSearch.Visible = false; } else if (textBoxFIOSuspect.Text.Equals("")) { conn.Open(); SqlCommand cmd = new SqlCommand(string.Format("Select ID AS 'П/н', NumberCriminalCases AS '№ у/д' , Article AS 'Статья', DateIntination AS 'Дата возбуждения у/д', Subdivision AS 'Подразделение', FIOEmployee AS 'ФИО Сотрудника', NumberCRB AS '№ КУСП', DateCRB AS 'Дата КУСП', Fabula AS 'Фабула', FIOSuspect AS 'ФИО подозреваемого', LimitationDate AS 'Срок' From dbo.ListOfCriminalCases Where YEAR(DateCRB) = {0} and Month(DateCRB) = {1} and Article = '{2}'", numericUpDownYear.Value, numericUpDownMonth.Value,textBoxArticle.Text), conn); DataTable dt = new DataTable(); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(dt); dgvArchive.DataSource = dt; conn.Close(); numericUpDownMonth.Value = 0; tabControlSearch.Visible = false; }

This is not the end, you have to write a bunch of conditions to empty this or that value.

Example I will try to describe what should be the types of search

  1. Search by year
  2. Search by month only
  3. Year and Month
  4. Year and Article
  5. Year and name
  6. Month and Article
  7. Month and name
  8. Year, month and name
  9. Year month and Article

I do not even know if I listed everything.

I hope for bright ideas.

Or am I doing the right thing or not?

You can throw your options as you would have done, maybe I will redo it.

    1 answer 1

    All your queries have a common part and it can be put into a separate variable.

    Permanent part of the request:

     Select ID AS 'П/н', NumberCriminalCases AS '№ у/д', Article AS 'Статья', DateIntination AS 'Дата возбуждения у/д', Subdivision AS 'Подразделение', FIOEmployee AS 'ФИО Сотрудника', NumberCRB AS '№ КУСП', DateCRB AS 'Дата КУСП', Fabula AS 'Фабула', FIOSuspect AS 'ФИО подозреваемого', LimitationDate AS 'Срок' From dbo.ListOfCriminalCases Where 1 = 1

    The variable part of the query for the year:

     and YEAR(DateCRB) = {0}

    Variable part of the query for the month:

     and Month(DateCRB) = {0}

    For the year and month at the same time:

     and YEAR(DateCRB) = {0} and Month(DateCRB) = {1}

    And so on.

    Adding the tautological condition 1 = 1 allows you to attach any variable parts and get rid of large pieces of duplicate code. You have a more compact switch ... case.

    PS I always forget how this technique in SQL queries is cleverly called, can someone tell me?

    PPS And more. Inside each if you have the same type of code pieces - and this means that they can be taken out of the if and only write once, and inside if there will be only cmd = . Well, after that, replace if with switch.

    Sample for example:

     string query = "select ... where 1 = 1"; if(x == 1) { query += "and year = 2017"; execute(query); } if(x == 2) { query += "and month = 01"; execute(query); }

    can be replaced by:

     string query = "select ... where 1 = 1"; if(x == 1) { query += "and year = 2017"; } if(x == 2) { query += "and month = 01"; } execute(query);

    Is the logic clear? You always have if inside conn.Open() - why do you need to constantly repeat this line, if you could once BEFORE if to write?

    • Tipo instead of the entire query to enter a variable in which most of the query will be described? - ne0n
    • It is possible by more detailed replacement of if by switch. Primerchik. - ne0n
    • What the x ? I sort of understood, but I’m not getting rid of a large number of if's? - ne0n
    • @ ne0n replace if with switch. Inside each case, leave only the choice of the variable part of the SQL query string. Everything else is easy to take out after the operator. - AK ♦
    • Thanks for the ideas. LIKE - ne0n