title: c# filter function with sql and parameters
body: <p>I've used code I've previously done in VB two years back, converted almost all of it to work in c# environment though I've hit a brick wall with the last part as i'm not sure how to approach it.</p>
  
  <p>Previous VB code</p>
  
  <pre><code>If Not binGotOne Then
  strSQL = Mid$(strSQL, 1, InStr(strSQL, "WHERE") - 1)
  End If
  </code></pre>
  
  <p>Current C# code</p>
  
  <pre><code>            /* This section I belive is substrings though I'm not sure,
               currently I can't get it to work as I'm not sure how to apporach it*/
              if (!filter)
              {
                  query = (query, 1,(query, "WHERE") - 1);
              }
  </code></pre>
  
  <p>The c# section is last part of full function shown below which I can't seem to get my head around.</p>
  
  <pre><code>        SqlConnection connection = new SqlConnection();
          Security security = new Security();
  
          try
          {
              connection.ConnectionString = connectionPath;
              connection.Open();
  
              Boolean filter = false;
              string query = string.Format("SELECT * FROM Staff WHERE ");
  
              if (txtstaffid.Text != null)
              {
                  filter = true;
                  query = query + "Staff_StaffId = " + txtstaffid.Text + "'";
              }
              else if (cbotitle.Text != null)
              {
                  filter = true;
                  query = query + "Staff_Title = '" + cbotitle.Text + "";
              }
              else if (cborole.Text != null)
              {
                  filter = true;
                  query = query + "Staff_Role = '" + cborole.Text + "'";
              }
              else if (txtfname.Text != null)
              {
                  filter = true;
                  query = query + "Staff_Firstname = '" + txtfname.Text + "'";
              }
              else if (txtsname.Text != null)
              {
                  filter = true;
                  query = query + "Staff_Surname = '" + txtsname.Text + "'";
              }
              else if (txtpostcode.Text != null)
              {
                  filter = true;
                  query = query + "Staff_Postcode = '" + txtpostcode.Text + "'";
              }
              else if (txtemail.Text != null)
              {
                  filter = true;
                  query = query + "Staff_Email = '" + txtemail.Text + "'";
              }
  
              /* This section I belive is substrings though I'm not sure,
               currently I can't get it to work as I'm not sure how to apporach it*/
              if (!filter)
              {
                  query = (query, 1, (query, "WHERE") - 1);
              }
  
              SqlCommand cmd = new SqlCommand(query, connection);
              SqlDataAdapter dap = new SqlDataAdapter(cmd);
              DataSet ds = new DataSet();
              dap.Fill(ds);
              BindingSource bs = new BindingSource();
              bs.DataSource = ds.Tables[0];
              dgv.DataSource = bs;
              dap.Update(ds);
          }
          catch (SqlException sql)
          {
              MessageBox.Show(sql.Message);
          }
          finally
          {
              connection.Close();
              connection.Dispose();
          }
  </code></pre>