Sql DataReader Has Rows but Returns With Empty data

Question

I am using a query to select data from sql database. It has rows but data raader.Read() function returns false and rows are empty as I have checked in debugging

Code that i have been using is

public void getSale()
    {
        DB db = new DB();

        try
        {
            db.cmd.CommandText = "select * from Sale where date is not null and (date between '"+StartDate+"' and '"+EndDate+"') order by date";
            db.cmd.Connection = db.con;
            db.con.Open();

            if(db.con.State == System.Data.ConnectionState.Open)
            {
                db.dataReader = db.cmd.ExecuteReader();

                if(db.dataReader.HasRows)
                {
                    while(db.dataReader.Read())
                    {
                        SaleModel sm = new SaleModel();
                        sm.SaleId = long.Parse(db.dataReader["Id"].ToString());
                        sm.UserName = db.dataReader["UserName"].ToString();
                        sm.ItemsQuantity = int.Parse(db.dataReader["ItemsQuantity"].ToString());
                        sm.TotalAmount = double.Parse(db.dataReader["TotalAmount"].ToString());
                        sm.SubTotal = double.Parse(db.dataReader["SubTotal"].ToString());
                        sm.Discount = double.Parse(db.dataReader["Discount"].ToString());
                        sm.Completed = bool.Parse(db.dataReader["Completed"].ToString());
                        sm.Date = DateTime.Parse(db.dataReader["Date"].ToString());
                        sm.CustomerPhone = long.Parse(db.dataReader["CustomerPhone"].ToString());

                        SalesList.Add(sm);
                    }

                    db.con.Close();
                }
            }
        }
        catch(Exception ex)
        {
            MessageBox.Show(ex.Message, "Exception", MessageBoxButton.OK, MessageBoxImage.Error, MessageBoxResult.OK);
        }
    }

And When I tested this query on Query editor in Visual studio rows were returned

If Anyone can help?

Answer 1

This is a much better way to structure you code, and there's a good chance it will fix your issue, too:

//accept the date values as parameter arguments, return the result. 
//  Do NOT mess about with variables at the global or class scope.
public IEnumerable<SalesModel> getSale(DateTime StartDate, DateTime EndDate)
{
    string sql = "select * from Sale where date is not null and (date between @StartDate and @EndDate) order by date";

    //DON'T abstract SqlCommand/SqlConnection. DO abstract your connection string.
    //Also, don't bother with the try/catch at this level. You can't really do anything with it here, so worry about the exception in calling code.
    using (var cn = new SqlConnection(DB.ConnectionString))
    using (var cmd = new SqlCommand(sql, cn))
    {
        cmd.Parameters.Add("@StartDate", SqlDbType.DateTime).Value = StartDate
        cmd.Parameters.Add("@EndDate", SqlDbType.DateTime).Value = EndDate
        cn.Open();

        using (SqlDataReader rdr = cmd.ExecuteReader())
        {
            while(rdr.Read())
            {
                var sm = new SaleModel();
                //If you have good schema design, these values are **already** in the correct type. 
                // The old code forces an expensive conversion to string, following by an expensive parse back to the type it already had.
                // We can do MUCH better.

                sm.SaleId = (long)rdr["Id"];
                //but it is okay for types that are *already* strings
                sm.UserName = rdr["UserName"].ToString();
                sm.ItemsQuantity = (int)rdr["ItemsQuantity"];
                sm.TotalAmount = (double)rdr["TotalAmount"]);
                sm.SubTotal = (double)rdr["SubTotal"];
                sm.Discount = (double)rdr["Discount"];
                sm.Completed = (bool)rdr["Completed"];
                sm.Date = (DateTime)rdr["Date"];
                sm.CustomerPhone = (long).rdr["CustomerPhone"];

                yield return sm;
            }
        }
    }
}

Here it is again without all the extra comments. The point here is this is still less code than the original that used string concatenation, and it took less than 10 minutes to write. Good code doesn't necessarily take longer.

public IEnumerable<SalesModel> getSale(DateTime StartDate, DateTime EndDate)
{
    string sql = "select * from Sale where date is not null and (date between @StartDate and @EndDate) order by date";

    using (var cn = new SqlConnection(DB.ConnectionString))
    using (var cmd = new SqlCommand(sql, cn))
    {
        cmd.Parameters.Add("@StartDate", SqlDbType.DateTime).Value = StartDate
        cmd.Parameters.Add("@EndDate", SqlDbType.DateTime).Value = EndDate
        cn.Open();

        using (SqlDataReader rdr = cmd.ExecuteReader())
        {
            while(rdr.Read())
            {
                var sm = new SaleModel();
                sm.SaleId = (long)rdr["Id"];
                sm.UserName = rdr["UserName"].ToString();
                sm.ItemsQuantity = (int)rdr["ItemsQuantity"];
                sm.TotalAmount = (double)rdr["TotalAmount"]);
                sm.SubTotal = (double)rdr["SubTotal"];
                sm.Discount = (double)rdr["Discount"];
                sm.Completed = (bool)rdr["Completed"];
                sm.Date = (DateTime)rdr["Date"];
                sm.CustomerPhone = (long).rdr["CustomerPhone"];

                yield return sm;
            }
        }
    }
}

Note that I return an IEnumerable rather than a List. If you really need a List (tip: you probably don't, and sticking with IEnumerable is faster), you can just call ToList() on the result.

Answer 2

Why you concatenate strings to build your sql query? NEVER do that. It is a source for sql-injection and can cause issues like this. Instead use parameterized queries.

Also don't use SqlConnection wrappers like your DB class. That can cause several other issues. Instead create, open, close and dispose them where you need them, best by using the using-statament. The connection-pooling will manage the rest for you.

public List<SaleModel> GetSale(DateTime startDate, DateTime endDate)
{
    string sql = @"select * from Sale 
                   where date is not null 
                   and date between @StartDate and @EndDate 
                   order by date";

    var salesList = new List<SaleModel>();

    try
    {
        using (var con = new SqlConnection("insert your connection string"))
        using (var cmd = new SqlCommand(sql, con))
        {
            cmd.Parameters.Add("@StartDate", SqlDbType.DateTime).Value = startDate;
            cmd.Parameters.Add("@EndDate", SqlDbType.DateTime).Value = endDate;
            con.Open();
            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    SaleModel sm = new SaleModel();
                    sm.SaleId = long.Parse(reader["Id"].ToString());
                    sm.UserName = reader["UserName"].ToString();
                    sm.ItemsQuantity = int.Parse(reader["ItemsQuantity"].ToString());
                    sm.TotalAmount = double.Parse(reader["TotalAmount"].ToString());
                    sm.SubTotal = double.Parse(reader["SubTotal"].ToString());
                    sm.Discount = double.Parse(reader["Discount"].ToString());
                    sm.Completed = bool.Parse(reader["Completed"].ToString());
                    sm.Date = DateTime.Parse(reader["Date"].ToString());
                    sm.CustomerPhone = long.Parse(reader["CustomerPhone"].ToString());

                    salesList.Add(sm);
                }
            }
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, "Exception", MessageBoxButton.OK, MessageBoxImage.Error, MessageBoxResult.OK);
    }

    return salesList;
}

I'm pretty sure that this works(f.e. can be a localization issue).

Side-note: a method GetSale should return a List<SaleModel> but not fill one. You should also pass the parameters as DateTime to the method. I've changed it in my code sample.

Answer 3

don't see any issue except that you are using date which is a reserve word and not your actual column name. Change your query to be

db.cmd.CommandText = "select * from Sale where [date] is not null and ([date] between '"+StartDate+"' and '"+EndDate+"') order by [date]";