0

I have a two page website - one page shows a list of documents, the other to edit the properties of that document.

Now if I edit the properties of a document, save the changes I can see the data is changed in the database. But when I reload the list of documents - the list includes the old data. Hit refresh and the modified data is displayed.

If I trace through the changes it really looks like the old data is being returned.

I have tried passing the number of ticks in the URL to make IE load a new version - but it still shows the old version of the data. Even if I refresh the page and it changes - show the same documents details and the old version of the data is shown, again refresh and it is updated.

I have checked the data is being changed in the underlying database.

So taking the following call

          using (nhs_patient_document_s documents = new nhs_patient_document_s(Properties.Settings.Default.DataConnectionEDM))
            {
                List<nhs_patient_document> found = documents.Select<nhs_patient_document>("nhs_patdoc_patientid", String.IsNullOrEmpty(criteria) ? String.Empty : criteria);

                output = JsonConvert.SerializeObject(found);
            }

Ultimately this is the code which executes the query

    public List<T> Select<T>(String Field, String Value)
    {
        List<T> results = null;
        String sql = String.Empty;

        try
        {
            sql = (String.Format("select * From {0} Where {1} = '{2}'", TableName, Field, Value.Replace("'", "''")));
            results = Execute<T>(sql, CommandType.Text);
        }
        catch (System.Exception e)
        {
             throw e;
        }
        finally
        {
            sql = String.Empty;
        }

        return results;
    }

The execute function called above is as follows;

    protected List<T> Execute<T>(String CommandString, CommandType CommandType)
    {
        DataSet dataSet             = null;
        SqlConnection connection    = null;
        SqlCommand command          = null;
        SqlDataAdapter dataAdapter  = null;

        List<T> results             = null;

        try
        {
            connection          = new SqlConnection(connectionString);

            command             = new SqlCommand(CommandString, connection);
            command.CommandType = CommandType;
            command.CommandTimeout = 0;

            if (connection.State != ConnectionState.Open) connection.Open();

            dataSet     = new DataSet();
            dataAdapter = new SqlDataAdapter(command);
            dataAdapter.Fill(dataSet);

            results     = ExtractData<T>(dataSet);
        }
        catch ( System.Exception e)
        {
            throw e;
        }

        return results;

    }

Any ideas how I can force it to get new data from the server.

PS Thanks for the suggestions of other frameworks but I only had a very short time to write this and no time to learn them.

Paul S Chapman
  • 832
  • 10
  • 37
  • Is it being cached by the browser? – stuartd Jan 06 '17 at 16:13
  • 1. don't do `catch(...) { throw e; }` - it redefines the origin of the exception, and `finally { sql = String.Empty; }` - it is useless. 2. Close connection as soon as possible. 3. Use parameterized queries. – Igor Jan 06 '17 at 16:18
  • Might be, but that is why I added an extra parameter to the page which made up the url so that was different when you returned to the page. Certainly hitting refresh on the browser brings up the updated data. – Paul S Chapman Jan 06 '17 at 16:19
  • 2
    This code is quite scary. It appears to be wide open to sql injection. Select * is bad but a generic method to retrieve all data from any data is just a really bad idea. This is like creating a dotnet method that can do anything to any object based on the object being passed in. – Sean Lange Jan 06 '17 at 16:19
  • How do you call this: `public List Select(String Field, String Value)` and from where and how do you use the result it returns? – CodingYoshi Jan 06 '17 at 16:20
  • Do you have a caching layer ? The first time you request the documents you make a roundtrip to the database to fetch them and then your service them to the client. The second time you request the documents (F5), without having changed anything, you make again a roundtrip to the database or you read them from a cache? – Christos Jan 06 '17 at 16:22
  • 1
    As far as the sql goes, I have the same concerns as others here. Check out dapper, it is FANTASTIC https://github.com/StackExchange/dapper-dot-net As far as the caching goes, if it is broswer caching, look here for some tips http://stackoverflow.com/questions/1922910/force-browser-to-clear-cache – nurdyguy Jan 06 '17 at 16:22
  • c# is not the language I use the most and am most comfortable, but the first thing I would do is transform your code to make use of the "using" statement. I think after you do that, it will work as you expect. Leveraging the "using" statement you end up with your transactions being committed and objects closed, etc. Without the "using" statement and blocks, I don't think that is happening and you have to do a little more work to make your transaction work.... or let the "using" statements handle that for you. https://www.dotnetperls.com/sqldataadapter – Thomas Carlisle Jan 06 '17 at 16:24
  • I second Dapper. Also, you have exposed yourself to major sql injection vulnerabilities – maccettura Jan 06 '17 at 16:29
  • Man so many attacks on the code you posted!!... but sadly its true, your app can be easily break. Well, returning to the real subject, I do not see the caching... are you sure you are not missing some code to post? seems that caching could be on another layer, or maybe you are using `session` data, or maybe your client is using `localstorage`. Just do a deeper review and post the missing piece. – David Espino Jan 06 '17 at 17:20

1 Answers1

0

Found it and the problem was not a cache in SQL or C# but in the way jquery makes an ajax call. Basically jquery was caching the result

I need to add

cache: false

to the ajax call

$.ajax({
    url: 'PatientGetdocuments.ashx?patientid=' + patientid,
    cache: false,
    success: function (result) {
.
.
.

        });

    }

});

Thanks for the comments showing how open it is to SQL injection but I had a short deadline of today to get it done - will consider changing that part of the code to avoid that issue when I can.

Paul S Chapman
  • 832
  • 10
  • 37