locked
MySql Connection not returning all records RRS feed

  • Question

  • User-683967333 posted

    All, I have a method to pull data from a stored procedure in a database and the method does return some records, but only a fraction of them.

    If I run the procedure in the database it returns almost 200,000 records.

    If I run it in an application using the method below, it only returns 700 records.

    public DataTable getMySqlTimeStoneExport(int i, DateTime s, DateTime e)
            {
                DataTable ae = new DataTable();
                MySqlCommand cmd = new MySqlCommand("TimestoneAllOrdersMonthly", getMyTimeStoneConnection(i));
                
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandTimeout = 820;
    
                cmd.Parameters.AddWithValue("@StartDate", s);
                cmd.Parameters.AddWithValue("@EndDate", e);
    
                cmd.Connection.Open();
    
                MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                ae.Load(dr);
    
                return ae;
            }

    Wednesday, December 19, 2018 4:57 PM

All replies

  • User475983607 posted

    All, I have a method to pull data from a stored procedure in a database and the method does return some records, but only a fraction of them.

    If I run the procedure in the database it returns almost 200,000 records.

    If I run it in an application using the method below, it only returns 700 records.

    public DataTable getMySqlTimeStoneExport(int i, DateTime s, DateTime e)
            {
                DataTable ae = new DataTable();
                MySqlCommand cmd = new MySqlCommand("TimestoneAllOrdersMonthly", getMyTimeStoneConnection(i));
                
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandTimeout = 820;
    
                cmd.Parameters.AddWithValue("@StartDate", s);
                cmd.Parameters.AddWithValue("@EndDate", e);
    
                cmd.Connection.Open();
    
                MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                ae.Load(dr);
    
                return ae;
            }

    Sounds like unexpected input or an unexpected connection.  Set a break point and verify the dates and connection.

    Wednesday, December 19, 2018 5:19 PM
  • User-683967333 posted

    I have tried that and I get no errors. I am passing in a start and end date. And it returns 700+ records. but, that is only a fraction of the records I should be getting?

    Is there any limitations to the connection, as far as how many records can be returned?

    Wednesday, December 19, 2018 5:28 PM
  • User475983607 posted

    dpimental2018

    I have tried that and I get no errors. I am passing in a start and end date. And it returns 700+ records. but, that is only a fraction of the records I should be getting?

    There are 3 input parameters; a connection and two dates.   The most likely cause are the inputs do not match the test that produces 200,000 records.   Are you sure that you are connecting to right DB?  Have you verified the dates?  Keep in mind that we can see only the tiny bit you've posted...

    dpimental2018

    Is there any limitations to the connection, as far as how many records can be returned?

    A connection does not  know what makes up a record or how many records to return.  

    On a side note, always close a DataReader.  Please see the reference documentation for proper syntax.

    https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/retrieving-data-using-a-datareader

    Wednesday, December 19, 2018 5:40 PM
  • User-683967333 posted

    Here is what I do in order to understand what difference, if any there is to running the stored procedure in the database, as compared to running it in an application.

    To run it in the database, I use Toad for MySQL and I right click the stored procedure and select "call code". It then prompts me for the two date parameters which I select using the built in date selectors. After I click ok, it runs the procedure with the dates that I select.

    To run the procedure in the application, I pass the start and end date to the method, along with an integer representing the connection it needs to make. The method gets the connection by way of the integer, then it runs the code in the method that I posted.

    Wednesday, December 19, 2018 5:46 PM
  • User475983607 posted

    Still sounds like issue with the input parameters.  This is an ASP.NET form and it is very common for culture date representations to cause issues.  In the US, dates are formatted as mm/dd/yyyy.  In other locations dates are formatted as dd/mm/yyyy.  This can cause issues when parsing the request from an HTML web form.  Have you placed a break point and verified the date is correct?  Have you verified the DB connection is correct?

    Keep in mind that we can only see the code you've posted.  

    Wednesday, December 19, 2018 6:13 PM
  • User-893317190 posted

    Hi dpimental2018,

    I have made a test and mine could return all the value.

    Below is my procedure

    DELIMITER //
    CREATE PROCEDURE myPro(IN startdate DATETIME,IN enddate DATETIME)
    
    BEGIN
         SELECT * FROM mydate WHERE  tdate BETWEEN STARTdate AND enddate;
       
    
    END //
    DELIMITER ;

    After I execute the sql below.

    SELECT COUNT(*) FROM mydate WHERE tdate BETWEEN '1994-01-01' AND '1995-01-01'

    It shows

    My  code.

     private static string constr = ConfigurationManager.ConnectionStrings["mySqlConnectionString"].ConnectionString;
            protected void Page_Load(object sender, EventArgs e)
            {
    
                MySqlConnection con = new MySqlConnection(constr);
                MySqlCommand com = new MySqlCommand("myPro", con);
    
                try
                {
                    DataTable ae = new DataTable();
    
                    com.CommandType = CommandType.StoredProcedure;
                    con.Open();
    
                    com.Parameters.AddWithValue("STARTdate",new DateTime(1994,1,1));
    
                    com.Parameters.AddWithValue("enddate",new DateTime(1995,1,1));
                    MySqlDataReader dr= com.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
                    ae.Load(dr);
                    Response.Write(ae.Rows.Count);
    
                }
                catch (Exception)
                {
                    con.Close();
                    con.Dispose();
    
                    throw;
                }
            }

    The result.

    As mgebhard  has said, it may caused my the input of your datetime.

    Please ensue the datetime correctly represents the datetime you want.

    You could use DateTime.ParseExect to sepcify the format of your datetime string.

                        //datetime string   // format string    //normally write  CultureInfo.InvariantCultur
    DateTime.ParseExact("12/09/1994", "dd/MM/yyyy", CultureInfo.InvariantCulture)

    https://docs.microsoft.com/en-us/dotnet/api/system.datetime.parseexact?view=netframework-4.7.2

    Best regards,

    Ackerly Xu

    Thursday, December 20, 2018 5:24 AM
  • User-1320437544 posted

    Hello,

    How much time takes to execute your query and return the 700 records? Could it be timeout?

    Saturday, December 22, 2018 11:20 PM