Shorten my ElseIf Statement. How?

Question

I have something here that I want to know, I hope everyone can help me with this. So here's my question, how can I shorten this code of mine. :)

If MetroTextBox1.Text = "" Then
            If MetroCheckBox1.Checked = True And MetroCheckBox2.Checked = False Then
                query = "select id, fname, mname, lname, dept, salary, ' ' as lengthservice from tbl_Employee where dresigned is null and " +
                        " (id like '%" & MetroTextBox1.Text & "%' or fname like '%" & MetroTextBox1.Text & "%' or mname like '%" & MetroTextBox1.Text & "%' or lname like '%" & MetroTextBox1.Text & "%' or dept like '%" & MetroTextBox1.Text & "%') "
                dt = c.GetDataTable(query)
            ElseIf MetroCheckBox2.Checked = True And MetroCheckBox1.Checked = False Then
                query = "select id, fname, mname, lname, dept, salary, ' ' as lengthservice from tbl_Employee where dresigned is not null and " +
                        " (id like '%" & MetroTextBox1.Text & "%' or fname like '%" & MetroTextBox1.Text & "%' or mname like '%" & MetroTextBox1.Text & "%' or lname like '%" & MetroTextBox1.Text & "%' or dept like '%" & MetroTextBox1.Text & "%') "
                dt = c.GetDataTable(query)
            ElseIf MetroCheckBox1.Checked = True And MetroCheckBox2.Checked = True Then
                query = "select id, fname, mname, lname, dept, salary from tbl_Employee" +
                        " where id like '%" & MetroTextBox1.Text & "%' or fname like '%" & MetroTextBox1.Text & "%' or mname like '%" & MetroTextBox1.Text & "%' or lname like '%" & MetroTextBox1.Text & "%' or dept like '%" & MetroTextBox1.Text & "%' "
                dt = c.GetDataTable(query)
            End If
        ElseIf MetroTextBox1.Text <> "" Then
            If MetroCheckBox1.Checked = True And MetroCheckBox2.Checked = False Then
                query = "select id, fname, mname, lname, dept, salary, ' ' as lengthservice from tbl_Employee where dresigned is null and " +
                        " (id like '%" & MetroTextBox1.Text & "%' or fname like '%" & MetroTextBox1.Text & "%' or mname like '%" & MetroTextBox1.Text & "%' or lname like '%" & MetroTextBox1.Text & "%' or dept like '%" & MetroTextBox1.Text & "%') "
                dt = c.GetDataTable(query)
            ElseIf MetroCheckBox2.Checked = True And MetroCheckBox1.Checked = False Then
                query = "select id, fname, mname, lname, dept, salary, ' ' as lengthservice from tbl_Employee where dresigned is not null and " +
                        " (id like '%" & MetroTextBox1.Text & "%' or fname like '%" & MetroTextBox1.Text & "%' or mname like '%" & MetroTextBox1.Text & "%' or lname like '%" & MetroTextBox1.Text & "%' or dept like '%" & MetroTextBox1.Text & "%') "
                dt = c.GetDataTable(query)
            ElseIf MetroCheckBox1.Checked = True And MetroCheckBox2.Checked = True Then
                query = "select id, fname, mname, lname, dept, salary from tbl_Employee" +
                        " where id like '%" & MetroTextBox1.Text & "%' or fname like '%" & MetroTextBox1.Text & "%' or mname like '%" & MetroTextBox1.Text & "%' or lname like '%" & MetroTextBox1.Text & "%' or dept like '%" & MetroTextBox1.Text & "%' "
                dt = c.GetDataTable(query)
            End If
        Else
            query = "select id, fname, mname, lname, dept, salary, ' ' as lengthservice from tbl_Employee where id = ''"
            dt = c.GetDataTable(query)
        End If

