Getting all records from database between two dates C#

Question

I have a date column in my database. I have two datetimepickers( past and present) three radio buttons called, retailer, sub dealer and dealer. I want to display all records between the two dates in my datagrid. But first I will set the two dates and pick one radio button and click the search button. I have solved my problem in radiobuttons. I just put "OR" in my query so that it will still even if m problem in getting date between two dates wont. I didn't use datime because I used varchar as the datatype for date in my database. I cant changed it to datetime because thats what my teacher has given me.

Here's my code. Thank u very much.

public static List<Retailer> GetDataByType(string type, string past, string present)
    {
        List<Retailer> data = new List<Retailer>();

        MySqlConnection con = DBConnection.ConnectDatabase();
        try
        {   // AND
            MySqlCommand cmd = new MySqlCommand("SELECT * FROM " + tablename + " WHERE date BETWEEN '" + past + "' AND '" + present + "'" + "'  OR type LIKE '%" + type + "%'", con);
            MySqlDataReader reader = cmd.ExecuteReader();

            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    Retailer rawData = new Retailer();
                    rawData.Date = reader.GetString(0);
                    rawData.Walletid = reader.GetString(1);
                    rawData.Fname = reader.GetString(2);
                    rawData.Lname = reader.GetString(3);
                    rawData.Birthdate = reader.GetString(4);
                    rawData.Address = reader.GetString(5);
                    rawData.Province = reader.GetString(6);
                    rawData.City = reader.GetString(7);
                    rawData.Balance = reader.GetDouble(8);
                    rawData.Frozen = reader.GetDouble(9);
                    rawData.Sponsor_id = reader.GetString(10);
                    rawData.Share = reader.GetDecimal(11);
                    rawData.Email = reader.GetString(12);
                    rawData.Password = reader.GetString(13);
                    rawData.Type = reader.GetInt32(14);
                    data.Add(rawData);

                    MessageBox.Show(rawData.Date);
                }
            }
            reader.Close();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
        finally
        {
            con.Close();
        }

        return data;
    }
}

Answer 1

Since your date column is a varchar, you will have to use str_to_date. I would suggest you to use Parameters where the string in the query comes from the user, even if you aren't asked to, as this would save you from sql injections.

MySqlCommand cmd = new MySqlCommand("SELECT * FROM " + tablename + " WHERE  STR_TO_DATE(`date`,  '%Y/%m/%d')  BETWEEN STR_TO_DATE(@pastvalue,  '%Y/%m/%d') AND STR_TO_DATE(@presentvalue,  '%Y/%m/%d')  OR type LIKE '%" + type + "%'", con);
command.Parameters.AddWithValue("@pastvalue", past);
command.Parameters.AddWithValue("@presentvalue", present);
MySqlDataReader reader = cmd.ExecuteReader();

I am assuming that the dates are stored in the following format 2016/09/01. If the dates are in another fromat then change the formatting of str_to_date respectively.

Without Parameters the query would look like

MySqlCommand cmd = new MySqlCommand("SELECT * FROM " + tablename + " WHERE STR_TO_DATE(`date`,  '%Y/%m/%d') BETWEEN STR_TO_DATE(past,  '%Y/%m/%d') AND STR_TO_DATE(present,  '%Y/%m/%d')" + "'  OR type LIKE '%" + type + "%'", con);