none
ExecuteReader does not return query RRS feed

  • Question

  • Hi

    I'm not a frequent visitor to this forums, so please forgive me if this is not the correct place.

     

    I am using sql express 2005 and I'm accessing it with a c# app using sqlclient and at the same time monitoring it via sql tables through a asp.net website that is also connected to the same database.

     

    The problem I have is that when I execute a certain query from the sql frontend it returns perfectly, but when I execute the same query verbatim in c# it does not return -> it keeps returning blanks for a while and then suddenly it returns the result (the delay is random).

     

    My first guess is that it could be a db logging issue - in that the updates are still in queue - but I don't know why it is available in the frontend, and I can't understand why that would be because I have not executed any giant queries, and the database is less than 3 mb in size.

     

    I have a couple of years experience with sql programming and would classify be as at least half an expert, but I have to admit that I am completely stumped as I have been double checking this for hours and I know that there must be something I am missing that is outside the scope of my frame of thinking.

     

    Does anyone here know how to solve this - I would really appreciate any help on this.

    Friday, March 28, 2008 4:01 PM

All replies

  • Please post your query and C# code to run the query. What do you mean "blanks"?

     

    This is the sample c# code to run the query:

    Code Snippet

    SqlConnection con = new SqlConnection("Data Source=Van-3005-VM2;Integrated Security=SSPI;Initial Catalog=Test");

    con.Open();

    SqlCommand cmd = new SqlCommand("SELECT * FROM testDecimal", con);

    SqlDataReader reader = cmd.ExecuteReader();

    while (reader.Read())

    {

    Console.WriteLine(reader.GetSqlDecimal(0));

    }

    reader.Close();

     

    Friday, March 28, 2008 6:09 PM
  • Thanks for the reply - but I know for a fact the problem is not with the code (apologies if it sounds arrogant, but I have been doing this for quite a while), as I have been using the code countless other times in the past, and have been testing the app for hours now with it working 90% of the time.  I also know that the sql isn't clashing directly with other queries/readers because I get no exceptions thrown.

     

    This has to be some kind of external issue - because the problem occurs between the call to ExecuteReader() and it's return (basically between the time the execution leaves my code and comes back) and I get no errors.  So to the code/environment everything seems to be normal.  That's why I think it may be something to do with the database logging, config, or some kind of limitation (although I have checked the sql express limitations, and none of them sticks out as a problem) that is not very common.  I am also inclined to think it has something to do with the app and the website working from the same database at the same time.

     

    What I mean with blanks is:

     

    If I execute the query (just a very basic query like 'select x from table where value=true') in sql MSE I get a couple of records.  But if I execute the same query in the code I get no records.  My app executes this code every 15 seconds to decide on what action to follow next but what I get is that the app executes the query but never gets any records, so it doesn't enter the reading loop.  Then after a while, when I do something else like, reading the table, or closing the web env, or opening sql, or closing sql, or anything (in no particular order, and I also cannot isolate which action works as it doesn't help all the time) the app suddenly resumes perfectly. 

     

    Crazy right?

    Friday, March 28, 2008 7:07 PM
  • I'm honestly not very familiar with the Express version and the tools it contains, but does it contain the SQL Server Profiler?

     

    http://msdn2.microsoft.com/en-us/library/ms181091.aspx

     

    If so, that may give you a few clues as to where you're getting hung up. The link should provide some instructions for how to use it.

    Friday, March 28, 2008 10:13 PM
  • I am also hitting the same issue.

    When I execute my code against the sqlexpress through .Net code, it does not return anythine. Whereas the same query when executed in Management studio works fine. Did you find anything?

     

    Friday, April 4, 2008 6:46 PM
  • I have not figured it out yet.  What is your scenario.  Maybe there are some similarities.

     

    Monday, April 7, 2008 6:34 PM