your code seems messy! i saw in the first `if`, you have: `If MetroTextBox1.Text = "" Then` but later in related code block, you use its value: `If MetroCheckBox1.Checked = True And MetroCheckBox2.Checked = False Then query = "select id, fname, ... " + " (id like '%" & **MetroTextBox1.Text** & ...` which dose not seems to be correct! — S.Serpooshan, Dec 31 '16 at 10:37
What is `c` in your code above? I was trying to write a parameterised version to avoid the vulnerability to SQL injection caused by passing the `MetroTextBox1.Text` in without any validation — Parrybird, Dec 31 '16 at 12:10
You should use `AndAlso`/`OrElse` instead of `And`/`Or`. [Why?](http://stackoverflow.com/q/302047/4934172). — 41686d6564 stands w. Palestine, Dec 31 '16 at 22:33

score 0 · Accepted Answer · answered Dec 31 '16 at 10:17

Since the question is about shortening the code, here's how:

query = " (id like '%" & MetroTextBox1.Text & "%' or fname like '%" & MetroTextBox1.Text & "%' or mname like '%" & MetroTextBox1.Text & "%' or lname like '%" & MetroTextBox1.Text & "%' or dept like '%" & MetroTextBox1.Text & "%')"
If MetroTextBox1.Text = "" Then
    If MetroCheckBox1.Checked = True And MetroCheckBox2.Checked = False Then
        query = "select id, fname, mname, lname, dept, salary, ' ' as lengthservice from tbl_Employee where dresigned is null and" + query
    ElseIf MetroCheckBox2.Checked = True And MetroCheckBox1.Checked = False Then
        query = "select id, fname, mname, lname, dept, salary, ' ' as lengthservice from tbl_Employee where dresigned is not null and" + query
    ElseIf MetroCheckBox1.Checked = True And MetroCheckBox2.Checked = True Then
        query = "select id, fname, mname, lname, dept, salary from tbl_Employee where" + query
    End If
ElseIf MetroTextBox1.Text <> "" Then
    If MetroCheckBox1.Checked = True And MetroCheckBox2.Checked = False Then
        query = "select id, fname, mname, lname, dept, salary, ' ' as lengthservice from tbl_Employee where dresigned is null and" + query
    ElseIf MetroCheckBox2.Checked = True And MetroCheckBox1.Checked = False Then
        query = "select id, fname, mname, lname, dept, salary, ' ' as lengthservice from tbl_Employee where dresigned is not null and" + query
    ElseIf MetroCheckBox1.Checked = True And MetroCheckBox2.Checked = True Then
        query = "select id, fname, mname, lname, dept, salary from tbl_Employee where" + query
    End If
Else
    query = "select id, fname, mname, lname, dept, salary, ' ' as lengthservice from tbl_Employee where id = ''"
End If
dt = c.GetDataTable(query)

S.Serpooshan · Answer 2 · 2016-12-31T12:09:44.010

This is better (and shorter) code:

Dim t1 As String = MetroTextBox1.Text
Dim c1 As Boolean = MetroCheckBox1.Checked
Dim c2 As Boolean = MetroCheckBox2.Checked

Dim a, b As String
a = "select id, fname, mname, lname, dept, salary"
b = "(id like '%" & t1 & "%' or fname like '%" & t1 & "%' or mname like '%" & t1 & "%' or lname like '%" & t1 & "%' or dept like '%" & t1 & "%') "

If c1 And Not c2 Then
    query = a + ", ' ' as lengthservice from tbl_Employee where dresigned is null and " + b

ElseIf c2 And Not c1 Then
    query = a + ", ' ' as lengthservice from tbl_Employee where dresigned is not null and " + b

Else 'If c1 And c2 Then
    query = a + " from tbl_Employee where " + b

End If

dt = c.GetDataTable(query)

(it is obvious that the final Else is never called because always one of MetroTextBox1.Text = "" or MetroTextBox1.Text <> "" will be true).

But, there is a note here. When MetroTextBox1.Text = "", the condition specified in b becomes (id like '%%' or fname like '%%' or ...), something like where 1=1 which can be removed from the query. but it is not based on your original code. if you like, i could give the modified code

Fabio · Answer 3 · 2017-01-02T14:57:14.587

Since the question is about shortening the code, here is short solution without If statement

Dim query As String = 
    "SELECT id, fname, mname, lname, dept, salary, ' ' AS lengthservice
     FROM tbl_Employee
     WHERE (id LIKE @MetroTextBox1 
         OR fname LIKE '%" & MetroTextBox1.Text & "%'
         OR mname LIKE '%" & MetroTextBox1.Text & "%' 
         OR lname LIKE '%" & MetroTextBox1.Text & "%' 
         OR dept LIKE '%" & MetroTextBox1.Text & "%')"

Dim condition As New Dictionary(Of Byte, String) From
{
    {0, ""}, 'You don't have result for case False, False
    {1, " AND dresigned IS NULL"}, 'If MetroCheckBox1.Checked = True
    {2, " AND dresigned IS NOT NULL"}, 'If MetroCheckBox2.Checked = True
    {3, ""}  'Both Checkboxes are checked
}

Dim checkBox1 As Byte = Convert.ToByte(MetroCheckBox1)
Dim checkBox2 As Byte = Convert.ToByte(MetroCheckBox2) << 1
Dim selection As Byte = checkBox1 Or checkBox2

query = query & condition(selection)

dt = c.GetDataTable(query)

And very important: Use SqlParameters for passing values to the query

Parameters will save you from Sql injection problems and also will make your queries run little bid faster.

Shorten my ElseIf Statement. How?

3 Answers3