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: stackoverflow.com/questions/5201383/… — Jens
– Jens, Commented Sep 1, 2016 at 5: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
– Zein Makki, Commented Sep 1, 2016 at 5:39
Possible duplicate: stackoverflow.com/questions/14952518/… — Zein Makki
– Zein Makki, Commented Sep 1, 2016 at 5: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
– Edwin Stoteler, Commented Sep 1, 2016 at 5:51

ZerosAndOnes · Accepted Answer · 2016-09-01 06:10:13Z

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

marc_s · Accepted Answer · 2016-09-11 15:58:58Z

0

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

edited Sep 11, 2016 at 15:58

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Sep 1, 2016 at 6:23

Zain Ul Abidin

2,7601 gold badge23 silver badges38 bronze badges

Collectives™ on Stack Overflow

Getting all records from database between two dates C#

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related