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;
    }
}

convert the string date to a date: http://stackoverflow.com/questions/5201383/how-to-convert-a-string-to-date-in-mysql — Jens, Sep 01 '16 at 05:35
The format of `past` and `present` is the key point here. You need to have them be in the default format of MySQL. However, the optimal solution is to use Parameters and specify the type to be DateTime. — Zein Makki, Sep 01 '16 at 05:39
Possible duplicate: http://stackoverflow.com/questions/14952518/mysql-date-time-format-using-c-sharp — Zein Makki, Sep 01 '16 at 05:40
Note that if one of the string parameters can be input in by a user, this code has a security leak when it comes to SQL injection. — Edwin Stoteler, Sep 01 '16 at 05:51

score 1 · Answer 1 · answered Sep 01 '16 at 06:10

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);

score 0 · Answer 2 · edited Sep 11 '16 at 15:58

If you can't change the type of date column to Datetime then your query will never work you have to manually implement your own date manipulation code assuming you have same date and time formats to compare in your query fetch all record regardless what date is then try following code

FilterData(ref rawData);

and the function definition is following

private void FilterData(ref List<Retailer> data)
{
   for(int i = 0 ; i < data.Count ; i++)
   {
     Retailer d = data[i];
     DateTime present = DateTime.parse(present);
     DateTime past = DateTime.parse(past);
     DateTime dt = DateTime.parse(d.Date);

     if(!(dt >= past && dt < present))
       data.RemoveAt(i);  //remove record it is not between present and past
  }
}

Getting all records from database between two dates C#

2 Answers2