C sharp

F D

Hello All,

I have a nested if and else statement that I would like to make short and simple but perform the same way. Any ideas will be greatly appreciated. I'm open to any or all suggestions.

Happy New Year.

Thanks.

private void searchButton_Click(object sender, EventArgs e)

{

if (searchComboBox.Text == "ID")

{

try

{

da.SelectCommand = new MySqlCommand("SELECT * FROM tblcontacts WHERE ID = '" + searchentryTextBox.Text + "'; ", conn);

ds.Clear();

idTextBox.DataBindings.Clear();

fnameTextBox.DataBindings.Clear();

lnameTextBox.DataBindings.Clear();

phoneTextBox.DataBindings.Clear();

na1TextBox.DataBindings.Clear();

na2TextBox.DataBindings.Clear();

na3TextBox.DataBindings.Clear();

da.Fill(ds);

bs.DataSource = ds.Tables[0];

idTextBox.DataBindings.Add(new Binding("Text", bs, "ID"));

fnameTextBox.DataBindings.Add(new Binding("Text", bs, "fname"));

lnameTextBox.DataBindings.Add(new Binding("Text", bs, "lname"));

phoneTextBox.DataBindings.Add(new Binding("Text", bs, "phone"));

na1TextBox.DataBindings.Add(new Binding("Text", bs, "na1"));

na2TextBox.DataBindings.Add(new Binding("Text", bs, "na2"));

na3TextBox.DataBindings.Add(new Binding("Text", bs, "na3"));

}

catch (Exception ex)

{

MessageBox.Show("Error [" + ex.Message + "]");

}

finally

{

conn.Close();

conn.Dispose();

}

else

{

if (searchComboBox.Text == "First Name")

{

try

{

da.SelectCommand = new MySqlCommand("SELECT * FROM tblcontacts WHERE FNAME = '" + searchentryTextBox.Text + "'; ", conn);

ds.Clear();

idTextBox.DataBindings.Clear();

fnameTextBox.DataBindings.Clear();

lnameTextBox.DataBindings.Clear();

phoneTextBox.DataBindings.Clear();

na1TextBox.DataBindings.Clear();

na2TextBox.DataBindings.Clear();

na3TextBox.DataBindings.Clear();

da.Fill(ds);

bs.DataSource = ds.Tables[0];

idTextBox.DataBindings.Add(new Binding("Text", bs, "ID"));

fnameTextBox.DataBindings.Add(new Binding("Text", bs, "fname"));

lnameTextBox.DataBindings.Add(new Binding("Text", bs, "lname"));

phoneTextBox.DataBindings.Add(new Binding("Text", bs, "phone"));

na1TextBox.DataBindings.Add(new Binding("Text", bs, "na1"));

na2TextBox.DataBindings.Add(new Binding("Text", bs, "na2"));

na3TextBox.DataBindings.Add(new Binding("Text", bs, "na3"));

}

catch (Exception ex)

{

MessageBox.Show("Error [" + ex.Message + "]");

}

finally

{

conn.Close();

conn.Dispose();

}

else

{

if (searchComboBox.Text == "Last Name")

{

try

{

da.SelectCommand = new MySqlCommand("SELECT * FROM tblcontacts WHERE LNAME = '" + searchentryTextBox.Text + "'; ", conn);

ds.Clear();

idTextBox.DataBindings.Clear();

fnameTextBox.DataBindings.Clear();

lnameTextBox.DataBindings.Clear();

phoneTextBox.DataBindings.Clear();

na1TextBox.DataBindings.Clear();

na2TextBox.DataBindings.Clear();

na3TextBox.DataBindings.Clear();

da.Fill(ds);

bs.DataSource = ds.Tables[0];

idTextBox.DataBindings.Add(new Binding("Text", bs, "ID"));

fnameTextBox.DataBindings.Add(new Binding("Text", bs, "fname"));

lnameTextBox.DataBindings.Add(new Binding("Text", bs, "lname"));

phoneTextBox.DataBindings.Add(new Binding("Text", bs, "phone"));

na1TextBox.DataBindings.Add(new Binding("Text", bs, "na1"));

na2TextBox.DataBindings.Add(new Binding("Text", bs, "na2"));

na3TextBox.DataBindings.Add(new Binding("Text", bs, "na3"));

}

catch (Exception ex)

{

MessageBox.Show("Error [" + ex.Message + "]");

}

finally

{

conn.Close();

conn.Dispose();

}

else

{

if (searchComboBox.Text == "Phone")

{

try

{

da.SelectCommand = new MySqlCommand("SELECT * FROM tblcontacts WHERE PHONE = '" + searchphoneTextBox.Text + "'; ", conn);

ds.Clear();

idTextBox.DataBindings.Clear();

fnameTextBox.DataBindings.Clear();

lnameTextBox.DataBindings.Clear();

phoneTextBox.DataBindings.Clear();

na1TextBox.DataBindings.Clear();

na2TextBox.DataBindings.Clear();

na3TextBox.DataBindings.Clear();

da.Fill(ds);

bs.DataSource = ds.Tables[0];

idTextBox.DataBindings.Add(new Binding("Text", bs, "ID"));

fnameTextBox.DataBindings.Add(new Binding("Text", bs, "fname"));

lnameTextBox.DataBindings.Add(new Binding("Text", bs, "lname"));

phoneTextBox.DataBindings.Add(new Binding("Text", bs, "phone"));

na1TextBox.DataBindings.Add(new Binding("Text", bs, "na1"));

na2TextBox.DataBindings.Add(new Binding("Text", bs, "na2"));

na3TextBox.DataBindings.Add(new Binding("Text", bs, "na3"));

}

catch (Exception ex)

{

MessageBox.Show("Error [" + ex.Message + "]");

}

finally

{

conn.Close();

conn.Dispose();

}

else

{

MessageBox.Show("Please fill in the search fields.");

}

9 Replies

Know the answer? Post it — somebody with the same question will find it here.

theLizard15y ago
Accepted answer
To be honest I have never needed to use stored procedures, I have always created my sql statements dynamically with each field value being filtered for bad words and processed for the fields data type.

Unless you are in a situation where stored procedures is the only way because of network traffic or other factors which would create bottle necks on the network then sending an sql with field / values is just as quick as sending an sql to run a stored procedure.

But yes it is good to learn about them though, but also learn when to use them and when not to use them, this should come with experience.

Any one can write programs but few can write them with new methods for old solutions, this should be your goal, find new and better ways to solve what has already been done.

Also by using data adapters and data bound controls you are limiting your knowledge of the underlying methods and procedures for getting data from databases, by all means use these controls but if you want to be really good at programming learn how to create these controls, by doing this you will be able to understand how data bound controls function.

Think outside the square...

0
Sam Hobbs15y ago
I am sorry I did not jump in earlier. In response to the switch statement you posted on the 10th, I think what I would do is to create the ComboBox in a way that eliminates the need for a switch. The items of a ComboBox can be objects that have more in them than just what is seen in the ComboBox; in other words, not just string objects. You can have other members of the objects so for example if the ComboBox item shows First Name then the object has another member that has FNAME in it. Look for articles about ComboBox for details. I think you will impress yourself by how much simpler you can make the code.
0
F D15y ago
Hello theLizard,
Great stuff thanks a bunch.
This is some really good information. I wanted to use stored procedures because I was reading up on the sql injection attacks. I don't need the stored procedure but from my reading it is a secure way from preventing attacks, but I am unsure on how to write the stored procedure. I am a beginner in SQL. Any ideas on the stored procedure? I'm teaching myself all this little by little and want to program in this way. I also appreciate the info on the error in the try & catch statement. I would have never known about that.
All in all thanks very much for your input.
0
theLizard15y ago
         switch (searchComboBox.Text)
         {
               case "ID"://Sql query to retrieve all records from database.
                             da.SelectCommand = new MySqlCommand("SELECT * FROM tblcontacts WHERE ID = '" + searchentryTextBox.Text + "'; ", conn);
                             tbltxtFunctions();
                             break;
               case "First Name"://Sql query to retrieve all records from database.
                                        da.SelectCommand = new MySqlCommand("SELECT * FROM tblcontacts WHERE FNAME = '" + searchentryTextBox.Text + "'; ", conn);
                                        tbltxtFunctions();
                                        break;
               case "Last Name"://Sql query to retrieve all records from database.
                                        da.SelectCommand = new MySqlCommand("SELECT * FROM tblcontacts WHERE LNAME = '" + searchentryTextBox.Text + "'; ", conn);
                                        tbltxtFunctions();
                                        break;
               case "Phone"://Sql query to retrieve all records from database.
                                  da.SelectCommand = new MySqlCommand("SELECT * FROM tblcontacts WHERE PHONE = '" + searchphoneTextBox.Text + "'; ", conn);
                                  tbltxtFunctions();
                                  break;
               default:
                                 MessageBox.Show("Please fill in the search fields.");
                                 break;
         }

//

all above can be done this way...

string field = null;

         switch (searchComboBox.Text)
         {
           case "ID"://Sql query to retrieve all records from database.
               field = "id";
                  break;
           case "First Name"://Sql query to retrieve all records from database.
            field = "fname";
                break;
          case "Last Name"://Sql query to retrieve all ecords from database.
              field = "lname";
                break;
           case "Phone"://Sql query to retrieve all records from database.
                field = "phone";
                 break;
             default:
                                 MessageBox.Show("Please fill in the search fields.");
                                 break;
         }

if(field != null)
   {
   da.SelectCommand = new MySqlCommand("SELECT * FROM tblcontacts WHERE " + field + " = '" + searchentryTextBox.Text + "'; ", conn);
   tbltxtFunctions();
}
else
return;

you may also want to parametrize your searchentryTextBox value to stop sql injection attacks,

There are for's and against for stored procedures do you really need them?

Never give user the real error message in a try catch as you have done, this gives potential hackers an insight into your table structures in database systems.

0
F D15y ago

This is how I made the code much simpler. Please let me know what you think. I was also considering converting the SQL statements into STORED PROCEDURES(Would that be a good idea?).
Thanks again!

private void searchButton_Click(object sender, EventArgs e)
{
   try
   {
         switch (searchComboBox.Text)
         {
               case "ID"://Sql query to retrieve all records from database.
                             da.SelectCommand = new MySqlCommand("SELECT * FROM tblcontacts WHERE ID = '" + searchentryTextBox.Text + "'; ", conn);
                             tbltxtFunctions();
                             break;
               case "First Name"://Sql query to retrieve all records from database.
                                        da.SelectCommand = new MySqlCommand("SELECT * FROM tblcontacts WHERE FNAME = '" + searchentryTextBox.Text + "'; ", conn);
                                        tbltxtFunctions();
                                        break;
               case "Last Name"://Sql query to retrieve all records from database.
                                        da.SelectCommand = new MySqlCommand("SELECT * FROM tblcontacts WHERE LNAME = '" + searchentryTextBox.Text + "'; ", conn);
                                        tbltxtFunctions();
                                        break;
               case "Phone"://Sql query to retrieve all records from database.
                                  da.SelectCommand = new MySqlCommand("SELECT * FROM tblcontacts WHERE PHONE = '" + searchphoneTextBox.Text + "'; ", conn);
                                  tbltxtFunctions();
                                  break;
               default:
                                 MessageBox.Show("Please fill in the search fields.");
                                 break;
         }
}
catch (Exception ex)
{
MessageBox.Show("An error has occured. " + ex.Message);
}
finally
{
conn.Close();
conn.Dispose();
}
}
0
Sam Hobbs15y ago
Whatever I would use to analyze the data, I would never make the code that big without putting the code into separate functions.
0
thiago costa15y ago
you guys are hardcore... lol ! ...

Back up your brains and upload the file :D
0
F D15y ago
It hit me 10 minutes later to use a switch.

Thanks for your help it is much appreciated.
0
Roy S15y ago
You could use a switch
switch (searchComboBox.Text)
{
case "ID":
    //...
    break;
case "First Name":
    //...
    break;
//...

default:
    MessageBox.Show("Please fill in the search fields.");
}
0