SQLite search using user inputs

Question

I have an app where i want to be able to search a database using multiple user inputs, i have transfer type, transfer amount between 2 figures and a date picker input, i have set it up so i am able to searched based on the transfer types fine bu then it comes to searching using 2 or all 3 of the inputs is there a quick way of setting that up or is it a case of just having loads of if statements to detect what inputs have been selected?

private void searches()
    {
        if (cb_Transfer_Type.SelectedIndex < 5)
        {
            // search using the parameters selected
            using (SQLiteCommand cmd = conn.CreateCommand())
            {
                try
                {
                    cmd.CommandText = @"SELECT * FROM tranx WHERE action = '" + cb_Transfer_Type.SelectedItem + "' AND accid = '" + Global.selectedAccountID + "';";

                    da_Transfers = new SQLiteDataAdapter(cmd.CommandText, conn);
                    dt_Transfers = new DataTable();
                    da_Transfers.Fill(dt_Transfers);
                    dgv_Transfers.DataSource = dt_Transfers;
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
        }
        if(cb_Transfer_Type.SelectedIndex == 5)
        {
            // search using the parameters selected
            using (SQLiteCommand cmd = conn.CreateCommand())
            {
                try
                {
                    cmd.CommandText = @"SELECT * FROM tranx WHERE accid = '" + Global.selectedAccountID + "';";

                    da_Transfers = new SQLiteDataAdapter(cmd.CommandText, conn);
                    dt_Transfers = new DataTable();
                    da_Transfers.Fill(dt_Transfers);
                    dgv_Transfers.DataSource = dt_Transfers;
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
        }
        
    }

thats my code for searching based on the transfer type, is it juts a case of saying if trasnfer type is selected and amount is selected then do this

if all 3 are selected then do this?

is there a quicker or tidier way of doing these types of searches?

I think you could find your answer on these thread: https://stackoverflow.com/questions/13276602/c-sharp-using-parameters-addwithvalue-in-sqldataadapter/13276651 — Daniel Bistuer, Mar 19 '21 at 09:56
I would also read [this](https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) before you start using `AddWithValue` — GuidoG, Mar 19 '21 at 12:34

GuidoG · Accepted Answer · 2021-03-19T13:14:24.343

Well you could start with putting your logic on another place, so you don't need so much code for your selection

private void searches()
    {
        // search using the parameters selected
        using (SQLiteCommand cmd = conn.CreateCommand())
        {
            try
            {
                cmd.CommandText = "SELECT * FROM tranx WHERE accid = @accid ";
                cmd.Parameters.Add(new SqlParameter("@accid", SqlDbType.Int) { Value = Global.selectedAccountID });

                if (cb_Transfer_Type.SelectedIndex < 5)
                {
                   cmd.CommandText += 'AND Global.selectedAccountID = @selectedAccountID';
                   cmd.Parameters.Add(new SqlParameter("@selectedAccountID", SqlDbType.Int) { Value cb_Transfer_Type.SelectedItem });
                }

                da_Transfers = new SQLiteDataAdapter(cmd.CommandText, conn);
                dt_Transfers = new DataTable();
                da_Transfers.Fill(dt_Transfers);
                dgv_Transfers.DataSource = dt_Transfers;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
    }

I don't know SQLiteCommand, so maybe you will have to replace SqlParameter and SqlDbType by the corresponding types for SQLite

SQLite search using user inputs

1 Answers1